How it works
A PostgreSQL server instance manages multiple databases and normally uses a separate backend process for each client connection. Backends share a buffer cache and coordinate through shared memory and locks. A connection pool limits active database work so application autoscaling does not create an uncontrolled number of database connections.
For each SQL statement, PostgreSQL parses and rewrites the query, then uses a cost-based planner to choose scans, join orders, and join algorithms. The executor accesses heap tables and indexes through the buffer cache. Planner statistics, data distribution, and index design strongly affect the chosen plan, so EXPLAIN ANALYZE is central to diagnosing slow queries.
Heap tables contain row versions, while indexes generally point to heap locations. B-tree is the default index type and supports equality, ranges, and ordered retrieval. Other index types serve specialized access patterns, such as GIN for many JSONB and full-text queries, GiST for extensible search operations, and BRIN for large tables whose values correlate with physical order.
Multi-version concurrency control, or MVCC, lets statements read visible row versions without generally blocking writers. Updates create new row versions rather than immediately replacing the old ones. VACUUM makes space from obsolete versions reusable and prevents transaction ID wraparound; long-running transactions and retained snapshots can delay cleanup.
Write-ahead logging records changes before the affected data pages are flushed to durable storage. With normal durability settings, a transaction waits for its commit record to be flushed to WAL before success is returned. Checkpoints bound crash-recovery work, while background processes write dirty buffers and maintain WAL; recovery replays WAL after a crash.
What it gives you in a design
- Transactions and database-enforced invariants
- Transactions combine related changes into one commit, while unique constraints, foreign keys, and check constraints enforce rules across every application writer. Put correctness rules in the database where possible rather than relying only on application checks.
- Concurrency control
- Row locks, conditional updates, and Serializable isolation support inventory allocation, balance changes, and state transitions. The application still needs to handle contention, deadlocks, and transaction retries.
- Workload-specific indexes
- Composite, partial, expression, and covering indexes can match specific query patterns. Each additional index consumes space and adds write and maintenance work, so justify indexes with actual filters, ordering, and query plans.
- Relational queries with flexible fields
- SQL joins and aggregations support evolving access patterns, while JSONB accommodates fields that do not fit a fixed relational shape. JSONB does not remove the need for schema discipline, selective indexes, or bounded document sizes.
- Physical and logical replication
- Physical streaming replication supports standbys that replay WAL, including read-only hot standbys. Logical replication publishes selected table changes and is useful for selective replication and migrations, but it does not automatically copy every database object or DDL change.
- Partitioning
- Declarative range, list, and hash partitioning divide a logical table into smaller physical tables. Partition pruning and dropping old partitions can simplify large-table operations, but partitioning alone does not distribute writes across servers.
- Transactional outbox and change capture
- An application can commit a business change and an outbox row in the same transaction, then publish events asynchronously. Logical decoding can expose committed changes, but consumers still need replay handling and idempotency.
Scaling, replication and failure
Start by making queries efficient, bounding active connections, and provisioning enough memory, storage throughput, and CPU. Read replicas can offload stale-tolerant reads, but every physical replica still replays the primary's writes. Replica count does not multiply write capacity.
Streaming replication is asynchronous by default, so replicas can lag and an acknowledged commit can be lost if failover promotes a standby that has not received it. Configured synchronous replication can make commits wait for selected standbys, trading latency and sometimes write availability for stronger durability. Remote WAL durability does not necessarily mean the change is already visible to queries on the standby.
Route read-after-write operations to the primary unless the application explicitly tracks replica replay progress. Safe failover needs promotion coordination, fencing of the old primary, and client reconnection behavior; PostgreSQL streaming replication alone is not a complete automatic failover system. Backups and retained WAL for point-in-time recovery are still necessary because replication also propagates accidental changes.
When one primary can no longer handle the workload after tuning and vertical scaling, shard by a key that keeps common transactions local, such as tenant ID. Application routing or a distributed PostgreSQL extension adds operational and query complexity. Cross-shard joins, uniqueness, transactions, and rebalancing must become explicit design decisions.
Limits and defaults worth knowing
Check the current documentation before relying on an exact value; many of these are configurable.
| Default transaction isolation | Read Committed is the default. Each statement gets a new snapshot, so two statements in one transaction can observe different committed data. |
|---|---|
| Default connection limit | max_connections is typically 100 by default, though initialization or managed-service settings can differ. It is configurable, requires a server restart to change, and is not a throughput target. |
| Storage page size | The default database block size is 8 KiB. It is selected at build time, not changed through a normal runtime setting. |
| Identifier length | Database object identifiers are limited to 63 bytes in a standard build. Longer names are truncated; the limit is a compile-time setting. |
| Columns in an index | A standard build allows up to 32 columns per index, including INCLUDE columns. This is a compile-time limit, not a recommendation for index width. |
Use it when
- Related records must commit together, such as a reservation and its inventory allocation or balanced ledger entries.
- Uniqueness, referential integrity, and race-free state transitions are core requirements.
- The workload needs joins, selective lookups, and evolving queries rather than only a fixed key-value access pattern.
- One write primary can meet the workload, with caching, read replicas, or tenant-based sharding available as later steps.
- A system needs durable metadata alongside asynchronous queues, search indexes, caches, or object storage.
Avoid it when
- The central requirement is transparent, horizontally distributed writes across many machines without application-level sharding or a distributed extension.
- Every region must accept writes to the same records during network partitions with automatic conflict convergence.
- The dominant workload is broad analytical scanning over massive datasets better served by a columnar warehouse or analytical engine.
- The system primarily stores large media blobs or serves ephemeral, extremely high-churn data that belongs in object storage or a purpose-built cache.
What interviewers ask about it
Follow-ups you can expect once PostgreSQL is on your whiteboard, and the core of a strong answer.
- How would you prevent two requests from booking the same seat?
- Use a database invariant, such as a unique allocation key, together with a conditional update or a row lock inside a short transaction. For example, update the seat only if it is available and check whether a row was returned. Do not hold the transaction open while calling a payment provider; represent a durable hold and handle expiry separately.
- Does MVCC mean PostgreSQL avoids locks?
- No. Ordinary snapshot reads generally do not block row updates, but concurrent writers can block each other, and explicit row locks and schema locks still matter. Long transactions can also retain old versions and cause vacuum-related space growth.
- How would you provide read-after-write consistency with replicas?
- The simplest approach is to send correctness-sensitive reads to the primary. A more complex approach records a WAL position and waits until the chosen replica has replayed through it. A fixed delay or an arbitrary replica does not guarantee the write will be visible.
- What changes when the primary fails?
- A failover manager or managed service must select an eligible standby, fence the old primary, promote the standby, and redirect clients. Asynchronous replication can lose acknowledged writes, while synchronous behavior depends on which standbys were required and available. Clients must also resolve uncertain transaction outcomes with idempotency keys or durable operation records.
- When would you choose Serializable isolation?
- Use it when a transaction's correctness depends on a set of reads and writes that is difficult to protect with constraints or targeted locks. PostgreSQL detects dangerous dependency patterns and can abort a transaction rather than allow a nonserializable result. Retry the entire transaction on serialization failure and keep external side effects outside the retryable transaction.
- How do you know whether PostgreSQL needs sharding?
- Measure the bottleneck first: expensive queries, lock contention, connection overload, storage latency, and WAL throughput require different fixes. Add indexes, reduce unnecessary work, bound concurrency, and offload suitable reads before distributing data. Shard when a single primary remains the limiting resource and the access patterns provide a workable shard key.
Common mistakes
- Calling PostgreSQL strongly consistent without specifying isolation, whether reads use the primary, and what replication and failover settings guarantee.
- Assuming read replicas or table partitioning distribute write load across independent primaries.
- Using a read-then-write application check for inventory or uniqueness without a constraint, conditional update, or appropriate locking.
- Ignoring connection limits, vacuum health, long transactions, and index write overhead when estimating capacity.
Alternatives
- MySQL with InnoDB
- A conventional relational primary with transactions fits, and team expertise, existing tooling, or operational requirements favor MySQL. Compare concrete isolation, replication, indexing, and failover behavior rather than treating either database as universally faster.
- Distributed SQL databases
- The design needs relational transactions across automatically distributed data and can accept distributed coordination costs. Evaluate transaction locality, cross-region latency, compatibility, and operational complexity.
- DynamoDB or Cassandra
- Predictable partition-key access and distributed write throughput matter more than general joins and flexible relational transactions. Their consistency, transaction, and query capabilities differ, so choose based on the actual access patterns.
- ClickHouse or a columnar warehouse
- Large scans and aggregations dominate the workload. Keep transactional application state in PostgreSQL and replicate analytical data when one engine should not serve both workloads.
Where it comes up
Interview questions where PostgreSQL is a natural fit, and why.
- Design a ticket booking system like TicketmasterTransactions, conditional updates, and uniqueness constraints protect seat allocation under concurrent requests.
- Design a payment systemTransactional ledger updates, idempotency records, and an outbox support correct payment state despite retries and uncertain external outcomes.
- Design a hotel booking systemAtomic multi-night inventory changes and durable hold state require explicit concurrency control.
- Design a notification systemA transactional outbox connects authoritative application changes to asynchronous notification delivery without an unsafe dual write.
- Design a distributed job schedulerDurable job state and short row-locking transactions can coordinate worker claims while leases and idempotency handle failures.
- Design Dropbox or Google DrivePostgreSQL fits transactional file metadata, version records, sharing permissions, and durable change records while object storage holds file contents.
Questions people ask
What is PostgreSQL architecture?
PostgreSQL uses client connections handled by backend processes, shared buffers for cached data pages, heap tables and indexes for storage, and WAL for recovery. MVCC controls row visibility, while background processes handle tasks such as checkpoints and vacuuming. The usual deployment has one writable primary with optional read replicas.
Can PostgreSQL scale horizontally?
It can scale reads through replicas and distribute data through application sharding or extensions. Core table partitioning divides tables within an instance but does not create a distributed write cluster. Once data spans independent servers, routing and cross-shard correctness require additional design.
What is the difference between PostgreSQL Read Committed and Serializable?
Read Committed gives each statement a snapshot of committed data, but does not make a multi-statement transaction equivalent to running alone. Serializable guarantees an outcome equivalent to some serial execution of successfully committed transactions. Applications must be prepared to retry transactions that fail serialization checks.