support pgbouncer with SpiceDB Postgres datastore w/o session pooler mode

[vroldanbet]

a few users have reported that having pgbouncer in front of Postgres does not work with the PG datastore. See this discord discussion:

2 UNKNOWN: unable to find revision: ERROR: prepared statement \"lrupsc_89_0\" does not exist (SQLSTATE 26000)

Another user reported an error like:

prepared statement name is already in use (SQLSTATE 08P01)

The issue seems to originate from the fact that SpiceDB uses a pgx execution mode that does 2 roundtrips, one to prepare the statement and another to execute it. PGBouncer would presumably switch the underlying connections, which can lead to errors like the above described.

The current workaround, as described by some SpiceDB users, is to configure PgBouncer with connection pooler mode set to session and to ignore plan_cache_mode with ignore_startup_parameters=plan_cache_mode.

Ideally, a sustainable long-term solution is found that does not involve configuring specific settings in PgBouncer.

[sashayakovtseva]

+1 pgbouncer is commonly used with postgres.

[vroldanbet]

Reopening as we are now testing it, but not documenting it, @bradengroom would you be so kind to add some docs? 🙏🏻 I think https://github.com/authzed/docs/blob/main/docs/spicedb/selecting-a-datastore.md would be the right place

[sashayakovtseva]

As I understand, session_pooling is the only possible way to use pgbouncer and it's not planned to support tx_pooling, correct?

[vroldanbet]

@sashayakovtseva from the work done by @bradengroom in https://github.com/authzed/spicedb/pull/1626, and also from a cursory look online, it appears that SpiceDB would need session_pooling for prepared statements. SpiceDB now has a nice test-suite contributed by @bradengroom that could help determining if it works.

It does not mean that it won't be supported, if there is a critical mass of folks requesting this, it could be supported, at the expense of query performance degradation (as you can see from https://github.com/authzed/spicedb/pull/1626).

I think some of the work on https://github.com/authzed/spicedb/pull/1626 could be reused to support having folks disabling prepared statements.

[pdeaudney]

In PostgreSQL you can also set GUC values such as plan_cache_mode on the database or user. https://www.postgresql.org/docs/current/config-setting.html#CONFIG-SETTING-SQL

spicedb=> alter user spicedb SET plan_cache_mode = force_custom_plan;
ALTER ROLE

or

spicedb=> alter DATABASE spicedb SET plan_cache_mode = force_custom_plan;
ALTER DATABASE

You can also set these at user/db creation time too I believe.

After altering the user or db, if you reconnect the plan_cache_mode will be set.

spicedb=> show plan_cache_mode;
  plan_cache_mode
-------------------
 force_custom_plan

If it is not set, the value you would see will be auto.

But this combination might resolve the performance regressions observed in #1626 when using pgbouncer without prepared transactions.

In PgBouncer you will need to set a config like this (key detail being ignore_startup_parameters=plan_cache_mode

[databases]
spicedb = host=localhost port=5432 dbname=spicedb

[pgbouncer]
pool_mode = session
ignore_startup_parameters=plan_cache_mode

listen_port = 6432
listen_addr = localhost
auth_type = md5
auth_file = userlist.txt
max_prepared_statements = 100

logfile = pgbouncer.log
pidfile = pgbouncer.pid

admin_users = someuser

Additionally in AWS RDS proxy you can not currently adjust startup parameters on the connection. So currently https://github.com/authzed/spicedb/blob/5ed2d0f28b42da82fad93601fec49c41a2d03a0e/internal/datastore/postgres/postgres.go#L694-L713 causes connections going through RDS proxy to be rejected with errors

RDS Proxy currently doesn't support the option plan_cache_mode. (SQLSTATE 0A000)