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 and 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 an identical copy, then apply it to main.

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 part 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 two to three hours for a first pass on one project. Work in the order below, because each step feeds the next.

01Read

Monitoring

CPU, connections and the local file cache hit rate, before touching anything.

02Size

Autoscaling

Min, max and scale-to-zero set from the charts, not the defaults.

03Route

Pooled or direct

The right connection string for each kind of client.

04Rank

pg_stat_statements

Top queries by total time, then EXPLAIN on each one.

05Prove

Branch test

Measure the fix on a copy of production before main.

---

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 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. The fix is usually 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 and 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 don't pay 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 (the 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 there. For a latency-sensitive production endpoint, turn it off on a paid plan (scale-to-zero docs) and treat the always-on floor 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 to 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. Also 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 swarm of Lambda functions. 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 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 pushing 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 (SELECT pg_postmaster_start_time(); tells you when that was). Capture the leaderboard while the compute has been awake through real traffic, or snapshot it into a table on a schedule if you need history:

CREATE TABLE IF NOT EXISTS stmt_snapshots AS
SELECT now() AS captured_at, queryid, query, calls,
       total_exec_time, mean_exec_time, rows,
       shared_blks_hit, shared_blks_read
FROM pg_stat_statements
WITH NO DATA;

INSERT INTO stmt_snapshots
SELECT now(), queryid, query, calls,
       total_exec_time, mean_exec_time, rows,
       shared_blks_hit, shared_blks_read
FROM pg_stat_statements;

Run the INSERT from a cron job or CI schedule before the compute's usual idle period. Note that the job's connection itself keeps the compute awake for as long as it runs.

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 turn 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.

Form the hypothesis before you branch. Say query #1 on the leaderboard is the one above, at 3.1 seconds mean (illustrative numbers). Three readings fit:

• No usable index. Predicts Seq Scan on orders with most rows removed by the filter.
• An index exists but statistics are stale. Predicts an index scan or a seq scan chosen on a row estimate that's off by 10x or more.
• Cache-cold reads, not a bad plan. Predicts a reasonable plan with high Buffers: shared read on the first run and much lower on the second.

The plan settles it:

Seq Scan on orders o  (cost=0.00..412300.00 rows=8100 width=24)
                      (actual time=0.41..2981.20 rows=7944 loops=1)
  Filter: ((status = 'pending') AND (created_at > (now() - '7 days'::interval)))
  Rows Removed by Filter: 11992056
  Buffers: shared hit=1820 read=210480

Twelve million rows removed by the filter to keep 7,944. The estimate (8,100) is close, so statistics are fine. A second run is just as slow, so it isn't cache warm-up. The hypothesis is the first one: 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).

01Branch

Copy-on-write

A branch of main at this instant, created in about a second.

02Baseline

EXPLAIN ANALYZE

The slow query on real rows, real statistics and real skew.

03Change

Index or rewrite

Apply the candidate, ANALYZE, re-run, compare buffers and time.

04Apply

CONCURRENTLY

Create the same index on main without locking writes, then delete the branch.

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 row counts and data skew, the planner makes the same choices it would on main, so there are no "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.

---

Which tool answers which question

Where the DIY loop runs out

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

01Time

A snapshot

Stats reset on every suspend, so the window is shorter than on classic Postgres.

02Cost

Engineer-hours

An afternoon for the first pass. The monthly repeat is what decays.

03Drift

Unwatched sliders

Autoscaling ranges go stale as traffic grows, and nobody is looking.

04Action

Detection only

Every finding becomes a ticket, a branch test and a change window.

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, because 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 around the clock for a service that went quiet.

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

The native tools stop where cadence and memory start: noticing that something moved, working out why, and running the branch test without being asked.

What comes next

The entire loop (capturing 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. CloudThinker does that, starting in Manual under Auto Mode, and it's part three of this series. Or start now: try CloudThinker free with 100 premium credits and no card required.