Postgres connection options you might not know

Postgres has two different “options” knobs that people mix up:

• Client connection parameters (libpq-style): things like connect timeout, SSL mode, application name.
• Server session settings (GUCs): things you can SHOW, SET, or set at startup with -c.

If you're using pg (node-postgres), you get a convenient bridge to the second bucket: you can pass Postgres -c flags through the connection startup message via an options string.

Here's the minimal pattern:

import { Pool } from 'pg';

export function createPgPool() {
  return new Pool({
    connectionString: getDatabaseUrl(),
    // Route Better Auth's unqualified table names (e.g. "user") to auth.*.
    options: '-c search_path=auth,public',
  });
}

This post is a handful of those “set it once, stop thinking about it” defaults. At the end there's a tiny Bun + TypeScript + Drizzle script that spins up a throwaway Postgres container and proves each one.

1) search_path: route unqualified names (and why this is both great and dangerous)

This is the trick from the snippet above: if a library uses unqualified table names (like "user"), you can make it “land” in your schema without forking anything.

new Pool({
  connectionString: getDatabaseUrl(),
  options: '-c search_path=auth,public',
});

The good

• It's zero-code in the library layer.
• It keeps “auth tables” out of public without rewriting SQL.

The footgun

The search path is name resolution. If two schemas have the same table/function name, the first one wins.

Rule of thumb:

• Use search_path for namespaces you own.
• Schema-qualify “critical” objects in app SQL (public.users, auth.users, …).
• Treat untrusted SQL as untrusted regardless of search_path (this doesn't replace parameterization).

2) Timeouts you want by default (because prod is where queries go to die)

These are session GUCs, so they're perfect for options: "-c …":

new Pool({
  connectionString: getDatabaseUrl(),
  options: [
    // Kill runaway queries.
    '-c statement_timeout=5s',
    // Fail fast on lock contention instead of hanging.
    '-c lock_timeout=250ms',
    // Kill leaked transactions (the classic “idle in transaction” problem).
    '-c idle_in_transaction_session_timeout=10s',
  ].join(' '),
});

How to pick values:

• statement_timeout: start with 5–30s for web requests; lower for background jobs that can retry.
• lock_timeout: 100–500ms is common for OLTP workloads; higher for migrations.
• idle_in_transaction_session_timeout: short (5–30s) for request/response apps.

If you run migrations through the same pool: either use a separate connection without strict timeouts, or override them for that path.

3) application_name: know your connections

You can set this as a client parameter, or as a server GUC. I like making it explicit as a GUC because it's visible and consistent.

new Pool({
  connectionString: getDatabaseUrl(),
  options: '-c application_name=myapp:web',
});

Now pg_stat_activity stops being a sea of “unknown”:

select pid, application_name, state, query
from pg_stat_activity
where datname = current_database();

If you run multiple pools (worker, web, migrations), give each a different name.

4) timezone=UTC: kill the “works on my laptop” time bug class

This is the easiest “why did this date shift?” prevention you can do:

new Pool({
  connectionString: getDatabaseUrl(),
  options: '-c timezone=UTC',
});

Yes, you can also set it in Postgres globally. Per-connection makes it harder to regress when you run the same DB shared by multiple services.

5) Isolation defaults: only set if you're willing to pay for it

The default is read committed. It's fast and it's what most apps expect. What does it mean?

• read committed: each statement reads from a snapshot taken at the start of that statement—later statements can see other transactions’ commits (non-repeatable reads and phantoms are possible). Jepsen’s model write-up is a great mental model: Read committed (Jepsen)
• repeatable read: in Postgres this is snapshot isolation—your whole transaction reads from one stable snapshot, but you can still get anomalies like write skew.
• serializable: aims for true serializability by aborting transactions that would produce anomalies; you must be prepared to retry on serialization failures.

If you've actually been bitten by anomalies and you have a clear reason, you can set:

new Pool({
  connectionString: getDatabaseUrl(),
  options: '-c default_transaction_isolation=repeatable\\ read',
});

But don’t cargo-cult it:

• Higher isolation can increase lock contention and abort rates.
• Many ORMs already wrap operations in transactions in ways that assume read committed.

If you do this, add a real load test and watch: deadlocks, lock waits, and serialization failures.

6) “Just make it predictable” toggles: jit=off, plan_cache_mode=…

These are situational, but worth knowing:

• jit=off: can reduce tail latency for short OLTP queries in some setups (JIT has startup cost).
• plan_cache_mode=force_custom_plan: useful when prepared statements produce terrible generic plans (rare, but it happens).

You can't “prove” these with a tiny demo, but you can enforce the setting and document why:

new Pool({
  connectionString: getDatabaseUrl(),
  options: '-c jit=off -c plan_cache_mode=auto',
});

The proof here is mostly operational: “is the session configured the way we think it is?”

7) io_uring (Linux): why it's not a connection option, and why it can still bite you

io_uring is not something your app enables from a connection string. It's a Postgres server I/O backend choice:

• Setting: io_method=io_uring
• It's evaluated by the server and typically requires restart.
• It's Linux + kernel dependent (and the environment matters: bare metal vs VM vs container host).

When to consider it

• You're on modern Linux with a kernel that has solid io_uring support.
• You're I/O bound (high concurrency reads/writes) and the storage is fast enough (NVMe, high IOPS).
• You're prepared to measure, roll back, and treat it like a production change.

When not to

• You don't control the host kernel (random managed platforms).
• Your workload is mostly CPU bound (query execution, not waiting on disk).
• You can't afford a misconfiguration that prevents Postgres from starting.

How to test it safely

In a container, you can attempt to start Postgres with:

docker run --rm -e POSTGRES_PASSWORD=app -p 5432:5432 postgres:17-alpine \
  -c io_method=io_uring

If startup fails or the setting is rejected, that’s your signal: treat it as “not supported here”.