Skip to content
Knowledge Hub/Article
Article

What Is PostgreSQL? Features, Versions and MySQL Differences

We explain PostgreSQL's MVCC, WAL, JSONB and extensions, its replication and backups, version support, how it differs from MySQL and key security settings.

Oğuzhan Gerçek··10 min read
What Is PostgreSQL? Features, Versions and MySQL Differences

Short answer: PostgreSQL is an open-source, object-relational database management system rooted in the POSTGRES project, which began at Berkeley in 1986. Its liberal license, similar to the BSD and MIT licenses, allows free use for any purpose. It manages concurrent access with MVCC and durability with the write-ahead log (WAL), and with JSONB, several index types and extensions such as PostGIS and pgvector, it carries varied workloads in a single engine. Each major version is supported for five years. It differs from MySQL in licensing, process model and storage architecture.

What is PostgreSQL?

In its own words, PostgreSQL is an open-source object-relational database system that uses and extends the SQL language, and it has been ACID-compliant since 2001. According to the documentation, users can extend it with new data types, functions, operators, index methods and procedural languages. Its license, the PostgreSQL License, is a liberal open-source license similar to the BSD and MIT licenses: the software can be used, modified and distributed for any purpose, free of charge and without a written agreement.

We cover the language it uses in our SQL article and the DBMS concept in our DBMS article; our glossary has a short definition of a database. In the 2026 Stack Overflow survey, 57.9% of the 12,061 developers who answered the database question said they had done extensive development work with PostgreSQL over the past year, ahead of SQLite (42.1%) and MySQL (35.1%).

A short history: from Berkeley to today

According to the project's history, the POSTGRES project at Berkeley was led by Professor Michael Stonebraker. Implementation began in 1986, Version 1 reached a few external users in June 1989, and the Berkeley project officially ended with Version 4.2. In 1994, Andrew Yu and Jolly Chen added an SQL interpreter, and SQL replaced the PostQUEL query language. The software was then released as open source under a new name, Postgres95. In 1996, it was renamed PostgreSQL, and version numbering restarted at 6.0 to continue the sequence begun at Berkeley.

Core features: MVCC, WAL, indexes, JSONB and extensions

  • MVCC: Each SQL statement sees a snapshot of the data, so reading never blocks writing and writing never blocks reading. VACUUM cleans up the old versions of updated rows; in many installations, the autovacuum daemon takes care of this.
  • WAL: Changes are written to data files only after the WAL records describing them reach permanent storage, and a crashed system recovers from this log. Replication and point-in-time recovery (PITR) also rely on the WAL.
  • Indexes: B-tree, Hash, GiST, SP-GiST, GIN and BRIN are built in, and B-tree is the default. GIN is an inverted index suited to values with multiple components, such as arrays.
  • JSON and JSONB: json stores an exact copy of the input text and reparses it on each execution. jsonb is stored in a decomposed binary format, is significantly faster to process and supports indexing; the documentation recommends jsonb for most applications.
  • Extensions: An extension packages related SQL objects and is loaded with CREATE EXTENSION. PostGIS adds support for storing, indexing and querying geospatial data, and pgvector stores vectors alongside the rest of your data for similarity search.

Replication: streaming and logical

  • Streaming replication: A standby server connects to the primary, which streams WAL records as they are generated. It is asynchronous by default, so there is a small delay between a commit on the primary and the change becoming visible on the standby. A standby that serves read-only queries is called a hot standby.
  • Logical replication: Copies data objects and their changes based on their replication identity (usually a primary key) rather than block addresses, using a publish and subscribe model. It works across major versions and platforms, but schema changes (DDL) and sequence data are not replicated.

We explain synchronous and asynchronous replication in our replication article, and how to measure lag in Reliability Friday 01.

Backup and PITR: pg_basebackup and the WAL archive

pg_basebackup takes a base backup of a running cluster without affecting other clients, and that backup is the starting point both for PITR and for a new standby. It always covers the entire cluster; for a single database or table, you need pg_dump. Since PostgreSQL 17, it can also take incremental backups that contain only changed blocks, but such a backup cannot be used until pg_combinebackup merges it with the earlier backups it depends on.

PITR requires a WAL archive, enabled with archive_mode, and recovery needs a continuous sequence of archived WAL files reaching back at least to the start of the base backup. We cover backup types in our backup article and PostgreSQL's three backup methods in our DBA article.

Versions and support policy

Under the versioning policy, PostgreSQL ships about one major version a year and supports each one for five years after its initial release. Bug and security fixes arrive at least every three months in minor releases; the roadmap targets the second Thursday of February, May, August and November. After five years a final minor version is released and the major version reaches end-of-life. The dates for PostgreSQL 14 to 18 (initial release; end of support) are:

  • PostgreSQL 18: September 25, 2025; November 14, 2030.
  • PostgreSQL 17: September 26, 2024; November 8, 2029.
  • PostgreSQL 16: September 14, 2023; November 9, 2028.
  • PostgreSQL 15: October 13, 2022; November 11, 2027.
  • PostgreSQL 14: September 30, 2021; November 12, 2026.

Support for PostgreSQL 14 ends on November 12, 2026. Beta 4 of PostgreSQL 19 came out on September 24, 2026. Because security fixes ship in minor releases, the project advises always using the latest minor version (patch management article).

PostgreSQL vs. MySQL

Both are open-source relational systems. According to the two projects' documentation, they differ in these areas:

  • Developer and license: PostgreSQL releases come from the PostgreSQL Global Development Group under a BSD- and MIT-like license. MySQL is developed by Oracle Corporation and distributed under the GPL, with a commercial license also available.
  • Process model: PostgreSQL starts a new process for each connection; MySQL is a multithreaded server.
  • Storage: MySQL uses pluggable storage engines that handle SQL operations for different table types; the default, InnoDB, follows the ACID model. In PostgreSQL, table access methods manage table storage, and new ones can be written as add-on code.
  • JSON: MySQL's JSON columns are not indexed directly; you index a generated column or use a multi-valued index. In PostgreSQL, jsonb can be searched with a GIN index.
  • Support: In MySQL, LTS series get five years of premier and three years of extended support, while Innovation releases are supported until the next Innovation release.

We discuss when a document database is the better fit in our PostgreSQL or MongoDB article and cover the NoSQL family in our NoSQL article.

Managed PostgreSQL: AWS, Azure and Google Cloud

  • Amazon RDS for PostgreSQL: According to AWS, it supports Multi-AZ deployments, read replicas and point-in-time restores, and to deliver a managed service experience, it does not provide SSH access to the host.
  • Azure Database for PostgreSQL: According to Microsoft, the service patches the underlying hardware, operating system and database engine, and keeps backups for seven days by default and up to 35 days.
  • Cloud SQL for PostgreSQL: According to Google, it takes care of backups, high availability and failover, and replication, and applies patches through maintenance updates that require a restart.

According to AWS, query tuning is still the customer's job (DBA article).

PostgreSQL security: pg_hba.conf, roles and TLS

  • pg_hba.conf: This file controls client authentication; each record sets an authentication method for a connection type, address range, database and user. The first matching record is used, and if authentication fails, later records are not considered. The trust method lets anyone who can connect log in as any user without a password.
  • Network and passwords: By default, the server listens only on localhost (listen_addresses) and on port 5432 (port article). Password encryption defaults to scram-sha-256, and MD5 support will be removed in a future release.
  • Roles: Access is managed through roles; a role can be a user, a group or both (IAM article).
  • TLS: Encrypted connections are enabled on the server with ssl = on, and the minimum protocol version defaults to TLS 1.2. On the client, sslmode defaults to prefer; the documentation recommends verify-full in most security-sensitive environments (SSL article).

We handle day-to-day operations in our database platform and operations service, high-availability design in our database platform engineering service and migrations to PostgreSQL in our application & database modernization service.

Frequently asked questions

What is PostgreSQL used for? It stores application data in tables, lets you query it with SQL and keeps concurrent transactions consistent; with JSONB and extensions, it also handles document, geospatial and vector data.

Is PostgreSQL free? Yes. The PostgreSQL License allows the software to be used, modified and distributed for any purpose, free of charge and without a written agreement.

What is the difference between PostgreSQL and MySQL? PostgreSQL is an object-relational system under a BSD-like license that starts a separate process for each connection. MySQL is developed by Oracle, distributed under the GPL and a commercial license, and runs as a multithreaded server.

What port does PostgreSQL use? By default, it listens on TCP port 5432 and accepts connections only from localhost.

How long are PostgreSQL versions supported? Each major version is supported for five years after its initial release, with fixes delivered at least every three months in minor releases.

Sources