Why architecture matters here
Partitioning fails on wrong key (no pruning), unbalanced load (skew), and maintenance surprises (detach + split at scale). Architecture matters because key + type + indexes shape performance.
The architecture: every piece explained
The top strip is types. Table partitioned. Range by date/id. Hash for even distribution. List / composite categorical or mixed.
The middle row is control. Partition key must be in WHERE for pruning. Partition pruning skips at plan time. Local indexes per partition. Global indexes across partitions.
The lower rows are ops. Maintenance — attach + detach + split. Metrics — hit + skew. Ops — retention, archival, rebalancing.
End-to-end flow
End-to-end: events table partitioned by day. Query for last week hits 7 partitions; pruning skips rest. Local index by user_id per partition keeps writes fast. Detach oldest partition monthly to archive. Metrics show pruning effectiveness.