# PostgreSQL for system design interviews

> PostgreSQL is a relational database with SQL, transactions, constraints, and flexible indexes. It is a strong default for authoritative application data when related records must change together. It suits systems where correctness matters more than unrestricted write scale.

Source: https://sysround.com/tech/postgresql
Updated: 2026-11-05


## 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. This limit prevents automatic application growth from creating an uncontrolled number of database connections.

For each SQL statement, PostgreSQL parses and rewrites the query. A cost-based planner then chooses 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 plan. EXPLAIN ANALYZE is a key tool to diagnose 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 reads. Other index types support specific access patterns. GIN supports many JSONB and full-text queries. GiST supports extensible search operations. BRIN supports 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 instead of immediately replacing old versions. VACUUM makes space from obsolete versions reusable and prevents transaction ID wraparound. Long transactions and retained snapshots can delay cleanup.

Write-ahead logging records changes before PostgreSQL writes affected data pages to durable storage. With normal durability settings, a transaction waits for PostgreSQL to write its commit record to WAL before PostgreSQL reports success. Checkpoints limit crash-recovery work. 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. Unique constraints, foreign keys, and check constraints enforce rules for every application that writes data. Put correctness rules in the database where possible. Do not rely only on application checks.
- **Concurrency control.** Row locks, conditional updates, and Serializable isolation support inventory allocation, balance changes, and state transitions. The application must still handle contention, deadlocks, and transaction retries.
- **Workload-specific indexes.** Composite, partial, expression, and covering indexes can match specific query patterns. Each additional index uses space and adds write and maintenance work. Justify indexes with actual filters, sort orders, and query plans.
- **Relational queries with flexible fields.** SQL joins and aggregations support access patterns that change. JSONB supports fields that do not fit a fixed relational structure. JSONB still requires schema discipline, selective indexes, and document size limits.
- **Physical and logical replication.** Physical streaming replication supports standbys that replay WAL, including read-only hot standbys. Logical replication publishes selected table changes and helps with selective replication and migrations. 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 removal of old partitions can simplify operations on large tables. 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. Consumers still need replay handling and idempotency.

## Scaling, replication and failure

First, make queries efficient and limit active connections. Provide enough memory, storage throughput, and CPU. Read replicas can serve reads that accept stale data. However, every physical replica still replays the primary's writes. More replicas do not multiply write capacity.

Streaming replication is asynchronous by default, so replicas can lag. Failover can lose an acknowledged commit if it promotes a standby that has not received the commit. Configured synchronous replication can make commits wait for selected standbys. This increases durability but adds latency and sometimes reduces write availability. Remote WAL durability does not necessarily mean that standby queries can already see the change.

Route read-after-write operations to the primary unless the application explicitly tracks replica replay progress. Safe failover requires coordinated promotion, fencing of the old primary, and client reconnection behavior. PostgreSQL streaming replication alone is not a complete automatic failover system. Replication also copies accidental changes. Backups and retained WAL for point-in-time recovery remain necessary.

Use sharding when one primary cannot handle the workload after tuning and vertical scaling. Choose a key, such as tenant ID, that keeps common transactions local. Application routing or a distributed PostgreSQL extension adds operational and query complexity. The design must explicitly address cross-shard joins, uniqueness, transactions, and rebalancing.

## Limits and defaults worth knowing

- **Default transaction isolation:** Read Committed is the default. Each statement gets a new snapshot. Thus, two statements in one transaction can see different committed data.
- **Default connection limit:** max_connections typically defaults to 100, but initialization or managed-service settings can differ. You can configure it, but a change requires a server restart. It is not a throughput target.
- **Storage page size:** The default database block size is 8 KiB. The build sets this size. A normal runtime setting cannot change it.
- **Identifier length:** A standard build limits database object identifiers to 63 bytes. PostgreSQL truncates longer names. A compile-time setting controls the limit.
- **Columns in an index:** A standard build allows up to 32 columns per index, including INCLUDE columns. This compile-time limit is not a recommended index width.

## Use it when

- Related records must commit together, such as a reservation and its inventory allocation or balanced ledger entries.
- The system requires uniqueness, referential integrity, and state transitions without race conditions.
- The workload needs joins, selective lookups, and queries that change, not only a fixed key-value access pattern.
- One write primary can handle the workload. Later options include a cache, read replicas, or tenant-based sharding.
- A system needs durable metadata alongside asynchronous queues, search indexes, caches, or object storage.

## Avoid it when

- The main 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. The system must automatically resolve conflicts to a common state.
- The main workload scans massive datasets for analysis. A columnar warehouse or analytical engine serves this workload better.
- The system mainly stores large media blobs or serves temporary data with extremely frequent changes. Object storage or a purpose-built cache suits this data.

## What interviewers ask about it

### How would you prevent two requests from reserving the same seat?

Use a database invariant, such as a unique allocation key, with a conditional update or row lock in a short transaction. For example, update the seat only if it is available. Check whether the update returned a row. Do not keep the transaction open during a call to a payment provider. Record 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. Explicit row locks and schema locks still matter. Long transactions can also retain old versions and cause space growth related to vacuum.

### How would you provide read-after-write consistency with replicas?

The simplest approach sends reads that require correct results to the primary. A more complex approach records a WAL position. It waits until the chosen replica has replayed WAL through that position. A fixed delay or an arbitrary replica does not guarantee that reads will show the write.

### What changes when the primary fails?

A failover manager or managed service must select an eligible standby and fence the old primary. It must promote the standby and redirect clients. Asynchronous replication can lose acknowledged writes. Synchronous behavior depends on which standbys the system required and which were available. Clients must also resolve uncertain transaction outcomes with idempotency keys or durable operation records.

### When would you choose Serializable isolation?

Use it when transaction correctness depends on reads and writes that constraints or targeted locks cannot easily protect. PostgreSQL detects dangerous dependency patterns. It can abort a transaction rather than allow a nonserializable result. Retry the entire transaction after a serialization failure. Keep external side effects outside the transaction that you can retry.

### 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, limit concurrency, and move suitable reads elsewhere before you distribute data. Shard when a single primary still limits capacity and the access patterns provide a workable shard key.

## Common mistakes

- You call PostgreSQL strongly consistent without specifying isolation, whether reads use the primary, or the guarantees from replication and failover settings.
- You assume read replicas or table partitioning distribute write load across independent primaries.
- You use a read-then-write application check for inventory or uniqueness without a constraint, conditional update, or suitable locks.
- You estimate capacity without considering connection limits, vacuum health, long transactions, or extra index write work.

## Alternatives

- **MySQL with InnoDB:** A conventional relational primary with transactions fits the design. Team expertise, existing tools, or operational requirements favor MySQL. Compare specific isolation, replication, index, and failover behavior. Do not assume either database is faster in every case.
- **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. These systems differ in consistency, transaction, and query capabilities. Choose based on actual access patterns.
- **ClickHouse or a columnar warehouse:** Large scans and aggregations dominate the workload. Keep transactional application state in PostgreSQL. Replicate analytical data when one engine should not serve both workloads.

## Interview questions where it comes up

- [Design a ticket booking system like Ticketmaster](https://sysround.com/questions/design-ticketmaster): Transactions, conditional updates, and uniqueness constraints protect seat allocation when requests occur at the same time.
- [Design a payment system](https://sysround.com/questions/design-payment-system): Transactional ledger updates, idempotency records, and an outbox support correct payment state despite retries and uncertain external outcomes.
- [Design a hotel booking system](https://sysround.com/questions/design-hotel-booking-system): Atomic inventory changes across multiple nights and durable hold state require explicit concurrency control.
- [Design a notification system](https://sysround.com/questions/design-notification-system): A transactional outbox connects authoritative application changes to asynchronous notification delivery without an unsafe dual write.
- [Design a distributed job scheduler](https://sysround.com/questions/design-job-scheduler): Durable job state and short transactions with row locks can coordinate worker claims. Leases and idempotency handle failures.
- [Design Dropbox or Google Drive](https://sysround.com/questions/design-dropbox): PostgreSQL suits transactional file metadata, version records, access permissions, and durable change records. Object storage holds file contents.

## FAQ

### What is PostgreSQL architecture?

PostgreSQL uses backend processes to handle client connections and shared buffers to cache data pages. It uses heap tables and indexes for storage, and WAL for recovery. MVCC controls row visibility. Background processes handle tasks such as checkpoints and vacuum. The usual deployment has one writable primary with optional read replicas.

### Can PostgreSQL scale horizontally?

PostgreSQL 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. When data spans independent servers, you need additional design for routing and cross-shard correctness.

### What is the difference between PostgreSQL Read Committed and Serializable?

Read Committed gives each statement a snapshot of committed data. It does not make a transaction with multiple statements equivalent to one that runs alone. Serializable guarantees a result equivalent to some serial execution of successfully committed transactions. Applications must be ready to retry transactions that fail serialization checks.

## Related concepts

- [MVCC](https://sysround.com/concepts/mvcc)
- [Write-ahead log](https://sysround.com/concepts/write-ahead-log)
- [Database indexing](https://sysround.com/concepts/database-indexing)
- [Database replication](https://sysround.com/concepts/replication)
- [Transaction isolation levels](https://sysround.com/concepts/isolation-levels)


---
SysRound (https://sysround.com) is an AI practice tool for system design interviews. It has a voice or typed AI interviewer and a shared whiteboard. At the end, you get feedback on each skill and a model answer. The AI feedback can be wrong.
