We moved an 800-million-row events table to monthly range partitions with a cutover lock of under a second. The trick was to attach the existing table as one big legacy partition, with every slow step done beforehand under weak locks.
A new operations dashboard refreshed every 15 minutes and our BigQuery bill jumped by 9,000 USD in a month. Each tile ran SELECT star over a 40 TB events table without a partition filter. Column pruning, required partition filters and a bytes billed cap fixed it.
Replication, partitioning and sharding solve different problems, and picking the wrong one buys complexity without relief. This guide starts from the bottleneck you can measure and walks to the option that fixes it, including when Citus is worth it.
We raised a keyed Kafka topic from 6 to 18 partitions to fix lag. Within minutes, order status events arrived out of order and some orders got stuck in the wrong state. The key-to-partition mapping changed under live traffic; here is why and how to resize safely.
Our monthly-partitioned orders table was supposed to make the daily report touch one partition. EXPLAIN showed all 36. A date cast on the partition key and a join-driven filter were blocking pruning, and two small query rewrites brought it back.