4 ms·

There is Proxy/Connector approaches - ProxySQL, pgPool etc solve this problem in a different way and often is best approach but I still think it is very interes

There is Proxy/Connector approaches - ProxySQL, pgPool etc solve this problem in a different way and often is best approach but I still think it is very interesting to see how much the database itself can handle.

Diggsey 8y ago
Frustratingly "solutions" like pgBouncer and similar do not properly maintain session state in transaction pooling mode and so commonly used features like prepared statements cannot be used, which is a non-starter in many cases. Meanwhile, session pooling mode provides almost no benefit unless you completely disable connection pooling in your application, which then means you incur the cost of setting up a new connection on each request. MySQL's ability to handle many thousands of idle connections without breaking a sweat actually solves this problem, and does so in a very convenient way, particularly in a micro-services architecture, and I really wish Postgres supported it.
- anarazel 8y ago
  > MySQL's ability to handle many thousands of idle connections without breaking a sweat actually solves this problem, and does so in a very convenient way, particularly in a micro-services architecture, and I really wish Postgres supported it. There's several PG developers aware of this, and hope to work on this. Getting the resources to work on it isn't trivial, but looks like it might happen. Obviously that's not going to help you short term...
- ioltas 8y ago
  There is a range of interesting problems here for Postgres. One is related to the costly way snapshots are built which needs to scan all entries in PGPROC to get back a list of transaction IDs. So even idle connections come at a cost. There have been proposed patches to improve the scalability in this area, one being called CSN (Commit Sequence Number) which I think is rather promising: https://www.postgresql.org/message-id/CA+CSw_tEpJ=md1zgxPkjH6CWDnTDft4gBi=+P9SnoC+Wy3pKdA@mail.gmail.com https://www.postgresql.org/message-id/CA+CSw_tEpJ=md1zgxPkjH...
  - Diggsey 8y ago
    Interesting, but surely snapshots are only needed for connections with active transactions? My understanding was that the difficulty here was Postgres's currently very simple forked-process-per-connection model. In order for idle connections to take minimal resource, connections can no longer be mapped 1:1 with processes. Instead, active connections will have to be balanced across a process or thread pool, whilst idle connections' states will have to be stored in a master process. Not only that, but it must be possible to transmit a full connection state between processes, and also to queue-up idle connections transitioning to the active state if there is a sudden burst exceeding the size of the process pool.
    - anarazel 8y ago
      > Interesting, but surely snapshots are only needed for connections with active transactions? Right, but the problem is that the cost to compute them essentially scales with the number of active connections (and to some degree with the number of allowed connections). Which means if your system actually does things, all those idle connections cost. We can, and should, make the constant factor for computing this considerably smaller. But obviously larger architectural issues need to be addressed too, at some point.. Edit: expand.
  - anarazel 8y ago
    FWIW, I don't think CSN actually gets us that far, because the data structures to map xids to CSNs aren't that cheap to maintain either (which is needed for MVCC visibility tests).
- jashmatthews 8y ago
  Prepared statements actually work fine with PGBouncer. Anonymous prepared statements via PqExecParams work completely without issue. No need to go back to escaping and putting params in the query string! Named prepared statements via PREPARE also work, but the client needs to use deterministic names and rescue if the query isn’t prepared: https://github.com/diesel-rs/diesel/issues/1028#issuecomment-315748691 https://github.com/diesel-rs/diesel/issues/1028#issuecomment...
  - EB66 8y ago
    I was just about to comment the same thing. We use PgBouncer for some of our production databases and we make heavy use of prepared statements, stored procedures, etc.
- deleted 8y ago
  [deleted]
tracker1 8y ago
Or, if you're mostly-read and can separate read vs. write scenarios, then one master for writes and read replicas works pretty well too. In any case, DBMS can usually scale vertically relatively well (lots of CPU, Ram, NVME etc).

Diggsey 8y ago

Frustratingly "solutions" like pgBouncer and similar do not properly maintain session state in transaction pooling mode and so commonly used features like prepared statements cannot be used, which is a non-starter in many cases. Meanwhile, session pooling mode provides almost no benefit unless you completely disable connection pooling in your application, which then means you incur the cost of setting up a new connection on each request. MySQL's ability to handle many thousands of idle connections without breaking a sweat actually solves this problem, and does so in a very convenient way, particularly in a micro-services architecture, and I really wish Postgres supported it.

anarazel 8y ago
> MySQL's ability to handle many thousands of idle connections without breaking a sweat actually solves this problem, and does so in a very convenient way, particularly in a micro-services architecture, and I really wish Postgres supported it. There's several PG developers aware of this, and hope to work on this. Getting the resources to work on it isn't trivial, but looks like it might happen. Obviously that's not going to help you short term...
ioltas 8y ago
There is a range of interesting problems here for Postgres. One is related to the costly way snapshots are built which needs to scan all entries in PGPROC to get back a list of transaction IDs. So even idle connections come at a cost. There have been proposed patches to improve the scalability in this area, one being called CSN (Commit Sequence Number) which I think is rather promising: https://www.postgresql.org/message-id/CA+CSw_tEpJ=md1zgxPkjH6CWDnTDft4gBi=+P9SnoC+Wy3pKdA@mail.gmail.com https://www.postgresql.org/message-id/CA+CSw_tEpJ=md1zgxPkjH...
- Diggsey 8y ago
  Interesting, but surely snapshots are only needed for connections with active transactions? My understanding was that the difficulty here was Postgres's currently very simple forked-process-per-connection model. In order for idle connections to take minimal resource, connections can no longer be mapped 1:1 with processes. Instead, active connections will have to be balanced across a process or thread pool, whilst idle connections' states will have to be stored in a master process. Not only that, but it must be possible to transmit a full connection state between processes, and also to queue-up idle connections transitioning to the active state if there is a sudden burst exceeding the size of the process pool.
  - anarazel 8y ago
    > Interesting, but surely snapshots are only needed for connections with active transactions? Right, but the problem is that the cost to compute them essentially scales with the number of active connections (and to some degree with the number of allowed connections). Which means if your system actually does things, all those idle connections cost. We can, and should, make the constant factor for computing this considerably smaller. But obviously larger architectural issues need to be addressed too, at some point.. Edit: expand.
- anarazel 8y ago
  FWIW, I don't think CSN actually gets us that far, because the data structures to map xids to CSNs aren't that cheap to maintain either (which is needed for MVCC visibility tests).
jashmatthews 8y ago
Prepared statements actually work fine with PGBouncer. Anonymous prepared statements via PqExecParams work completely without issue. No need to go back to escaping and putting params in the query string! Named prepared statements via PREPARE also work, but the client needs to use deterministic names and rescue if the query isn’t prepared: https://github.com/diesel-rs/diesel/issues/1028#issuecomment-315748691 https://github.com/diesel-rs/diesel/issues/1028#issuecomment...
- EB66 8y ago
  I was just about to comment the same thing. We use PgBouncer for some of our production databases and we make heavy use of prepared statements, stored procedures, etc.
deleted 8y ago
[deleted]

tracker1 8y ago

Or, if you're mostly-read and can separate read vs. write scenarios, then one master for writes and read replicas works pretty well too. In any case, DBMS can usually scale vertically relatively well (lots of CPU, Ram, NVME etc).