FWD » Core Development » Database

One idea could be:

Configure a single cluster-wide connection budget per physical database instead, and let each node derive its own share of that budget automatically from how many peers currently exist.

Shape of the design:
- Each node's pool ceiling becomes a derived value, not a configured one: total budget ÷ current member count, recomputed whenever membership changes (a node joins or leaves) rather than fixed at startup.
- A floor guards the derived value so a temporarily small cluster (e.g., mid rolling-deploy, with only one node briefly up) doesn't get starved down to an unworkably small pool.
- The recompute needs to be debounced against membership flapping — a node's derived share shouldn't visibly change on every transient blip, only on a stable, sustained topology change.
- The mechanism for actually changing a live node's pool ceiling already exists conceptually in how pools are configured today — this design reuses that same idea, just triggered by a membership event instead of only at process startup, and understanding that shrinking a live pool is gradual (existing checked-out connections aren't forcibly reclaimed; the ceiling just stops future growth and lets the pool drain toward the new target).

I like this a lot. In order to handle load balancing and the natural variability of the number of tenant sessions per server, it will likely mean that the per-tenant pool size will need to be larger than the current single-server value by some factor. I would hope that factor could be smaller than the single_server_tenant_connections * n (where n is the number of FWD servers).

max_connections is 'per cluster'. This needs to consider the max_connections for that cluster, and group these values per host:port (or other ways to identify the psql cluster).

From the cluster, it can be read via show max_connections. And after this we can infer what c3p0 maxPoolSize can be for that cluster, considering the number of databases in the postgresql cluster, the number of agents/MSA sessions, batch processes, etc. I think we may be able to move further with auto-configuring this.

As a follow up to the today's meet discussion, I think some other thing that we could do is to impose a logical trashhold to the nodes (insead of imposing a hard requirement of maxPoolSize).

1. Set maxPoolSize high, once, and never touch it again. (could be as high as max_connections)
Pick a value generous enough that no node/sibling would ever legitimately need more. Because it's static, c3p0 never has to tear down and rebuild the pool — the destructive-resize problem goes away entirely.

2. Add a local gate in front of each pool that actually limits concurrent use.
Since maxPoolSize no longer does the enforcing, something else must cap how many connections a given pool is allowed to have checked out at once. This gate can change instantly, because it's just an in-memory limit, not a pool resize.

3. Within one node, split that node's share among its sibling pools by demand.
This part already exists is implemented in 11633a — a sibling that's actively used earns a bigger slice of the node's local share, an idle one settles toward a floor. That local share now feeds the gate from step 2 instead of feeding setMaxPoolSize().

4. Across nodes, let each node broadcast its demand and recompute its own share.
Every node publishes roughly how much it currently needs; every node reads everyone else's published demand and works out its own fair slice of the whole cluster budget — no node asks another for permission, they all just react to the shared picture. A busy node's share grows, an idle node's share shrinks, without any node's actual pool being resized to make that happen.

Eduard Soltan wrote:

As a follow up to the today's meet discussion, I think some other thing that we could do is to impose a logical trashhold to the nodes (insead of imposing a hard requirement of maxPoolSize).

1. Set maxPoolSize high, once, and never touch it again. (could be as high as max_connections)
Pick a value generous enough that no node/sibling would ever legitimately need more. Because it's static, c3p0 never has to tear down and rebuild the pool — the destructive-resize problem goes away entirely.

Are we sure c3p0 or hikari aren't preemptively establishing connections in the background? I.e. it sees you want one connection, another one or more are created 'just in case'.

The connections are acquired in batches configurable by the user.

Artur Școlnic wrote:

The connections are acquired in batches configurable by the user.

So what is 'in use' and allowed by the gate, is not what c3p0 has actually acquired; unless we set the batch to 1?

Yes, but if a large number of connections are required to be acquired by the pool, this will perform worse than with a larger batch, although not sure how much worse.

Wait, if you meant that the maxpoolsize will be exceeded because of the batch, than the answer is no.

Artur Școlnic wrote:

Wait, if you meant that the maxpoolsize will be exceeded because of the batch, than the answer is no.

No, I mean if the maxPoolSize is set to a limit close to max_connection, and you have N FWD servers with this value, then theoretically (depending on the batch size) you will be able to exceed max_connections, when you will reach batch number max_connections / batch_size / N, across the nodes - unless the gate considers the batch size.

Constantin Asofiei wrote:

No, I mean if the maxPoolSize is set to a limit close to max_connection, and you have N FWD servers with this value, then theoretically (depending on the batch size) you will be able to exceed max_connections, when you will reach batch number max_connections / batch_size / N, across the nodes - unless the gate considers the batch size.

I checked this batch size is configured through acquireIncrement which is set to 2 for majority of customers.

Yes, but for the purpose of this task, I think it is reasonable to test with batch size 1, I doubt the performance hit is huge.

Artur Școlnic wrote:

Yes, but for the purpose of this task, I think it is reasonable to test with batch size 1, I doubt the performance hit is huge.

Agreed; not exceeding max_connections for the cluster is a hard constraint.

The budget:

- if clusterConnectionBudgetAuto (directory configuration) is set, it is read from DBMS max_connections parameter, minus ~10% safety margin (configurable).

Two levels of splitting:

Level 1 — across nodes: always equal.
Each node takes budget ÷ number of nodes. The node count comes from Geode membership (polled, debounced so restarts don't cause flapping). Nodes never negotiate — cluster size is the only shared fact. 3 nodes, budget 180 → each node gets 60. A node joining or leaving simply changes the divisor for everyone.

Level 2 — across databases on a node: equal by default, weighted if enabled.
All databases (and tenants) that point at the same Postgres server share the node's slice.
- Equal spread (default): slice ÷ number of databases. Two DBs on a 60 slice → 30 each. That is blind to load.
- Weighted spread (clusterWeightLive directory configuration): each database's share follows its actual use — every session opened moves its weight up, every session closed brings it down. A busy database grows its share while it's busy and gives it back as it quiets down, automatically and immediately. A busy DB1 next to an idle DB2 might hold 50 of the 60 instead of 30.

Equal spread and Weighted spread are fairly tested and works as expected, rev. 16660.

An alternative would be using a PgBouncer (there is something similar for MariaDB, there isn't unfortunately for SQLServer so this implementation could be kept as a fall out).

PgBouncer is a lightweight connection pooler that sits between the application and Postgres. Clients connect to PgBouncer instead of Postgres; PgBouncer keeps only a small pool of real server connections and, in transaction pooling mode, lends one to a client only for the duration of a single transaction. Between transactions the client holds just a cheap PgBouncer-side connection (a few KB), while the real connection serves someone else.

While the max_connection distribution can be shared on demand across one single node, it becomes pretty hard to manage when multiple nodes come into picture.

Eduard Soltan wrote:

PgBouncer keeps only a small pool of real server connections and, in transaction pooling mode, lends one to a client only for the duration of a single transaction.

The application defines the transaction scope and for the most part, we map our database transactions to those application transactions.

In addition, and I don't recall if this code was rewritten, we were at one point opening implicit database transactions for read operations which could occur outside of application transactions.

For this to work, we would need to be absolutely certain we don't do anything outside a transaction scope which requires a JDBC connection.

This is the flow of PgBouncer connection acquisition:

  
1. The application opens a connection through the c3p0. What it actually gets is a connection to PgBouncer — a lightweight object on the PgBouncer side. No PostgreSQL connection is involved yet, even though from the application's point of view it has a database connection.
2. The application begins a transaction. Typically nothing happens on the wire yet — drivers defer the BEGIN until there's real work to send. Still zero PostgreSQL resources in use.
3. The application executes the first statement. PgBouncer takes one real PostgreSQL connection from its pool and links it to this client connection. If the pool has a free one, the link is instant; if the pool is at its limit, the statement waits in a queue.
4. For the rest of the transaction — every statement, every savepoint, until commit or rollback — that same real connection stays linked to this client. Whatever the transaction does, including sitting idle in the middle, it occupies exactly one PostgreSQL connection, just as it would without a pooler.
5. The application commits (or rolls back). The instant the commit completes, PgBouncer unlinks the real connection and hands it to whichever other client's transaction is waiting. The application's connection
  remains open and usable — but from PostgreSQL's point of view, it now consumes nothing.

What I am trying to say with this mechanism explanation, the point that I don't see any difference from FWD perspective (whether it gets its connection directly from Postgres or PgBouncer).

But the real advantage PgBouncer holds — over both the current state and anything the 11633a changes could ever achieve — is true demand-driven distribution of Postgres connections across a cluster. And solves the holding of idle connection inside a database c3p0 instances within one node.

But while trying to set PgBouncer to work with on a small FWD test project, I got into same PgBouncer configuration of it own (still config that could be broken, this is why I am not sure this is the right choice).

Meaning of the key configuration in PgBouncer:

c3p0.maxPoolSize in directory.xml — could be set also pretty high. It now counts connections to PgBouncer, and can be actually set by FWD code.