What is the difference between sharding and partitioning

Both Partitioning and Sharding refer to breaking large volumes of data into smaller subsets, but they operate at fundamentally different architectural boundaries:

🟢 Junior Level

Both Partitioning and Sharding refer to breaking large volumes of data into smaller subsets, but they operate at fundamentally different architectural boundaries:

Table Partitioning (Within a SINGLE Database Server):
┌────────────────────────────────────────────────────────┐
│           Single Physical Database Server              │
│  Table "orders"                                        │
│  ├── orders_2024 (Data file on local drive A)          │
│  ├── orders_2025 (Data file on local drive B)          │
│  └── orders_2026 (Data file on local drive C)          │
│  [Shared CPU, Shared RAM, Unified Local ACID Engine]   │
└────────────────────────────────────────────────────────┘

Database Sharding (Across MULTIPLE Physical Servers):
┌────────────────┐      ┌────────────────┐      ┌────────────────┐
│    Server 1    │      │    Server 2    │      │    Server 3    │
│    (Shard 1)   │      │    (Shard 2)   │      │    (Shard 3)   │
│  Users: A – H  │      │  Users: I – P  │      │  Users: Q – Z  │
│ [Own CPU/RAM]  │      │ [Own CPU/RAM]  │      │ [Own CPU/RAM]  │
└────────────────┘      └────────────────┘      └────────────────┘

The Core Distinction

Partitioning (Single-Server): Dividing a large table into smaller physical file segments within a single database engine instance. It optimizes disk I/O, accelerates query execution via partition pruning, and allows instant purging of historical records.
Sharding (Multi-Server): Distributing disjoint subsets of table rows across multiple independent physical database servers (Shared-Nothing Architecture). It is employed when total data storage or write throughput exceeds the physical capacity of a single machine.

[!NOTE] Summary Axiom: All Sharding is a form of horizontal partitioning, but Partitioning is strictly local to one server, whereas Sharding spans multiple network nodes.

🟡 Middle Level

1. Architectural & Operational Comparison Matrix

Architectural Feature	Table Partitioning (Single DB)	Database Sharding (Multi-Server)
Physical Nodes	Exactly one database server (shared CPU, RAM, disk bus)	Multiple independent servers communicating across the network
ACID Guarantees	🟢 100% Native Local ACID: Managed by the local database engine	🔴 Distributed ACID: Requires Two-Phase Commit (2PC) or Sagas
Foreign Keys	🟢 Supported natively across local partition tables	🔴 Relational engines cannot enforce cross-node foreign keys
SQL Joins	⚡ High-speed local disk/memory joins	🔴 Expensive Scatter-Gather network joins across nodes
Operational Complexity	Low: Configured via native SQL DDL commands (`PARTITION BY`)	Very High: Requires shard routers, distributed IDs, and rebalancing
Primary Goal	Query Optimization (Pruning) and administrative data lifecycle	Horizontal Scaling of write IOPS and massive storage capacity

2. Declarative Table Partitioning in PostgreSQL

Modern relational databases (PostgreSQL 10+, MySQL 8+) support native declarative partitioning:

-- 1. Create Partitioned Root Table:
CREATE TABLE audit_logs (
    id BIGINT GENERATED ALWAYS AS IDENTITY,
    service_name VARCHAR(64) NOT NULL,
    payload JSONB,
    created_at TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (id, created_at) -- Partition key MUST be part of the Primary Key!
) PARTITION BY RANGE (created_at);

-- 2. Create Physical Partition Sub-Tables:
CREATE TABLE audit_logs_2026_q1 PARTITION OF audit_logs
    FOR VALUES FROM ('2026-01-01 00:00:00+00') TO ('2026-04-01 00:00:00+00');

CREATE TABLE audit_logs_2026_q2 PARTITION OF audit_logs
    FOR VALUES FROM ('2026-04-01 00:00:00+00') TO ('2026-07-01 00:00:00+00');

3. The Power of Partition Pruning

The primary performance benefit of table partitioning is Partition Pruning. When a query specifies criteria on the partition key:

EXPLAIN ANALYZE 
SELECT * FROM audit_logs 
WHERE created_at >= '2026-02-01' AND created_at < '2026-03-01';

The database query planner inspects table constraints and completely skips scanning audit_logs_2026_q2 and all other unneeded partition files on disk. The engine scans only the compact audit_logs_2026_q1 sub-table, loading significantly fewer disk pages into the database buffer pool and dramatically reducing memory pressure.

🔴 Senior Level

1. The Database Scaling Evolutionary Ladder

Before undertaking the high complexity and maintenance burden of database sharding, senior architects follow a structured progression of scaling techniques:

[1. Indexes, Query Tuning & Connection Pooling]
       │ (Single-server CPU/disk limits reached)
       ▼
[2. Table Partitioning] ──► Accelerates large table scans and streamlines VACUUM
       │ (Single server bottlenecked on read throughput)
       ▼
[3. Read Replicas]      ──► Distributes read queries (SELECT) across replica pool
       │ (Primary writer saturated by INSERT/UPDATE IOPS or disk capacity exhausted)
       ▼
[4. Database Sharding]  ──► Distributes writes across multiple independent primary nodes

2. Instant Archiving: `DROP TABLE` vs `DELETE`

Table partitioning is essential for time-series, audit, and log datasets due to the drastic difference in resource consumption between row deletion and partition drops:

Conventional Large Table (DELETE): Executing DELETE FROM audit_logs WHERE created_at < '2025-01-01' over 100 million rows:
1. Writes every deleted row to the Write-Ahead Log (WAL), generating gigabytes of WAL disk traffic.
2. In MVCC engines (PostgreSQL), rows are not immediately removed from disk; they become “Dead Tuples”, creating massive table bloat.
3. Forces heavy subsequent VACUUM operations that saturate disk IOPS for hours.
Partitioned Table (DROP PARTITION): Executing DROP TABLE audit_logs_2024_q4 takes under 10 milliseconds: the database simply unlinks the physical file from the OS filesystem. Zero WAL deletion entries are created, zero table bloat occurs, and no VACUUM is required.

3. Composite (Hybrid) Partitioning

In large-scale enterprise systems, partitioning and sharding are frequently combined into a Composite Architecture:

Tier 1 (Sharding across servers): The database is sharded by tenant_id or user_id across 16 independent physical database servers.
Tier 2 (Partitioning within each server): On each of the 16 shard nodes, individual order tables are locally partitioned by month (created_at) to enable fast range queries and instant data archiving.

4 Tricky Questions

1. Why must the partition key column be included in the `PRIMARY KEY` of a partitioned table in PostgreSQL?

Answer: Each partition in PostgreSQL is an independent physical table with its own local B-Tree primary key index. PostgreSQL does not support cross-partition Global Indexes. If a table had PRIMARY KEY (id) while being partitioned by created_at, inserting a record with id = 100 into the 2026 partition would require the database to synchronously scan the indexes of all other partitions (2025, 2024, etc.) to verify that id = 100 does not already exist. This cross-partition search would destroy write performance. Requiring PRIMARY KEY (id, created_at) guarantees that uniqueness can be validated strictly within the local partition’s index.

2. What is the operational danger of creating too many table partitions (e.g., 10,000 daily partitions)?

Answer:

Query Planning Overhead: During query compilation, the PostgreSQL query planner must inspect metadata and acquire locks for every partition in the system catalog (pg_class). With thousands of partitions, query planning time can grow exponentially, often taking hundreds of milliseconds—far exceeding actual query execution time.
Lock Table & File Descriptor Exhaustion: Each open partition consumes operating system file descriptors and shared memory entries in PostgreSQL’s lock table (max_locks_per_transaction). Under high concurrency, this triggers query failures with out of shared memory errors. Rule of thumb: Limit tables to a few hundred active partitions at most.

3. At what precise point does Table Partitioning reach its physical limits, mandating a transition to Sharding?

Answer: Table partitioning fails to resolve scaling issues when the bottleneck is rooted in the physical resource ceilings of a single computer:

Write IOPS Saturation: The incoming rate of INSERT and UPDATE statements exceeds the maximum physical IOPS and write bandwidth of the host’s storage controller (NVMe/RAID).
Backup & Recovery SLAs (RTO/RPO): Taking physical snapshots or database dumps (pg_dump) of a monolithic 15–20 TB database takes 24+ hours, violating business disaster recovery SLAs.
Hardware Ceiling: The database has already been scaled vertically to the maximum available cloud compute instance (e.g., 128 vCPUs and 4 TB RAM).

4. What is the difference between Plan-Time and Run-Time Partition Pruning?