The leading data integration platform for ETL / ELT data pipelines from APIs, databases & files to data warehouses, data lakes & data lakehouses. Both self-hosted and Cloud-hosted.
I’m facing an issue when trying to run Airbyte/Temporal with PgBouncer.
At my workplace, we have some instances of Airbyte, all sharing a single database server, with each instance using its own exclusive set of databases.
As the number of Airbyte instances has grown, we noticed that the number of open connections was increasing rapidly, exhausting the database server’s resources. I started encountering the error:
FATAL: remaining connection slots are reserved for non-replication super-users
By observing these connections, it became clear that they are opened and not closed, with almost all of them remaining in an idle state.
We first attempted to use the SQL_MAX_IDLE_CONNS setting as described here, but this didn’t work; setting this variable didn’t make any difference.
Our idea was to use PgBouncer in transaction pool mode to avoid this large number of open connections, but the airbyte/temporal services didn’t behave well when running behind PgBouncer.
The error I’m seeing is:
pq: bind message supplies X parameters, but prepared statement \"\" requires Y
After researching this issue further, I discovered that this is happening because of prepared statements, which by default do not work with this type of DB connection pooling. One potential solution is to use SQL_CONNECT_ATTRIBUTES=binary_parameters=yes, but nothing changed; it seems that airbyte/temporal is not using this connection attribute.
The full log can be found here.
Some questions I have:
Is this large number of database connections expected?
Should Temporal work when running behind PgBouncer, or is this a pattern that should be avoided?
Platform Version
0.50.29
What step the error happened?
On Deploy
Relevant information
I’m facing an issue when trying to run Airbyte/Temporal with PgBouncer.
At my workplace, we have some instances of Airbyte, all sharing a single database server, with each instance using its own exclusive set of databases.
As the number of Airbyte instances has grown, we noticed that the number of open connections was increasing rapidly, exhausting the database server’s resources. I started encountering the error:
FATAL: remaining connection slots are reserved for non-replication super-users
By observing these connections, it became clear that they are opened and not closed, with almost all of them remaining in an
idle
state.We first attempted to use the
SQL_MAX_IDLE_CONNS
setting as described here, but this didn’t work; setting this variable didn’t make any difference.Our idea was to use PgBouncer in
transaction
pool mode to avoid this large number of open connections, but theairbyte/temporal
services didn’t behave well when running behind PgBouncer.The error I’m seeing is:
pq: bind message supplies X parameters, but prepared statement \"\" requires Y
After researching this issue further, I discovered that this is happening because of prepared statements, which by default do not work with this type of DB connection pooling. One potential solution is to use
SQL_CONNECT_ATTRIBUTES=binary_parameters=yes
, but nothing changed; it seems thatairbyte/temporal
is not using this connection attribute.The full log can be found here.
Some questions I have:
Links to identical and similar issues:
Relevant log output