Odyssey – Scalable PostgreSQL connection pooler
1–10 of 42 posts
Re: Odyssey – Scalable PostgreSQL connection pooler
#2> Advanced transactional pooling
> Odyssey tracks current transaction state and in case of unexpected client disconnection can emit automatic Cancel connection and do Rollback
that's my biggest issue with pgbouncer, is there a docker image for it?
Re: Odyssey – Scalable PostgreSQL connection pooler
#3Would be interesting to have a side by side comparison with PgBouncer
Re: Odyssey – Scalable PostgreSQL connection pooler
#4I often wonder why connections aren't made more lightweight in Postgres, or if there was an option to steal a connection and have a "RESET" command that destroyed all state. In my Postgres library, I have to keep state information too just so I can "reset" a connection. Also maybe the protocol can add a (optionally client supported) PING to check for socket death to know if a connection is stealable.
Re: Odyssey – Scalable PostgreSQL connection pooler
#5Would be interesting to have a side by side comparison with PgBouncer
agreed, what's the difference?
Re: Odyssey – Scalable PostgreSQL connection pooler
#6> Advanced transactional pooling > Odyssey tracks current transaction state and in case of unexpected client disconnection can emit automatic Cancel connection and do Rollback that's my biggest issue with pgbouncer, is there a docker image for it?
is this a problem with pg bouncer? pg bouncer supports transactional pooling which is meant to only return connections back into the pool when they are not in a transaction. so seeing as it keeps tracking of the transaction status it seems pretty crazy that it would put a connection back into the pool that has an open transaction.
i tested this on my local machine and if i drop a connection while it is inside a transaction it closes the server connection.
client close:
2018-05-30 15:28:42.068 21702 LOG C-0x7f9728816a10: DB/USER@[::1]:64342 closing because: client close request (age=85)
2018-05-30 15:28:42.068 21702 LOG S-0x7f972980a190: DB/USER@127.0.0.1:5432 closing because: unclean server (age=85)
2018-05-30 15:33:57.197 21702 LOG C-0x7f9728816a10: DB/USER@[::1]:64376 closing because: client unexpected eof (age=15)
2018-05-30 15:33:57.198 21702 LOG S-0x7f972980a190: DB/USER@127.0.0.1:5432 closing because: unclean server (age=10)
Re: Odyssey – Scalable PostgreSQL connection pooler
#7I often wonder why connections aren't made more lightweight in Postgres, or if there was an option to steal a connection and have a "RESET" command that destroyed all state. In my Postgres library, I have to keep state information too just so I can "reset" a connection. Also maybe the protocol can add a (optionally client supported) PING to check for socket death to know if a connection is stealable.
Is there any connection state that is not stored in pg_settings? You can check that table to see what settings were overridden in the session.
Re: Odyssey – Scalable PostgreSQL connection pooler
#8I often wonder why connections aren't made more lightweight in Postgres, or if there was an option to steal a connection and have a "RESET" command that destroyed all state. In my Postgres library, I have to keep state information too just so I can "reset" a connection. Also maybe the protocol can add a (optionally client supported) PING to check for socket death to know if a connection is stealable.
That already exists via the "DISCARD" command: https://www.postgresql.org/docs/current/static/sql-discard.h...
Re: Odyssey – Scalable PostgreSQL connection pooler
#9This looks pretty interesting. Will definitely spend some time testing it.
Shameless plug. We have recently forked pgbouncer to add multicore support[0]. We are running in production for couple of weeks and the performance is great.
Our design is very straightforward. Instead of touching the current code, we've extended it by a manager, that spins workers, which are essentially forks of pgbouncer itself (one per core, or whatever you specify in the settings), and then distributes the connections between the clients and the workers. So if you decide not to use the multiprocessing part, you can just turn it off and you will be running the same old pgbouncer you are used to. It also allows for the code to be merged to the original code base without any significant changes.
Re: Odyssey – Scalable PostgreSQL connection pooler
#10I often wonder why connections aren't made more lightweight in Postgres, or if there was an option to steal a connection and have a "RESET" command that destroyed all state. In my Postgres library, I have to keep state information too just so I can "reset" a connection. Also maybe the protocol can add a (optionally client supported) PING to check for socket death to know if a connection is stealable.
That already exists via the "DISCARD" command: https://www.postgresql.org/docs/current/static/sql-discard.h...
I should have clarified, I meant at the protocol level. It's basically a state machine, and I want a "break" to reset the state machine. This would flush remaining query results, close named prepared statements, rollback any in-process transactions, and put the state back at ready-for-query. In the meantime, those with connections pools (this lib, my client-side lib, etc) have to keep this info (often except prepared statements which is the caller's responsibility to close).