Diagnose and Fix Excessive Postgres Egress Costs

Guides a user through diagnosing and fixing application-side query patterns that cause excessive data transfer (egress) out of Postgres, on the premise that most high egress bills come from the application fetching more data than it uses. Step 1 diagnoses using the pg_stat_statements extension (checking availability, creating it if missing, and resetting stats for a clean measurement window if the compute recently scaled to zero and cleared them) with four diagnostic queries: queries returning the most total rows, queries returning the most rows per call (poorly scoped SELECTs or missing pagination), the most frequently called queries (caching candidates), and the longest-running queries. Results are ranked by estimated egress impact - high row count combined with wide columns (JSONB, TEXT, BYTEA) is the biggest contributor, and extreme call frequency on even small queries adds up (50,000 calls/day returning 10 rows each is 500,000 rows/day). If no production stats are available, Step 2 analyzes the codebase directly for the same red flags: unbounded column selection, missing LIMIT/pagination, cacheable-but-uncached frequent queries, application-side aggregation of raw fetched data, and JOINs that duplicate parent columns across child rows. Step 3 fixes each anti-pattern found: replacing SELECT * with only the needed columns; adding LIMIT/OFFSET pagination to unbounded list queries (flagged as a risk regardless of current table size, since it worsens silently as the table grows); adding a caching layer in front of high-frequency queries on rarely-changing data (configuration tables, category lists, feature flags); pushing application-side aggregation (averages, counts, sums, groupings) into SQL with GROUP BY instead of transferring the full dataset to compute a summary; and splitting a JOIN that duplicates a wide parent row across many child rows (e.g. a 50KB product JSONB column repeated across 200 review rows, ~10MB for one request) into two separate queries instead. Step 4 verifies the fixes: run existing tests, check that the API's response shape is unchanged (column selection and pagination changes can break clients expecting specific fields or full result sets), and re-measure with a fresh pg_stat_statements_reset() and comparison window. As a complementary, separate cost lever, neon.ts's declarative branch function can cap non-production compute (scale-to-zero autoscaling limits, a suspend timeout) and set a branch TTL so dev/preview/CI branches don't quietly inflate storage and compute costs alongside egress.

Use it when a user mentions high database bills, unexpected data transfer costs, egress spikes, or asks why their Neon bill jumped, or wants to optimize SELECT * or unbounded queries. It targets application-side query patterns specifically - it doesn't cover compute cost tuning directly, though it points to the neon.ts branch-config lever as a complementary fix for that separate cost driver.

Input is access to pg_stat_statements data (or, if unavailable, the application codebase's database queries) plus knowledge of which columns are wide (JSONB/TEXT/BYTEA). Output is a ranked list of egress-heavy queries, a concrete fix for each anti-pattern found, and a before/after measurement comparing egress under representative traffic.

Uses Postgres's pg_stat_statements extension (enabled by default on Neon, may need CREATE EXTENSION) for diagnosis, and optionally neon.ts infrastructure-as-code for capping non-production compute cost as a separate, complementary lever.

Developers and teams facing an unexpectedly high Postgres or Neon bill who want to find and fix the specific query patterns - unscoped SELECTs, missing pagination, uncached hot queries, application-side aggregation, and duplicating JOINs - driving the data transfer cost.