Postgres load balancing is secretly broken: The cancellation problem
Presented by:
Jelte Fennema-Nio
Currently I'm working on Citus, Postgres and Pgbouncer at Microsoft. Before that I was a big time Postgres user at Stream, where I worked on low latency APIs for chat and social timelines. I'm one of the current maintainers of the PgBouncer project. I studied at the University of Amsterdam where I got my BSc in Computer Science and MSc in System and Network Engineering.
Checkout the slides
On certain workloads a single Postgres server cannot deliver the desired performance. If the traffic is read heavy, you could already resolve this by adding read replicas. If the traffic is write heavy then the Citus comes to the rescue.
In both cases the queries of your application need to be sent to a randomly chosen Postgres server, to make these servers share the load. A TCP load balancer can do this easily. However, such a load balancer has a hidden downside. When you use it and try to cancel a query, you'll notice that cancelling only works some of the time.
This talk will explain why this problem with cancelations occurs. And it shows a few ways to work around it, including changes that I got merged into Postgres and PgBouncer.
If any of the following topics sound interesting, then this talk is for you:
- Postgres read replicas
- Scaling writes with Citus
- Running PgBouncer on multiple CPU cores
- Implementation details of query cancellations
- Date:
- 2024 February 29 - 12:20
- Duration:
- 40 min
- Room:
- Grand Victoria B
- Conference:
- PGConf India, 2024
- Language:
- Track:
- Database Administration
- Difficulty:
- Easy
- Unlocking the Power of Vector Similarity Search with pgvector
- Start Time:
- 2024 February 29 12:20
- Room:
- Grand Victoria A
- YDB — Adding PostgreSQL compatibility to a Distributed SQL database
- Start Time:
- 2024 February 29 12:20
- Room:
- Robusta + Arabica