PostgreSQL comes with many features aimed to help developers build applications, administrators to protect data integrity and build fault-tolerant environments, and help you manage your data no matter how big or small the dataset. In addition to being free and open source, PostgreSQL is highly extensible. For example, you can define your own data types, build out custom functions, even write code from different programming languages without recompiling your database!
PostgreSQL tries to conform with the SQL standard where such conformance does not contradict traditional features or could lead to poor architectural decisions. Many of the features required by the SQL standard are supported, though sometimes with slightly differing syntax or function. Further moves towards conformance can be expected over time. As of the version 16 release in September 2023, PostgreSQL conforms to at least 170 of the 179 mandatory features for SQL:2023 Core conformance. As of this writing, no relational database meets full conformance with this standard.
Below is an inexhaustive list of various features found in PostgreSQL, with more being added in every major release:
- Data TypesPrimitives: Integer, Numeric, String, BooleanStructured: Date/Time, Array, Range / Multirange, UUIDDocument: JSON/JSONB, XML, Key-value (Hstore)Geometry: Point, Line, Circle, PolygonCustomizations: Composite, Custom Types
- Data IntegrityUNIQUE, NOT NULLPrimary KeysForeign KeysExclusion ConstraintsExplicit Locks, Advisory Locks
- Concurrency, PerformanceIndexing: B-tree, Multicolumn, Expressions, PartialAdvanced Indexing: GiST, SP-Gist, KNN Gist, GIN, BRIN, Covering indexes, Bloom filtersSophisticated query planner / optimizer, index-only scans, multicolumn statisticsTransactions, Nested Transactions (via savepoints)Multi-Version concurrency Control (MVCC)Parallelization of read queries and building B-tree indexesTable partitioningAll transaction isolation levels defined in the SQL standard, including SerializableJust-in-time (JIT) compilation of expressions
- Reliability, Disaster RecoveryWrite-ahead Logging (WAL)Replication: Asynchronous, Synchronous, LogicalPoint-in-time-recovery (PITR), active standbysTablespaces
- SecurityAuthentication: GSSAPI, SSPI, LDAP, SCRAM-SHA-256, Certificate, and moreRobust access-control systemColumn and row-level securityMulti-factor authentication with certificates and an additional method
- ExtensibilityStored functions and proceduresProcedural Languages: PL/pgSQL, Perl, Python, and Tcl. There are other languages available through extensions, e.g. Java, JavaScript (V8), R, Lua, and RustSQL/JSON constructors and path expressionsForeign data wrappers: connect to other databases or streams with a standard SQL interfaceCustomizable storage interface for tablesMany extensions that provide additional functionality, including PostGIS
