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

TOC

PostgreSQL
Features

Featured Platforms