Introduction to PostgreSQL

PostgreSQL

PostgreSQL originated in the Department of Computing at the University of California, Berkeley, and was originally conceived in 1986 as the Berkley Postgres Project.In 1995, developers Andrew Yu and Jolly Chen added a SQL translator to Postgres and released it in the open source community, called Postgres95. In 1996, the developers again made major changes to Postgres95 and made it PostgresSQLVersion 6.0 is officially released.

PostgreSQL is a powerful open source object-relational database system that uses and extends the SQL language and incorporates many features to securely store and scale the most complex data workloads.It has earned a strong reputation for its proven architecture, reliability, data integrity, robust feature set, scalability, and the open source community's dedication to the software's continuous delivery performance and innovative solutions.PostgreSQL runs on all major operating systems, has been acid compatible since 2001, and has powerful plugins such as the popular PostGIS geospatial database extender.Since MySQL was acquired by Oracle, PostgreSQL has gradually become the open source relational database of choice.

Features

Data Type

• Basic types: Integer, Numeric, String, Boolean
• Structure types: Date/Time, Array, Range / Multirange, UUID
• Document Types: JSON/JSONB, XML, Key-value (Hstore)
• Geometry types: Point, Line, Circle, Polygon
• Custom Types: Composite, Custom Types

Integrity constraints

• Data unique constraint, not null constraint
• Primary key constraints
• Foreign key constraints
• Exclude constraints
• Explicit locking, Advisory locking

Concurrency, performance

• Index: B-tree, composite index, expression index, local index
• Advanced Indexing: GiST, SP-Gist, KNN GiST, GIN, BRIN, Covering Index, Bloom Filter
• Sophisticated query planner/optimizer, index scan only, multi-column statistics
• Transactions, Nested Transactions
• Multi-version concurrency control
• Parallel read query and build B-tree index
• Table partitioning
• All transaction isolation levels defined in the SQL standard, including Serializable
• Just-in-time (JIT) compilation of expressions

Reliability

• Write Ahead Log (WAL)
• Data replication (asynchronous, synchronous, logical): For Web applications, the characteristics of replication are very important. PostgreSQL can achieve synchronous, asynchronous, and semi-synchronous replication.PostgreSQL's replication is based on WAL and can achieve synchronous replication.At the same time, PostgreSQL also provides stream replication.
• point-in-time recovery
• Active Backup
• Tablespace

Security

• Authentication methods: GSSAPI, SSPI, LDAP, SCRAM-SHA-256, certificate, etc.
• Robust access control system
• Column and row level security
• Multi-factor authentication using certificates and additional methods

Scalability

• Stored Functions and Procedures

• Procedural languages: PL/PGSQL, Perl, Python (and more)

• SQL/JSON path expressions

• Foreign Data Wrapper: use standard SQL interface to connect to other databases or streams

• Customizable table storage interface

• Many extensions provide additional functionality, including PostGIS

Internationalization, text search

• Supports international character sets, such as sorting by ICU

• Case-insensitive and accent-insensitive collation

• Full Text Search