What is PostgreSQL?

Origins, governance and licence

PostgreSQL descends from POSTGRES, a research project started at the University of California, Berkeley in 1986. SQL support arrived in the mid-1990s and the project took its current name in 1996. There is no owning vendor: development is run by the PostgreSQL Global Development Group, a community of contributors employed by many different companies or working independently. The code is released under the PostgreSQL License, a liberal open-source licence comparable to BSD or MIT, so it can be used, modified and redistributed in commercial products as long as the copyright notice is kept. That freedom is why so many cloud providers offer managed PostgreSQL and why several commercial databases are built on its code.

What draws projects to it

• Rich types: JSONB, arrays, range types, UUIDs, network addresses and user-defined types, so semi-structured fields can live next to relational columns without a second database.
• Several index methods: B-tree by default, GIN for JSONB and full-text search, GiST for geometric and nearest-neighbour queries, BRIN for huge tables whose rows arrive in natural order.
• Extensions: PostGIS adds geospatial queries and pgvector adds embedding storage with similarity search, all without touching the core. For many small and mid-sized projects that removes the need for a separate vector database.
• Transactional DDL: most schema changes, such as ALTER TABLE, run inside a transaction and roll back cleanly on failure, which makes database migrations far less likely to leave a half-applied schema.
• Advanced SQL: common table expressions (WITH), window functions, row-level security and logical replication.

-- Keep product attributes in a JSONB column and index them
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

SELECT id, name
FROM products
WHERE attributes @> '{"color": "black", "size": "M"}';

MVCC and the work VACUUM does

PostgreSQL handles concurrency with multi-version concurrency control. An UPDATE never overwrites a row in place; it writes a new version and keeps the old one until no running transaction could still need it. Readers therefore never block writers and writers never block readers. The cost is that dead row versions must be cleaned up, which autovacuum does in the background. A transaction left open for hours prevents that cleanup and lets tables bloat, so long-running reports and forgotten idle-in-transaction sessions deserve monitoring. The default isolation level is Read Committed; the trade-offs are covered under database transaction.

One process per connection

Every client connection gets its own server process. The model is robust, but each connection costs memory, and when hundreds of app instances or serverless functions each open their own connections the server runs out of headroom quickly. The usual answer is a connection pool such as PgBouncer between the application and the database.

Releases, upgrades and backups

A new major version ships roughly once a year and each one is supported for five years from its first release, with minor releases carrying bug and security fixes at least every three months. Applying a minor release means swapping binaries and restarting. Moving between major versions needs pg_upgrade, a dump and restore, or logical replication, and should be rehearsed in staging first. Logical dumps with pg_dump plus continuous WAL archiving for point-in-time recovery form the basis of a sound backup strategy.