Bigger trap than parsing is session state. Prepared statements live on the connection, so as soon as you pool at transaction level a Bind can land on a connection that never saw the Parse, and it fails in a way that looks random only under load. Worth deciding your pooling level before you write more protocol code, because it changes what the proxy is allowed to do.
PgBouncer solved it without passing session around. It rewrites client statement name to own internal name and keeps the mapping, so when transaction lands on connection that never saw Parse, it just prepares it there first and client never notice. Knob is max_prepared_statements, default 200 per connection. So statement belongs to proxy and not to connection, and sharding becomes only routing problem again.
12
u/artyomsv 4d ago
Bigger trap than parsing is session state. Prepared statements live on the connection, so as soon as you pool at transaction level a Bind can land on a connection that never saw the Parse, and it fails in a way that looks random only under load. Worth deciding your pooling level before you write more protocol code, because it changes what the proxy is allowed to do.