Partitioning a large table in PostgreSQL with Rails
a year ago
- #PostgreSQL
- #Rails
- #Database Optimization
- PostgreSQL table partitioning in Rails helps manage large datasets efficiently.
- Auditing system tracks changes with Active Record callbacks, storing data in an audits table.
- Large audits table caused high costs, operational risks, and degraded query performance.
- Hot-to-cold access pattern: 90% of queries target recent data (last 30 days).
- Archived older audits to Amazon S3 for cost-effective, durable storage.
- Declarative partitioning (range, list, hash) improves performance by pruning irrelevant partitions.
- Chose range partitioning by ID (correlated with created_at) for chronological data.
- Migration strategy: Attach existing table as a partition with CHECK constraints to avoid downtime.
- Handled out-of-range data using a default partition and inheritance as a temporary solution.
- Automated partition maintenance with Rails jobs for seamless inserts and cleanup.