pgconf NY: PostgreSQL operational pain points and solutions

10 ● Cost effective scalability: Scale systems efficiently while minimizing infrastructure costs ● Developer Productivity: Streamline developer workflows to handle rapid iteration with lean teams. ● Operational Costs: Control costs while scaling infrastructure, avoiding over-provisioning. “Had a bunch of issues hitting a scale where the instance size wasnʼt good enough. Downtime was a problem.ˮ “We needed faster iteration cycles with limited infrastructure. The team couldnʼt afford to spend extra time on database maintenance.ˮ 11 ● Schema Migrations: Ensure schema migrations are done without downtime. ● Backup and Recovery: Robust backup and disaster recovery systems as their data grows. ● Operational Costs: Balance performance and cost as they scale data systems. “Schema migrations have always been a challenge, especially without downtime. We donʼt have the luxury of waiting for off-hours.ˮ “We handle over a terabyte of transactional data every day, and backups are critical. Ensuring recovery plans are solid is non-negotiable.ˮ 12 ● High Availability: Zero downtime and fault tolerance across globally distributed systems. ● Real-Time Data Replication: Reliable real-time replication systems to manage large-scale, globally distributed data. ● Performance Monitoring: Require tools to optimize performance at scale and ensure reliable uptime. “Real-time replication across multiple regions is our biggest challenge. We handle petabytes of data that need to be constantly synced.ˮ “Vacuuming and scaling problems with Aurora are a constant headache. Would like something horizontally scalable like CockroachDB.ˮ … 16 Some Postgres schema changes are difficult ● Locking issues (e.g. on a busy table, in a fast a migration can wait at an exclusive lock and cause downtime) https://xata.io/blog/migrations-and-exclusive-locks ● Constraints might require data back-filling (if you add a NOT NULL  UNIQUE column, how do you fill the data? ● Backwards incompatible changes require a multiple step process (e.g. renames) Motivation