Postgres Egress Cost Optimization: Cutting Network Bandwidth by 70% in High-Throughput APIs

Why Egress Is the Line Item Nobody Instruments

A high-throughput API can burn more money on bytes leaving the database than on the database itself. Compute and storage are capacity-planning problems you can forecast. Egress is a behavioral problem: it is a direct function of how your application queries — and query behavior drifts silently with every sprint.

The failure mode is always the same. An endpoint ships with SELECT *. An ORM adds an eager-loaded association. A client starts requesting ?include=items on a list view. Six months later the monthly data-transfer line is 4.2 TB and nobody can attribute it to a route, a query, or a team.

This article walks through a measurement-first reduction from 4,300 GB/month to 1,270 GB/month (−70.5%) on a Postgres-backed order API serving ~1,100 req/s, with the exact SQL, TypeScript, and operational guardrails used. The patterns apply equally to RDS/Aurora, Cloud SQL, and serverless Postgres like Neon.

Deconstructing the Bill: What Actually Counts as Postgres Egress

The four network paths that bill differently

A subtle killer: if your API tier is in a different availability zone than the database, you pay both the request bytes and the response bytes. Co-locating is a one-line infrastructure change worth ~$0.02/GB on every byte.

Where bytes originate at the protocol level

Every SELECT round trip costs more than your result set:

1. Startup/authentication — TLS handshake plus SCRAM-SHA-256 challenge-response. With no pooling, a 1,000-RPS service can spend hundreds of MB/month on handshakes alone.
2. Protocol framing — a RowDescription message per prepared statement, DataRow headers, CommandComplete. Small per row, but not zero at 10⁹ rows.
3. Tuple payload — the actual column values. This is 80–95% of egress and where all real optimization happens.

Postgres wire protocol has no built-in compression. TLS compression is disabled by design (CVE-2012-4929 / the CRIME class of attacks), so application-layer compression is your only lever — and it is a large one.

The Four Egress Multipliers

1. Column overfetching

SELECT * on a table with a jsonb audit column or a TEXT description easily returns 10–50× the bytes the endpoint consumes. ORMs make this worse: RETURNING * on writes, eager include on reads.

2. Row overfetching

Unbounded LIMITs, offset pagination that re-scans, and N+1 fan-out (one query for the parent list, then one per row for children) amplify both egress and latency.

3. Payload duplication

TOASTed columns >2 KB are stored out-of-line and compressed — but they are detoasted into full size on read. A single SELECT * over 500 rows with a 4 KB metadata column ships 2 MB you may never parse.

4. Round-trip amplification

Chatty queries, no caching, no conditional requests, and connection churn all multiply the same logical data across the wire repeatedly.

Step 1: Instrument Egress Before You Optimize

You cannot reduce what you do not measure, and core Postgres does not expose per-query wire bytes. pg_stat_statements gives you rows and buffer touches; you need a proxy for true bytes.

Use PgBouncer as your byte meter

If PgBouncer sits in front of Postgres (it should, for connection churn), its admin console already counts bytes:

-- psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer
SHOW STATS;
-- total_received = bytes sent FROM client, total_sent = bytes sent TO client

Poll SHOW STATS every 60s and emit the deltas to Prometheus. This gives you accurate, per-database egress attribution with zero application changes.

Rank offenders with pg_stat_statements

SELECT
  queryid,
  left(regexp_replace(query, '\s+', ' ', 'g'), 110) AS query_sample,
  calls,
  rows,
  round(rows::numeric / NULLIF(calls, 0), 1)                AS avg_rows_per_call,
  round(total_exec_time::numeric / NULLIF(calls, 0), 2)    AS avg_ms,
  round(100.0 * shared_blks_read / NULLIF(shared_blks_hit + shared_blks_read, 0), 1)
                                                            AS cache_miss_pct
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY rows DESC
LIMIT 25;

This surfaces the two signals that matter: high absolute row volume and high rows-per-call (the overfetching signature).

Estimate true row width

-- Sampled, decompressed wire size of a full row
SELECT
  avg(pg_column_size(t))::bigint AS avg_wire_row_bytes,
  max(pg_column_size(t))::bigint AS max_wire_row_bytes
FROM (SELECT * FROM orders TABLESAMPLE SYSTEM (2)) t;

Multiply avg_wire_row_bytes × rows from pg_stat_statements to get a defensible per-query egress estimate. In our case, orders averaged 2.4 KB/row — 78% of it in two columns the API never serialized.

Step 2: Column Projection and Covering Indexes

Replace SELECT * with an explicit, typed projection and back it with a covering index so the projection costs you nothing in I/O.

-- The index serves the filter, the sort, AND the projection → index-only scan
CREATE INDEX CONCURRENTLY idx_orders_customer_keyset_covering
  ON orders (customer_id, created_at DESC, id DESC)
  INCLUDE (status, total_cents);

import type { Pool } from 'pg';

interface OrderSummaryRow {
  id: string;
  created_at: Date;
  status: 'pending' | 'paid' | 'shipped' | 'cancelled';
  total_cents: number;
}

const FIRST_PAGE_SQL = `
  SELECT id, created_at, status, total_cents
  FROM orders
  WHERE customer_id = $1
  ORDER BY created_at DESC, id DESC
  LIMIT $2
`;

const NEXT_PAGE_SQL = `
  SELECT id, created_at, status, total_cents
  FROM orders
  WHERE customer_id = $1
    AND (created_at, id) < ($2::timestamptz, $3::text)
  ORDER BY created_at DESC, id DESC
  LIMIT $4
`;

export async function listOrderSummaries(
  pool: Pool,
  customerId: string,
  cursor: { createdAt: Date; id: string } | null,
  limit = 50,
): Promise<OrderSummaryRow[]> {
  // Never trust a client-supplied limit — this is an egress DoS vector.
  const bounded = Math.min(Math.max(Math.trunc(limit) || 50, 1), 200);

  try {
    const { rows } = cursor
      ? await pool.query<OrderSummaryRow>(NEXT_PAGE_SQL, [
          customerId,
          cursor.createdAt,
          cursor.id,
          bounded,
        ])
      : await pool.query<OrderSummaryRow>(FIRST_PAGE_SQL, [customerId, bounded]);

    return rows;
  } catch (err) {
    // 57014 = query_canceled (statement_timeout), 42P01 = undefined_table
    const code = (err as { code?: string }).code;
    if (code === '57014') throw new Error('ORDERS_QUERY_TIMEOUT');
    if (code === '42P01') throw new Error('ORDERS_SCHEMA_MISSING');
    throw err;
  }
}

Two separate statements instead of ($2 IS NULL OR ...) is deliberate: the IS NULL guard prevents the planner from using the composite index on every page. Keyset pagination also eliminates the deep OFFSET re-scan problem entirely.

Measured impact: 1,850 GB → 720 GB/month on this endpoint family alone.

Step 3: Collapse Round Trips with Server-Side Aggregation

The classic N+1: fetch 50 orders, then 50 queries for order_items. That is 51 round trips, 51 sets of framing overhead, and 50× the connection pool churn. Collapse it in the database:

SELECT
  o.id,
  o.created_at,
  o.status,
  o.total_cents,
  COALESCE(
    jsonb_agg(
      jsonb_build_object('sku', i.sku, 'qty', i.qty, 'unit_cents', i.unit_cents)
      ORDER BY i.sku
    ) FILTER (WHERE i.id IS NOT NULL),
    '[]'::jsonb
  ) AS items
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
WHERE o.customer_id = $1
GROUP BY o.id, o.created_at, o.status, o.total_cents
ORDER BY o.created_at DESC
LIMIT $2;

The FILTER (WHERE i.id IS NOT NULL) guard is essential with LEFT JOIN — without it, orders with no items return [{...nulls}] instead of [].

Additional wins at this layer:

• Precompute aggregates. A customer_order_stats table refreshed on write kills an entire read path.
• Keep `GROUP BY` columns minimal. GROUP BY o.id alone works in Postgres ≥ 9.1 when id is the PK, but being explicit avoids planner surprises on partitioned tables.

Measured impact: 940 GB → 240 GB/month, plus a 340 ms p99 improvement.

Step 4: Shrink the Wire Format

Enable binary transfer for numeric-heavy rows

import type { Pool, QueryResult } from 'pg';

const res: QueryResult<{ id: string; created_at: Date; total_cents: number }> =
  await pool.query({
    text: 'SELECT id, created_at, total_cents FROM orders WHERE id = $1',
    values: [orderId],
    binary: true, // Bind message requests binary field format
  });

Binary transfer converts a 19-byte ASCII timestamptz into 8 bytes and a 12-digit bigint into 8. Caveat: node-postgres only decodes a subset of OIDs from binary — numeric and int8 arrive as strings unless you register type parsers. Verify per column before shipping; a silent "NaN" string is worse than the bytes you saved.

Stream large result sets instead of buffering

import { pool } from './db.js';
import QueryStream from 'pg-query-stream';
import { pipeline } from 'node:stream/promises';

await pipeline(
  pool.query(new QueryStream('SELECT * FROM events WHERE tenant_id = $1', [tenantId], {
    batchSize: 500,
    highWaterMark: 1000,
  })),
  async function* (source) {
    for await (const row of source) yield JSON.stringify(row) + '\n';
  },
  response.raw, // Express/Fastify response stream
);

For bulk exports, COPY (SELECT ...) TO STDOUT WITH (FORMAT csv, HEADER) avoids DataRow framing entirely and is markedly more compact than SELECT.

Compress at the HTTP edge (the single biggest lever)

import Fastify from 'fastify';
import compress from '@fastify/compress';
import { constants as zlibConstants } from 'node:zlib';

const app = Fastify({ logger: true });

await app.register(compress, {
  global: true,
  encodings: ['br', 'gzip', 'deflate'],
  threshold: 1024, // don't compress tiny payloads
  brotliOptions: {
    // Quality 4–5 is the dynamic-content sweet spot: within ~10% of q11's
    // ratio at a fraction of the CPU. Never use q11 for per-request JSON.
    params: { [zlibConstants.BROTLI_PARAM_QUALITY]: 4 },
  },
});

On repetitive JSON keys, Brotli at q4 typically lands 2.5–3.5× on the wire. Never double-compress: skip already-compressed payloads (images, gzip archives) or you pay CPU for a worse result.

Measured impact (binary + Brotli): 1,410 GB → 490 GB/month.

Step 5: Eliminate Bytes You Never Send

Conditional requests: the cheapest byte is the one you don't emit

import { createHash } from 'node:crypto';

app.get('/v1/customers/:id/orders', async (request, reply) => {
  const body = await buildOrderList(request.params.id);
  const etag = `W/"${createHash('sha1').update(JSON.stringify(body)).digest('base64url')}"`;

  if (request.headers['if-none-match'] === etag) {
    // 304: headers only, zero body bytes
    return reply.code(304).header('etag', etag).send();
  }

  return reply.header('etag', etag).header('cache-control', 'private, max-age=15').send(body);
});

A 304 Not Modified carries no payload. On a polling dashboard hitting an endpoint every 10s, this alone removes >90% of that endpoint's bytes. Pair it with stale-while-revalidate and a shared cache where the data permits it.

Sparse fieldsets with a strict allowlist

const PROJECTABLE = {
  id: 'id',
  createdAt: 'created_at',
  status: 'status',
  totalCents: 'total_cents',
} as const;

function buildProjection(fields: string[] | undefined): string {
  if (!fields?.length) return Object.values(PROJECTABLE).join(', ');
  const cols = fields
    .map((f) => PROJECTABLE[f as keyof typeof PROJECTABLE])
    .filter((c): c is string => Boolean(c));
  // Never interpolate raw user input into SQL — map through the allowlist.
  return cols.length ? cols.join(', ') : Object.values(PROJECTABLE).join(', ');
}

Measured impact (ETag/304 + sparse fieldsets): 320 GB/month eliminated.

Neon-Specific Egress Economics

Serverless Postgres changes the cost shape rather than the physics. Neon bills storage, compute-hours, and data transfer separately, so the same four multipliers apply — plus three platform-specific ones:

1. Region co-location is non-negotiable. If your compute hours run in us-east-1 and your Neon project lives in eu-central-1, every query pays cross-region egress and 90–120 ms RTT. Pin both.
2. Use the pooled endpoint. Neon's -pooler host runs PgBouncer in transaction mode. For serverless fan-out (hundreds of concurrent cold lambdas), unpooled connections multiply TLS handshakes and StartupMessage bytes — pure waste.
3. The HTTP driver collapses connection setup. @neondatabase/serverless ships queries over HTTPS in a single round trip with no handshake, no SCRAM negotiation, and gzip on the response. For single-shot queries in edge functions this is often a 15–25% byte reduction before any SQL tuning. Note that it does not support sessions, LISTEN, or interactive transactions.

Also watch logical replication consumers (CDC pipelines, read replicas in another region). A cross-region replica continuously streams WAL — a cost that never appears in pg_stat_statements.

The 70% Reduction Playbook: Before and After

Baseline: 4,300 GB/month, ~1,100 req/s, API and database in different AZs.

Result: 4,300 → 1,270 GB/month (−70.5%). At ~$0.09/GB that is $387 → $114/month, roughly $3,270/year. At 10× traffic the same ratios hold, making it a ~$33k/year line item — which is why this deserves an SLO, not a quarterly cleanup.

Operational Checklist

• PgBouncer (or a TCP proxy) in front of Postgres, SHOW STATS scraped to Prometheus
• pg_stat_statements enabled with track = all; weekly review of top-20 by rows
• Zero SELECT * in application code — enforced with a CI grep or SQL linter (sqlfluff)
• All list endpoints bounded: LIMIT clamped server-side, keyset pagination only
• Covering indexes on the three highest-volume read paths; verify EXPLAIN shows Index Only Scan
• Brotli q4 enabled globally with a 1 KB threshold
• ETag + If-None-Match on every cacheable GET
• API tier and database in the same AZ (or same Neon region)
• Region-pinned read replicas; alert on any cross-region replica creation
• Monthly cost-per-request metric published to the team dashboard

Pitfalls That Inflate Your Next Bill

Logging the payload. Emitting full query results or response bodies to CloudWatch/Stackdriver is egress twice — once from Postgres, once to the log sink. Log sizes and IDs, not bodies.

*`RETURNING on writes.** A bulk UPDATE ... RETURNING *` over 10k rows with wide columns returns megabytes the caller discards.

Unbounded `IN (...)` expansion. ORMs that expand WHERE id IN (…) with 5,000 IDs create a statement whose parse traffic rivals the result.

`jsonb` as a document store. A 40 KB settings blob re-serialized on every read costs 40 KB × request rate. Normalize hot keys into columns; keep jsonb for genuinely sparse data.

Detoasting for a filter. WHERE metadata->>'status' = 'active' forces detoast of the whole column. Add an expression index on the extracted value.

Aggressive offset pagination. LIMIT 50 OFFSET 50000 reads and discards 50,050 rows server-side; the egress is small, but the buffer churn and latency are not.

Compressing twice. Setting Content-Encoding: gzip on an already-compressed S3 passthrough wastes CPU and can increase bytes.

Sources & Technical References

• PostgreSQL Global Development Group — The `pg_stat_statements` Module
• PostgreSQL Global Development Group — Monitoring Database Activity (`pg_stat_io`, `pg_stat_database`)
• PostgreSQL Global Development Group — Frontend/Backend Protocol: Message Formats
• PostgreSQL Global Development Group — `COPY`
• PostgreSQL Global Development Group — TOAST: The Oversized-Attribute Storage Technique
• PgBouncer Project — Usage & Admin Console (`SHOW STATS`)
• Neon — Pricing & Data Transfer
• Neon — Connection Pooling
• AWS — EC2 On-Demand Pricing & Data Transfer
• IETF RFC 9110 — HTTP Semantics (Conditional Requests, 304 Not Modified)
• IETF RFC 9111 — HTTP Caching
• IETF RFC 7932 — Brotli Compressed Data Format
• IETF RFC 8878 — Zstandard Compression and the `application/zstd` Media Type
• IETF RFC 8446 — The Transport Layer Security (TLS) Protocol Version 1.3
• MDN Web Docs — `ETag` and `Content-Encoding`
• node-postgres — Queries, Binary Mode & Type Parsers