Part 144: Table Partitioning: Range, List & Hash

Dividing a 500-million row table into physical child tables while querying through a single parent view.

1. Declarative Range Partitioning in PostgreSQL

CREATE TABLE sensor_readings (
  id BIGINT,
  reading_time TIMESTAMPTZ NOT NULL,
  temperature DECIMAL(5, 2)
) PARTITION BY RANGE (reading_time);

-- Create Child Partitions for each month:
CREATE TABLE readings_2024_01 PARTITION OF sensor_readings
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE readings_2024_02 PARTITION OF sensor_readings
  FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

Key Takeaways

• Partition Pruning: The query planner scans only the specific partition matching your date filter, ignoring millions of irrelevant records.

In Part 145, we explore Database Sharding & Horizontal Scaling Architecture!