--- title: Overview | PostgreSQL description: PostgreSQL is the world's most acclaimed open source database management system. Explore PostgreSQL, its ecosystem, its internal architecture and how it compares to other databases. url: https://canonical.com/data/postgresql/what-is-postgresql?format=md --- # 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](https://gist.github.com/joelonsql/e5aa27f8cc9bd22b8999b7de8aee9d47) 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. --- [Contact us for all of your PostgreSQL needs ›](https://canonical.com/contact-us) --- ## 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](https://www.postgresql.org/docs/14/ddl-foreign-data.html)) making it an excellent choice for running analytical queries without the need to duplicate your data. --- ##### Banking PostgreSQL provides the transactional [ACID](https://en.wikipedia.org/wiki/ACID) guarantees and the mature replication capabilities that finance systems need. The [pgAudit](https://www.pgaudit.org/) extension can complement PostgreSQL with the auditing capabilities that regulated industries require. --- ##### Geospatial data The [PostGIS](https://postgis.net/) 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 **WAL**s (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](https://www.postgresql.org/docs/current/sql-createsubscription.html) to configure logical replication to filter replicated data or to perform migration across major versions and different platforms. PostgreSQL uses [replication slots](https://www.postgresql.org/docs/current/warm-standby.html#STREAMING-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](https://www.postgresql.org/docs/current/parallel-query.html) 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](https://www.postgresql.org/docs/current/non-durability.html). --- ## 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: * [Deb packages](https://ubuntu.com/server/docs/install-and-configure-postgresql) * [Charmed PostgreSQL](https://canonical.com/blog/postgresql-high-availability) --- [Deploy PostgreSQL on your favourite K8s ›](https://canonical.com/data/postgresql/docs/latest/how-to/deploy/k8s-clouds/) --- ## PostgreSQL security PostgreSQL provides several class-leading security features such as: * [Row level security](https://www.postgresql.org/docs/current/ddl-rowsecurity.html) for fine grained access control * Comprehensive auditing capabilities thanks to [pgAudit](https://www.pgaudit.org/) * Built-in cryptographic functions thanks to [pg\_crypto](https://www.postgresql.org/docs/current/pgcrypto.html) * Built-in authentication and database firewall using [pg\_hba.conf](https://www.postgresql.org/docs/current/auth-pg-hba-conf.html) --- [Get up to 10 years of security maintenance for your PostgreSQL deployments](https://ubuntu.com/pro/subscribe) --- ## 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 ›](https://drive.google.com/file/d/1rpaXT8Q1gW6lLVxSZiDSxW17EzJAAytH/view?usp=sharing) --- PostgreSQL ROCK (Included in Ubuntu Pro + Support) Our PostgreSQL [rock](https://ubuntu.com/server/docs/about-rock-images) 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](https://ubuntu.com/engage/postgresql-mysql) 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](https://canonical.com/data/postgresql/docs/latest) and [VMs](https://canonical.com/data/postgresql/docs/latest/explanation/) Read our tutorials and installation guides to learn how to deploy and manage PostgreSQL within and across public and private clouds. --- [A guide to database cloud migration](https://ubuntu.com/engage/database-cloud-migration) Gain insights on how to succeed in migrating your most valuable assets, your data, to the cloud. --- [A guide to data backups](https://ubuntu.com/engage/data-backups) 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. " Close ### Talk to our relational databases' experts