PostgreSQL

In short: A particularly feature-rich, open-source relational database system — known for strict standards compliance, advanced data types (JSON, arrays, geodata) and high reliability.

In more detail: PostgreSQL is often considered the “technically more powerful” choice compared to MySQL/MariaDB (e.g. for complex queries, transaction behaviour, custom data types), with similarly easy setup. Services like Neon or Supabase offer PostgreSQL as a managed “serverless” variant.

In Depth

PostgreSQL (often called “Postgres” for short) has been continuously developed since the 1980s and is considered especially standards-compliant — it often implements SQL standards more completely than competing systems. Features that set it apart from MySQL include native JSON with indexing (JSONB, efficiently searchable instead of just stored as text), array columns, custom data types and functions, and very strict adherence to the ACID properties (Atomicity, Consistency, Isolation, Durability) for transactions.

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  items JSONB
);

Extensibility is another distinguishing feature: extensions like PostGIS (geodata/mapping functions) or pgvector (vector search for AI embeddings) make PostgreSQL a versatile foundation even for use cases that used to require a separate specialised database. Providers like Neon or Supabase build “serverless” variants directly on real PostgreSQL, instead of developing their own, incompatible database engine.

MVCC instead of locks

PostgreSQL uses a method called MVCC (Multi-Version Concurrency Control) for concurrency: instead of blocking other read access during a running transaction, the database keeps several versions of a row at once — a read access always sees a consistent data state, as it existed at the start of its own transaction, even if another transaction changes the same row in parallel. This enables high concurrency (many simultaneous read and write accesses) without the performance penalties of classic, lock-based systems, but requires a regular background cleanup process (“vacuum”) that removes outdated row versions.

Indexes and query optimisation

Besides the standard B-tree index, PostgreSQL supports specialised index types for different use cases: GIN indexes suit full-text search and JSONB columns, GiST indexes suit geodata (in combination with PostGIS), and BRIN indexes suit very large, naturally sorted tables (e.g. time-series data), where they need considerably less storage than a full B-tree index. The built-in EXPLAIN ANALYZE command shows a query’s actual execution plan, including the indexes used — a central tool for diagnosing slow queries in practice.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Ecosystem and tooling

PostgreSQL’s popularity has produced a rich ecosystem of tools: pg_dump/pg_restore for backups, pgAdmin as a graphical management interface, and modern TypeScript ORMs like Drizzle ORM or Prisma, which enable type-safe database access directly from application code. Broad support from practically every cloud provider (AWS RDS, Google Cloud SQL, Azure Database, as well as specialised serverless providers like Neon) makes PostgreSQL a low-risk long-term choice, since a provider switch, thanks to SQL standards compliance, rarely requires fundamental application changes.

See also: SQL, NeonDB, MySQL, JSON