Tweet
Tweet

Slide 1

Slide 1 text

Postgres load balancing is secretly broken: The cancellation problem Jelte Fennema (@JelteF) Developing Citus and Postgres at Microsoft & PgBouncer maintainer 2022-09-14

Slide 2

Slide 2 text

©Microsoft Corporation Azure What am I going to talk about? • Load balancing across Postgres servers • Read replicas • Citus • Low level details about cancelling queries • PgBouncer

Slide 3

Slide 3 text

©Microsoft Corporation Azure What is load balancing? • Sharing workload across servers • Different servers handle independent requests

Slide 4

Slide 4 text

©Microsoft Corporation Azure Why load balancing for Postgres? • Performance • Scaling reads with Postgres read replicas • Scaling writes with the Citus extension for Postgres

Slide 5

Slide 5 text

©Microsoft Corporation Azure Scaling reads or writes? Why? Clients

Slide 6

Slide 6 text

©Microsoft Corporation Azure Scaling reads or writes? Why? Clients

Slide 7

Slide 7 text

©Microsoft Corporation Azure Scaling reads or writes? Why? Clients

Slide 8

Slide 8 text

©Microsoft Corporation Azure Scaling reads or writes? Why? Clients

Slide 9

Slide 9 text

©Microsoft Corporation Azure What are read replicas? Client Client Clients

Slide 10

Slide 10 text

©Microsoft Corporation Azure What is Citus?

Slide 11

Slide 11 text

©Microsoft Corporation Azure How to do load balancing? • Option 1: Client-side load balancing Client knows about all servers and connects to all of them • Option 2: Server-side load balancing Client only knows a single logical server, that server “secretly” forwards requests to multiple servers

Slide 12

Slide 12 text

©Microsoft Corporation Azure Client-side load balancing explained Common approaches: • Hardcoded list of IPs or domains used by client • Multiple DNS records for the same domain, one for each server • With smart client • Or with round-robin resolver Downsides: • Every client needs to know about all the servers • Every client needs logic to choose a server • Caching: what happens when you add a server

Slide 13

Slide 13 text

©Microsoft Corporation Azure Client-side load balancing with Postgres • JDBC (Java) and Npgsql (C#) support • PgBouncer released client-side load balancing last March in v1.17.0 • libpq does not support client-side load balancing natively I submitted a patch to add support for this: https://commitfest.postgresql.org/39/3679/ • Many DNS resolvers always return a single fixed result So, a single client does not load balance

Slide 14

Slide 14 text

©Microsoft Corporation Azure Server-side load balancing explained Common approaches: • Proxy server • Software defined networking (SDN) Problems: • Proxy server introduces latency • Costs extra money Big advantage: • Clients don’t need special support and/or configuration

Slide 15

Slide 15 text

©Microsoft Corporation Azure Server-side load balancing with Postgres Options: 1. A dedicated PgBouncer server configured in client-side load balancing mode • Can use transaction or session load balancing • Extra network hop introduces latency • Single threaded 2. Off the shelf TCP load balancer • Each TCP stream is assigned to a different server • Probably has “fancy” things like health-checks • No extra network hop in case of SDN based load balancer

Slide 16

Slide 16 text

©Microsoft Corporation Azure Decision seems simple Off the shelf TCP based load balancer it is

Slide 17

Slide 17 text

©Microsoft Corporation Azure Not so fast • Important assumption: Different TCP streams are independent

Slide 18

Slide 18 text

©Microsoft Corporation Azure How Postgres cancellation requests work Client

Slide 19

Slide 19 text

©Microsoft Corporation Azure How Postgres cancellation requests work Client

Slide 20

Slide 20 text

©Microsoft Corporation Azure How Postgres cancellation requests work Client

Slide 21

Slide 21 text

©Microsoft Corporation Azure How Postgres cancellation requests work Client

Slide 22

Slide 22 text

©Microsoft Corporation Azure How Postgres cancellation requests work Client CANCEL SECRET-TOKEN-123

Slide 23

Slide 23 text

©Microsoft Corporation Azure How Postgres cancellation requests work Client CANCEL SECRET-TOKEN-123

Slide 24

Slide 24 text

©Microsoft Corporation Azure So what happens with cancellations and a load balancer Client Load balancer

Slide 25

Slide 25 text

©Microsoft Corporation Azure So what happens with cancellations and a load balancer Client Load balancer

Slide 26

Slide 26 text

©Microsoft Corporation Azure So what happens with cancellations and a load balancer Client Load balancer

Slide 27

Slide 27 text

©Microsoft Corporation Azure So what happens with cancellations and a load balancer Client Load balancer CANCEL SECRET-TOKEN-123

Slide 28

Slide 28 text

©Microsoft Corporation Azure So what happens with cancellations and a load balancer Client Load balancer CANCEL SECRET-TOKEN-123 CANCEL SECRET-TOKEN-123

Slide 29

Slide 29 text

©Microsoft Corporation Azure So what happens with cancellations and a load balancer Client Load balancer CANCEL SECRET-TOKEN-123 CANCEL SECRET-TOKEN-123 I don’t know SECRET-TOKEN-123. This is probably a hacker. Let’s ignore this request

Slide 30

Slide 30 text

©Microsoft Corporation Azure Not so fast (continued) • Important assumption: Different TCP streams are independent • Correct: Query on same Postgres session == same TCP stream • Correct: New session == new TCP stream • Wrong: Query cancellation request == new TCP stream

Slide 31

Slide 31 text

©Microsoft Corporation Azure The cancellation problem • Query cancellations end up at the wrong server most of the time • Workaround: Trigger cancellations multiple time at the client side • Solutions: None exist so far

Slide 32

Slide 32 text

©Microsoft Corporation Azure Idea: Make Postgres servers know about each other Client Load balancer

Slide 33

Slide 33 text

©Microsoft Corporation Azure Idea: Make Postgres servers know about each other Client Load balancer

Slide 34

Slide 34 text

©Microsoft Corporation Azure Idea: Make Postgres servers know about each other Client Load balancer

Slide 35

Slide 35 text

©Microsoft Corporation Azure Idea: Make Postgres servers know about each other Client Load balancer CANCEL SECRET-TOKEN-123

Slide 36

Slide 36 text

©Microsoft Corporation Azure Idea: Make Postgres servers know about each other Client Load balancer CANCEL SECRET-TOKEN-123 CANCEL SECRET-TOKEN-123

Slide 37

Slide 37 text

©Microsoft Corporation Azure Idea: Make Postgres servers know about each other Client Load balancer CANCEL SECRET-TOKEN-123 CANCEL SECRET-TOKEN-123 I don’t know SECRET-TOKEN-123 This was probably meant for another server. Let me ask Server A

Slide 38

Slide 38 text

©Microsoft Corporation Azure Idea: Make Postgres servers know about each other Client Load balancer CANCEL SECRET-TOKEN-123 CANCEL SECRET-TOKEN-123

Slide 39

Slide 39 text

©Microsoft Corporation Azure Idea: Make Postgres servers know about each other Client Load balancer CANCEL SECRET-TOKEN-123 CANCEL SECRET-TOKEN-123

Slide 40

Slide 40 text

©Microsoft Corporation Azure How to make this idea work? • Postgres does not support this out of the box • What can we do to make it work?

Slide 41

Slide 41 text

©Microsoft Corporation Azure Option 1: Postgres extension • No extension hooks exist for cancellation related code  • Getting hooks (or built-in functionality) in Postgres will take a long time and won’t work on old Postgres versions • Need to build cancellation forwarding support

Slide 42

Slide 42 text

©Microsoft Corporation Azure Option 2: Modify PgBouncer • PgBouncer can run in front of any Postgres server version • PgBouncer already has cancellation forwarding code to forward cancellations to Postgres • This code only needs to be modified to send to other PgBouncer servers • PR for this can be found here: https://github.com/pgbouncer/pgbouncer/pull/666

Slide 43

Slide 43 text

©Microsoft Corporation Azure Option 2: Modify PgBouncer Client Load balancer PgBouncer B PgBouncer A Postgres A Postgres B

Slide 44

Slide 44 text

©Microsoft Corporation Azure Option 2: Modify PgBouncer Client Load balancer PgBouncer B PgBouncer A Postgres A Postgres B

Slide 45

Slide 45 text

©Microsoft Corporation Azure Option 2: Modify PgBouncer Client Load balancer PgBouncer B PgBouncer A Postgres A Postgres B

Slide 46

Slide 46 text

©Microsoft Corporation Azure Option 2: Modify PgBouncer Client Load balancer PgBouncer B PgBouncer A Postgres A Postgres B

Slide 47

Slide 47 text

©Microsoft Corporation Azure Option 2: Modify PgBouncer Client Load balancer CANCEL SECRET-TOKEN-123 PgBouncer B PgBouncer A Postgres A Postgres B

Slide 48

Slide 48 text

©Microsoft Corporation Azure Option 2: Modify PgBouncer Client Load balancer CANCEL SECRET-TOKEN-123 CANCEL SECRET-TOKEN-123 PgBouncer B PgBouncer A Postgres A Postgres B

Slide 49

Slide 49 text

©Microsoft Corporation Azure Option 2: Modify PgBouncer Client Load balancer CANCEL SECRET-TOKEN-123 CANCEL SECRET-TOKEN-123 PgBouncer B PgBouncer A Postgres A Postgres B I don’t know SECRET-TOKEN-123 This was probably meant for another server. Let me ask Server A

Slide 50

Slide 50 text

©Microsoft Corporation Azure Option 2: Modify PgBouncer Client Load balancer CANCEL SECRET-TOKEN-123 CANCEL SECRET-TOKEN-123 PgBouncer B PgBouncer A Postgres A Postgres B

Slide 51

Slide 51 text

©Microsoft Corporation Azure Option 2: Modify PgBouncer Client Load balancer CANCEL SECRET-TOKEN-123 CANCEL SECRET-TOKEN-123 PgBouncer B PgBouncer A Postgres A Postgres B

Slide 52

Slide 52 text

©Microsoft Corporation Azure Option 2: Modify PgBouncer Client Load balancer CANCEL SECRET-TOKEN-123 CANCEL SECRET-TOKEN-123 PgBouncer B PgBouncer A Postgres A Postgres B

Slide 53

Slide 53 text

©Microsoft Corporation Azure Making PgBouncer performant • Single-threaded design becomes a bottleneck on high throughput servers • PgBouncer supports multi-process by using so_reuseport=1 • The Linux kernel then load-balances TCP streams across all processes • So, what we end up with is a multi-layered load balancer

Slide 54

Slide 54 text

©Microsoft Corporation Azure What we end up with Client Load balancer PgBouncer B1 PgBouncer A1 Postgres A Postgres B PgBouncer A2 Linux kernel PgBouncer B2 Linux kernel

Slide 55

Slide 55 text

©Microsoft Corporation Azure What we end up with Client Load balancer PgBouncer B1 PgBouncer A1 Postgres A Postgres B PgBouncer A2 Linux kernel PgBouncer B2 Linux kernel

Slide 56

Slide 56 text

©Microsoft Corporation Azure What we end up with Client Load balancer PgBouncer B1 PgBouncer A1 Postgres A Postgres B PgBouncer A2 Linux kernel PgBouncer B2 Linux kernel

Slide 57

Slide 57 text

©Microsoft Corporation Azure What we end up with Client Load balancer PgBouncer B1 PgBouncer A1 Postgres A Postgres B PgBouncer A2 Linux kernel PgBouncer B2 Linux kernel

Slide 58

Slide 58 text

©Microsoft Corporation Azure What we end up with Client Load balancer PgBouncer B1 PgBouncer A1 Postgres A Postgres B PgBouncer A2 Linux kernel PgBouncer B2 Linux kernel

Slide 59

Slide 59 text

©Microsoft Corporation Azure What we end up with Client Load balancer CANCEL SECRET-TOKEN-123 PgBouncer B1 PgBouncer A1 Postgres A Postgres B PgBouncer A2 Linux kernel PgBouncer B2 Linux kernel

Slide 60

Slide 60 text

©Microsoft Corporation Azure What we end up with Client Load balancer CANCEL SECRET-TOKEN-123 PgBouncer B1 PgBouncer A1 Postgres A Postgres B PgBouncer A2 Linux kernel PgBouncer B2 Linux kernel

Slide 61

Slide 61 text

©Microsoft Corporation Azure What we end up with Client Load balancer CANCEL SECRET-TOKEN-123 PgBouncer B1 PgBouncer A1 Postgres A Postgres B PgBouncer A2 Linux kernel PgBouncer B2 Linux kernel

Slide 62

Slide 62 text

©Microsoft Corporation Azure What we end up with Client Load balancer CANCEL SECRET-TOKEN-123 PgBouncer B1 PgBouncer A1 Postgres A Postgres B PgBouncer A2 Linux kernel PgBouncer B2 Linux kernel

Slide 63

Slide 63 text

©Microsoft Corporation Azure What we end up with Client Load balancer CANCEL SECRET-TOKEN-123 PgBouncer B1 PgBouncer A1 Postgres A Postgres B PgBouncer A2 Linux kernel PgBouncer B2 Linux kernel

Slide 64

Slide 64 text

©Microsoft Corporation Azure What we end up with Client Load balancer CANCEL SECRET-TOKEN-123 PgBouncer B1 PgBouncer A1 Postgres A Postgres B PgBouncer A2 Linux kernel PgBouncer B2 Linux kernel

Slide 65

Slide 65 text

©Microsoft Corporation Azure What we end up with Client Load balancer CANCEL SECRET-TOKEN-123 PgBouncer B1 PgBouncer A1 Postgres A Postgres B PgBouncer A2 Linux kernel PgBouncer B2 Linux kernel

Slide 66

Slide 66 text

©Microsoft Corporation Azure What we end up with Client Load balancer CANCEL SECRET-TOKEN-123 PgBouncer B1 PgBouncer A1 Postgres A Postgres B PgBouncer A2 Linux kernel PgBouncer B2 Linux kernel

Slide 67

Slide 67 text

©Microsoft Corporation Azure The final problem • To which one should PgBouncer forward it? • Sending the cancellation to all servers is quite heavy

Slide 68

Slide 68 text

©Microsoft Corporation Azure The final solution • Encode an identifier in the cancellation token: e.g. SECRET-TOKEN-123-A1

Slide 69

Slide 69 text

© Copyright Microsoft Corporation. All rights reserved. Any questions?