PostgreSQL/Features

An outstanding Goal

The very first concept paper - published in 1986 - defines a goal that distinguishes PostgreSQL from many other systems until today: "provide user extendibility for data types, operators, and access methods". This goal is reached. And it is not only available for users, even the internal implementation utilizes those interfaces to create system components.

Architecture

PostgreSQL implements a client/server model. Each client process connects to one backend process at the server site. Such backend processes are part of the instance, a group of many processes which act closely together and handle the data access. PostgreSQL does not use threading in the backend processes or elsewhere.

Features

Security

• Authentication methods: SCRAM-SHA-256, GSSAPI, SSPI, LDAP, RADIUS, Certificate, PAM
• Roles (users and groups) authorize access to data and execution of functions

Reliability

• Transactions with full ACID support and diverse isolation levels
• Savepoints (Sub-transactions)
• Multi-Version Concurrency Control (MVCC)
• Point-in-Time Recovery
• Partitioning of tables and indexes
• Synchronous, asynchronous, and logical replication
• Bi-Directional replication
• Publish/subscribe mechanism
• Parallel execution of single queries at multiple CPUs

Application Aspects

• Rich set of predefined data-types, i.a. JSON
• Support for arrays
• Composite type (similar to a record in some programming languages), constructor for rows via row keyword
• Check constraints
• Referential integrity with foreign keys
• Table inheritance
• Views, materialized views, updateable views

Extendability

• User defined data-types, operators, and index access methods
• User-defined functions, procedures, triggers, and procedural languages
• Create extension interface to create user-defined packages. Some publicly available examples:
  • Foreign data wrappers to other - PostgreSQL or non-PostgreSQL - databases or to the file-system
  • PostGIS: an extention for Spatial and Geographic Objects
  • hstore: a key/value storage

SQL Support

• High degree of conformance to the SQL standard: 170 out of 177 features
• Outer join, union, intersect, except
• Group by, grouping set, cube, rollup
• Common table expressions (CTE)
• Recursive queries, graph queries
• Window functions, analytic functions