Skip to content
PostgreSQLDatabasesShardingSystem DesignArchitecture

PostgreSQL Table Partitioning vs Sharding: Scaling Guide

CoreConceptAugust 1, 20263 min read

As relational databases grow beyond millions to billions of rows, single-table query performance degrades due to massive B-Tree index sizes and memory swapping. PostgreSQL provides two distinct scaling strategies: Declarative Table Partitioning (vertical splitting on a single database host) and Horizontal Sharding (distributing table partitions across a cluster of database instances using Citus).

This guide breaks down range, list, and hash partitioning in PostgreSQL, evaluates Citus distributed table sharding, and analyzes query pruning, maintenance overhead, and cross-shard JOIN constraints.

One DB overload → four scaling levers
One DB overload → four scaling levers

Declarative Table Partitioning Mechanics (Range, List, Hash)

Declarative Table Partitioning breaks a large logical table into smaller physical child tables on a single PostgreSQL server. The query planner automatically uses Partition Pruning to skip scanning child tables whose constraints do not match query WHERE predicates.

PostgreSQL supports Range Partitioning (e.g., date ranges for time-series logs), List Partitioning (e.g., region or tenant IDs), and Hash Partitioning (distributing rows evenly via hash keys).

Quick reference

  • Partition Pruning drastically reduces I/O by scanning only relevant child partitions.
  • Dropping old data is instant via DROP TABLE audit_logs_2026_07;, eliminating expensive DELETE queries.
  • Declarative partitioning operates on a single PostgreSQL node, limited by the host's VRAM and disk capacity.
Declarative Range Partitioning in PostgreSQL (SQL)
1-- Create parent partitioned table2CREATE TABLE audit_logs (3    id UUID NOT NULL,4    user_id UUID NOT NULL,5    created_at TIMESTAMPTZ NOT NULL,6    payload JSONB7) PARTITION BY RANGE (created_at);8 9-- Create monthly child partitions10CREATE TABLE audit_logs_2026_07 PARTITION OF audit_logs11    FOR VALUES FROM ('2026-07-01 00:00:00+00') TO ('2026-08-01 00:00:00+00');12 13CREATE TABLE audit_logs_2026_08 PARTITION OF audit_logs14    FOR VALUES FROM ('2026-08-01 00:00:00+00') TO ('2026-09-01 00:00:00+00');15 16-- Index on child partitions is built automatically17CREATE INDEX idx_audit_logs_user_id ON audit_logs (user_id);
Querying Partitioned Tables with Partition Pruning
1-- PostgreSQL planner skips scanning audit_logs_2026_07 completely2EXPLAIN ANALYZE3SELECT * FROM audit_logs 4WHERE created_at >= '2026-08-15' AND created_at < '2026-08-20';

Remember this

Declarative partitioning keeps B-Tree indexes small on a single server, making queries and historical data purges fast.

Horizontal Sharding & Distributed Tables (Citus)

When data exceeds the disk or memory limits of a single machine, Horizontal Sharding splits table rows across a cluster of PostgreSQL worker nodes.

The Citus extension transforms PostgreSQL into a distributed database: a Coordinator Node routes queries, distributes shard partitions via hash keys, and parallelizes JOIN operations across worker nodes.

Quick reference

  • Distributed Tables: Sharded by a Distribution Key (e.g., tenant_id) across worker nodes.
  • Reference Tables: Replicated across all worker nodes for fast local JOINs with sharded tables.
  • Compare with Database Storage Engines and Sharding & Replication.

Remember this

Citus horizontal sharding enables PostgreSQL to scale write throughput and storage linearly across dozens of server nodes.

PostgreSQL Scaling Decision Matrix

Choosing between local partitioning and horizontal sharding depends on your total dataset volume and single-node hardware limits.

For related guides, see our articles on Hot Partitioning Mitigation and Database Indexing.

Quick reference

  • Choose Local Partitioning for datasets up to 1–5 TB on a single high-memory server instance.
  • Choose Citus Sharding for multi-tenant SaaS applications or time-series data exceeding 5–10 TB.
  • Enforce distribution keys in all queries to prevent multi-shard scatter-gather queries.

Remember this

Start with local declarative partitioning first; transition to Citus sharding when dataset volume exceeds single-host limits.

Key takeaway

PostgreSQL declarative partitioning and Citus sharding provide a seamless progression path for scaling relational data from millions to billions of rows.

Share:

Related Articles

DatabasesPostgreSQL
B-Tree vs LSM-Tree: Database Storage Engine Mechanics

Aug 1, 2026 · 3 min read

At the heart of every database system lies a Storage Engine that determines how data is written to disk, indexed, and re…

Read
PostgreSQLMongoDB
PostgreSQL vs MongoDB: Relational vs Document Databases

Aug 1, 2026 · 5 min read

Selecting the right primary database is one of the most critical architectural decisions for software teams. PostgreSQL …

Read
ClickHousePostgreSQL
ClickHouse vs PostgreSQL: OLAP Columnar vs OLTP Engines

Aug 1, 2026 · 3 min read

Relational databases like PostgreSQL excel at Online Transaction Processing (OLTP)—handling frequent single-row reads, u…

Read

Explore this topic

Microservices & Distributed SystemsDatabases & Data Storage

Keep learning

Follow a structured path or browse all courses to go deeper.

Learning PathsAll Courses