Why Your Database Connection String Has More Options Than You Think

Most connection strings in production look something like this:

postgresql://user:[email protected]:5432/mydb

That is correct. It is also leaving several useful configuration knobs on the floor. The Postgres connection string supports parameters that change how your application connects, authenticates, and behaves at the session level.

Timeout Parameters

connect_timeout is the TCP connection timeout — how long the client waits for the server to accept the connection before giving up. Default is 0, meaning wait indefinitely. If your database server is unreachable, the client will block forever unless you set this:

postgresql://user:pass@host/db?connect_timeout=10

options can set session-level GUC parameters including statement_timeout and lock_timeout:

postgresql://user:pass@host/db?options=-c%20statement_timeout%3D30000%20-c%20lock_timeout%3D5000

Setting statement_timeout at the connection level gives you a safety net against runaway queries. 30 seconds is a reasonable application default. Setting lock_timeout prevents your application from blocking indefinitely on a row another transaction is holding.

sslmode=verify-full

The option you want in production is verify-full, not require:

postgresql://user:pass@host/db?sslmode=verify-full

verify-full requires SSL and verifies that the server certificate is signed by a trusted CA and that the hostname matches. This prevents man-in-the-middle attacks. Without it, require encrypts the connection but does not verify who you are connected to.

application_name

This appears in pg_stat_activity for every active connection:

postgresql://user:pass@host/db?application_name=myapp-api

When you are looking at pg_stat_activity during a performance incident and you have 40 connections from three different services, knowing which connections belong to which application changes the diagnosis from guesswork to targeted investigation. Use distinct names for distinct service instances: myapp-api, myapp-worker, myapp-migrations.

target_session_attrs

If you are running Postgres with a primary and read replica:

postgresql://user:pass@primary,replica1/db?target_session_attrs=read-write

read-write requires a primary that accepts writes. In failover scenarios, if you pass multiple hosts and the primary has failed, the client cycles through the hosts until it finds the new primary.

options=-c for Session Parameters

Beyond timeouts, the options parameter is useful for setting search_path for multi-tenant schemas, timezone to enforce UTC at the connection level, and work_mem for connections needing larger sort memory:

?options=-c%20timezone%3DUTC%20-c%20search_path%3Dtenant_123%2Cpublic

Setting timezone=UTC at the connection level prevents timestamp interpretation bugs when the server timezone differs from your application timezone.

What This Looks Like in Practice

postgresql://myapp:[email protected]:5432/myapp?connect_timeout=10&sslmode=verify-full&application_name=myapp-api&options=-c%20statement_timeout%3D30000%20-c%20lock_timeout%3D5000%20-c%20timezone%3DUTC

These parameters exist specifically for scenarios that cause production incidents: unreachable databases, slow queries blocking connection pools, timezone bugs in timestamp handling. Setting them at the connection string level means they apply to every connection in the pool without requiring application code changes or database-side configuration updates.

Building in public at builds.anethoth.com.