Table partitioning for large datasets

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