Sign inBook a Demo

How To

Neon Postgres Tuning with Native Tools: Branches, Stats, and Sizing

A hands-on Neon Postgres tuning guide using only native tools — part two of our Neon serverless Postgres series. Read the console Monitoring dashboard (local file cache hit rate, CPU, connections), size autoscaling min/max CU from evidence instead of defaults, choose pooled vs direct connection strings correctly, enable pg_stat_statements and work around its reset-on-suspend caveat, run EXPLAIN (ANALYZE, BUFFERS), then prove every index on a copy-on-write branch of production data before applying it to main. Closes with the honest limits of DIY tuning.

ST
·
neondatabaseperformancepostgresserverlessbranchingtuning
Cover Image for Neon Postgres Tuning with Native Tools: Branches, Stats, and Sizing

Neon Postgres Tuning with Native Tools: Branches, Stats, and Sizing

Imagine testing a new index against a full copy of production data — every row, every table statistic, every data-distribution quirk — without provisioning a replica, without a dump/restore, and without waiting. On Neon you can, because a branch is a copy-on-write pointer to your storage, created in about a second and billed only for the data that diverges. That one capability changes how Neon Postgres tuning works: instead of reasoning about what an index might do to production, you measure what it does to a byte-identical copy, then apply it to main with confidence.

This is part two of a three-part series on Neon performance. Part one covered where serverless Postgres performance goes wrong — cold starts from scale-to-zero, autoscaling ceilings set too low, pooled-vs-direct connection confusion, and the classic missing-index problems that follow you to any Postgres. This article is the hands-on tuning pass using only what Neon ships: the console's Monitoring dashboard, compute sizing controls, the two connection string flavors, pg_stat_statements, EXPLAIN (ANALYZE, BUFFERS), and branching. Part three automates the whole loop.

Budget 2–3 hours for a first pass on one project. Work in the order below — each step feeds the next.

Step 1: Read the Monitoring dashboard before touching anything

Console: Neon Console → your project → Monitoring (docs). You get charts for CPU, RAM, connection count, database size, deadlocks, and — the one specific to Neon — local file cache hit rate.

Neon separates compute from storage, so the traditional shared_buffers mental model changes: each compute keeps a Local File Cache (LFC) of recently read pages, and reads that miss it go to the pageserver over the network. That makes the cache hit rate the single most important chart on the page.

How to read it. LFC hit rate should sit at 99% or above for an OLTP workload. If it's lower, your working set — the pages your queries actually touch — doesn't fit in the cache, and the fix is usually either a larger compute (cache scales with compute size) or an index that shrinks how many pages queries touch. CPU pinned at the top of the chart during business hours means your autoscaling max is the ceiling, not a size the workload chose. A connection count climbing toward the limit for your compute size means you need the pooled endpoint (Step 3).

You can also query the cache directly with Neon's own extension:

CREATE EXTENSION IF NOT EXISTS neon;

SELECT * FROM neon_stat_file_cache;

The file_cache_hit_ratio column is the number to watch (docs).

Step 2: Size autoscaling min/max deliberately

Console: Branches → select your branch → your compute → Edit. Two sliders matter: minimum and maximum compute size, in Compute Units (1 CU = 1 vCPU + 4 GB RAM), plus the scale-to-zero toggle (autoscaling docs).

Most projects run the defaults, and the defaults are a guess. Set them from evidence instead:

  • Max CU: take the CPU chart from Step 1. If usage flat-tops at the current max during peak hours, raise the max one step and re-check in a day. If it never comes within 50% of the max, lower it — you're not paying for the max unless you scale into it, but a lower ceiling is a useful guardrail against a runaway query bill.
  • Min CU: this is the setting people get backwards. A higher floor costs compute-hours around the clock, so justify it with data: raise it only if the LFC hit rate craters after every scale-down (cache is partially rebuilt when compute resizes) or if latency at the floor is visibly worse. Otherwise keep the floor low and let autoscaling do its job.
  • Scale to zero: the default suspends an idle compute after five minutes, and the next connection eats a cold start (typically a few hundred milliseconds, plus cache warm-up). Keep it on for dev branches and staging — it's most of your savings. For a latency-sensitive production endpoint, turn it off on a paid plan (scale-to-zero docs) and accept the always-on floor cost as an SLA line item.

Because Neon bills compute in CU-hours, this step is also cost tuning: every CU-hour a bad query forces you to scale into is money, which is why Steps 4–6 exist.

Step 3: Use the right connection string for each client

Every Neon branch exposes two connection strings: direct, and pooled — the pooled one routes through Neon's built-in PgBouncer and carries a -pooler suffix in the hostname (connection pooling docs). Console: Dashboard → Connect, then toggle Connection pooling in the dialog.

# direct
postgresql://user:pass@ep-cool-sky-123456.us-east-2.aws.neon.tech/neondb

# pooled — note the -pooler suffix
postgresql://user:pass@ep-cool-sky-123456-pooler.us-east-2.aws.neon.tech/neondb

The rule:

  • Pooled for application traffic — serverless functions, API pods, anything that opens many short-lived connections. PgBouncer runs in transaction mode and supports up to 10,000 concurrent client connections, versus a max_connections in the low hundreds on a direct connection to a small compute.
  • Direct for anything that needs session state: migrations, LISTEN/NOTIFY, advisory locks, logical replication — and your own tuning session below, since pg_stat_statements queries and long EXPLAIN ANALYZE runs are simplest without a transaction pooler in the middle.

A surprising share of "Neon is slow" reports are apps hammering the direct endpoint from a lambda swarm. Check which string your app actually uses before tuning anything else.

Step 4: Find the expensive queries with pg_stat_statements

Neon supports pg_stat_statements; you just enable it per database (docs):

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Let it collect during representative traffic, then pull the leaderboard:

SELECT
  calls,
  round(mean_exec_time::numeric, 2)  AS avg_ms,
  round(total_exec_time::numeric, 0) AS total_ms,
  rows,
  shared_blks_read,
  shared_blks_hit,
  left(query, 90)                    AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

How to read it. Sort by total_exec_time, not mean_exec_time — a 40 ms query called 200,000 times a day costs more compute than a 4-second report run hourly. High shared_blks_read relative to shared_blks_hit marks the queries dragging cold pages through the LFC; those are the ones holding your cache hit rate under 99% and forcing autoscaling upward.

The Neon-specific caveat: pg_stat_statements data lives in compute memory and does not survive a restart — and on Neon, scale-to-zero suspends are restarts. On an endpoint that suspends regularly, your stats window is only as old as the last wake-up. Capture the leaderboard while the compute has been awake through real traffic, or snapshot it periodically into a table if you need history.

Step 5: EXPLAIN the top offenders

Nothing serverless about this step — EXPLAIN works on Neon exactly as on any Postgres:

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

You're hunting the usual suspects: Seq Scan on large tables where an index should exist, row-estimate misses of 10x or more (stale statistics — run ANALYZE), and sorts spilling to disk. The BUFFERS line has extra meaning on Neon: read counts are pages that missed Postgres's buffer pool, and on a cache-cold compute they translate into pageserver round-trips. A plan that "reads" 200,000 buffers is expensive anywhere; on Neon it's also the reason your Monitoring chart dips after every autoscaling resize.

By the end of this step you have a hypothesis: "an index on orders (status, created_at) fixes query #1." On classic Postgres, the next move is a staging environment that only approximates production. On Neon, it's better.

Step 6: Prove the fix on a branch before it touches main

This is the tuning superpower. Create a branch — a copy-on-write snapshot of production data at this instant (branching docs):

Console: Branches → Create branch, parent main, from Current point in time. Or with the Neon CLI:

npm install -g neonctl
neon auth

neon branches create --project-id <project-id> --name idx-test
neon connection-string idx-test --project-id <project-id>

Connect to the branch and run the experiment against real data:

-- baseline on the branch
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;   -- your Step 5 query

CREATE INDEX idx_orders_status_created
  ON orders (status, created_at);

ANALYZE orders;

-- after
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;

Compare execution time, buffer counts, and the plan shape. Because the branch has production's exact row counts and data skew, the planner behaves identically — no more "worked in staging" surprises. Test your riskier ideas here too: query rewrites, work_mem changes, dropping an index you suspect is dead weight.

When the numbers hold up, apply to production the safe way:

CREATE INDEX CONCURRENTLY idx_orders_status_created
  ON orders (status, created_at);

Then clean up — branch computes scale to zero, but tidy is tidy:

neon branches delete idx-test --project-id <project-id>

A branch of a multi-hundred-GB database costs storage only for pages you change, and the whole test loop takes minutes. This is the workflow classic Postgres teams build entire staging pipelines to approximate.

Where the DIY loop runs out

Everything above works, and you should do it. Also know what it doesn't give you:

It's a snapshot. The leaderboard you pulled today reflects today's traffic — and on Neon, pg_stat_statements resets on every suspend, so the window is narrower than you're used to. Next month's feature launch regenerates slow queries silently.

It's engineer-hours, repeatedly. The first pass takes an afternoon. The discipline of re-running it monthly is what actually decays — tuning competes with feature work and usually loses.

Nothing here watches the sliders. Autoscaling settings drift out of date as traffic grows; nobody notices the CPU chart flat-topping until latency complaints arrive, or the floor billing 24/7 for a service that quieted down.

Detection isn't remediation. Every finding still becomes a ticket, a branch test, and a change window that a human has to drive.

What comes next

The entire loop — watching query stats before they reset, checking autoscaling headroom and connection saturation, testing index proposals on a Neon branch, and applying only what you approve — can run continuously. That's Tony, CloudThinker's database agent, and it's part three of this series. Or start now: try CloudThinker free — 100 premium credits, no card required.