Definition
- Partition pruning – The planner's ability to eliminate partitions that cannot contain matching rows by examining the WHERE clause filter against the partition key.
- Precondition – The query must filter on the partition key for pruning to work; without it, every partition is scanned.
- Wrong-key trap – A table partitioned by date but queried by account_id (not the partition key) scans every partition, often slower than a plain indexed table.
- Match the access pattern – Choose range for time ranges, list for discrete categories, hash for even-spread equality lookups, and composite for multi-dimension queries.
The National Bank split its transaction ledger into one book per month (partitioned by date). If a teller asks 'show me February's transactions,' he grabs only the February book: fast. But if he asks 'show me all of Rajesh's transactions' and the books are organized by month, he must open every monthly book and search each one. Partitioning by the wrong thing for your questions makes you search everything anyway.
Understanding the Concepts
Why does partitioning not automatically make queries faster?
Partitioning is primarily a manageability feature. It only speeds up queries through partition pruning, which has a strict precondition: the query must filter on the partition key.
-- Table partitioned by date into monthly partitions
CREATE TABLE transactions (
txn_id BIGSERIAL, account_id VARCHAR(20),
amount NUMERIC(15,2), created_at TIMESTAMP
) PARTITION BY RANGE (created_at);
-- Query WITH partition key filter: FAST (pruning works)
EXPLAIN
SELECT * FROM transactions
WHERE created_at >= '2024-02-01' AND created_at < '2024-03-01';
-- Append
-- -> Seq Scan on transactions_2024_02 ← only February scanned
-- Query WITHOUT partition key filter: SLOW (no pruning)
EXPLAIN
SELECT * FROM transactions
WHERE account_id = 'SAV2001';
-- Append
-- -> Seq Scan on transactions_2024_01
-- -> Seq Scan on transactions_2024_02
-- -> Seq Scan on transactions_2024_03
-- -> ... (EVERY partition scanned!)
What is the wrong-key trap?
If the table is partitioned by date but the dominant query filters by account_id with no date filter, the planner cannot know which months contain Rajesh's transactions. It must scan every partition, which is often slower than an unpartitioned table with a good index.
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 WrongPartitionKeyService {
private final DataSource dataSource;
public WrongPartitionKeyService(DataSource dataSource) {
this.dataSource = dataSource;
}
// SLOW: partitioned by date, but queried by account_id
// The planner scans every monthly partition
public BigDecimal getAllTransactionsForAccount(String accountId)
throws SQLException {
String sql = "SELECT SUM(amount) FROM transactions "
+ "WHERE account_id = ?";
// Without a date filter, EVERY partition is scanned
// With 36 monthly partitions: 36 index lookups instead of 1
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;
}
}
}
// FAST: include the partition key in the query
public BigDecimal getMonthlyTotal(String accountId, String start, String end)
throws SQLException {
String sql = "SELECT SUM(amount) FROM transactions "
+ "WHERE account_id = ? "
+ "AND created_at >= ?::date AND created_at < ?::date";
// With date filter, planner prunes to one partition
try (Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, accountId);
ps.setString(2, start);
ps.setString(3, end);
try (ResultSet rs = ps.executeQuery()) {
return rs.next() ? rs.getBigDecimal(1) : BigDecimal.ZERO;
}
}
}
}
When should you use range partitioning?
Range partitioning fits a continuous, ordered key where queries filter by ranges. Time-based partitioning is the classic use case.
-- Range partitioning by date: perfect for
-- "Show me transactions between March 1 and March 31"
CREATE TABLE transactions_range (
txn_id BIGSERIAL, account_id VARCHAR(20),
amount NUMERIC(15,2), created_at TIMESTAMP
) PARTITION BY RANGE (created_at);
-- Bonus: cheap archival by dropping old partitions
ALTER TABLE transactions_range DETACH PARTITION transactions_2023_01;
DROP TABLE transactions_2023_01; -- Instant, no row-by-row delete
When should you use list partitioning?
List partitioning fits a discrete key with a small set of known values.
-- List partitioning by region
CREATE TABLE accounts_by_region (
account_id VARCHAR(20), holder_name VARCHAR(100),
balance NUMERIC(15,2), region VARCHAR(10)
) PARTITION BY LIST (region);
CREATE TABLE accounts_north PARTITION OF accounts_by_region
FOR VALUES IN ('NORTH');
CREATE TABLE accounts_south PARTITION OF accounts_by_region
FOR VALUES IN ('SOUTH');
-- Query: "Show all accounts in SOUTH region"
SELECT * FROM accounts_by_region WHERE region = 'SOUTH';
-- Only accounts_south is scanned (pruning works)
When should you use hash partitioning?
Hash partitioning fits a high-cardinality key with no natural ordering where you mainly do equality lookups.
-- Hash partitioning by account_id
CREATE TABLE transactions_hash (
txn_id BIGSERIAL, account_id VARCHAR(20),
amount NUMERIC(15,2)
) PARTITION BY HASH (account_id);
CREATE TABLE txn_h0 PARTITION OF transactions_hash
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE txn_h1 PARTITION OF transactions_hash
FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE txn_h2 PARTITION OF transactions_hash
FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE txn_h3 PARTITION OF transactions_hash
FOR VALUES WITH (MODULUS 4, REMAINDER 3);
-- Query: "Show transactions for SAV2001"
SELECT * FROM transactions_hash WHERE account_id = 'SAV2001';
-- Prunes to the single partition where hash(SAV2001) % 4 lands
What about composite (sub-) partitioning?
When queries filter on two dimensions, use composite partitioning.
-- Composite: range by date, then hash by account_id
CREATE TABLE transactions_composite (
txn_id BIGSERIAL, account_id VARCHAR(20),
amount NUMERIC(15,2), created_at TIMESTAMP
) PARTITION BY RANGE (created_at);
CREATE TABLE txn_2024_02 PARTITION OF transactions_composite
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01')
PARTITION BY HASH (account_id);
CREATE TABLE txn_2024_02_h0 PARTITION OF txn_2024_02
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE txn_2024_02_h1 PARTITION OF txn_2024_02
FOR VALUES WITH (MODULUS 4, REMAINDER 1);
-- ...
-- Query: "Rajesh's transactions in February"
SELECT * FROM transactions_composite
WHERE account_id = 'SAV2001'
AND created_at >= '2024-02-01' AND created_at < '2024-03-01';
-- Prunes to February partition, then to one hash sub-partition
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 CompositePartitionService {
private final DataSource dataSource;
public CompositePartitionService(DataSource dataSource) {
this.dataSource = dataSource;
}
// Both dimensions filter: range prunes month, hash prunes account
public BigDecimal getAccountMonthlyTotal(String accountId, String start,
String end) throws SQLException {
String sql = "SELECT SUM(amount) FROM transactions_composite "
+ "WHERE account_id = ? "
+ "AND created_at >= ?::date AND created_at < ?::date";
try (Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, accountId);
ps.setString(2, start);
ps.setString(3, end);
try (ResultSet rs = ps.executeQuery()) {
return rs.next() ? rs.getBigDecimal(1) : BigDecimal.ZERO;
}
}
}
}
What is the decision framework?
Partition scheme decision:
Dominant query filters on date ranges?
→ Range partitioning by date
Dominant query filters on discrete category (region, type)?
→ List partitioning by that category
Dominant query filters on equality of high-cardinality key?
→ Hash partitioning by that key
Dominant query filters on TWO dimensions (account + date)?
→ Composite: range by date, sub-partition by hash of account
Dominant query does NOT filter on any good partition key?
→ Do NOT partition. Use a plain indexed table instead.
What should a senior engineer know?
Never partition for its own sake. Partitioning adds complexity (more child tables, more plan nodes). If queries do not filter on the partition key, a plain indexed table is almost always better.
package com.bank.config;
public class PartitioningDecision {
// Ask these questions before partitioning:
// 1. Is the table genuinely large (hundreds of millions of rows)?
// If not, an index is sufficient.
// 2. Do the dominant queries filter on a good partition key?
// If not, partitioning hurts rather than helps.
// 3. Is there a data lifecycle need (archival, retention)?
// If yes, range partitioning by date is strongly motivated.
// 4. Are maintenance operations (VACUUM, REINDEX) painful on the full table?
// If yes, partitioning makes them per-partition and manageable.
}
Visual Overview
┌────────────────────────────────────────────────────────────┐ │ Partition Pruning: When It Works │ ├────────────────────────────────────────────────────────────┤ │ │ │ Table partitioned by RANGE(created_at): │ │ ┌──────┐ ┌──────┐ ┌──────┐ ┌──────┐ │ │ │ Jan │ │ Feb │ │ Mar │ │ Apr │ │ │ └──────┘ └──────┘ └──────┘ └──────┘ │ │ │ │ Query: WHERE created_at IN February │ │ ┌──────┐ ┌──────┐ ┌──────┐ ┌──────┐ │ │ │ SKIP │ │ SCAN │ │ SKIP │ │ SKIP │ ← Pruning works! │ │ └──────┘ └──────┘ └──────┘ └──────┘ │ │ │ │ Query: WHERE account_id = 'SAV2001' (no date filter) │ │ ┌──────┐ ┌──────┐ ┌──────┐ ┌──────┐ │ │ │ SCAN │ │ SCAN │ │ SCAN │ │ SCAN │ ← No pruning! │ │ └──────┘ └──────┘ └──────┘ └──────┘ Slower than │ │ plain index │ │ │ │ Fix: composite partitioning │ │ Range(date) → Hash(account_id) │ │ Both filters prune ✓ │ └────────────────────────────────────────────────────────────┘
Key Takeaway
Partitioning does not automatically make queries faster. It is primarily a manageability feature, and it only speeds up queries through partition pruning, which has a strict precondition: the query must filter on the partition key. If a table is partitioned by date but the dominant query filters by account_id with no date filter, the planner cannot prune and must scan every partition. That is often slower than an unpartitioned table with a good index, because instead of one index lookup the engine does a lookup in every partition.
The scheme must come from the actual access pattern. Range partitioning fits time-based queries and enables cheap archival. List partitioning fits discrete categories like region or account type. Hash partitioning fits high-cardinality keys with equality lookups. When queries filter on two dimensions, composite partitioning (range by date, sub-partition by hash of account) lets both filters prune.
At the National Bank of India, the team partitioned transactions by month because the table was enormous, but the busiest query was all of Rajesh's transactions with no date filter. The planner scanned every partition and performance got worse. Pankaj redesigned it as a composite scheme: range by date and sub-partitioned by hash of account, so a per-customer query within a date window prunes first to the month and then to one hash sub-partition.
The overriding rule is: never partition for its own sake. If nothing filters on a good partition key, a plain indexed table is better.
Remember one sentence: partitioning helps only when the query filters on the partition key, so choose range, list, or hash to match how you actually query.
---