-- 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
);
