Premium Video

This video is available to premium subscribers only

Unlock Full Access

In this chapter, we'll explore how PostgreSQL works under the hood, how to scale it, and when to consider using it in a system design interview.

Let's start with a few common system design interview problems where PostgreSQL would be a good choice.

What do all these systems have in common? They need well-defined relationships between data, strong consistency, and the ability to run complex queries beyond simple key-based lookups.

This is where relational databases like PostgreSQL work well. PostgreSQL is mature, reliable, and widely available as a managed service from major cloud providers. That makes it a practical choice for many applications.

PostgreSQL is also a great choice to consider in a system design interview because it's a versatile relational database that can handle a wide range of use cases.

Before we dive into how PostgreSQL stores data and handles transactions, let's first understand its overall architecture and how the different components work together.

2.1 Processes and Shared Memory

When PostgreSQL starts, its main server process, traditionally called the postmaster, listens for incoming client connections.

For each new connection, PostgreSQL creates a separate operating system process called a backend. This process handles all the queries coming from that client. So, if a hundred clients connect directly to PostgreSQL, we'll generally have a hundred backend processes running.

These backend processes share a common region of memory:

2.2 How a Query Runs

What happens when a client sends a SQL query?

You can inspect the chosen plan using the EXPLAIN command:

2.3 Background Processes

PostgreSQL also has several background processes that support these operations:

So that's the basic architecture of PostgreSQL. Now let's take a closer look at how PostgreSQL actually stores data inside a table.

PostgreSQL typically stores table data in a structure called a heap, which is divided into fixed-size pages. By default, each page is 8 KB in size.

Rows, also called tuples, are stored inside these pages wherever space is available. PostgreSQL doesn't keep rows physically sorted by a particular key.

So, how does PostgreSQL quickly find a user by their ID or email address? That's what indexes are for.

An index is a separate data structure that stores indexed values along with references to the corresponding rows in the heap. For example, when you define a primary key, PostgreSQL automatically creates a unique index to enforce it.

Since indexes are stored separately from the actual table data, PostgreSQL can use different types of indexes to optimize different query patterns without changing how the rows are physically stored.

Now that we understand how PostgreSQL organizes and finds data, let's look at what happens when we modify it.

A transaction lets us group multiple operations together so that either all of them succeed or none of them take effect.

For example, suppose Alice wants to transfer $100 to Bob. The application starts a transaction, deducts $100 from Alice's account, adds it to Bob's account, and then commits the transaction.

What happens if the server crashes before the transaction commits? Neither change is considered committed, so money can't disappear from one account without reaching the other.

But what if the server crashes right after PostgreSQL confirms that the transaction has committed? Both changes must still be there when the database restarts.

4.1 How WAL Works

PostgreSQL handles this with its write-ahead log, or WAL.

Whenever PostgreSQL modifies data in memory, it also generates WAL records describing those changes. The key rule is that PostgreSQL must write the relevant log records to durable storage before writing the corresponding modified data pages.

When a transaction commits, PostgreSQL normally flushes the WAL records, including the commit record, to durable storage before telling the application that the transaction succeeded.

This means PostgreSQL doesn't have to write the actual data pages to disk immediately. Instead, background processes write these modified pages to disk over time, while checkpoints establish recovery points so PostgreSQL doesn't have to replay the entire log after a restart.

After a crash, PostgreSQL can replay the WAL to recover committed changes that haven't yet been written to the data files.

Because WAL records are written sequentially, PostgreSQL also doesn't need to perform random writes across multiple data pages every time a transaction commits.

And WAL isn't just useful for crash recovery. It also plays a key role in replication, which we'll explore shortly.

Imagine one transaction is updating a product's price while another transaction is trying to read it. If every read had to wait for ongoing updates to finish, the database could spend a lot of time waiting for locks, especially under heavy traffic.

PostgreSQL solves this problem using Multi-Version Concurrency Control, or MVCC.

The basic idea is simple. Instead of overwriting an existing row when it's updated, PostgreSQL creates a new version of that row. Each version contains transaction information that helps PostgreSQL determine when it should be visible to other transactions.

When a transaction reads data, PostgreSQL uses a snapshot to decide which row versions it can see.

So, going back to our example, while one transaction is updating the product's price, another transaction can continue reading the previously committed price without waiting for the update to finish. The reader won't see the uncommitted change, and once the writer commits, subsequent reads can see the new price, depending on their transaction's isolation level.

This allows ordinary reads and writes to happen concurrently without blocking each other, which significantly improves performance when many transactions are running at the same time.

However, MVCC doesn't eliminate locking entirely. If two transactions try to update the same row, one may still need to wait for the other to finish. Similarly, queries that explicitly acquire row locks can also cause blocking.

But there's an important trade-off. Since PostgreSQL creates new row versions whenever data is updated, older versions can accumulate over time. These versions take up storage space, even after they're no longer needed by any transaction.

So PostgreSQL needs a way to clean up these old row versions and make that space available for reuse. And that brings us to our next topic: VACUUM.

Every time PostgreSQL updates a row, the older version remains in the table. Even when a row is deleted, it still occupies space, although new transactions can no longer see it.

Over time, these old row versions accumulate. Once they're no longer needed by any transaction, PostgreSQL can reclaim their space. This cleanup process is called VACUUM.

PostgreSQL also has an automatic background process called autovacuum, which handles this cleanup based on how much a table has changed and the configured thresholds.

A regular VACUUM makes the reclaimed space available for future writes within the same table, but it usually doesn't return that space to the operating system.

6.1 Bloat

What happens if PostgreSQL can't clean up old row versions fast enough? Over time, tables and indexes can grow much larger than they need to be. This is known as bloat.

Bloat not only wastes storage space but can also slow down queries because PostgreSQL may need to read more data pages than necessary.

Long-running transactions can make this problem worse because they can prevent PostgreSQL from removing older row versions that are still potentially visible to those transactions.

6.2 Frequently Updated Data

This becomes especially important when we're dealing with data that changes very frequently.

For example, imagine we're building a ride-sharing application where drivers update their locations every few seconds. If we store every location update directly in PostgreSQL by repeatedly updating the same rows, each update creates a new row version, increasing the amount of cleanup work the database needs to perform.

Depending on our requirements, a better approach might be to keep the latest driver locations in Redis, where frequent updates can be handled efficiently, and periodically persist them to PostgreSQL if we need durable storage.

Snapshots determine which data a transaction can see. Isolation levels control how PostgreSQL uses these snapshots and which concurrency problems it prevents.

7.1 Read Committed

By default, PostgreSQL uses Read Committed isolation. At this level, every SQL statement gets a fresh snapshot of committed data.

This means that if you run the same query twice within a transaction, you might get different results if another transaction commits changes between those queries.

7.2 Repeatable Read

The next level is Repeatable Read. Here, PostgreSQL creates a snapshot when the transaction executes its first query or data-changing statement and continues using that same snapshot throughout the transaction.

So, even if other transactions commit changes in the meantime, your transaction still sees a consistent view of the data, along with any changes it makes itself.

However, there's an important detail. If your transaction tries to update a row that another transaction has already modified since your snapshot was taken, PostgreSQL may reject the operation with a serialization error. When this happens, the application needs to retry the entire transaction.

7.3 Serializable

Finally, we have Serializable, the strongest isolation level.

At this level, PostgreSQL monitors dependencies between concurrent transactions to detect situations that could lead to inconsistent results. If it detects a dangerous pattern, it may abort one of the transactions to prevent the problem.

This ensures that all successfully committed transactions produce the same result as if they had executed one after another, even though they were actually running concurrently.

Of course, this comes with additional overhead, and the application must be prepared to retry transactions that get aborted.

7.4 Read Committed Isn't Enough on Its Own

Read Committed works well for many applications, but it doesn't automatically prevent every concurrency problem. We still need to be careful about how we read and update shared data, especially when multiple transactions are modifying it at the same time.

Let's understand why with a common example.

Suppose Alice has $500 in her bank account, and two withdrawal requests try to withdraw $100 at the same time.

Both requests read the current balance as $500. Each one subtracts $100 in the application code and writes the updated balance as $400.

But there's a problem. Both withdrawals have been processed, so the correct balance should actually be $300. Instead, the database shows $400 because one update overwrote the other. This is known as a lost update.

The problem happens because we're reading the balance, calculating a new value in our application, and then writing it back. Another transaction can modify the balance between these steps.

So, how do we prevent this? There are three common approaches.

8.1 Atomic Updates

The first, and often the simplest, is to use an atomic update. Instead of calculating the new balance in our application code, we tell PostgreSQL to subtract $100 directly from the current balance.

Under Read Committed isolation, PostgreSQL locks the row during the update. If another transaction tries to update the same row, it has to wait until the first transaction finishes. Once the first transaction commits, the second update subtracts its amount from the latest balance instead of using the old value.

We can also add a condition to make sure the account has enough money before processing the withdrawal. For example, we only subtract $100 if the balance is at least $100. The application can then check whether a row was actually updated to determine if the withdrawal succeeded.

8.2 SELECT FOR UPDATE

The second approach is to use SELECT FOR UPDATE. Here, the transaction explicitly locks the row before reading the balance and deciding what to do.

The lock remains in place until the transaction finishes, so other transactions trying to update or lock the same row have to wait.

This is especially useful when an operation involves multiple steps that depend on the current balance, and we need to make sure another transaction doesn't change it while we're making those decisions.

8.3 Optimistic Concurrency Control

The third approach is optimistic concurrency control. Instead of locking the row in advance, we add a version number to each row.

When we update the balance, we also check whether the version number still matches the one we originally read. If it matches, PostgreSQL updates the balance and increments the version number.

But if no row gets updated, it means the version has changed, usually because another transaction modified the data in the meantime. In that case, the application can read the latest balance, check the business rules again, and retry the operation.

Row locks are also useful when we use PostgreSQL as a job queue.

Imagine we have a table of pending jobs, and multiple workers are trying to pick up jobs for processing. Normally, if every worker tries to lock the oldest pending job using SELECT FOR UPDATE, they could all end up waiting for the same row.

9.1 SKIP LOCKED

This is where the SKIP LOCKED option becomes useful. With SELECT FOR UPDATE SKIP LOCKED, a worker skips jobs that are already locked by other transactions and moves on to the next available job.

This allows multiple workers to pick up different jobs and process them concurrently, without blocking each other.

9.2 Making Sure Jobs Aren't Lost

But there's another important question: how do we make sure jobs aren't lost if a worker crashes?

For short tasks that only involve database operations, we can process the job, mark it as completed, and commit everything in a single transaction, as shown above. If the worker crashes before committing, PostgreSQL automatically rolls back the transaction, and the job becomes available for another worker to pick up.

However, for long-running tasks, keeping a database transaction open throughout the entire processing time isn't a good idea. Instead, a worker can claim a job in a short transaction, commit that claim, and then process the job separately.

We can also use a lease or timeout mechanism so that if a worker crashes or stops responding, the job eventually becomes available for another worker to retry.

Since retries can cause a job to run more than once, the processing logic should also be idempotent whenever possible.

Using PostgreSQL as a job queue works well for many applications with moderate workloads, especially when we want to avoid introducing another system. However, as the number of jobs and workers grows, a dedicated message queue might become a better choice.

Transactions and locks help us coordinate operations that happen at the same time. But PostgreSQL also provides another important way to keep our data correct: database constraints.

Constraints let us define rules that our data must follow, and PostgreSQL automatically enforces these rules whenever data is inserted or updated.

Unique constraints

A unique constraint prevents duplicate values in a column or a combination of columns. For example, in a ticket booking system, we can add a unique constraint on the show ID and seat ID to make sure the same seat can't be booked twice for the same show.

Foreign keys

Foreign keys ensure that relationships between tables remain valid. For example, if an order references a customer, PostgreSQL can enforce that the customer actually exists in the database. This prevents us from creating orders that reference nonexistent customers.

Check constraints

Check constraints enforce specific conditions on the data. For example, we can add a check constraint to make sure an account balance never goes below zero. If an insert or update violates this rule, PostgreSQL rejects the operation.

Exclusion constraints

PostgreSQL also supports exclusion constraints, which are particularly useful when we need to prevent overlapping values. For example, imagine we're building a hotel booking system. We can represent each reservation as a date range and use an exclusion constraint to make sure two reservations for the same room don't overlap.

10.1 Why Constraints Matter

The biggest advantage of database constraints is that these rules are enforced directly by PostgreSQL, regardless of where the write comes from. Whether it's an API request, a background worker, or an administrative script, the same rules apply.

This gives us an additional layer of protection and helps prevent invalid data from entering the database, even when our application code has bugs.

Now that we've covered how PostgreSQL keeps data consistent and reliable, let's move on to another important topic: query performance.

One of the most effective ways to improve query performance is through proper indexing. Without the right indexes, PostgreSQL may have to scan an entire table just to find a few matching rows. As the table grows, these scans become increasingly expensive and can significantly slow down queries.

The most common index type in PostgreSQL is the B-tree. It stores keys in sorted order using a balanced tree structure, making it efficient for both exact lookups, like finding a user by their email address, and range queries, like fetching all orders placed in the last seven days.

11.1 Composite Indexes

PostgreSQL also supports composite indexes, which combine multiple columns into a single index. However, the order of these columns matters because it affects which queries can use the index efficiently.

A good starting point is to place columns used for equality filters first, followed by columns used for range queries or sorting.

There are two more useful indexing features worth understanding for system design interviews: partial indexes and covering indexes.

12.1 Partial Indexes

A partial index only includes rows that match a specific condition.

For our job queue table, we could create an index on creation time that only includes jobs with a pending status. This allows workers to quickly find pending jobs without having to search through all the completed ones.

And if most jobs have already been completed, the partial index will be much smaller than an index covering every job, making it more efficient to maintain and search.

12.2 Covering Indexes

The second feature is a covering index. The idea is simple. If an index contains all the columns a query needs, PostgreSQL may be able to return the results directly from the index without reading the actual table rows.

PostgreSQL supports this through the INCLUDE option, which lets us store additional columns in an index without making them part of the search key.

This can enable something called an index-only scan, which reduces the number of times PostgreSQL needs to access the actual table.

However, there's one important detail. PostgreSQL still needs to check whether the rows are visible to the current transaction. It uses a visibility map to determine whether all tuples on a heap page are visible to every transaction. If they are, PostgreSQL can skip reading that page from the heap. Otherwise, it still needs to access the table to check visibility.

12.3 The Cost of Indexes

Finally, there's an important trade-off to keep in mind. While indexes can make queries much faster, they also add overhead to writes. Every insert needs to update the relevant indexes, and updates and deletes can also require additional index maintenance.

So, creating an index on every column isn't necessarily a good idea. Instead, focus on the queries your application runs most frequently and create indexes based on the actual access patterns.

Some types of data need more than a regular B-tree index.

13.1 GIN Indexes

One useful option in PostgreSQL is the GIN index, which stands for Generalized Inverted Index. Instead of indexing an entire value as a single unit, a GIN index indexes its individual components and keeps track of which rows contain them.

This makes it particularly useful for data types like arrays, JSONB, and full-text search.

13.2 JSONB

JSONB allows PostgreSQL to store JSON data in an efficient binary format. It's especially useful when different records need to store different attributes.

For example, in an e-commerce application, a shirt might have attributes like size and color, while a laptop might have RAM and screen size. Instead of creating separate columns for every possible attribute, we can store these product-specific details in a JSONB column.

This gives us more flexibility without having to change the table schema whenever we introduce a new product category.

13.3 GiST Indexes

PostgreSQL also supports another index type called GiST, which stands for Generalized Search Tree. It's particularly useful for data types like ranges and spatial data.

For example, in a hotel booking system, we can use a GiST-backed exclusion constraint to prevent overlapping reservations for the same room (see the reservations table in the constraints section). This allows PostgreSQL to enforce the rule directly at the database level, even when multiple users try to book the same room at the same time.

Extensions allow us to add new data types, functions, and indexing capabilities to PostgreSQL without having to switch to a different database.

14.1 PostGIS

One popular example is PostGIS, which adds support for geographic and geometric data.

For example, imagine we're building a service like Yelp. We can use PostGIS to store business locations, create spatial indexes, and efficiently find restaurants within 2 kilometers of a user's location.

14.2 pgvector

Another useful extension is pgvector, which lets us store vector embeddings and perform similarity searches directly in PostgreSQL.

For example, we can store document embeddings alongside our regular application data and use them to find semantically similar documents.

pgvector also supports approximate nearest-neighbor indexes like HNSW, which can make similarity searches much faster as the number of vectors grows.

14.3 Full-Text Search

PostgreSQL also comes with built-in full-text search capabilities using tsvector and tsquery. It can break text into searchable terms, create GIN indexes to make searches efficient, and rank matching documents based on their relevance to a search query.

14.4 One Database or Specialized Systems?

The main advantage of these capabilities is that we can support different types of workloads within the same database instead of introducing a separate system for each one. This keeps our architecture simpler and reduces the operational overhead of managing multiple databases.

However, as our application grows, there may come a point where specialized systems become a better choice. For example, Elasticsearch provides more advanced search capabilities and is designed to distribute search workloads across multiple nodes. Similarly, a dedicated vector database might be a better fit when we're working with billions of embeddings or need to support large-scale similarity searches with strict performance requirements.

Before we talk about how PostgreSQL scales, there's one important topic we need to understand: database connections.

In PostgreSQL, each client connection gets its own backend process, which consumes memory and other server resources. By default, PostgreSQL typically allows up to 100 concurrent connections (max_connections).

We can increase this limit, but allowing more connections doesn't necessarily improve performance. In fact, too many active connections can increase resource contention and slow down the database.

So, how do we handle this? One common solution is to use a connection pooler like PgBouncer. Instead of maintaining a dedicated database connection for every client, PgBouncer manages a smaller pool of connections that can be shared across multiple clients.

One popular approach is called transaction pooling. In this mode, a client is assigned a database connection when it starts a transaction. Once the transaction finishes, the connection is returned to the pool and can be reused by another client.

This allows hundreds or even thousands of clients to share a much smaller number of actual PostgreSQL connections. If all connections are busy, additional requests wait until one becomes available.

Using connection pooling helps keep resource usage under control and allows PostgreSQL to handle a large number of clients more efficiently.

A single PostgreSQL server can handle much more traffic than you might expect. With the right hardware and well-optimized queries, it can process thousands or even tens of thousands of transactions per second. It can also store several terabytes of data on a single machine.

Of course, these are just rough estimates, not fixed limits. Actual performance depends on several factors:

As our application grows and traffic increases, we may eventually need to scale the database to handle more reads and writes. However, scaling reads and scaling writes are two different challenges, and each requires a different approach.

Let's start by looking at how we can scale reads.

Read replicas help us distribute read traffic across multiple database servers instead of sending every query to the primary.

PostgreSQL supports this through streaming replication. The primary sends write-ahead log records to standby servers, which replay these changes to keep their copies of the data up to date. These replicas can then serve read queries, reducing the load on the primary.

17.1 Asynchronous Replication and Replication Lag

By default, PostgreSQL uses asynchronous replication. This means the primary can commit a transaction without waiting for the replicas to receive or apply the changes.

As a result, replicas may fall behind the primary, depending on network conditions and how quickly they can process the incoming changes. This delay is called replication lag, and it means replicas may temporarily return stale data.

One common way to handle this is to route a user's reads to the primary for a short period after they make a change, so they can immediately see their updated data.

17.2 Synchronous Replication

PostgreSQL also supports synchronous replication. In this mode, the primary waits for designated replicas to acknowledge the WAL records before confirming a transaction has committed.

Depending on the configuration, this acknowledgment can mean the records have been received, written to durable storage, or applied:

This provides stronger guarantees during failover, but it comes with a trade-off. Waiting for replicas increases write latency, and transactions may be delayed or blocked if the required replicas become unavailable.

Read replicas can also help us recover from database failures. If the primary becomes unavailable, we can promote one of the standby replicas to become the new primary and start accepting writes.

PostgreSQL provides the mechanisms for replication and promotion, but it doesn't handle automatic failover on its own. For that, we typically use tools like Patroni or a managed database service. These systems can detect primary failures, coordinate the failover process, and help applications connect to the new primary.

18.1 Split Brain

However, there's an important challenge we need to handle. We must make sure the old primary stops accepting writes before the new primary takes over.

Otherwise, we could end up with two servers acting as the primary at the same time, potentially accepting conflicting writes. This situation is known as split brain, and preventing it is an important part of designing a reliable failover system.

18.2 Data Loss During Failover

Another concern is data loss. As we discussed earlier, PostgreSQL uses asynchronous replication by default, which means replicas can lag behind the primary. If the primary crashes and we promote a replica, that replica might not have received some transactions that were already committed on the old primary.

Whether this is acceptable depends on the application's requirements.

For systems where losing committed transactions is unacceptable, such as payment systems, we can use synchronous replication along with a failover policy that only promotes a replica containing all acknowledged transactions. This provides stronger durability guarantees, but it also means we may have to sacrifice write availability if no sufficiently up-to-date replica is available.

Another useful feature PostgreSQL provides for handling large amounts of data is table partitioning. Partitioning allows us to split a large logical table into smaller physical tables based on ranges, specific values (lists), or hashes.

For example, imagine we have an orders or events table containing several years of data. Instead of keeping everything in one large table, we could partition it by month, so each month's data is stored in a separate partition.

19.1 Partition Pruning

The application can still query the parent table as if it were a single table. But when a query includes a date filter, PostgreSQL can identify which partitions contain the relevant data and skip the rest.

This optimization is called partition pruning, and it can significantly reduce the amount of data PostgreSQL needs to scan.

19.2 Dropping Old Data

Partitioning also makes managing old data much easier. For example, suppose we're building a logging system where we only need to keep the last ninety days of logs. Instead of deleting millions of old rows individually, we can remove entire partitions that are no longer needed.

19.3 Partitioning Stays on One Server

PostgreSQL's native partitioning doesn't distribute data across multiple database servers. All the partitions still share the same server's CPU, memory, and storage resources.

Once that server can no longer handle the workload, we may need to take the next step: distributing data across multiple servers.

Sharding allows us to distribute data across multiple PostgreSQL servers, with each server responsible for storing and managing a portion of the data.

PostgreSQL doesn't automatically distribute tables across multiple servers, so we need additional mechanisms to implement sharding:

20.1 Choosing a Shard Key

Regardless of the approach, choosing the right shard key is one of the most important decisions. Ideally, we want a key that keeps related data together while distributing the workload evenly across servers.

For example, imagine we're building a multi-tenant SaaS application. We could shard the data by tenant ID, so each customer's data stays on the same shard. This allows most queries and transactions for that customer to run within a single database, without involving other shards.

Now, let's consider a different example. Suppose we shard an orders table using a hash of the order ID. In this case, a single user's orders could be spread across multiple shards. So, when we want to fetch that user's complete order history, we might need an additional lookup structure or have to query multiple databases and combine the results.

20.2 The Cost of Sharding

And this is where sharding starts to introduce complexity:

That's why we shouldn't jump straight into sharding just because our PostgreSQL database is getting slower. Instead, we should first explore simpler optimizations like indexes, caching, read replicas, and partitioning. If these approaches aren't enough and a single server can no longer handle the workload, then sharding becomes a reasonable next step.

PostgreSQL is a great choice for many applications, but there are situations where another database might be a better fit.

Very high write volume

If we're building a system that needs to handle millions of events per second, a distributed database like Cassandra might be a better choice, depending on our query patterns and consistency requirements.

Key-based access

If most of our operations involve reading or writing data using a specific key, DynamoDB can be a good option. It handles partitioning and scaling for us, although we still need to choose our partition keys carefully to avoid hot partitions.

Multi-region writes

A typical PostgreSQL deployment has a single writable primary. So if users are spread across different regions, those far from the primary may experience higher write latency.

Distributed SQL databases like CockroachDB and Spanner offer more flexibility in distributing data and handling writes across regions. However, strongly consistent writes still require coordination between nodes, which adds latency.

Large analytical workloads

For large analytical workloads, where we need to scan billions of rows and perform complex aggregations, column-oriented systems like BigQuery or ClickHouse are often a better fit.

21.1 PostgreSQL as the Source of Truth

Choosing a specialized database doesn't mean we need to replace PostgreSQL entirely. We can continue using PostgreSQL as our primary transactional database and use change data capture tools like Debezium to send updates to other systems that are better suited for specific workloads.

This way, PostgreSQL remains the source of truth for our transactional data, while specialized systems handle workloads like search, analytics, or large-scale data processing.

Now, let's bring everything together and see how you can apply what we've learned in a system design interview.

PostgreSQL is a strong choice for systems that need relational data, flexible queries, and reliable transactions.