DEV Community: Akshay

PostgreSQL Connection Strings Explained (DATABASE_URL)

Akshay — Tue, 14 Jul 2026 18:38:35 +0000

A PostgreSQL connection string is the single line that tells a driver everything it needs to reach your database: which user, which host and port, which database, and how to secure the connection. You usually meet it as the DATABASE_URL environment variable, and every backend tutorial eventually says 'set your DATABASE_URL' as though its format were self-evident. It nearly is - once someone actually walks you through it. This is that walkthrough: the anatomy of the string, the parameters worth knowing, where it should live, and the four errors everyone hits exactly once.

![Every segment of a connection string: scheme, user, password, host, port, database, and parameters.]

Every segment of a connection string: scheme, user, password, host, port, database, and parameters.

The anatomy of a PostgreSQL connection string

postgresql://app_user:s3cret@db.example.com:5432/myapp_db?sslmode=require
\________/  \______/ \____/ \_____________/ \__/ \_______/ \_____________/
  scheme      user    pass        host       port  database    parameters

scheme - postgresql:// (or postgres://; equivalent).
user:password - the database role and its password. Reserved characters in either must be percent-encoded (see FAQ - this causes real outages).
host - hostname or IP of the server. localhost in dev; a provider hostname in production. (Inside Docker Compose, it's the service name - db, not localhost - a classic gotcha.)
port - 5432 is Postgres's default; poolers sometimes listen elsewhere (6432 is a PgBouncer convention).
database - one server hosts many databases; this picks yours.
parameters - ?key=value&key=value options, of which a handful matter constantly:

The parameters that matter

sslmode - whether the connection is encrypted and verified. Set it explicitly, always; require is the pragmatic floor and verify-full the strict goal - the differences are their own article.
connect_timeout - seconds to wait before giving up; a small value (5-10) turns network problems into fast, clear errors instead of hangs.
application_name - labels your app's sessions in pg_stat_activity, which future-you will bless during any connection investigation.
Pool sizing - lives in your driver/ORM config rather than the URL (e.g. Prisma's connection_limit URL param being a notable exception). Either way: configured deliberately, not defaulted.

Where the string lives

In the environment - never in code, never in git. The pattern every framework supports: read process.env.DATABASE_URL / os.environ["DATABASE_URL"], provide it via a gitignored .env locally and the platform's secret store in production, and commit only an .env.example documenting the shape. One variable per environment is the entire multi-env story: dev points at local Docker Postgres, staging and prod at their own databases, and the code never knows the difference. This one-variable portability is also why migrating providers is a config change: on Swyftstack, the dashboard hands you the full URL with SSL parameters included - paste, deploy, done.

The four errors everyone meets

connection refused - nothing listening at host:port from where you are: wrong host (localhost inside a container?), wrong port, database not running, or a firewall. It's a network statement, not an auth one.
password authentication failed - reached the server, wrong credentials: user/password mismatch, or the percent-encoding trap mangling both.
database "x" does not exist - reached and authenticated; the path component names a database nobody created. Create it, or fix the name.
no pg_hba.conf entry / SSL off - the server demands TLS you didn't offer: add the right sslmode.

Debugging tip: test the exact string in isolation with psql "$DATABASE_URL" -c 'select 1' - it removes your app from the equation and turns 'the app can't connect' into a precise, googleable error.

Beyond the URL: how frameworks consume it

Knowing the anatomy, the framework-specific consumption is quick. Prisma reads env("DATABASE_URL") in its datasource block and layers its own URL parameters (connection_limit, pool_timeout) on top. Django wants the pieces separated, which dj_database_url.parse() does in one call. Rails reads DATABASE_URL natively and merges it with database.yml. Node's pg accepts the whole string as connectionString. SQLAlchemy takes it as the engine URL, though async drivers want the scheme spelled postgresql+asyncpg:// - the one place the flexible scheme prefix actually matters. In every case the pattern holds: the URL is the single source of truth, and framework config decorates rather than replaces it.

Multiple databases, one convention

Apps grow second databases - a read replica, an analytics copy, a queue. Resist inventing per-case formats: the convention that scales is one URL per purpose (DATABASE_URL, REPLICA_URL, ANALYTICS_DATABASE_URL), each complete and self-contained, each documented in .env.example. Deriving URLs in code - swapping hostnames, rewriting ports - is the road to a staging app quietly writing to production; complete, explicit URLs make every connection target reviewable at a glance. The same one-URL-per-purpose rule is what makes provider migrations a config change and local Docker development identical in shape to production - the humble env var doing quiet architectural work.

For the genuinely-beginner readers who made it here: don't be discouraged that a single line of configuration carried this much explanation. The connection string is where networking, authentication, encryption, and application config all meet - which is exactly why it's the most common thing to get wrong on a first deployment, and why understanding it pays off across every backend technology you'll ever touch. Every database, message queue, and cache you meet from here uses the same URL grammar; you've just learned all of them at once.

Three parameters we skipped that you'll meet eventually, so they're not strangers: pgbouncer=true (some poolers/ORMs want a hint that a transaction-mode pooler is in the path), options=-c%20statement_timeout%3D5000 (session settings smuggled through the URL, percent-encoded), and target_session_attrs=read-write (multi-host strings that prefer the writable primary - yes, a URL can list several hosts). Each is niche; each has saved someone a day when they knew it existed.

A closing exercise that cements it: open your current project's DATABASE_URL and narrate every segment aloud - who connects, to where, on what port, to which database, under what encryption. If any segment draws a blank, this article's relevant section is a scroll away. Being able to read the line fluently is a small skill with an outsized payoff: it turns the most common category of deployment failure into something you diagnose in seconds rather than search in panic.

The same literacy also transfers sideways: Redis URLs, AMQP strings, SMTP DSNs, and MongoDB connection strings all follow the identical scheme-credentials-host-database-parameters grammar with different vocabularies. Learning to read one URL family fluently means never squinting at any of them again - a rare case of a fifteen-minute skill covering an entire category of tools.

A connection string is your database's address, keys, and safety rules in one line. Learn to read it once, set sslmode explicitly, keep it out of git - and half of backend debugging becomes translation instead of mystery.

PostgreSQL EXPLAIN ANALYZE: Read Plans, Fix Slow Queries

Akshay — Tue, 14 Jul 2026 18:27:41 +0000

Slow database queries rarely announce themselves. The app just feels sluggish, one endpoint times out under load, and everyone suspects the framework, the network, or the phase of the moon. PostgreSQL will tell you precisely what's slow and why - if you ask it correctly. This is the workflow we use: find the offenders with pg_stat_statements, understand them with EXPLAIN ANALYZE, and fix them with one of three well-aimed changes.

![In a real plan, one node usually owns nearly all the time - find it, then fix that node.]

In a real plan, one node usually owns nearly all the time - find it, then fix that node.

EXPLAIN ANALYZE is the single most useful diagnostic command in Postgres. Plain EXPLAIN prints the plan the query planner intends to run, with cost estimates. Add ANALYZE and Postgres actually executes the query and reports the real timing and row count at every step of the plan - so you can see exactly where the wall-clock time goes instead of guessing. (Because it really runs the query, use it on a replica or inside a rolled-back transaction when the statement writes.) The rest of this post is how to read that output and act on it.

Step 1: Find the queries that actually matter

Don't guess from application logs. The pg_stat_statements extension aggregates every query's execution count and total time. Enable it once, then ask for the top offenders by total time - a metric that automatically balances 'very slow' against 'very frequent':

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT round(total_exec_time) AS total_ms,
       calls,
       round(mean_exec_time, 1) AS mean_ms,
       left(query, 90) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

The output almost always surprises. The 'slow report query' everyone blames is frequently nowhere near the top; the real cost is a mid-speed query on a hot path, or an N+1 pattern where the ORM fires the same 5 ms query five hundred times per request. Fix in order of total_ms, top to bottom.

Step 2: Read the plan, not the tea leaves

Take the worst query and run it under EXPLAIN ANALYZE with buffers enabled:

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > now() - interval '7 days'
  AND o.status = 'paid';

You don't need to understand every node type. Scan for three signals, in priority order:

**Seq Scan on a large table. **PostgreSQL read every row because no usable index existed. If the filter is selective (returns a small fraction of rows), this is your fix - add the index.
*Estimated vs actual rows wildly different. *'rows=100' estimated but 'actual rows=2,000,000' means the planner is flying blind on stale statistics. Run ANALYZE tablename; and re-plan before touching anything else.
**Nested Loop with a huge inner count. **A join executed row-by-row thousands of times, usually downstream of a bad estimate or a missing index on the join key.

Step 3: Apply the right fix

Fix A: the missing or wrong index

Most slow queries are one index away from fast. Match the index to the query's shape: equality columns first, then range columns. For the query above:

-- status is an equality filter, created_at a range filter:
CREATE INDEX CONCURRENTLY idx_orders_status_created
ON orders (status, created_at);

CONCURRENTLY builds the index without locking writes - essential on a production table. Re-run the EXPLAIN afterwards and confirm the Seq Scan became an Index Scan and the runtime dropped. If it didn't, the planner had a reason; check the estimate mismatch signal again.

Fix B: reshape the query

When an index exists but can't be used, the query shape is usually at fault:

Functions on indexed columns - WHERE lower(email) = $1 needs an expression index on lower(email), or store the value normalised.
N+1 loops - replace per-row lookups with one query using WHERE id = ANY($1) or a join. This is an application change, but it's the single biggest win in ORM-heavy codebases.
Overfetching - SELECT * on wide tables drags TOASTed columns and blocks index-only scans. Name the columns.
OFFSET pagination on deep pages - OFFSET 100000 reads and discards 100k rows. Use keyset pagination (WHERE id > $last LIMIT 50) instead.

Fix C: give the planner fresh statistics

PostgreSQL plans queries using sampled statistics that autovacuum refreshes opportunistically. After bulk loads, mass deletes, or on very fast-growing tables, those samples lag reality and the planner picks catastrophic plans with full confidence. ANALYZE is instant and safe to run any time; if a table's stats lag chronically, lower its autovacuum_analyze_scale_factor.

Make it a habit, not a heroic

The teams with fast databases aren't smarter - they just look at the top-10 query list monthly and after every feature launch, when a regression is one commit old instead of one quarter old. A managed platform should make this loop effortless: Swyftstack's managed PostgreSQL surfaces query rate, active connections, and a slow query log on the dashboard, so step 1 is a glance instead of an extension install. And because it's standard PostgreSQL, every EXPLAIN technique in this post works unchanged.

Reading a real plan, line by line

Here is the kind of output that intimidates people, trimmed to the parts that matter. Suppose the orders query above produces this plan fragment:

Nested Loop  (actual time=0.4..5205.1 rows=1842 loops=1)
  -> Seq Scan on orders o
       (actual time=0.2..5100.7 rows=1842 loops=1)
       Filter: (status = 'paid' AND created_at > ...)
       Rows Removed by Filter: 4,318,240
  -> Index Scan using customers_pkey on customers c
       (actual time=0.05..0.05 rows=1 loops=1842)

Three readings jump out once you know where to look. The Seq Scan on orders consumed 5.1 of the 5.2 total seconds - that line IS the problem; everything else is noise. 'Rows Removed by Filter: 4.3 million' tells you the scan read the whole table to keep 1,842 rows - a selectivity of 0.04%, which is exactly the profile an index exists for. And the customers side is already healthy: an index scan executed 1,842 times at 0.05 ms each is the nested loop working as designed. The fix writes itself - the composite index on (status, created_at) from Fix A - and the re-run plan should show an Index Scan on orders with the total time dropping from seconds to single-digit milliseconds.

This is the general skill: do not read plans top to bottom like prose. Find the node that owns the wall-clock time, check whether its actual row count embarrasses its estimate, and ask what access path would have served its filter. Ninety percent of plan-reading is those three questions applied to one node.

One index, one N+1 fix, and one ANALYZE resolve the overwhelming majority of slow-query incidents. The skill is knowing which of the three you're looking at - and EXPLAIN ANALYZE tells you.

PostgreSQL query analyzer tools: what's worth using

People searching for a 'PostgreSQL query analyzer' usually want one of three different tools, and EXPLAIN ANALYZE is only the first:

EXPLAIN ANALYZE - the built-in plan analyzer this guide covers. Free, always available, answers 'why is this query slow'.
pg_stat_statements - the built-in workload analyzer. It aggregates timing across every query the server runs, so it answers the prior question: 'which query should I even be looking at?' Enable it before you need it.
Visualizers - tools like explain.depesz.com and explain.dalibo.com turn a pasted plan into an annotated tree, which is the fastest way to read large plans. Your data stays in the plan text you choose to paste.

A good managed host wires the first two in for you - Swyftstack's managed Postgres ships with pg_stat_statements available, and our PostgreSQL hosting comparison covers what else to expect from a provider on the observability front.

PostgreSQL Connection Pool Sizing: The Formula That Works

Akshay — Tue, 14 Jul 2026 18:20:00 +0000

Almost every question about connection pooling eventually collapses into one practical number: how big should the pool be? Get pool sizing wrong in one direction and you meet FATAL: sorry, too many clients already during a traffic spike.

Get it wrong in the other direction - a pool that's far too large - and you quietly make the database slower under load, for reasons most people never suspect.

This post gives you the sizing formula that actually works, the PgBouncer math behind it, and the two things you have to understand first for the numbers to make sense: why Postgres connections are so expensive, and the transaction-versus-session-mode choice that decides how much a pooler can multiplex.

![Many app connections share a small PgBouncer pool sized (cores x 2) + 1 into a few Postgres backends.]

Many app connections share a small PgBouncer pool sized (cores x 2) + 1 into a few Postgres backends.

If you just want the short answer: a good starting point for the pool that talks to Postgres is roughly (cpu_cores x 2) + 1 connections - often a low-tens number, not the hundreds people reach for. The rest of this post explains why that formula holds, how to translate it into PgBouncer's default_pool_size and max_client_conn, and when you can skip a dedicated pooler entirely.

Why PostgreSQL runs out of connections

Every PostgreSQL connection is backed by its own operating-system process on the server, with its own memory. That design makes Postgres robust and isolated, but it also makes connections expensive and finite.

A typical instance is configured for a few hundred connections via max_connections, and each idle connection still costs memory. So the ceiling is lower than people assume - you don't need thousands of concurrent queries to hit it, just thousands of mostly-idle connections that someone forgot to close.

Two patterns blow through that ceiling fast:

**Connection-per-request code. **An app that opens a new connection for every HTTP request and doesn't reuse a pool will stack up connections under load.
**Serverless sprawl. **On Lambda, Vercel functions, or Cloudflare Workers, each concurrent invocation is effectively its own process. A thousand concurrent invocations, each opening even one connection, is a thousand connections - a stampede the database can't absorb.

What a connection pooler actually does

A pooler like PgBouncer (or Supabase's Supavisor, or pgcat) sits between your application and PostgreSQL. Your app opens lots of cheap connections to the pooler; the pooler maintains a small set of real, expensive connections to Postgres and multiplexes your traffic across them. A thousand app connections can share a pool of, say, twenty backend connections, because at any given instant most of them aren't actually running a query. The pooler is essentially a traffic controller that keeps the expensive resource - real Postgres connections - busy and bounded.

A pooler doesn't make Postgres faster. It makes a small number of connections serve a large number of clients, which is exactly what you need when you have far more clients than the database can hold.

Transaction mode vs session mode - the choice that matters

PgBouncer offers a few pooling modes, but two matter in practice, and picking the wrong one causes subtle bugs.

Session mode

In session mode, a client holds a backend connection for the entire duration of its session - from connect to disconnect. This is the most compatible mode: everything that relies on connection-level state works normally, including SET settings, LISTEN/NOTIFY, session-level advisory locks, and named prepared statements. The downside is lower multiplexing - one client ties up one backend connection for as long as it's connected, so you get less of the pooling benefit.

Transaction mode

In transaction mode, a backend connection is assigned to a client only for the duration of a single transaction, then returned to the pool. This gives you maximum multiplexing - ideal for serverless and high-concurrency apps - because a connection is held for milliseconds, not minutes. The trade-off: because you might get a different backend connection for the next transaction, anything that depends on session state breaks. That includes session-level SET, LISTEN/NOTIFY, and - the classic footgun - server-side named prepared statements.

The prepared-statement gotcha (a true story shape)

Here's the bug that catches everyone. Many drivers and ORMs use named prepared statements under the hood (Prisma is a well-known example). In transaction mode, prepared statement s0 might be created on one backend connection and then reused on a different one that's never heard of it - producing errors like prepared statement "s0" already exists (SQLState 42P05) or prepared statement "s0" does not exist. The fix is to tell your driver to disable named prepared statements when talking through a transaction-mode pooler (for example, the pgbouncer=true flag on the connection string for some clients), or to use the pooler only for the runtime app and the direct connection for migrations and admin tasks.

A practical rule for which connection to use where

**Runtime app traffic: **use the pooler (transaction mode for serverless, session mode for long-running servers that need session features).
**Migrations, schema changes, pg_dump, seeds: **use the direct (non-pooled) connection. These need session state and prepared statements, and they're not high-concurrency.

Connection pool sizing: the formula that actually works

Pool sizing is where intuition misleads almost everyone. The instinct is 'more traffic, so make the pool bigger'. But the pool that matters most - the one from your pooler (or app) to Postgres - should be sized to the database's ability to do work in parallel, not to how many clients are knocking. Once every CPU core is busy running a query, adding more concurrent connections doesn't do more work; it just adds lock contention, context-switching, and memory pressure. Past that point a bigger pool makes throughput worse.

The widely-used starting formula, popularised by the PostgreSQL wiki and the HikariCP project, is:

connections = (cpu_cores x 2) + effective_spindle_count


# On modern SSD/NVMe systems the spindle term is small, so the
# practical rule of thumb collapses to:

connections = (cpu_cores x 2) + 1

So a 4-core Postgres instance wants a backend pool in the neighbourhood of 9 connections, not 90. That feels far too small until you internalise the key insight: a pool of 9 that stays busy will out-throughput a pool of 100 that spends its time fighting over the same cores. Start near the formula, then measure and adjust - if CPU is underused and queries are waiting on the pool, nudge it up; if CPU is pinned and latency is climbing, it's already too big.

Turning the number into PgBouncer settings

PgBouncer exposes a few knobs, and the sizing formula maps onto them directly. The three that matter:

*default_pool_size *- the number of real backend connections PgBouncer opens to Postgres per (user, database) pair. This is where your (cores x 2) + 1 number goes. It defaults to 20.
*max_client_conn *- how many application connections PgBouncer will accept out front. Because pooled client connections are cheap, this is deliberately large, often in the thousands.
*reserve_pool_size *- a small emergency pool that kicks in when a regular pool is exhausted, to absorb short bursts.

The hard constraint to respect: the sum of every pool's default_pool_size across all (db, user) pairs must stay below Postgres's own max_connections, with 10-20 connections left as headroom for superuser access, replication, and monitoring. If PgBouncer is allowed to open more backend connections than Postgres permits, you've just moved the 'too many clients' error one layer down instead of fixing it.

A worked example

A single 4-core Postgres instance serving one application database and one role:

# postgresql.conf
max_connections = 100          # plenty of headroom above the pool

# pgbouncer.ini
[databases]
app = host=127.0.0.1 port=5432 dbname=app

[pgbouncer]
pool_mode = transaction        # max multiplexing for a web app
default_pool_size = 20         # backend conns to Postgres (near (4x2)+1, rounded up for burst)
max_client_conn = 2000         # cheap app-side connections out front
reserve_pool_size = 5          # short-burst headroom

That configuration lets up to 2,000 application connections share a pool of ~20 real backend connections - and 20 sits comfortably under the database's 100-connection ceiling. Two thousand clients, twenty expensive resources, one database that never sees 'sorry, too many clients already'.

Sizing the pool inside your application

The pooler isn't the only pool - your app has one too, and the right size depends entirely on your runtime:

**Long-running server (Express, a worker): **one shared pool per process, with a modest max (think 10-20), reused across all requests. Size the total across all processes to fit under the database or PgBouncer ceiling.
**Serverless function: **a tiny pool per instance (1-2 connections), with a transaction-mode pooler doing the real multiplexing in front of Postgres. A thousand warm instances each holding 1-2 connections is exactly the stampede a pooler exists to absorb.

The mistake to avoid is creating a new client or pool on every request or every cold start. Create the pool once at module scope, reuse it everywhere, attach an error handler, and close it cleanly on shutdown.

Do you even need a pooler?

If you run a single long-lived server with a sensible shared pool and your concurrency is modest, you may never hit the ceiling - a pooler adds a component you don't need. You almost certainly do need one if you're serverless, if you run many app instances, or if you've already met the 'too many clients' error. The honest answer is: pool inside your app first, add a dedicated pooler when your deployment model multiplies connections beyond what the database can hold.

Where Swyftstack fits

Pooling is one of those things that's easy to get wrong and tedious to operate. Swyftstack ships managed PostgreSQL with pooling configured for you, so you can pick transaction or session mode without standing up and tuning PgBouncer yourself. You still get a standard connection string and real, unmodified Postgres - the pooler is just handled. If you're building on Next.js or another serverless runtime, that means the 'too many clients' error is one less thing you have to design around.

The takeaway

Connection pooling exists because Postgres connections are heavyweight and your app wants to open lots of them. A pooler multiplexes many cheap client connections onto a few real backend ones. Use transaction mode for serverless (and disable named prepared statements), session mode when you need connection-level features, the direct connection for migrations, and a modest, reused pool everywhere. Get that right and you'll never see 'sorry, too many clients already' again.

PgBouncer pool-size settings, in one table

The settings people actually search for, and what each one really controls:

Setting	What it controls	Sane starting point
default_pool_size	Server connections per user+database pair	(cores x 2) + 1 on the Postgres box
min_pool_size	Connections kept warm even when idle	0, or a small number if cold bursts hurt
max_client_conn	Client connections PgBouncer itself accepts	High (1,000+) - clients are cheap, servers are not
max_db_connections	Hard cap on server connections per database	Below Postgres max_connections, leaving admin headroom
pool_mode	When a server connection is released	transaction, unless you need session features
reserve_pool_size	Emergency connections under burst	A handful; alerts should fire before it's used

The recurring mistake is raising default_pool_size to fix queueing that is actually slow queries holding connections - measure with SHOW POOLS before turning any of these dials. If you are still choosing where the database itself should live, our PostgreSQL hosting comparison notes which providers bundle a pooler (several do, including Swyftstack) so these settings arrive pre-tuned.