Motivation

The current PostgreSQL Retriever implementation utilizes pgx.Conn for database access. This is problematic because the system initialises a separate database connection for each flagset configured.

1. Resource Bottleneck: Every Retriever instance establishes and maintains its own dedicated pgx.Conn. If a user configures 100 or 200 flagsets, this leads to connection sprawl, potentially creating hundreds of independent database connections.

2. Connection Overhead: Each active database connection is resource-intensive. When managing large numbers of them, we end up using the database in a way it wasn’t designed for. This forces us to scale vertically and consume more and more resources.

Requirements

The primary goal is to reduce the excessive number of connections and improve resource efficiency, we must migrate to a shared, global pgxpool.Pool and use it across all Postgres retrievers.

This change is supposed to do the following:

1. Single Pool: The entire application uses one high-performance connection pool, reusing a limited set of connections across all PostgreSQL Retriever instances, regardless of how many flagsets are configured.
2. Safe Cleanup: The pool only closes when the last active Retriever shuts down.