Mostafa Abdelraouf - Going beyond a Single Postgres Instance with Rails - Rails World 2024

Start with optimizing a single Postgres instance first (connection pooling, indexing, caching, query refactoring) before adding complexity

When adding database replicas, key challenges include:

• Routing/switching between primary and replicas
• Managing replication lag
• Handling failover events
• Load balancing between instances

Use connection poolers (like PgBouncer or PgCat) in production to improve efficiency and handle connection management

Consider query patterns when scaling:

• Identify read vs write workloads
• Understand top queries and their requirements
• Use tools like pg_hero and pg_stat_statements for analysis

Two main approaches to sharding:

• Vertical sharding: Moving entire tables to separate instances
• Horizontal sharding: Splitting table data across instances using shard keys

Important sharding considerations:

• Choose appropriate sharding keys
• Cannot have transactions spanning multiple shards
• Joins across sharded tables are not possible
• Plan for zero-downtime migrations

Rails has built-in support for replica routing, but custom adapters like Makara can provide additional functionality

Monitor and measure:

• Replication lag
• Load balancing effectiveness
• Failover events
• Error rates
• Query performance

Keep configurations time-bound (timeouts, connection limits) to prevent cascading failures

Always validate routing decisions and keep query patterns in mind when making architectural changes