Improving Delphi Database Access with FireDAC Connection Pooling

The problem: repeated connect‑open‑close cycles hurt performance

In many Delphi applications each data‑access component creates its own TFDConnection, opens a socket, runs a query, and then closes the connection. When the workload consists of many short transactions—such as a web service handling dozens of requests per second—the overhead of establishing TCP connections and performing authentication can dominate query latency.

Thesis: FireDAC’s connection pooling can reuse existing sockets, lowering latency while keeping the number of concurrent connections predictable

FireDAC includes a transparent pool that lives inside the TFDManager (or the TFDConnection.Params of each component). When several components share the same ConnectionDefName, the manager hands out an already‑opened physical connection from its internal list instead of creating a new one. After a transaction ends, the connection is returned to the pool in a clean state.

Enabling pooling

Pooling is active by default for drivers that support it. To control it explicitly, set the following parameters either on the global FDManager component (dropped on a data module or form) or on each TFDConnection:

• PoolSize – maximum number of physical connections the pool may hold.
• PoolTimeout – milliseconds a request waits for a free connection before raising an exception.
• ConnectionDefName – a logical name that groups connections that should share the same pool.

Example: dropping a TFDManager named FDManager1 on a data module and setting its properties in the Object Inspector:

FDManager1.PoolSize := 20;
FDManager1.PoolTimeout := 15000; // 15 seconds

Then, for each TFDConnection that should participate:

FDConnection1.ConnectionDefName := 'MyAppDB';
FDConnection1.Params.DriverID := 'PG'; // PostgreSQL example
FDConnection1.Params.Database := 'mydb';
FDConnection1.Params.User_Name := 'appuser';
FDConnection1.Params.Password := 'secret';
// No need to set PoolSize here; it inherits from FDManager1

Worked example: measuring latency with and without pooling

Assume a simple VCL form with a button that executes 100 quick SELECT statements.

procedure TForm1.BenchmarkClick(Sender: TObject);
var
  i: Integer;
  Start, Stop: TDateTime;
  Qry: TFDQuery;
begin
  Qry := TFDQuery.Create(nil);
  try
    Qry.Connection := FDConnection1;
    Start := Now;
    for i := 1 to 100 do
    begin
      Qry.Close;
      Qry.SQL.Text := 'SELECT pg_sleep(0.01), now();';
      Qry.Open;
      Qry.First; // consume result
    end;
    Stop := Now;
    ShowMessage(Format('Elapsed: %.2f sec', [(Stop - Start) * 24 * 60 * 60]));
  finally
    Qry.Free;
  end;
end;

Run the benchmark twice:

1. Without pooling – set FDManager1.PoolSize := 0 (or omit the manager). Each iteration forces a new physical connection.
2. With pooling – set FDManager1.PoolSize := 10 (or any value ≥ the concurrent demand).

You should see a noticeable drop in elapsed time when pooling is enabled, because the socket establishment cost is amortized over the 100 iterations.

Trade‑offs and limitations

• Database connection limits – The pool can cause the server to see more concurrent connections than your code would without pooling. Monitor your DB (e.g., SELECT count(*) FROM pg_stat_activity; for PostgreSQL) to ensure you stay below the server’s max_connections.
• Driver support – Some legacy or custom drivers ignore FireDAC’s pool parameters. Verify the driver’s documentation or test with a simple connection‑count query before relying on pooling in production.
• Transaction isolation – Connections returned to the pool are reset, but if you rely on session‑level variables or temporary tables, they may leak across threads. Either avoid such state or explicitly reset it in an AfterConnect event.

Actionable next steps

1. Add a TFDManager to your main data module.
2. Set PoolSize to a value that matches your expected concurrent workload (start with 2 × average active threads).
3. Set PoolTimeout to a reasonable wait time (e.g., 15000 ms) to surface configuration problems early.
4. Run a realistic load test (using your actual UI or service endpoints) and watch the database’s connection count.
5. If the count approaches the server limit, lower PoolSize or increase the database’s max_connections after verifying capacity.
6. Document the chosen ConnectionDefName so future developers know which components share the pool.

By following these steps you can turn the expensive connect‑open‑close pattern into a cheap reuse pattern, cutting latency while keeping the connection footprint predictable and monitorable.