What is PostgreSQL?

PostgreSQL, or just Postgres, is an open-source database management system (DBMS). It is known for its excellent support of the SQL standard, its extensibility and its enterprise-grade features – including procedural languages, extensive index types and comprehensive security.

PostgreSQL has been successfully used for more than three decades in all IT sectors. Its maturity and its vibrant community consistently make it a first choice database among developers.

Why choose Postgresql?

Comprehensive security features
Versatile thanks to hundreds of extensions
Reliable and proven with a 30 year track record

Why do companies use PostgreSQL?

Open source and open ecosystem

PostgreSQL is backed by a large community of independent or company-backed contributors that share code under a permissive open-source licence. PostgreSQL is also famous for being built from the ground up to be easily extensible. You can choose from over 1,000 extensions to cover more use cases with the same solution.

Enterprise-grade features

PostgreSQL comes with features comparable to those offered by closed source DBMS solutions. For example, it ships with a large number of built-in index types, support for several procedural languages, highly customisable replication and fine grained security controls.

Loved by developers

PostgreSQL has been a developer favourite for years. This can make it easy to find and retain high-level talent.

How do companies use PostgreSQL?

Analytics

PostgreSQL is famous for its excellent support of the SQL standard. It also supports a large panel of index types and can nicely integrate with other data stores (using foreign wrappers) making it an excellent choice for running analytical queries without the need to duplicate your data.

Banking

PostgreSQL provides the transactional ACID guarantees and the mature replication capabilities that finance systems need. The pgAudit extension can complement PostgreSQL with the auditing capabilities that regulated industries require.

Geospatial data

The PostGIS extension of PostgreSQL is an excellent choice to support use cases managing spatial data.

Search

PostgreSQL offers a variety of index structures (e.g. GIN and GIST) to perform advanced search and similarity operations on your data without the need for duplicating it or compromising on its consistency.

How does PostgreSQL work?

The diagram depicts an overview of the PostgreSQL architecture. It illustrates the processes, memory areas and some of their associated parameters. PostgreSQL adopts the following database design patterns:

Write Ahead Logging (WAL) provides atomicity and durability for database transactions. WALs are written by the WAL writer and archived by the WAL archiver to another storage medium, typically an ObjectStore.
Replication is implemented by sending WALs (by the WAL sender) to the standbys where they will be applied using either WAL receiver or Apply workers.
PostgreSQL adopts a process per connection model where a dedicated new process is spawned to manage every new connection.
PostgreSQL implements MultiVersion Concurrency Control (MVCC) by keeping multiple versions of the same row. Autovacuum workers will reclaim no longer used versions.
PostgreSQL uses kernel buffered IO so it can benefit from careful memory allocation to the OS in addition to PostgreSQL itself.

Feature breakdown

Broad replication capabilities

PostgreSQL has wide replication capabilities. It supports asynchronous and synchronous physical replication to create highly available deployments. It also provides a flexible subscription model to configure logical replication to filter replicated data or to perform migration across major versions and different platforms.

PostgreSQL uses replication slots for fault tolerant and resumable replication. Finally, PostgreSQL's cascading replication helps you create complex replication topologies.

Highly concurrent and parallel DBMS

PostgreSQL uses MVCC to provide efficient snapshot isolation while supporting concurrent access to the same data. PostgreSQL also supports the parallelisation of a growing number of query types which help make some operations run faster.

Tunable transactional guarantees

You can fine-tune PostgreSQL's durability guarantees to meet your own needs using these parameters.

Choose the right database to fit your requirements*

Features		MySQL	PostgreSQL	MongoDB	Valkey/Redis	OpenSearch
Primary data paradigm		Relational	Object Relational	Document	Key/Value	Text Search
Highest isolation level		Serialisable	Serialisable	Linearalisable	Linearalisable	Eventual read consistency
Typical achievable response times in milliseconds		1-100	1-100	1-100	0.1-10	10-1000
Max manageable data size in typical single clusters		1-10 TBs	1-50 TBs	1-100 TBs	Available memory	1-100 TBs
DBMS managed sharding		With extension	With extension	Built-in	Built-in	Built-in
DBMS managed partitioning		Yes	Yes	No	No	No
Supported data types	Tabular	Yes	Yes	-	-	-
	JSON	Yes	Yes	Yes	-	Yes
	XML	Yes	Yes	-	-	-
	Binary	Yes	Yes	Yes	Yes	Yes
	Generic Text	Yes	Yes	Yes	Yes	Yes
	Natural Language Text	Yes	Yes	Yes	With extension	Yes
	Time Series	-	With extension	Yes	-	Yes
	Geospatial data	Yes	With extension	Yes	Yes	Yes
	Graph data	-	With extension	-	-	-

*Only the editions supported by Canonical are considered in the table above

Installing PostgreSQL

PostgreSQL supports a large number of platforms and architectures. You can install PostgreSQL using:

Deploy PostgreSQL on your favourite K8s ›

PostgreSQL security

PostgreSQL provides several class-leading security features such as:

Row level security for fine grained access control
Comprehensive auditing capabilities thanks to pgAudit
Built-in cryptographic functions thanks to pg_crypto
Built-in authentication and database firewall using pg_hba.conf

Get up to 10 years of security maintenance for your PostgreSQL deployments

Canonical's PostgreSQL

We provide PostgreSQL artefacts and extensive automation to make using and scaling your PostgreSQL footprint a breeze.

Charmed PostgreSQL

(Included in Ubuntu Pro + Support)

Thanks to Charmed PostgreSQL, your deployments will be:

Highly available and reliable
Disaster recovery ready
Hybrid and multi-cloud ready
Shipped with a holistic observability and alerting solution

Access the datasheet ›

PostgreSQL ROCK