Basic introduction of Postgres Database

Recently, I have been working on a project related to PostgreSQL database, documenting the knowledge that I have learned in this process. About PostgreSQL database online already have too many relevant introduction, in order to blog the system or first look at the Wikipedia on the PostgreSQL database introduction.

PostgreSQL is a free object-relational database server (database management system), issued under a flexible bsd--style license. It provides a choice for users in addition to other open source database systems such as MySQL and Firebird, and proprietary systems such as Oracle, Sybase, IBM DB2, and Microsoft SQL Server.

PostgreSQL's unusual name causes some readers to stop and try to spell it, especially those who pronounce SQL "sequel". The PostgreSQL developer spelt it "post-gress-q-l". (Audio sample,5.6k MP3). It is also often abbreviated as "Postgres".

PostgreSQL has evolved over a long period of time, starting with the Ingres program in UC Berkeley. Michael Stonebraker, the leader of the program, left Berkeley to commercialize Ingres in 1982, but eventually returned to academia. After returning to Berkeley in 1985, Stonebraker began the Post-ingres program to focus on the problems of contemporary database systems that became increasingly clear in the early 1980. The code base for Postgres and Ingres starts (and remains) completely detached.

The purpose of the new project Postgres is to fully support the type by increasing the minimum required functionality. These features include the type definition and the ability to fully describe the connection. The ability to fully describe the contact is widely used but needs to be maintained by the user. The Postgres database is able to "understand" the contact and can use rules to retrieve information in the related tables in a natural way.

Starting from 1986, the project team published a number of papers describing the fundamentals of the system, and in 1988 the plan was completed and a prototype version was run. The project team issued version 1 to a handful of users in June 1989, followed by Release 2 with the rewritten rule system in June 1990. Version 3 of 1991 re-rewritten the rule system and added support for multiple storage managers and improved query engines. A large number of users existed in the 1993 and began to drown out the plan with demands for support and features. After the release of the main as final cleanup version 4 the plan was terminated.

Despite the formal termination of the Postgres program, the BSD license (Berkeley under its postgres) does allow open source developers to obtain replicas and further develop the system. In 1994, two UC Berkeley University graduate students, Andrew Yu and Jolly Chen, added a SQL language interpreter to replace the earlier Ingres-based quel system, creating Postgres95. The code was then released to the Web to find its own way out of the world. In the year 1996 the plan was renamed: to reflect the new SQL query language of the database, Postgres95 became PostgreSQL.

The first PostgreSQL release formed version 6.0. A group of database developers and volunteers from around the world then collaborated on the Internet to maintain the software. Since version 6.0, there have been a lot of follow-up releases, and there have been many improvements in the system, and on January 19, 2005, version 8.0 became the current release. After 8.0, PostgreSQL is executed in native (Native) mode in Windows windowing system.

Although the license allows Postgres to be commercialized, the Postgres code has not been commercially developed as fast as Ingres-it is surprising to consider the benefits Postgres offers. The main branch originated from Paula Hawthorn (a member of the original Ingres project team transferred from Ingres) and Michael Stonebraker to build Illustra information technology company to commercialize Postgres.

In January 2005, PostgreSQL received its first aid from a database vendor. Pervasive software ([1]) announced business support and community involvement.

In 2000, former Red Hat investors formed a company called Great Bridge to commercialize PostgreSQL to compete with other commercial database vendors. Great Bridge has funded several PostgreSQL developers and contributed a lot of resources to the community. By the end of 2001, however, great Bridge terminated its operations because it was like red Hat ... And so the company is facing a tough competition, and the market is in poor condition.

In 2001, Command Prompt, Inc. released Mammoth PostgreSQL, the most established PostgreSQL business package. They are sponsored by developers and to the like Pl/perl, pl/php, maintenance PostgreSQL Build Farm ... and other ways to support the PostgreSQL community.

In January 2005, PostgreSQL received support from another database vendor, Pervasive software, who is known for Btrieve products that are very common to Novell NetWare platforms. They announced their business support and their involvement in the community. After they had been successful for some time in July 2006, pervasive software left the PostgreSQL support market.

In 2005 years, two other companies announced the commercialization of PostgreSQL, respectively, into different niche markets. Enterprisedb announces that it will focus on enabling applications that use Oracle to run more easily on PostgreSQL. Greenplum is dedicated to the applications that contribute to data warehousing and business intelligence, especially the Bizgres project.

"We are not going to OEM Microsoft products, we are looking at PostgreSQL," said John Loiacono, vice president of the Software division of Sun Yang in October 2005, although no specifications were released. By November 2005, the Sun announced that it would support PostgreSQL. In June 2006, Solaris 10 includes PostgreSQL publishing together.

As for the PostgreSQL project itself, he continues to release a major version of the year, as well as minor debug releases, all of which can be obtained under the BSD license. These are based on commercial vendors, support companies, and open source hackers.

Describe

If you look at PostgreSQL in a cursory manner, you will find this database system very similar to other databases. Because PostgreSQL uses the SQL language to perform data queries. These data are linked by foreign keys and are present in a series of tables. The main advantage of PostgreSQL with respect to competitors is programmability: PostgreSQL makes development and use easier with the actual application of database data.

SQL data stores simple data types in a "flat table", requiring users to gather information about them using queries. This is in contrast to the way that applications and users take advantage of the data themselves: typically using high-level languages with rich data types, where all the relevant data is manipulated as a complete unit of its own. A typical salutation is a record or an object (according to the respective language).

Transforming information from the SQL world into an object-oriented programming world is difficult, since both have very different models of data organization. Industry calls this problem an object-relational mismatch: mapping from one model to another takes 40% of the project developer's time. Some mapping solutions, typically called object-relational mappings, are dedicated to this problem, but they cost a lot and have their own problems, leading to poor performance or forcing all data access to a language supported by the mapping.

PostgreSQL can solve many of these problems directly in the database. PostgreSQL allows users to define new types based on formal SQL types, allowing the database to understand complex data itself. For example, you can define an address to combine strings of things such as street numbers, cities, and countries. From this point you can easily create a table that contains all the fields needed to save the address in a single row and column.

PostgreSQL also allows types to include inheritance, which is the main concept in object-oriented programming. For example, you can define a Post_code type and then create Us_zip_code and Canadian_postal_code based on it. The address in the database can take the form of us_address or canadian_address, and specific rules can validate the data in their own case. In earlier versions of PostgreSQL, implementing new types required writing C extensions and compiling them into the database server; In version 7.4, creating and using custom types from create domain became easy.

The programming of the database itself can derive great benefits from using functions. Most SQL systems allow users to write stored procedures, which are a piece of SQL code that other SQL statements can invoke. But SQL itself is still not suitable as a programming language, and SQL users experience great difficulty in constructing complex logic. Worse, SQL itself does not support the most basic operations in many programming languages, such as branching and looping. Each vendor instead writes their own extensions to the SQL language to augment these features, which do not have to cross the database platform.

In PostgreSQL, programmers can write this logic in any of a sizable set of supported languages.

The built-in language, called Pl/pgsql, that resembles Oracle's process language, PL/SQL, provides unique advantages when dealing with query-intensive processes.

Popular scripting languages such as PERL,PYTHON,TCL, and Ruby wrappers, allow the power to take advantage of them in string processing and to connect to a vast external library of functions.

The high-performance process that requires complex logic to be compiled into machine code can take advantage of C or C + +.

In more esoteric ways, the processor of the R statistical language allows database queries to take advantage of its rich set of statistical functions.

A programmer can insert code into a server as a function, which is a small wrapper that makes the code resemble a stored procedure. In this way, the SQL code can invoke (for example) C code or vice versa.

Performance is improved because the database engine invokes all of the logic in one place at a time, reducing the number of round trips between the client and the server.

Reliability is enhanced because the data validation code is centralized in one place, on the server, without relying on the synchronization logic in multiple customer applications, which may even be written in a variety of programming languages.

By adding useful abstractions to the server, the customer code can become shorter and simpler.

These advantages combine to confirm that PostgreSQL is the most advanced database system from a programming standpoint. Using PostgreSQL can significantly reduce the overall programming time of many projects, and this advantage grows with the complexity of the project.

Characteristics

Function

Through the function, the command program can be executed on the database server side. Although such instructions can be written using basic SQL statements, the ability to write functions using other programming languages has been introduced in PostgreSQL due to their lack of functionality such as Process control, including:

A built-in process language named Pl/pgsql, similar to that of Oracle PL/SQL;

scripting language including PL/PERL,PL/PHP,PL/PYTHON,PL/RUBY,PL/SH,PL/TCL and pl/scheme;

Compiler language: c,c++, or Java (via Pl/java).

R Statistical Language (PL/R).

The language of the above section can even be executed within the trigger. PostgreSQL supports row return functions: Their output is a collection of data for a series of row types that can be used as tables in a query. Functions can also be defined to run as either the creator or the caller. In some cases, or in other database products, functions are also referred to as "stored procedures", but technically they are not too different.

Index

In PostgreSQL, the user can customize the index method, or use the built-in b-tree, hash table and GIST index. The Posrgresql indexing feature also has the following features:

Reverse index retrieval: An operation similar to the ORDER by field Desc can be achieved without an additional index.

Expression index: You can create an index that is based on an expression value rather than a numeric or column.

Partial index: Only the portion of the table is indexed, and you can create a smaller index by adding a WHERE clause to the CREATE INDEX statement port.

Bitmap Index Scanning: This feature is supported starting with version 8.1. This feature reads multiple indexes and generates a bitmap that represents a multivariate group intersection between them that conforms to the query criteria. This solves the problem of mixed indexes. In a table with 20 columns, you can theoretically create 20! Index, which is not practical in practical applications. With a bitmap index scan, each time it is queried, it will be able to make arbitrary permutations of the indexes of the columns involved in the constraints.

Trigger

A trigger is an event that is triggered by a SQL statement query. For example, an INSERT statement might trigger a trigger that checks the integrity of the data. Triggers are typically triggered by an INSERT or UPDATE statement.

In PostgreSQL, a trigger can be set on a datasheet, but cannot be set in a view (an update to the image or an insert operation can be defined using a rule). Multiple triggers can be executed sequentially in alphabetical order. In addition, the function of the trigger can be written in languages such as Pl/perl,pl/python, in addition to using the inline Pl/pgsql language.

Multi-version concurrency control

PostgreSQL uses a multi-version concurrency control (mvcc,multiversion concurrency controls) system for concurrency control, which provides each user with a "snapshot" of the database, each modification made within a transaction by the user, Not visible to other users until the transaction is successfully committed. This greatly reduces the reliance on read locks and ensures that the database is effectively compliant with the acid principle.

Rules

Rules allow a query to be rewritten and often used as a view to implement updatable views.

Data type

PostgreSQL has a rich data type built into it, including:

Values of arbitrary precision

Unlimited length text

Geometric entities

IP address and IPV6 address

Non-class inter-domain routing address block, MAC address

Array

In addition, users can create custom data types, often through PostgreSQL's gist mechanism, which can also be well indexed, such as the data types of PostGIS GIS.

User-defined objects

Users can define new types for almost all objects in the database, including:

Index

Operator (You can overload an existing operator.) ）

Aggregation functions

Data fields

Data type conversions

Session (Encoding conversion)

Inherited

The structure and properties of a data table can be inherited from a "parent" table, and the data is shared between the two. The insertion or deletion of data in a child table will also be reflected in the parent table, and changes made to the parent table, such as adding columns, will also cause the child table to change accordingly. This feature has not yet been fully implemented, and in fact, table constraints are not yet inherited. For example, inserting a record with a record ID data in a child table in a table that has an external reference to the parent table ID field causes a failure because PostgreSQL does not check the contents of the child table in a FOREIGN key constraint check on the parent table.

Other features

Relationship integrity constraints: Including foreign keys, column constraints, and row checking

View: Temporarily does not implement updatable views, but you can use the rule system to achieve the same functionality

Full, inner, outer (left/right) union query

Sub-query

Transaction processing

The main features that support most sql:2003 standards [2] and support for some future versions are not supported, and can be found in the version manual.

SSL Encrypted connection

Storage of binary/text large objects

Online backup

Data fields

Table partitioning

Table Space

Save Point

Time Point Recovery

Two-stage submission

TOAST (the Oversized-attribute Storage technique, the ultra-large attribute storage technology) transparently compresses large table properties (such as large MIME attachments or XML messages) and stores them in a separate area

Regular expression [3]

Database management and development tools

Navicat navigation cat for PostgreSQL is a powerful database management and development tool designed for PostgreSQL. It can be used with any version of the PostgreSQL database and supports most of the features of PostgreSQL, including triggers, indexes, views, and so on.

Phppgadmin Program for managing PostgreSQL database based on PHP language

Pgadmin another software for managing the PostgreSQL database

Extended

Geographic Data objects: PostGIS GPL

Full-Text Search: TSEARCH2 is embedded in version 8.3 via TSEARCH2 or openfts. Gpl

Multiple asynchronous master/slave replication scenarios, including SLONY-I (BSD authorization), Mammoth Replicator

XML/XSLT support for XPath extension GPL in contrib software package

Well-known users

. org domain name library [4]

The American Chemical Society

chicagocrime.org [5]

Skype

Penny Arcade

IMDB

Sony Online [6]

U.S. Department of Labor

Wisconsin Circuit Court Access with 6 * 180GB DBs replicated in real time

Openacs and. LRN

evergreen-an integrated library system with an open source (GNU General Public License)

Version

Postgresql-red Hat Edition[1] is a branch version made by Red Hat, also known as Red Hat Database.

See

relational database list

Object Database List

relational database comparison

Comparison of relational database management system

Comparison of object database management systems

Comments

^ postgresql-red Hat Edition Project

External links

PostgreSQL International Official website

PostgreSQL International Traditional Chinese community

PostgreSQL Simplified Chinese Community

PostgreSQL:: International Chinese Community Blog::

PostgreSQL documentation

SourceForge related projects on PostgreSQL

Pgfoundry-postgresql Related Projects website

Open Source Database Network

Database Journal's PostgreSQL column

PostgreSQL Chinese Language Promotion teaching Platform-mammoth

Database management System (DBMS) (view • discussion • Edit)
Concept Database Data Model · Database storage Structure · Relationship (Database) · Relationship Model · Distributed Database · ACID (Atomicity/consistency/isolation/durability) · Null value relational database · Relationship Model · Database Normalization · Database Integrity · Entity Integrity · Referential integrity · relational database management System Primary KEY · Foreign Key · Surrogate Keys · Super Key · Candidate Keys
Database Components Trigger View Database Tables · Indicators (Database) · Transaction log · Database Transactions · concurrency control · Optimistic lock • Pessimistic lock · Database index Storage Program · Database partitioning	Sql Category: Data Query Language (DQL)-Data definition language (DDL)-Data Manipulation Language (DML)-Data Control Language (DCL) directive: Select Insert Update Merge Delete Join Union Create Drop Begin work Commit Rollback Truncate Alter Security: SQL injection Attack · parameterized queries
Implementation of database management system
Implementation type relational database · File-based database · Deductive Dimension Database · Hierarchical structure · Graphics Database · Object Database · Object Relational Database · Temporal XML database
Database Products Object type (contrast) · Relationship type (contrast)	Database Components Data Query Language · Query Optimizer · Query Item · Embedded SQL ODBC JDBC OLE DB