System Design (PostgreSQL): Distruted Relational Database

June 22, 2026

When we store data in a database, it's ultimately written to disk as a collection of files. The main table data is typically stored as a heap file - essentially a collection of rows in no particular order. Think of this like a notebook where you write entries as they come, one after another.

Design Rationale

What is a Data Page?

A database does not store one row at a time on disk. Instead, it groups multiple rows together into a fixed-size block called a page (also called a block in Oracle).

The database always reads and writes entire pages, not individual rows.

Depending on the specific DBMS architecture, page sizes are pre-determined and usually range from 4KB to 64KB. For instance, PostgreSQL defaults to 8KB.

What is Indexing?

Indexes help the database locate rows quickly without scanning the entire table.

An index only stores the indexed column(s) and pointers to the actual table rows. After finding matching entries in the index, the database still has to fetch the corresponding rows from the table.

If we are performing order by operation on a column which has a index on it, the database does not run a sorting operation on the data since the index is already sorted.

B-Tree Indexes

Maintains a balanced tree structure that minimizes the number of disk reads needed to find any piece of data.

A B-tree is a self-balancing tree that maintains sorted data and allows for efficient insertions, deletions, and searches.

When you create a table like this in PostgreSQL:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE
);

PostgreSQL automatically creates two B-tree indexes: one for the primary key and one for the unique email constraint. These B-trees maintain sorted order, which is crucial for both uniqueness checks and range queries.

NOTE: PostgreSQL uses them for almost everything - primary keys, unique constraints, and most regular indexes are all B-trees.

What is Query Execution Plan?

A Query Execution Plan (QEP) is the strategy chosen by the database optimizer to execute a SQL query. For the same query, the database may choose different execution plans depending on the table size, available indexes, data distribution, statistics and join conditions.

What are Database statistics?

Database statistics are metadata collected by the database about tables and indexes, such as the total number of rows, number of distinct values, data distribution, NULL counts, and index cardinality. The query optimizer uses these statistics to estimate how many rows a query will return and to calculate the cost of different execution plans. Based on these estimates, it decides whether to use an Index Seek, Index Scan, or Full Table Scan.

If the statistics become outdated after significant data changes, the optimizer may choose an inefficient execution plan, leading to slower query performance. Updating statistics allows the optimizer to make better decisions.

Follow-up Questions

Q1. You have a table storing market price curves.

MARKET_PRICE
------------
curve_id
trade_date
delivery_date
price

Initially, a query on a table with 100k took 100ms. After the table grew to 10M rows, the same query now takes 5 seconds. What could be the probable reason?

SELECT * 
FROM market_price 
WHERE curve_id = 101
AND trade_date = CURRENT_DATE;

A. Most Probable Reason: Table Size is Growing Daily

If the amount of data being read everyday increases gradually, the runtime of query execution is expected to increase with time.

Possible solutions

  1. Add Appropriate Indexes: If an appropriate index is not present, the optimizer may perform a full table scan, causing query execution time to increase with table size. For example, create a composite index on the curve_id and trade_date columns.

  2. Archive Historical Data: Move old market prices that are rarely accessed to an archive table or data lake.

  3. Partition Table: Partition the table by trade_date (yearly or monthtly).

Q2. A query that consistently executed in 100 ms suddenly started taking 5 seconds, even though no application code changes were deployed. What could be the probable reason?

A. Most Probable Reason: Query Execution Plan Changed

The database optimizer may have selected a different execution plan because the existing index is no longer selective enough or because table statistics are outdated.

An index is most effective when it filters down to a small number of rows. As the data grows or its distribution changes, many rows may start matching the indexed column. In such cases, using the index can become more expensive than scanning the table, causing the optimizer to choose a different execution plan.

Example

Initially:

Total Rows          : 10,000,000
curve_id = 101      : 100 rows
Execution Plan      : Index Seek
Execution Time      : 100 ms

After a bulk data load:

Total Rows          : 12,000,000
curve_id = 101      : 3,000,000 rows
Execution Plan      : Index Scan / Full Table Scan
Execution Time      : 5 sec

Since nearly 25% of the table now matches curve_id = 101, the index is no longer highly selective. The optimizer decides that scanning the table is cheaper than performing millions of index lookups.

Possible solutions

  1. Review Index Selectivity: Redesign indexes if the indexed column is no longer selective. For example, create a composite index using additional filter columns (e.g., curve_id, trade_date) to improve selectivity.

Q3. How to optimize query performance?

  1. Analyze the Query Execution Plan
  2. Create or Optimize Indexes
  3. Reduce the Amount of Data Read: Partition Large Tables, Archive Historical Data
  4. Optimize JOIN Operations: Join indexed columns, Filter data before joining.

Q4. How to write an optimized query?

  1. Select Only the Columns You Need
  2. Filter Data as Early as Possible
  3. Use Indexed Columns in WHERE Clauses
  4. Avoid Functions on Indexed Columns
  5. Avoid Leading Wildcards
  6. Create Composite Indexes for Multiple Filters
  7. Join on Indexed Columns

Q5. So when might indexes actually hurt more than help?

The classic case is a table with frequent writes but infrequent reads.

Q. Write a SQL query to identify customers whose account balance decreased by more than 30% compared to the previous month.

Q. How would you partition a large transaction table to improve query performance while maintaining balanced data distribution?

We can use time-based partitioning, usually by transaction date, and combine it with hash/sub-partitioning if a single time partition becomes too large.

The following steps can be taken to partition the table:

  1. Start with the query patttern. If most queries look like the one below, then transaction_time is a natural partition key.

SELECT * 
FROM transactions
WHERE transaction_time >= '2026-01-01'
    AND transaction_time < '2026-02-01'
    AND account_id = 123456

  1. Create monthly or daily partitions depending on the volume. For example:

transactions
|
|--- transactions_2026_01
|--- transactions_2026_02
|___ ...

  1. Time partitioning alone can become unbalanced if transaction volumes vary significantly by month. For example:

Januuary -> 100M rows
February -> 101M rows
March    -> 105M rows
...
December -> 900M rows

A huge December partition could become a hotspot. To avoid this, we can use composite partitioning. For example, each monthly partition can be further partitioned using a hash of account_id.

             transactions
                   │
             RANGE by date
                   │
       ┌───────────┼───────────┐
       ↓           ↓           ↓
     Aug-26      Sep-26      Oct-26
       │           │           │
    HASH         HASH        HASH
 account_id    account_id   account_id
       │
 ┌─────┼─────┐
 ↓     ↓     ↓
 P0    P1    P2 ... P15