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 extension 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 100%