What is database partitioning, and what are the types | Database & SQL

Definition

Partitioning splits one logical table into smaller physical pieces: horizontally by rows (range/list/hash, and sharding when across servers) or vertically by columns.

Partitioning – Splitting one large logical table into smaller physical pieces (partitions), while the application still sees a single table.
Horizontal partitioning – Divides by rows: each partition has the same columns but holds a disjoint subset of rows chosen by a partition key (range, list, or hash).
Sharding – Horizontal partitioning across multiple servers, scaling writes and storage beyond a single machine but adding distributed-system complexity.
Vertical partitioning – Divides by columns: a wide table is split into narrower tables sharing the same primary key.

The National Bank's single giant transaction ledger is unwieldy. Partitioning splits it into smaller books. Horizontal partitioning puts different rows in different books: January transactions in one, February in another. If those books sit in different branch buildings, that is sharding. Vertical partitioning instead splits the columns: the frequently-read account summary in one slim book and the rarely-read KYC details in another. To the teller it still feels like one ledger.

Understanding the Concepts

What is database partitioning?

Database partitioning splits one large logical table into smaller physical pieces called partitions. The application keeps using one table name, and the database engine routes queries to the appropriate partitions transparently. Partitioning exists because very large tables become hard to manage and slow to query, back up, and maintain.

SQL

-- At National Bank of India, the transactions table has 500 million rows
-- Partitioning splits it into manageable monthly pieces

CREATE TABLE transactions (
    txn_id BIGSERIAL,
    account_id VARCHAR(20) NOT NULL,
    amount NUMERIC(15,2) NOT NULL,
    txn_type VARCHAR(10) NOT NULL,
    created_at TIMESTAMP NOT NULL
) PARTITION BY RANGE (created_at);

-- Each partition holds one month
CREATE TABLE transactions_2024_01 PARTITION OF transactions
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE transactions_2024_02 PARTITION OF transactions
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
CREATE TABLE transactions_2024_03 PARTITION OF transactions
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

-- Queries use the same table name
SELECT * FROM transactions WHERE created_at >= '2024-02-01' AND created_at < '2024-03-01';
-- The planner prunes to transactions_2024_02 only

What is horizontal partitioning?

Horizontal partitioning divides by rows. Every partition has the same columns but holds a disjoint subset of the rows, chosen by a partition key. There are three common schemes.

SQL

-- Range partitioning: contiguous key ranges
-- Best for time-series data and date-filtered queries
CREATE TABLE txn_range (
    txn_id BIGSERIAL, account_id VARCHAR(20),
    amount NUMERIC(15,2), created_at TIMESTAMP
) PARTITION BY RANGE (created_at);

-- List partitioning: explicit value sets
-- Best for discrete categories like region or account type
CREATE TABLE txn_by_region (
    txn_id BIGSERIAL, account_id VARCHAR(20),
    amount NUMERIC(15,2), region VARCHAR(10)
) PARTITION BY LIST (region);

CREATE TABLE txn_north PARTITION OF txn_by_region FOR VALUES IN ('NORTH');
CREATE TABLE txn_south PARTITION OF txn_by_region FOR VALUES IN ('SOUTH');
CREATE TABLE txn_east  PARTITION OF txn_by_region FOR VALUES IN ('EAST');
CREATE TABLE txn_west  PARTITION OF txn_by_region FOR VALUES IN ('WEST');

-- Hash partitioning: hash of key modulo partition count
-- Best for even distribution with no natural range or list
CREATE TABLE txn_by_hash (
    txn_id BIGSERIAL, account_id VARCHAR(20),
    amount NUMERIC(15,2)
) PARTITION BY HASH (account_id);

CREATE TABLE txn_hash_0 PARTITION OF txn_by_hash FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE txn_hash_1 PARTITION OF txn_by_hash FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE txn_hash_2 PARTITION OF txn_by_hash FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE txn_hash_3 PARTITION OF txn_by_hash FOR VALUES WITH (MODULUS 4, REMAINDER 3);

JAVA

package com.bank.service;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;

public class PartitionedQueryService {

    private final DataSource dataSource;

    public PartitionedQueryService(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    // This query benefits from partition pruning
    // The planner scans only the February partition
    public BigDecimal getMonthlyTotal(String accountId) throws SQLException {
        String sql = "SELECT SUM(amount) FROM transactions "
                   + "WHERE account_id = ? "
                   + "AND created_at >= '2024-02-01' AND created_at < '2024-03-01'";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, accountId);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() ? rs.getBigDecimal(1) : BigDecimal.ZERO;
            }
        }
    }
}

What is sharding?

Sharding is horizontal partitioning taken across multiple servers. Instead of partitions being separate pieces inside one database instance, each shard is a separate database on its own node. This scales writes and storage beyond a single machine.

DIAGRAM

Partitioning within one database:
┌─────────────────────────────────────────────┐
│ PostgreSQL Instance                          │
│ ┌──────────┐ ┌──────────┐ ┌──────────┐     │
│ │ Jan 2024 │ │ Feb 2024 │ │ Mar 2024 │     │
│ │ partition │ │ partition │ │ partition │     │
│ └──────────┘ └──────────┘ └──────────┘     │
│ Joins work. Transactions work. One backup.  │
└─────────────────────────────────────────────┘

Sharding across servers:
┌──────────┐  ┌──────────┐  ┌──────────┐
│ Server 1 │  │ Server 2 │  │ Server 3 │
│ Shard A  │  │ Shard B  │  │ Shard C  │
│ Accts 0-N│  │ Accts N+ │  │ Accts M+ │
└──────────┘  └──────────┘  └──────────┘
Cross-shard joins? Scatter-gather.
Cross-shard transactions? 2PC or Saga.
Separate backups per shard.

What is vertical partitioning?

Vertical partitioning divides by columns rather than rows. A wide table with many columns is split into narrower tables sharing the same primary key.

SQL

-- The accounts table carries a large KYC blob loaded on every balance check
-- Vertical partition: split hot columns from cold columns

-- Hot table: accessed on every request
CREATE TABLE accounts_hot (
    account_id VARCHAR(20) PRIMARY KEY,
    holder_name VARCHAR(100) NOT NULL,
    balance NUMERIC(15,2) NOT NULL,
    account_type VARCHAR(20) NOT NULL
);

-- Cold table: accessed rarely (KYC verification)
CREATE TABLE accounts_cold (
    account_id VARCHAR(20) PRIMARY KEY,
    kyc_document BYTEA,           -- Large binary blob
    address_json JSONB,           -- Detailed address
    notes TEXT,                   -- Free-form notes
    FOREIGN KEY (account_id) REFERENCES accounts_hot(account_id)
);

-- Balance check: reads only the narrow hot table
SELECT balance FROM accounts_hot WHERE account_id = 'SAV2001';
-- Fast: small rows, more rows per page, better cache hit rate

-- KYC check: joins hot + cold only when needed
SELECT h.holder_name, c.kyc_document
FROM accounts_hot h
JOIN accounts_cold c ON h.account_id = c.account_id
WHERE h.account_id = 'SAV2001';

JAVA

package com.bank.service;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;

public class VerticalPartitionService {

    private final DataSource dataSource;

    public VerticalPartitionService(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    // Fast: reads only the narrow hot table
    public BigDecimal getBalance(String accountId) throws SQLException {
        String sql = "SELECT balance FROM accounts_hot WHERE account_id = ?";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, accountId);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() ? rs.getBigDecimal("balance") : BigDecimal.ZERO;
            }
        }
    }
}

How does partition pruning work?

Partition pruning is the planner's ability to eliminate partitions that cannot contain matching rows by examining the WHERE clause.

SQL

EXPLAIN
SELECT * FROM transactions
WHERE created_at >= '2024-02-01' AND created_at < '2024-03-01';

-- Output:
-- Append
--   -> Seq Scan on transactions_2024_02
--        Filter: (created_at >= ... AND created_at < ...)
-- (transactions_2024_01 and transactions_2024_03 are PRUNED)

What are the trade-offs of each type?

TEXT

| Type       | Divides | Use Case                    | Benefit            |
|------------|---------|-----------------------------|--------------------||
| Range      | Rows    | Time-series, date queries   | Pruning + archival |
| List       | Rows    | Region, type, category      | Pruning by value   |
| Hash       | Rows    | Even spread, equality query | No hot spots       |
| Vertical   | Columns | Wide tables, hot vs cold    | Narrow hot table   |
| Sharding   | Rows    | Write scale beyond 1 node   | Horizontal scale   |

What are common interview follow-up questions?

When should you use partitioning versus sharding?
How does partition pruning work with composite partition keys?
What happens to foreign key constraints across partitions?
How do you add or remove partitions in a live production system?

What should a senior engineer know?

A senior engineer should understand that partitioning within one instance has none of the cross-shard challenges. Joins, transactions, and foreign keys all work normally across partitions within the same database. Sharding introduces distributed-systems complexity that single-instance partitioning avoids.

DIAGRAM

Decision tree:
  Table too big to manage/query efficiently?
    → Partition within one database (range/list/hash)
    → Retains joins, transactions, foreign keys

  Write throughput or data size exceeds one server?
    → Shard across servers
    → Loses cross-shard joins, transactions, FKs

  Table is wide with hot and cold columns?
    → Vertical partition (split columns)
    → Hot table stays narrow and fast

Visual Overview

DIAGRAM

┌────────────────────────────────────────────────────────────┐
│            Database Partitioning Types                    │
├────────────────────────────────────────────────────────────┤
│                                                            │
│  Horizontal (by rows):                                    │
│  ┌──────────┐ ┌──────────┐ ┌──────────┐                  │
│  │ Jan 2024 │ │ Feb 2024 │ │ Mar 2024 │  Range           │
│  │ rows     │ │ rows     │ │ rows     │                  │
│  └──────────┘ └──────────┘ └──────────┘                  │
│                                                            │
│  ┌──────┐ ┌──────┐ ┌──────┐ ┌──────┐                     │
│  │NORTH │ │SOUTH │ │EAST  │ │WEST  │  List               │
│  └──────┘ └──────┘ └──────┘ └──────┘                     │
│                                                            │
│  ┌──────┐ ┌──────┐ ┌──────┐ ┌──────┐                     │
│  │ h%4=0│ │ h%4=1│ │ h%4=2│ │ h%4=3│  Hash               │
│  └──────┘ └──────┘ └──────┘ └──────┘                     │
│                                                            │
│  Vertical (by columns):                                   │
│  ┌─────────────┐  ┌──────────────────┐                    │
│  │ accounts_hot│  │ accounts_cold    │                    │
│  │ id, name,   │  │ id, kyc_blob,    │                    │
│  │ balance     │  │ address, notes   │                    │
│  └─────────────┘  └──────────────────┘                    │
│                                                            │
│  Sharding (horizontal across servers):                    │
│  ┌──────────┐  ┌──────────┐  ┌──────────┐                │
│  │ Server 1 │  │ Server 2 │  │ Server 3 │                │
│  │ Shard A  │  │ Shard B  │  │ Shard C  │                │
│  └──────────┘  └──────────┘  └──────────┘                │
└────────────────────────────────────────────────────────────┘

Key Takeaway

Database partitioning splits one large logical table into smaller physical pieces while the application still sees a single table. It exists because very large tables become slow to query, back up, and maintain, so keeping each piece manageable improves performance and operations.

Horizontal partitioning divides by rows, with three common schemes. Range partitioning gives each partition a contiguous range of the key, such as one month, which is ideal for time-series data and cheap archival. List partitioning gives each partition an explicit set of values, such as one per region. Hash partitioning assigns rows by a hash of the key for even distribution when there is no natural range. Vertical partitioning divides by columns: hot columns every request needs stay in one narrow table, cold or large columns move to a side table, keeping the hot rows small and fast.

Sharding is horizontal partitioning across multiple servers, where each shard is a separate database on its own node. It scales writes and storage beyond one machine but adds cross-shard query and transaction complexity that single-instance partitioning avoids. All shards are horizontal partitions, but partitions within one instance are not shards.

At the National Bank of India, Rajesh range-partitioned the transactions table by month, so date-filtered queries prune to a single partition and old months can be archived by dropping one partition. The accounts table was vertically partitioned: the narrow hot table with account_id, holder_name, and balance served every balance check, while the cold table with KYC documents was joined only when needed.

Remember one sentence: partitioning splits one logical table into smaller physical pieces: horizontally by rows using range, list, or hash, becoming sharding across servers, or vertically by columns.

---