PgBouncer is beneficial, vital, and fraught with peril

To begin, I wish to say that I’m appreciative that PgBouncer exists and the work its open supply maintainers put into it. I additionally love working with PostgreSQL, and I’m grateful for the unbelievable quantity of labor and enhancements that go into it as nicely.

I additionally suppose group and business enthusiasm round Postgres is at an all time excessive. There are extra managed internet hosting choices than ever (Crunchy Data, Render, Fly.io, and on and on), deep extensions like PostgresML, Citus and Timescale, serverless choices like Neon, and real-time providers like Supabase with Postgres at their heart. Postgres is a sturdy, superior and quick RDBMS able to dealing with the wants of most each software.

I simply discover the present state of suggestions and steering round scaling Postgres to be confounding. And it feels stunning for brand new Postgres customers to find that one of the frequent scaling choices depends on an answer like PgBouncer.

Over time I’ve learn dozens of articles round scaling and sustaining Postgres databases, they usually at all times understate the impression of PgBouncer in your software. They casually point out unusable options with none exploration, or the quite a few methods you possibly can silently break anticipated question habits. The recommendation is simply to show it on. I need it to be clear that as your software scales, PgBouncer is commonly mandatory however isn’t free.

The next sections present an summary of what connection pooling is normally, how connection pooling modes work in PgBouncer and related instruments, after which I dig into each Postgres characteristic that doesn’t work in PgBouncer transaction mode and what the implications are. That is the PgBouncer article I want existed the primary time I used it – let’s get going ????!

Contents

What’s connection pooling?

PgBouncer is a light-weight connection pooler for PostgreSQL. What does that imply precisely? What’s connection pooling and why is it wanted?

Opening a connection is dear: a brand new Postgres consumer connection includes TCP setup, course of creation and backend initialization – all of that are expensive by way of time and system sources. A connection pool retains a set of connections out there for reuse so we are able to keep away from that overhead previous preliminary connection.

There are three important ranges of connection pooling^:

Framework connection pooling. This can be a frequent characteristic of many frameworks/libraries. Inside a given course of, you keep a pool of lively connections which are shared between code, typically working throughout threads. Everytime you deal with some processing in a server request, a background course of, a job, and many others, you open a connection and hold that connection open. When that piece of labor finishes and a brand new piece of labor begins, you possibly can reuse the connection with out the expense of opening a brand new connection to the database each single time. These connections are normally native to a selected working system course of, so that you acquire no profit outdoors of that course of (and for those who’re scaling Postgres, you in all probability have numerous processes)

One stage greater, you possibly can have consumer stage connection pooling outdoors of your code. PgBouncer can deal with this, and as a substitute of impartial unsharable process-isolated connections you proxy your entire connections via PgBouncer. But it surely runs in your server, so you continue to can not share connections between servers (and once more, when needing to do it you in all probability have numerous servers).

Server stage connection pooling. Right here we host PgBouncer impartial of our servers and hook up with a single central PgBouncer occasion^{. That is probably the most strong type of connection pooling as a result of impartial of the rest in your code or server, you might be assured that any consumer connection is coming from the pool.}

That’s all nice however… why do we want it?

There are two main layers to this:

1. Sustaining connections is helpful as a base characteristic. Much less reminiscence and io churn, much less latency earlier than working queries. Much less strain on the database continually opening and shutting connections.
2. Postgres connections get costly in a short time. Surprisingly rapidly.

Listed here are some normal group tips round allowable Postgres connection counts primarily based on a mix of group expertise and particular benchmarking:

• By way of what some managed providers even supply: Supabase affords a max of 50 connections, Neon affords a max of 100 connections, and Render affords a max of 397 connections.
• The final higher sure suggestion is a max of 500 lively connections. Providers like Heroku Postgres even implement a tough restrict of 500 connections
• Even at 500 connections, your server goes to be strained. This more recent (as of 2023) enterprisedb article analyzed connection efficiency and located that 300-400 lively connections appears optimum. This article from Brandur is older (2018) however appears to strengthen this concept as nicely
• There have been some more recent connection improvements in Postgres (as of model 14) dealing with idle connections extra effectively^{, however lively connections are nonetheless costly and idle connections haven’t reached the size of a devoted pooler}
• The truth of 500 connections is it sounds extraordinarily low however these connections can deal with a ton of throughput. The downside is, as a metric of pure concurrency, actual connections have a tough higher restrict. So for those who attempt to have 5 thousand purchasers join concurrently, you’re going to start out getting a great deal of connection errors^.

To enhance the price of connection overhead, normal connection pooling is useful and a PgBouncer occasion in its default session primarily based mode can deal with it. However to enhance concurrency issues must get a bit… quirky.

There are two modes in PgBouncer which give purchasers entry to extra connections than Postgres truly has out there. They depend on the concept at any given time lots of your connections are idle, so you possibly can unlock utilization of idle connections to enhance concurrency.

Can I simply activate PgBouncer and get scaling without spending a dime?

Sort of? However not likely? It’s difficult.

Internally, PgBouncer will handle a pool of connections for you. The default pooling mode it begins with, session pooling, is conservative, and generally is not going to present improved concurrency^.

I’m going at hand wave a bit previous two of the modes and concentrate on the everyday suggestion.

Session mode is the default and most conservative mode. This can be a 1:1 – your native connection actually holds onto a full connection till you shut it. This does little that can assist you scale connection concurrency, nevertheless it helps with latency and connection churn overhead.

Assertion mode is probably the most aggressive mode and means your connection goes again into the pool after each assertion. You lose the flexibility to make use of transactions ???? – that appears wild and unusable for under probably the most specialised of use instances.

The mode that ends in a extra sane stability of improved concurrency and retained important database options is transaction mode. Transaction mode means your connection stays constant so long as you’re in a transaction. As soon as the transaction finishes, your code thinks it nonetheless has actual connection however PgBouncer truly releases the connection again into the pool internally. That is session sharing, your session goes to be shared with different connections with out being reset or closed.

Transaction mode is a robust idea. Your code usually has numerous database downtime. Most code doesn’t solely function on the database – it takes CPU cycles, interacts with information, makes community calls, and calls different knowledge shops. Throughout that point, your connection sits idle and unused for what in computing and database phrases is an eternity. By releasing that again into the pool outdoors of transactions you unlock your idle connection to be used by a consumer who truly wants it. This manner your 500 out there connections can providers 1000’s of purchasers, as a substitute of a 1:1 with the variety of out there connections.

-- connection is definitely pulled from the pool inside PgBouncer
BEGIN;
INSERT INTO...;
UPDATE;
COMMIT;
-- connection goes again to the pool inside PgBouncer

The issue with transaction mode is that this tiny configuration change quietly modifications not solely your potential to scale, but in addition the way in which your connections behave. It breaks the anticipated command semantics between consumer and database server. And understanding whether or not you’ve gotten issues proper in transaction mode is very tough.

Let’s say you’ve been working with PgBouncer in session mode (or working with no proxy in any respect), and also you make the change to transaction mode. Your perspective on how you should use Postgres wants to vary – so now we’re onto the peril.

Perils

Lots of the following objects are documented shortcomings of PgBouncer in transaction mode. However:

1. They’re handled calmly
2. Their repercussions and disadvantages should not mentioned
3. PgBouncer is commonly really useful with out mentioning them
4. PgBouncer is commonly really useful concurrently recommending incompatible transaction mode options like session stage advisory locks and session stage assertion timeouts
5. The non-determinism launched by utilizing incompatible statements shouldn’t be mentioned (ie, I execute an announcement in Course of A and abruptly Course of B errors out because of it)

Assume anytime I point out PgBouncer after this level I’m referring to transaction mode. Right here we go!

Detecting invalid statements ????

PgBouncer fortunately accepts statements that aren’t supported in transaction mode. The issue is pushed onto the developer, which suggests they can and will get it incorrect^.

That is by design. The sense I get is that PgBouncer was particularly architected to not analyze any statements and so it will be an enormous change for them to deal with this^.

Amazon has an identical device to PgBouncer referred to as RDS Proxy, and it has a characteristic referred to as “connection pinning”. If it detects an announcement that’s incompatible with transaction mode, it’s going to robotically maintain that connection for that consumer during their session.

That is each extremely helpful and concurrently problematic. It means question habits is constant along with your expectations (????????) but in addition you can silently kill all concurrency advantages (????). If sufficient queries are run that set off connection pinning, rapidly you might throttle your throughput. But it surely does offer you an escape hatch for safely working statements which aren’t transaction suitable with out having to leap via any hoops.

I’d be tremendous with some logging I might monitor. So far as I can inform there may be nothing like this in PgBouncer, and so all of the burden lands on you to get it proper. As one engineer, or a number of engineers, all conscious of potential points, you possibly can in all probability keep that. However what about dozens of engineers? Or tons of? Hundreds? All with various ranges of expertise with databases and poolers? There’s going to be errors.

Lock Timeouts (SET/RESET) ????

Until you like app downtime, you have to be utilizing a lock_timeout when working DDL. It’s a important facet of zero downtime migrations.

The concept is to set it to a restrict that might be acceptable for queries in your software to slowdown by – ready to amass a lock may cause associated queries to queue up behind your DDL operation:

-- gradual choose
SELECT * FROM my_table;

-- DDL begins in separate course of, blocked on buying the lock by the 
--    gradual question
ALTER TABLE my_table...

-- Subsequent queries begin queuing up...
SELECT * FROM my_table WHERE id = 123;
SELECT * FROM my_table WHERE id = 234;
--- ...

In that state of affairs, the gradual question is working the present. Till it finishes, all the opposite queries to that desk are caught. That goes on lengthy sufficient and customers can’t use the system. A bit longer and your app begins timing out. A bit longer you’re working out of connections. Now you’re observing a complete app outage, about able to kill your entire connections in a determined try to salvage issues, considering a profession change to landscaping the place you possibly can at most impression one particular person at a time, proper? That sounds good, doesn’t it?

I’ve in fact by no means skilled that. I’m simply very inventive ????. But when you have skilled that, otherwise you’d prefer to keep away from experiencing that, use a lock_timeout:

SET lock_timeout TO '2s';

Now in case your DDL can not purchase a lock it’s going to throw an error after 2 seconds. That needs to be an okay delay in working queries, and you may retry the operation later.

However wait! Are you related to PgBouncer?! You could wish to deliver up that landscaping help-wanted itemizing once more… ????

SET operations apply on the session level. Which means on a PgBouncer connection, there isn’t a assure our lock_timeout will nonetheless be utilized once we run our DDL:

-- Course of 1
-- PgBouncer pulls connection 1
SET lock_timeout TO '2s';
-- connection 1 goes again to the pool

-- In the meantime, in Course of 2:
-- PgBouncer pulls connection 3
SELECT id FROM my_table, pg_sleep(30);

-- Again in Course of 1:
-- PgBouncer pulls connection 2
-- This connection has no lock_timeout set, so it's going to dangle 
--    till our pg_sleep question finishes 30 seconds later, and all
--    queries to my_table after it are caught for these 30 seconds as nicely
ALTER TABLE my_table...

It’d be simple to argue “don’t have gradual queries”. And that needs to be the objective! However we don’t name it “glad path uptime ????”, we name it “zero downtime”. It means even when issues go incorrect, you don’t go down. There can be different operations that maintain a lock in your desk, so that you merely can’t depend on efficiently buying that lock^.

So what can we do? There are two choices:

1. Bypass PgBouncer and go straight to the database
2. Use a transaction stage lock_timeout

Bypassing PgBouncer

Your most secure guess is to go together with possibility (1). You must have some potential to immediately hook up with your database, so make the most of it and don’t soar via hoops to run DDL safely.

The largest impediment you hit with (1) is transparency: PgBouncer actually doesn’t need you to know whether or not you might be related to the actual database or not. There’s no simple reply there, however by validating a setup the place you persistently run your DDL course of immediately towards Postgres you then’re set.

Use transaction stage statements

There’s a transaction native equal to the SET assertion: SET LOCAL. Utilizing our instance from earlier:

-- Course of 1
-- PgBouncer pulls connection 1
BEGIN;
SET LOCAL lock_timeout TO '2s';
-- connection 1 stays checked out

-- In the meantime, in Course of 2:
-- PgBouncer pulls connection 3
SELECT id FROM my_table, pg_sleep(30);

-- Again in Course of 1:
-- Connection 1 remains to be checked out
ALTER TABLE my_table...
-- lock_timeout raises an error after 2 seconds ready, and 
--    we keep away from our downtime!

DDL in Postgres is transactional, so it’s legitimate to start out our transaction, set our lock_timeout utilizing SET LOCAL, then begin our DDL operation. Our transaction native setting will follow us till the transaction commits or rolls again, so we safely hold our timeout and rollback our DDL.

It’s not a horrible answer (1 remains to be higher), besides for 2 issues:

1. Concurrent indexes
2. Tooling

One other zero downtime star is the concurrent index. Whenever you create a brand new index on a desk you run the prospect of locking it up lengthy sufficient to trigger downtime. Right here’s the reply to that downside:

CREATE INDEX CONCURRENTLY index_name ON my_table;

Concurrent indexes are created with out an unique lock, so your regular operations hold going whereas it builds the index within the background. The downside is they will’t be run in a transaction, so SET LOCAL shouldn’t be an possibility.

As a result of they don’t require an unique lock, setting a lock_timeout is much less vital. But when there may be competition and also you simply can’t get that index to amass it’s shared lock, do you actually need it to run perpetually?

As for (2), widespread tooling normally doesn’t deal with SET LOCAL for you. Within the Rails/ActiveRecord world there are a number of libraries that can robotically apply zero downtime insurance policies for you, however all of them assume you may have an unique connection and function on the SET session stage.

In PgBouncer, the road to downtime is paved with session level statements.

Simply go together with (1), hold your sanity, throw away the diary entries about dwelling out your days respiratory within the odor of recent minimize grass, and join on to Postgres to run DDL with SET lock_timeout calls.

Assertion timeouts (SET/RESET) ⏳

Decided to not repeat your experiences from lock_timeout, you examine this factor referred to as statement_timeout. This little magic wand makes it so that you management how lengthy an announcement is allowed to run ????.

So right here it’s:

SET statement_timeout TO '2s';

These grasping queries now don’t stand an opportunity. You may tame your lengthy working queries and keep away from blocking your DDL! You ignore my recommendation to at all times use lock_timeout, say “bye losers” to lengthy working queries, and hearth off that DDL once more… oh god. Why are issues slowing down. Now they’re timing out. The connections are filling up. What’s occurring?

Oh riiiight. You forgot. You’re utilizing PgBouncer. SET is off the desk. Ought to have set that lock_timeout ????…

If I had a nickel for each time somebody talked about SET statement_timeout and PgBouncer in the identical article… ^{I do know nobody sharing this content material is doing it maliciously, however bear in mind that these are deceptive and incompatible options.}

With lock_timeout, why does statement_timeout even matter?

• Assertion timeouts are useful for lengthy working queries in order that they cancel earlier. If a consumer disconnects, Postgres will periodically verify for the connection and attempt to cancel the question when it goes away. However a question with runaway cpu utilization will simply hold working even when the consumer dies or disconnects. Meaning you lose that connection till the question finishes, which might take minutes (or hours)
• The database default is 0, which suggests there isn’t a restrict. In some contexts this isn’t an issue, however notably for net requests that is extreme

The primary time I used statement_timeout was from a weblog suggestion to restrict statements for requests in net functions. In an online request, you normally have an higher restrict on how lengthy you permit them to run earlier than they day trip – this conserves sources, protects towards runaway buggy code and helps with unhealthy actors. It made sense that I’d set it to one thing conservative on all my net connections to cope with lengthy working queries.

I deployed the code and for a short time issues appeared to work nicely. Then I noticed one thing odd. This began popping up:

canceling assertion because of assertion timeout

However in my… background jobs? My net requests had been tuned to be quick, however the constraints round my background processes had been a bit… looser. Are you able to guess what I had not too long ago enabled? PgBouncer in transaction mode. My session stage assertion timeout was being swapped out from my net request, picked up by my job, and induced my job to timeout as a substitute – net request security was off the rails and longer working jobs had been intermittently failing.

So is there any means we are able to use it? There’s a pair methods I do know of, however nothing nice when pooling.

Our previous buddy transaction

BEGIN;
SET LOCAL statement_timeout '5s';
SELECT ...
COMMIT;

One thing about wrapping a SELECT in a transaction feels type of unusual, nevertheless it works. You probably have focused issues, you possibly can wrap explicit queries in a transaction and use SET LOCAL to localize the statement_timeout.

That is completely not a viable answer for an entire request lifecycle. If I needed to try my net request stage timeouts once more, no means am I wrapping each net request in a single big transaction. Postgres doesn’t have an idea of nested transactions so any code I’ve that could be working transactionally is gonna be in for some complicated surprises^{. And most significantly, wrapping my entire request in a transaction means I’ve utterly negated the good thing about proxy pooling – now my request lifecycles are mainly 1:1 with my connection classes.}

Apply assertion timeouts per consumer

I’ve by no means tried it, however I’ve seen it really useful to set assertion timeouts per consumer when utilizing PgBouncer. That appears to have a pair issues I can consider:

1. It’s not dynamically configurable.
2. It dilutes the pool of accessible connections per context

(1) is certainly inconvenient. You probably have completely different contexts the place you’d like to use completely different timeout constraints, this might be means too cumbersome to take care of.

However (2) feels like a deal breaker. If I wish to constrain my net requests to a conservative timeout, however give my background processes extra wiggle room, my pool measurement of actual connections is now cut up as a substitute of sharing a pool of whole out there database connections. I additionally must handle ensuring every context makes use of the suitable consumer, or issues will go badly.

It’s technically an possibility, however appears trickier to take care of and monitor.

Transparency ????

I don’t perceive why my session options aren’t working. I at all times be certain to make use of loads of Postgr…PgBouncer?!

It is rather tough to inform if you find yourself or aren’t utilizing PgBouncer, which is unfortunately by design. It considers itself a clear proxy. In session mode, that’s just about true. However in transaction and statement mode you might be working with bizarro Postgres. All of it works the identical besides when it doesn’t.

So if you need a daily connection since you want a characteristic not out there in transaction mode, being positive you probably did it proper is extraordinarily tough.

I’ve had a hell of a time verifying that some servers are or aren’t working with PgBouncer. Server A is utilizing pub sub, I don’t need it. Server B wants the throughput, I need it. How can I be certain somebody by no means makes a mistake and attaches the server to the incorrect place? Principally, I can’t.

On the subject of manufacturing code I prefer to be paranoid. On a big sufficient codebase, and staff, and consumer base, uncommon issues are sure to occur, typically recurrently. I attempt to write code and configure environments so the best means is straightforward and the incorrect means is difficult. PgBouncer doesn’t make that simple.

On this explicit level I’d like to say I’ve some type of recommendation to behave on, nevertheless it principally takes testing and validating your setup. If somebody on the market has higher concepts or suggestions, I’m all ears.

Ready Statements (PREPARE/DEALLOCATE, Protocol-level ready plans) ✔️

PgBouncer has a public relations downside relating to ready statements. That is all of the PgBouncer docs say about them:

* It’s potential so as to add help for that into PgBouncer

Sort of feels… alarming. No ready statements in transaction mode?! Aren’t these… vital? Even additional whenever you go to make use of PgBouncer with Hibernate or ActiveRecord (and I’m positive others) you’ll see the advice to configure them to flip off ready statements ????. Does it shock you a bit to listen to that? Make you’re feeling slightly queasy possibly?

I had it drilled into me early in my profession that ready statements are a important a part of defending towards SQL injection. Within the OWASP SQL Injection Prevention Cheatsheet the very first suggestion is:

• Use of Ready Statements (with Parameterized Queries)

So PgBouncer tells me I have to flip them off?

The primary time I used PgBouncer in an software I spent lots of time determining how turning off ready statements was secure to do. It seems that ready statements in Postgres imply a number of issues, however come down to 2 important choices:

1. Named ready statements
2. Unnamed ready statements

Named ready statements are reusable, and are tied to the connection session.

Unnamed ready statements are single use, and don’t have any affiliation to the connection session.

There are two methods to create a named ready assertion and one option to create an unnamed ready assertion:

1. PREPARE
2. Protocol-level Parse/Bind/Execute with a reputation specified
3. Protocol-level Parse/Bind/Execute with no identify specified

PgBouncer says it doesn’t help ready statements in both PREPARE or protocol-level format. What it truly doesn’t help are named ready statements in any type. That’s as a result of named ready statements dwell within the session and in transaction mode you possibly can change classes.

-- PgBouncer pulls connection 1
PREPARE bouncer_since (int, timestamp) AS
SELECT * 
FROM bouncers b
INNER JOIN company g ON g.bouncer_id = b.id
WHERE b.id = $1 AND b.created > $2;
-- connection 1 goes again to the pool

-- PgBouncer pulls connection 2
EXECUTE bouncer_since(1, now() - INTERVAL '2 days');
-- ???? ERROR: ready assertion "bouncer_since" doesn't exist ????

However unnamed ready statements are completely tremendous. Actually, I’d be shocked if the present consumer library you’re utilizing to hook up with Postgres doesn’t already change to them if “ready statements” (once more, so rattling deceptive) are “turned off”.

However wait. What the heck is an unnamed assertion? PREPARE requires a reputation… how can I make a ready assertion with no identify?

Protocol-level ready plans

The choice to the PREPARE assertion is to immediately talk with Postgres on the protocol stage.

I needed to dig a bit to get a deal with on this – I began from a typical Ruby ORM referred to as ActiveRecord, dug into the Ruby “pg” gem it makes use of, then went one layer deeper into libpq, which is a part of Postgres itself.

If we use lively document for example, when prepared statements are “disabled”, the postgres adapter internally calls exec_no_cache in activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb:

def exec_no_cache(sql, identify, binds...)
  #...
  conn.exec_params(sql, type_casted_binds)

That’s powered by the ruby “pg” gem, which when calling exec_params from ruby in the end calls into the libpq operate PQsendQueryParams:

// Ruby "pg" gem
// ext/pg_connection.c
static VALUE
pgconn_async_exec_params(int argc, VALUE *argv, VALUE self) {}

// internally calls...
static VALUE
pgconn_send_query_params(int argc, VALUE *argv, VALUE self) {}

// internally calls this from the libpq c postgres internals:
// src/interfaces/libpq/fe-exec.c
int PQsendQueryParams(PGconn *conn,
  const char *command,
  int nParams,
  const Oid *paramTypes,
  const char *const *paramValues,
  const int *paramLengths,
  const int *paramFormats,
  int resultFormat) {}

What does PQsendQueryParams do? It calls an inside methodology named PQsendQueryGuts. Discover the empty string and use unnamed assertion remark ????.

return PQsendQueryGuts(conn,
    command,
    "", /* use unnamed assertion */
    nParams,
    paramTypes,
    paramValues,
    paramLengths,
    paramFormats,
    resultFormat);

What does that operate do (other than making me chuckle each time I learn the identify PQsendQueryGuts ????)? Internally PQsendQueryGuts communicates with Postgres on the protocol stage:

/* assemble the Parse message */
if (pqPutMsgStart('P', conn) < 0 ||
  pqPuts(stmtName, conn) < 0 ||
  pqPuts(command, conn) < 0) {}

/* Assemble the Bind message */
if (pqPutMsgStart('B', conn) < 0 ||
  pqPuts("", conn) < 0 ||
  pqPuts(stmtName, conn) < 0) {}

/* assemble the Execute message */
if (pqPutMsgStart('E', conn) < 0 ||
  pqPuts("", conn) < 0 ||
  pqPutInt(0, 4, conn) < 0 ||
  pqPutMsgEnd(conn) < 0) {}

That is the Parse/Bind/Execute course of I discussed earlier.

• The code sends a Parse message with the question and an elective identify. In our case the identify is empty
• The code then Binds params to that question (if the question is parameterized)
• It then Executes utilizing the mixture of the parsed question and the sure params

That is completely secure to do in transaction mode, and from a SQL security perspective ought to behave identically to a named ready assertion.

Named protocol-level statements

For comparability, when ActiveRecord has ready statements turned on, issues look a bit completely different, however by the tip we’re in the identical place:

def exec_cache(sql, identify, binds...)
  #...pseudo coded a bit however importantly
  #   it calls `put together`
  if !cached
    stmt_key = conn.put together(sql)
  # then it calls exec_prepared
  conn.exec_prepared(stmt_key, type_casted_binds)

It first has to name put together with no matter sql we’re going to run. The caller is in control of protecting monitor of whether or not the sql has been ready earlier than, in any other case Postgres will hold overwriting our earlier sql and it’d as nicely simply execute an unnamed assertion. Then it calls exec_prepared with solely the stmt_key, which ought to match the identify of a beforehand ready question.

If we skip forward to what will get referred to as in libpq:

// conn.put together(sql)
int
PQsendPrepare(PGconn *conn,
    const char *stmtName, 
    const char *question,
    int nParams, 
    const Oid *paramTypes) {
  //...
  if (pqPutMsgStart('P', conn) < 0 ||
      pqPuts(stmtName, conn) < 0 ||
      pqPuts(question, conn) < 0) {}
  //...
}

We see one thing much like our earlier Parse/Bind/Execute, however now we’re solely calling the Parse portion and this time we now have a stmtName. We then set off the ready assertion calling exec_prepared, which in the end calls PQsendQueryPrepared:

// conn.exec_prepared(stmt_key, type_casted_binds)
int
PQsendQueryPrepared(PGconn *conn,
    const char *stmtName,
    int nParams,
    const char *const *paramValues,
    const int *paramLengths,
    const int *paramFormats,
    int resultFormat) {
  //...
  return PQsendQueryGuts(conn,
      NULL,     // no sql
      stmtName, // named
      nParams,
      NULL,
      paramValues,
      paramLengths,
      paramFormats,
      resultFormat);
  //...
}

Something look acquainted? That’s the identical PQsendQueryGuts operate we referred to as for the unnamed assertion! This time it doesn’t hand a command in as a result of we already parsed our SQL within the earlier put together name. We even have a stmtName outlined, as a substitute of handing in an empty string. This model goes on to skip the Parse, name the Bind with the stmtName, then name Execute – similar circulate as our unnamed model.

For SQL injection security, each named and unnamed variations are equal: they separate question construction (Parse) from knowledge values (Bind). Including question bindings when not in a ready assertion merely makes an unnamed assertion.

Nothing about these calls is particular to the libpq library, it’s only a rock strong implementation of them ^{– any language might make the identical protocol calls. If a library is using this protocol, they’re doing the identical issues when binding to an unnamed ready assertion as they’re when binding to a named ready assertion.}

So long as your code makes use of parameterized queries, “turning off” ready statements for PgBouncer is secure, even when it appears a bit unnerving. There’s a PR to allow PgBouncer to track prepared statements, so possibly this received’t trigger folks like me as a lot heartburn sooner or later ????.

Pool throughput / Lengthy working queries ????‍♂️

We’ve bought two kinds of connections to Postgres: lively and idle. Idle connections are the spine of poolers – having idle connections means we’ve bought capability to swap round transactions for related purchasers. What about lively connections?

An lively connection signifies that connection is actively tied up by the database. For that timespan, the connection can’t be swapped out to do the rest till its operation completes. We all know that lively connections get costly rapidly, and we additionally know that almost all managed providers vary someplace from 50 to 500 allowed whole, non-pooled connections.

Utilizing a max PgBouncer connection pool of 10k and Render’s managed Postgres service with a max of 397 whole connections means we’d have:

10000 / 397 = ~25 connections per lively connection

Utilizing Supabase’s 50 connections the unfold is even greater:

10000 / 50 = ~200 connections per lively connection

That signifies that for each lengthy working operation, you might be probably down 200 connections price of pooling.

These numbers are very again of the serviette and naturally don’t characterize the true scaling functionality and connection dealing with of an actual pooler. However the level is that this:

• Energetic connections are very worthwhile to a pooler
• Lengthy working queries disproportionally impression concurrency

For instance, you’re utilizing Render Postgres fronted by PgBouncer and also you’ve bought 10k out there connections backed by the max of 397 Postgres connections. Let’s say a brand new characteristic is launched for populating some graph knowledge in your app’s touchdown web page. It’s powered by a brand new question that appears nice, has indexes, and appears nicely optimized. It’s even run towards some load testing and representatively sized knowledge as a QA verify. It will get deployed to manufacturing and OOF, it’s taking 15 seconds per question ????. Customers are logging in or navigating to the touchdown web page on a regular basis so inside moments you’ve had 1000’s of hits to this question. Clearly that is going to get rapidly rolled again, however what does it imply on your pool within the meantime?

It means you’re maxed out. Your pooler being there means no less than you’re much less prone to begin erroring out instantly, however transaction mode can’t repair a caught question. For every of these 15 second chunks of time your concurrency mainly went from 10k again all the way down to 397.

This isn’t the final habits you’ll see when utilizing PgBouncer until you’ve actually bought some intermittent hassle with runaway queries. But it surely does emphasize an vital level to recollect: these should not actual Postgres connections. Your higher sure on lengthy working, lively queries is at all times constrained by your precise pool of actual Postgres connections.

Guarding towards gradual queries

• Log your slow queries utilizing log_min_duration_statement. This selection helps you to set a threshold and if queries take lengthy than that threshold Postgres will log the offending question. This received’t assist the sudden mass gradual question scenario talked about above, nevertheless it helps to keep watch over general app question well being
• Use streaming queries sparingly. In most consumer libraries you possibly can set your question to run in “single row mode”. This implies you retrieve your rows separately as a substitute of getting one huge consequence set without delay. That is useful for effectivity with very massive consequence units however is slower than a full consequence set question, and doubtless means you might be working queries massive sufficient to be slower within the first place
• Use statement timeouts. That is difficult, particularly when pooling, however see that part for concepts on learn how to strategy it
• Unfold out reads throughout learn replicas

Session Stage Advisory Locks ????

Session stage advisory locks work tremendous in PgBouncer.

Sorry ????.

Should you’ve learn the earlier sections you’ve already picked up on the sample: “session” something means it in all probability doesn’t work in transaction mode. However what does that matter to you?

Advisory locks are an incredible possibility for creating easy, cross course of, cross server software mutexes primarily based on a offered integer key. In contrast to conventional locks you utilize/encounter elsewhere in Postgres that are tied to tables or rows, advisory locks will be created impartial of tables to regulate software stage issues. There are many different instruments you could possibly use for this job outdoors of Postgres, however since Postgres is already a part of your tech stack it’s a handy and easy possibility.

Throughout languages a typical use case for session stage advisory locks is to carry a lock whereas database migrations (ie, DDL) are being run. For instance:

-- 1234 is bigoted, it may be any integer
SELECT pg_advisory_lock(1234);
SET lock_timeout TO '1s';
ALTER TABLE my_table...;
INSERT INTO migrations VALUES (1234567);
-- If we do not explicitly unlock right here, the lock will likely be held till this 
--    connection is closed
SELECT pg_advisory_unlock(1234);

If one other connection went to amass the identical lock, it will be blocked:

-- This may block indefinitely till the opposite connection is closed, 
--    or calls pg_advisory_unlock(1234)
SELECT pg_advisory_lock(1234);

That is largely an try to enhance consistency of migration monitoring, and assist coordinate multi course of deploys:

• Steady deployment with the potential to set off a number of deployments in succession
• Propagating code modifications to a number of servers with deploy scripts robotically triggering migrations in every context

By ready to amass a lock on the Postgres stage, every course of waits for the primary lock proprietor to complete earlier than persevering with, coordinating every course of primarily based on a shared lock key.

As soon as extra, with feeling PgBouncer

Now for the compulsory instance of attempting the identical factor when related to PgBouncer ????:

-- Seize the lock on connection 1
SELECT pg_advisory_lock(1234);
-- Connection 1 goes again into pool
-- ...
-- Attempt to unlock on connection 2, which doesn't personal the 1234 lock
SELECT pg_advisory_unlock(1234);
-- WARNING: you do not personal a lock of sort ExclusiveLock

We attempt to unlock, however as a result of we’re on a special connection we are able to’t. The lock stays locked for so long as connection 1 stays alive, which suggests now nobody else can purchase that lock until that connection naturally closes in some unspecified time in the future or is explicitly pg_cancel_backended ????.

Extra session advisory lock use instances

Outdoors of migrations, advisory locks can serve different use instances:

• Utility mutexes on delicate operations like ledger updates
• Leader election for sustaining a single however fixed daemon operation throughout servers
• Precisely as soon as run job controls for Postgres primarily based job techniques like GoodJob and Que

If this stuff sound attention-grabbing or helpful, they’re! However provided that you join on to Postgres.

Transaction stage locks

Advisory locks do have a transaction primarily based companion:

-- Course of 1
BEGIN;
SELECT pg_advisory_xact_lock(1234);

-- Course of 2 
-- Blocks whereas course of 1 is within the transaction
SELECT pg_advisory_lock(1234);

-- Again in Course of 1
SET LOCAL lock_timeout TO '1s';
ALTER TABLE my_table...;
INSERT INTO migrations VALUES (1234567);
COMMIT; -- robotically unlocks on commit or rollback
-- Course of 2 now can purchase the lock

-- If it is advisable manually unlock whereas nonetheless within the transaction 
-- SELECT pg_advisory_xact_unlock(1234);

You would use it as a alternative for sure situations, just like the above migration working transactionally. For customized functions, it’s a very good different!

Sadly most migration tooling, issues like chief election, and request or job lifetime locks, all use or require an extended lived lock than a single transaction might fairly present.

Flip off advisory migration locks

If it is advisable run migrations towards PgBouncer, in Rails you possibly can flip them off with an advisory_locks flag in database.yml. Different migration instruments doubtless have one thing related. Do it at your personal peril ????????‍♂️

Sustaining a separate direct connection to Postgres

If the lock is important, however the operations previous the lock fan out and purchase a number of connections, you could possibly probably have two items:

• A direct connection to Postgres the place you purchase a session stage advisory lock
• Your regular code level connection pooling utilizing your PgBouncer connections so it may capitalize on the scaling alternatives offered there

There’s an apparent draw back – you’re consuming an additional direct connection and probably impacting throughput – nevertheless it’s another out there if wanted.

Pay attention / Notify ????

Postgres comes out of the field with a helpful pub/sub characteristic referred to as LISTEN/NOTIFY.

You merely name:

LISTEN channel_name;

And that connection will obtain NOTIFY occasions:

NOTIFY channel_name, 'hello there!';

Like session stage advisory locks, there are extra strong pub/sub options on the market. However the Postgres implementation works nicely, and you have already got it out there in your stack.

Trying on the instance, you’ll discover that the LISTEN name is only a single assertion, and it prompts the listener for the present session. What have we mentioned so many instances already? Periods unhealthy. Transactions good… type of.

type of?

Just like ready statements, the docs are deceptive relating to LISTEN/NOTIFY.

PgBouncer formally lists LISTEN/NOTIFY as an unsupported characteristic in transaction mode, which isn’t exactly true. LISTEN doesn’t work in transaction mode, however NOTIFY does.

NOTIFY is a single assertion, and doesn’t depend on any session semantics. It’s additionally transactional^:

BEGIN;
NOTIFY channel_name, 'hello!';
ROLLBACK; -- no notification is shipped

Each NOTIFY codecs (inside and outdoors a transaction) work tremendous with transaction mode pooling. If you wish to use pub/sub, you simply want to ensure your LISTENer is related on to Postgres. Since it can be hard to tell if you’re connected to Postgres or PgBouncer that is considerably difficult, sadly.

I’ve constructed implementations LISTENing on a non-PgBouncer connection and NOTIFYing on PgBouncer that work tremendous. There’s not a lot writing on this, however I’ve discovered this strategy to work nicely.

The only thread ????

In distinction to the multi course of monster that’s Postgres, PgBouncer runs on a paltry single course of with a single thread.

Which means regardless of how succesful a server is, PgBouncer is simply going to make the most of a max of 1 CPU core so as soon as you’ve maxed out on that core you possibly can’t scale that single occasion anymore.

A preferred possibility is to load balance PgBouncer instances. In any other case, nearly each different to PgBouncer (like Odyssey, PgCat and Supavisor) make the most of a number of cores.

Should you’re utilizing a managed Postgres service (like Crunchy Information, Supabase, Neon or Heroku), your default possibility goes with PgBouncer as a connection pooler – so it is going to be as much as these providers to supply a load balanced possibility.

pg_dump ????

Should you’re working pg_dump towards PgBouncer, it’s in all probability by mistake.

So far as I can inform, pg_dump is damaged when run towards PgBouncer. See https://github.com/pgbouncer/pgbouncer/issues/452.

The reply right here is to be sure to’re utilizing a direct connection to Postgres for utility operations like pg_dump.

Different unavailable options ????

There are some remaining options which transaction mode is incompatible with as nicely^{. I’ve much less or no expertise with these:}

• WITH HOLD CURSOR – A WITH HOLD continues to exist outdoors of a transaction, which looks as if it might have helpful use instances however I’ve by no means personally used it in my everyday.
• PRESERVE/DELETE ROWS temp tables – non permanent tables are a session stage characteristic so is not going to work correctly, and protect/delete rows are modifiers on how these non permanent tables behave on commit, and are unsupported
• LOAD assertion – that is for loading shared libraries into Postgres, so it is sensible this isn’t one thing you have to be doing via a pooler. I haven’t truly tried, so I’m unsure if PgBouncer would cease you, nevertheless it requires tremendous consumer privileges so it’s not possible that’s what your PgBouncer consumer has

PgBouncer paperwork a easy “SQL feature map for pooling modes” the place you possibly can see all of the options talked about on this put up.

Linting ????

Other than having recognized potential points – what can we do to keep away from them in an automatic means?

Surprisingly, not a lot exists. And by not a lot, I imply i’ve discovered nothing outdoors of recommendation.

It makes me really feel a bit like I’m exaggerating the significance of those points. Perhaps I’m the oddball that has truly encountered lots of them in actual manufacturing utilization and needed to tackle them. I’ve had assertion timeouts and lock timeouts misapplied. I’ve needed to cope with rearranging connections due to code utilizing a session advisory lock and LISTEN/NOTIFY, or drop libraries that use them. I’ve needed to bear in mind to show off ready statements in my ORM to keep away from named ready assertion errors.

The implications can really feel small, however they are often stunning and notably round migrations may cause actual severe downtime.

We lint in every single place. As engineers we attempt to automate away as many errors as potential with linting and specs. As growth groups develop, the significance of automation turns into important to scaling as a result of in any other case somebody someplace goes to do the incorrect factor and it received’t get caught.

Some concepts that might be nice to see:

• PgBouncer elective course of that detects unhealthy queries and logs them
• RDS connection pinning habits
• Static evaluation instruments for app queries
• Runtime extension to consumer libraries
• Ensuring your growth circulate runs PgBouncer regionally to try to encounter this habits earlier than working on manufacturing

Within the rails world there are several active gems dedicated to protecting a codebase secure from points that might trigger downtime whereas migrating tables (ie, zero downtime). However throughout ecosystems I couldn’t discover something associated to defending towards PgBouncer points.

As a step on this route, I’ve revealed a (presently experimental) gem to be used in Rails/ActiveRecord apps referred to as pg_pool_safe_query. It is going to log warnings if SQL is run that’s incompatible with PgBouncer and lift an error if advisory locks and ready statements should not disabled.

Can we enhance connections with no pooler?

A more moderen growth in Postgres 14 was enhancements to snapshot scalability, which appear to have resulted in huge enhancements in effectively maintaining more idle connections in Postgres.

It’s thrilling to see effort being utilized to growing connection effectivity in Postgres itself. The writer of that snapshot scalability enchancment strains up with my very own frustrations:

• Ideally Postgres would higher deal with site visitors spikes with out requiring a pooler
• Poolers minimize out helpful database options
• Postgres itself would ideally transfer in the direction of structure modifications throughout a number of key areas, finally culminating in a bigger transfer in the direction of a lighter weight course of/thread/async mannequin which higher aligns with the C10k downside out of the field

Many of the work within the business appears to focus on constructing higher poolers, relatively than enhancing the internals of Postgres connection dealing with itself^{. Outdoors of PgBouncer you’ve bought RDS Proxy, Odyssey, PgCat, Supavisor, PgPool II and I’m positive others. All have their very own advantages however undergo from the identical transactional scaling limitations.}

In equity to the unbelievable work that goes into Postgres – each efficiency enchancment they make in each new model can be a connection scalability enchancment. If the queries, indexes, plans, and processes are making huge efficiency good points with every model then much less connections can do extra.

PgBouncer alternate options

There are alternate options to PgBouncer, however the identical transaction limitations apply to all of them: every has a transaction mode (or function completely in transaction mode) that gives one of the best scaling. As soon as in transaction mode you possibly can’t help most session stage options anymore and also you’re working off of the truth that database connections spend extra time being idle than lively.

All of them have their very own distinctive advantages compared, however have the identical elementary transaction limitations.

Am I lastly accomplished with this put up?

I believe I’ve mentioned sufficient.

Postgres is nice. PgBouncer is vital. Know what can go incorrect and account for it.

???? ✌???? ????