PostgreSQL Overview

PostgreSQL is a row-oriented relational database with multi-version concurrency control (MVCC), a cost-based query planner and an extension system that lets third-party code add index types, data types and background workers without forking the server.

It is the default choice for transactional application data in most teams: the type system is strict, constraints are enforced, and the planner handles joins well. The cost of that generality is that PostgreSQL is a single-writer system per cluster — scaling writes beyond one node means sharding at the application level or moving to a distributed SQL engine.

Architecture

PostgreSQL uses a process-per-connection model. The postmaster accepts a connection and forks a backend process that lives for the duration of the session. Backends share memory through shared_buffers, and a set of auxiliary processes handles work that must not block queries.

The pieces that matter operationally:

• Backend processes. One OS process per connection, each with its own work_mem allocations. This is why unbounded connection counts are expensive and why a pooler is standard in production — see Connection Management.
• Shared buffers. The database's own page cache, sitting in front of the operating system page cache. Pages are read into shared_buffers, modified there, and written back by the checkpointer and background writer.
• Write-ahead log (WAL). Every change is written to WAL before the corresponding data page is flushed. WAL is what makes crash recovery, streaming replication and point-in-time recovery possible.
• Checkpointer. Periodically flushes dirty pages so that recovery does not have to replay an unbounded amount of WAL.
• Autovacuum workers. Reclaim space from dead row versions and refresh planner statistics. On busy systems this is the single most common source of operational trouble.

MVCC and its consequences

An UPDATE in PostgreSQL does not overwrite a row. It writes a new row version and marks the old one as dead. Readers continue to see the version valid for their snapshot, so readers never block writers and writers never block readers.

The consequence is that deleted and updated rows accumulate as dead tuples until vacuum removes them. A table that receives heavy updates without effective vacuuming grows on disk, its indexes grow with it, and sequential scans read progressively more empty space. That failure mode is covered in Vacuum and Autovacuum.

Best use cases

• Transactional application state where correctness matters: orders, ledgers, inventory, user accounts. Foreign keys, check constraints and serializable isolation are available and cheap to use.
• Mixed read/write workloads up to whatever a single well-provisioned node handles. That ceiling is far higher than most teams assume — tens of thousands of transactions per second is achievable on commodity hardware with a sensible schema.
• Moderate analytical queries over operational data, especially with partitioning and parallel query. For heavy scan-oriented analytics, a column store is a better fit; see ClickHouse vs PostgreSQL.
• Geospatial workloads through PostGIS, which is the most capable open-source spatial implementation available.
• Time series data through the TimescaleDB extension, which adds automatic partitioning and columnar compression while keeping the PostgreSQL interface.

When not to use it

• When writes exceed what one node can absorb. PostgreSQL has exactly one writable primary per cluster. If you need multi-node write scaling without application-level sharding, look at Distributed SQL.
• For high-cardinality analytical scans over billions of rows. Row storage means reading whole rows to aggregate one column.
• As a cache. A network round trip to a durable, MVCC-managed store is not a substitute for an in-memory key-value store.
• As a queue at very high throughput. SELECT ... FOR UPDATE SKIP LOCKED makes a workable queue, but at high rates the dead-tuple churn on the queue table becomes an autovacuum problem.

Data model

Data is stored in tables composed of typed columns. PostgreSQL's type system is unusually strong: native jsonb, arrays, ranges, uuid, network address types, enumerated types and user-defined composite types are all first class and all indexable.

CREATE TABLE orders (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id  bigint      NOT NULL REFERENCES customers (id),
    status       text        NOT NULL CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
    total_cents  bigint      NOT NULL CHECK (total_cents >= 0),
    metadata     jsonb       NOT NULL DEFAULT '{}'::jsonb,
    created_at   timestamptz NOT NULL DEFAULT now()
);
 
CREATE INDEX orders_customer_created_idx
    ON orders (customer_id, created_at DESC);

Consistency and transactions

PostgreSQL is fully ACID on a single node. It implements three of the four SQL isolation levels — READ COMMITTED (the default), REPEATABLE READ and SERIALIZABLE. READ UNCOMMITTED is accepted but behaves as READ COMMITTED, because MVCC never exposes uncommitted data.

SERIALIZABLE uses Serializable Snapshot Isolation: it detects dangerous read/write dependency cycles and aborts one transaction with a serialization failure rather than taking extra locks. Applications using it must retry transactions that fail with SQLSTATE 40001. See Transactions and Isolation Levels.

Scaling model

Read replicas scale reads only. Every replica applies the full write stream of the primary, so adding replicas does not reduce write load — it increases total I/O across the fleet.

Replication

Two mechanisms exist, and they solve different problems:

• Streaming (physical) replication ships WAL records byte for byte. The replica is an exact copy of the primary, including all databases and extensions, and can serve read-only queries. This is what you use for high availability. See Streaming Replication.
• Logical replication decodes WAL into row-level changes and applies them to a subscriber, which may run a different major version or a different schema subset. This is what you use for major version upgrades and selective data movement. See Logical Replication.

Synchronous commit is configurable per transaction. synchronous_commit = on with a synchronous_standby_names entry means a commit is not acknowledged until a standby has persisted the WAL — durable, but every commit now pays a network round trip.

Backup and recovery

• pg_dump / pg_dumpall produce logical dumps: portable across versions, slow to restore for large databases, and they do not support point-in-time recovery.
• Physical base backups (pg_basebackup, pgBackRest, Barman) plus archived WAL give you point-in-time recovery, which is what most production systems actually need.

Monitoring

Start with these views and extensions; the details are in PostgreSQL Monitoring.

Common mistakes

Production checklist

The full checklist lives in PostgreSQL Production Checklist. The minimum before taking traffic: