PostgreSQL/Features
Appearance
An outstanding Goal
[edit | edit source]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
[edit | edit source]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
[edit | edit source]Security
[edit | edit source]- Authentication methods: SCRAM-SHA-256, GSSAPI, SSPI, LDAP, RADIUS, Certificate, PAM
- Roles (users and groups) authorize access to data and execution of functions
Reliability
[edit | edit source]- 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
[edit | edit source]- 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
[edit | edit source]- 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
[edit | edit source]- 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