(change) PostgreSQL Connection Pool Configuration

[gogbog]

Motivation

Currently the PostgreSQL retriever supports basic connection settings such as the uri, table, and custom column names. However, it doesn’t provide user-configurable settings for connection pooling (max connections, idle connections, connection lifetimes, timeouts, etc.).

For many production workloads, especially under high concurrency or when using managed cloud Postgres instances, having control over these pool settings is essential for performance, stability, and resource efficiency.

Requirements

Allow users to configure PostgreSQL connection pool parameters when using the postgresql retriever, instead of relying solely on driver defaults.

The configuration must:

1. Add an optional pool section under the PostgreSQL retriever configuration.
2. Map configuration values to the underlying Go database/sql pool settings - (MaxOpenConns, MaxIdleConns, ConnMaxLifetime, ConnMaxIdleTime)
3. If pool is not defined, current behavior and defaults must remain unchanged.
4. Validate configuration values

Proposed solution:

Introduce optional PostgreSQL retriever configuration properties for pool management. For example:

retrievers:
  - kind: postgresql
    uri: "postgres://user:pass@host/db"
    table: "go_feature_flag"
    pool:
      max_open_conns: 50
      max_idle_conns: 10
      conn_max_lifetime: "30m"
      conn_max_idle_time: "10m"

[gogbog]

@thomaspoignant Let me know what you think about this approach and whether you had a different implementation in mind or if I’m missing anything - I’d be happy to work on it 🙂

[thomaspoignant]

I like the idea, but I see an issue when using the same pool for multiple retrievers.

Let's say that we have 2 retrievers pointing to the same DB, with different pool configurations.

retrievers:
  - kind: postgresql
    uri: "postgres://user:pass@host/db"
    table: "go_feature_flag"
    pool:
      max_open_conns: 50
      max_idle_conns: 10
      conn_max_lifetime: "30m"
      conn_max_idle_time: "10m"

  - kind: postgresql
    uri: "postgres://user:pass@host/db"
    table: "go_feature_flag"
    pool:
      max_open_conns: 150
      max_idle_conns: 110
      conn_max_lifetime: "40m"
      conn_max_idle_time: "15m"

What should we do in that case?

[gogbog]

Good question - I see two possible approaches here, though I’m not entirely sure which would align best with the intended architecture:

1. Keep it per retriever – Each retriever owns its own *sql.DB. In this case, each retriever creates and manages its own pool. The advantages are simplicity and full backward compatibility. The downside is that multiple retrievers pointing to the same DSN could silently multiply connections and potentially exhaust database limits.
2. Pool registry by DSN – Similar to what we currently do with flagsets (if I’m not mistaken). If a pool already exists for a given DSN, we reuse it. If two retrievers use the same DSN with different pool configurations, we could either return a configuration error or ignore it and log a warning.

What do you think would be the most appropriate approach here?

[thomaspoignant]

I would go more for another way to configure that directly on top level.
Something like this:

retrievers:
  - kind: postgresql
    uri: "postgres://user:pass@host/db"
    table: "go_feature_flag"

retrieverOptions:
  postgresql:
    - uri: "postgres://user:pass@host/db"
      pool:
        max_open_conns: 50
        max_idle_conns: 10
        conn_max_lifetime: "30m"
        conn_max_idle_time: "10m"
    # optional: another DB, different pool
    - uri: "postgres://other@otherhost/otherdb"
      pool:
        max_open_conns: 10

With this option it is clear that we can't configure only once the pool even if it is used by multiple providers.
@gogbog what do you think about this option?

[gogbog]

That sounds like a good idea!

Then, when we initialise multiple retrievers, we can look up the pool configuration by DSN, and if the user hasn’t configured anything for it, we fall back to the default pool settings.

I like this approach - does anything else come to mind, or I can already create a task to start working on it?

[thomaspoignant]

I think we are good, you can go for it 👍
I've assigned the issue to yourself.

[thomaspoignant]

@gogbog do you need any help for this issue ?