database partitioning postgresql sharding performance time-series

Table partitioning for large datasets

Maria Garcia Feb 2026

2 tabs

-- PostgreSQL Declarative Partitioning (10+)

-- Create partitioned table by date range
CREATE TABLE measurements (
  id BIGSERIAL,
  sensor_id INT NOT NULL,
  temperature DECIMAL(5,2),
  humidity DECIMAL(5,2),
  measured_at TIMESTAMP NOT NULL,
  PRIMARY KEY (id, measured_at)
) PARTITION BY RANGE (measured_at);

-- Create partitions for each month
CREATE TABLE measurements_2024_01 PARTITION OF measurements
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

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

CREATE TABLE measurements_2024_03 PARTITION OF measurements
  FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

-- Default partition for out-of-range values
CREATE TABLE measurements_default PARTITION OF measurements
  DEFAULT;

-- Query automatically uses partition pruning
EXPLAIN ANALYZE
SELECT * FROM measurements
WHERE measured_at >= '2024-02-01'
  AND measured_at < '2024-03-01';
-- Only scans measurements_2024_02 partition

-- Create indexes on partitions
CREATE INDEX idx_measurements_2024_01_sensor
  ON measurements_2024_01(sensor_id);

-- Attach existing table as partition
CREATE TABLE measurements_2023_12 (
  LIKE measurements INCLUDING ALL
);

ALTER TABLE measurements
  ATTACH PARTITION measurements_2023_12
  FOR VALUES FROM ('2023-12-01') TO ('2024-01-01');

-- Detach partition (for archival)
ALTER TABLE measurements
  DETACH PARTITION measurements_2023_12;

-- Archive to S3, then drop
DROP TABLE measurements_2023_12;

-- Partition by list (regions)
CREATE TABLE orders (
  id BIGSERIAL,
  customer_id INT,
  region VARCHAR(10),
  total DECIMAL(10,2),
  created_at TIMESTAMP,
  PRIMARY KEY (id, region)
) PARTITION BY LIST (region);

CREATE TABLE orders_us PARTITION OF orders
  FOR VALUES IN ('US', 'USA');

CREATE TABLE orders_eu PARTITION OF orders
  FOR VALUES IN ('UK', 'DE', 'FR', 'ES');

CREATE TABLE orders_asia PARTITION OF orders
  FOR VALUES IN ('JP', 'CN', 'IN');

-- Hash partitioning (even distribution)
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  username VARCHAR(50),
  email VARCHAR(100)
) PARTITION BY HASH (id);

CREATE TABLE users_p0 PARTITION OF users
  FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE users_p1 PARTITION OF users
  FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE users_p2 PARTITION OF users
  FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE users_p3 PARTITION OF users
  FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- Automatic partition creation function
CREATE OR REPLACE FUNCTION create_monthly_partitions(
  table_name TEXT,
  start_date DATE,
  end_date DATE
)
RETURNS VOID AS $$
DECLARE
  partition_date DATE;
  partition_name TEXT;
  start_range TEXT;
  end_range TEXT;
BEGIN
  partition_date := DATE_TRUNC('month', start_date);

  WHILE partition_date < end_date LOOP
    partition_name := table_name || '_' ||
      TO_CHAR(partition_date, 'YYYY_MM');
    start_range := partition_date::TEXT;
    end_range := (partition_date + INTERVAL '1 month')::TEXT;

    EXECUTE format(
      'CREATE TABLE IF NOT EXISTS %I PARTITION OF %I
       FOR VALUES FROM (%L) TO (%L)',
      partition_name, table_name, start_range, end_range
    );

    partition_date := partition_date + INTERVAL '1 month';
  END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Create partitions for next 12 months
SELECT create_monthly_partitions(
  'measurements',
  CURRENT_DATE,
  CURRENT_DATE + INTERVAL '12 months'
);

-- View partition information
SELECT
  nmsp_parent.nspname AS parent_schema,
  parent.relname AS parent_table,
  nmsp_child.nspname AS child_schema,
  child.relname AS partition_name,
  pg_get_expr(child.relpartbound, child.oid) AS partition_bounds
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
JOIN pg_namespace nmsp_parent ON parent.relnamespace = nmsp_parent.oid
JOIN pg_namespace nmsp_child ON child.relnamespace = nmsp_child.oid
WHERE parent.relname = 'measurements'
ORDER BY partition_name;

-- Partition size statistics
SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE tablename LIKE 'measurements_%'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Drop old partitions (data retention)
DO $$
DECLARE
  partition_record RECORD;
  cutoff_date DATE := CURRENT_DATE - INTERVAL '2 years';
BEGIN
  FOR partition_record IN
    SELECT tablename
    FROM pg_tables
    WHERE tablename ~ '^measurements_\d{4}_\d{2}$'
      AND tablename < 'measurements_' || TO_CHAR(cutoff_date, 'YYYY_MM')
  LOOP
    EXECUTE 'DROP TABLE ' || partition_record.tablename;
    RAISE NOTICE 'Dropped partition: %', partition_record.tablename;
  END LOOP;
END $$;

-- Sub-partitioning (partition of partition)
CREATE TABLE logs (
  id BIGSERIAL,
  level VARCHAR(10),
  message TEXT,
  created_at TIMESTAMP,
  PRIMARY KEY (id, level, created_at)
) PARTITION BY LIST (level);

CREATE TABLE logs_error PARTITION OF logs
  FOR VALUES IN ('ERROR', 'CRITICAL')
  PARTITION BY RANGE (created_at);

CREATE TABLE logs_error_2024_01 PARTITION OF logs_error
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

-- MySQL Partitioning
CREATE TABLE sales (
  id INT NOT NULL AUTO_INCREMENT,
  sale_date DATE NOT NULL,
  amount DECIMAL(10,2),
  PRIMARY KEY (id, sale_date)
)
PARTITION BY RANGE (YEAR(sale_date)) (
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- Add partition
ALTER TABLE sales
  ADD PARTITION (PARTITION p2025 VALUES LESS THAN (2026));

-- Drop partition
ALTER TABLE sales DROP PARTITION p2022;

-- Reorganize partition
ALTER TABLE sales REORGANIZE PARTITION p_future INTO (
  PARTITION p2025 VALUES LESS THAN (2026),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

2 files · sql Explain with highlit

Partitioning splits large tables into smaller physical pieces. Range partitioning divides by value ranges—dates, IDs. List partitioning groups by specific values—regions, categories. Hash partitioning distributes evenly across partitions. I use partitioning for time-series data, archival strategies, query performance. Partition pruning scans only relevant partitions—dramatic speedup. Declarative partitioning in PostgreSQL 10+ simplifies management. Attach/detach partitions for efficient archival. Partitioned indexes improve query performance. Understanding partition key selection is critical—commonly queried columns. Partitioning enables dropping old data instantly. Balance partition count with maintenance overhead. Partitioning is essential for multi-terabyte tables and time-series workloads.