Skip to main content

Your submission was sent successfully! Close

Thank you for signing up for our newsletter!
In these regular emails you will find the latest updates from Canonical and upcoming events where you can meet our team.Close

Thank you for contacting our team. We will be in touch shortly.Close

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:


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

(Included in Ubuntu Pro + Support)

Our PostgreSQL rock is:

  • OCI-compliant
  • Secure for up to 10 years
  • Optimised for small footprint
  • Self contained with all the dependencies needed to run an enterprise grade PostgreSQL

PostgreSQL consultancy and support

(Advanced professional services for PostgreSQL when you need them)

Unlock the potential of your data with Canonical's advisory services. We can:

  • Design and architect tailored solutions for your specific use cases
  • Deliver Proof of Concepts (PoC) to implement your ideas and de-risk your projects
  • Train your staff on Canonical's solutions for PostgreSQL

Learn more about PostgreSQL

PostgreSQL vs MySQL webinar

Dive into the differences between the two most popular open-source databases and what those differences mean from a performance, operability and community engagement perspective.


Further reading


Read the documentation for K8s and VMs

Read our tutorials and installation guides to learn how to deploy and manage PostgreSQL within and across public and private clouds.


Gain insights on how to succeed in migrating your most valuable assets, your data, to the cloud.


Build a comprehensive backup strategy to meet your requirements in terms of data protection and recovery times.


"PostgreSQL” is a trademark or registered trademark of PGCA. Other trademarks are property of their respective owners. Canonical's PostgreSQL offerings are not sponsored, endorsed, or affiliated with PGCA. "