Database Partition Manager

Overview

Implement and manage table partitioning for PostgreSQL and MySQL to improve query performance and simplify data lifecycle management on large tables. This skill covers range partitioning (by date or ID), list partitioning (by category or region), hash partitioning (for even distribution), and composite partitioning.

Prerequisites

• PostgreSQL 10+ (declarative partitioning) or MySQL 5.7+ (native partitioning)
• Database admin credentials with CREATE TABLE and ALTER TABLE permissions
• psql or mysql CLI for executing partition DDL
• Table size metrics: SELECT pg_size_pretty(pg_total_relation_size('table_name')) or SELECT data_length FROM information_schema.TABLES
• Query patterns on the target table (especially WHERE clause columns used for filtering)
• Maintenance window availability for initial partition migration on existing tables

Instructions

1. Identify partitioning candidates by finding tables that exceed 10GB or 100M rows, have time-based query patterns, or require periodic data purging. Query pg_stat_user_tables to find tables with high sequential scan counts on large row sets.

2. Select the partition key based on the most common query filter column. For time-series data, use the timestamp column. For multi-tenant data, use tenant_id. The partition key must appear in most WHERE clauses to enable partition pruning.