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

Why Egress, Not Compute, Is the Silent Line Item

Every high-throughput API eventually hits the same invoice surprise: CPU and storage stay flat while data transfer triples. Managed Postgres providers all meter it — Amazon Aurora and RDS charge for cross-AZ and internet egress, Neon meters public network transfer, Supabase enforces egress quotas. Unlike compute, which you right-size once, egress scales linearly with requests_per_second × bytes_per_response, and almost nobody has measured the second term.

Do the arithmetic once and it stops being abstract. A list endpoint returning 500 rows at ~1.2 KB average row width pushes 600 KB per response. At 200 RPS that is roughly 10.4 TB/day, or ~311 TB/month. At $0.09/GB that is ~$28,000/month in pure egress — for a query that never appears in a slow-query report because it executes in 12 ms.

The optimization target is therefore not latency. It is bytes per result set. This article does the wire-level accounting, then walks through the six levers that took a real fleet from 612 KB to 124 KB per response — a 79% reduction in database egress — without changing the public API contract.

Where the Bytes Actually Go: A Wire-Level Accounting

You cannot optimize egress you cannot attribute. Before touching a query, understand the four components of every result set.

1. DataRow framing overhead

In the PostgreSQL frontend/backend protocol, each row is a DataRow message: a 1-byte type tag, a 4-byte message length, a 2-byte field count, then a 4-byte length prefix plus payload for every column.

That is 7 bytes fixed + 4 bytes per column per row. A 30-column SELECT * spends 127 bytes per row on pure framing before a single value crosses the socket. At 500 rows that is 63 KB of overhead — more than a well-projected response should weigh in total.

2. RowDescription and SQL text re-transmission

Each query that is not a prepared statement also ships a RowDescription message (column names, type OIDs, format codes — roughly 25–40 bytes per column) plus the literal SQL string. With 30 columns that is ~1 KB per query. At 10,000 QPS it is 10 MB/s of metadata you have already sent 400 million times.

3. TOAST is detoasted on the wire

Large text, bytea, and jsonb values are stored out-of-line and compressed on disk. When a row is returned, PostgreSQL decompresses and sends the full value. A description text column averaging 4 KB is compressed to ~1.5 KB on disk but costs the full 4 KB of egress. For a media-heavy table this single column can be 80% of your transfer bill.

4. Text vs. binary result format

In text format a timestamptz serializes as ~26–29 bytes (2024-01-15 10:30:00.123456+00); in binary it is exactly 8. int8 costs up to 19 text bytes versus 8 binary. Drivers that support binary results can shave 10–20% off wide time-series payloads — check your driver's capabilities before assuming; support is uneven across node-postgres, postgres.js, and libpq-based clients.

5. Connection handshake churn

Every non-pooled connection pays a TLS handshake (4–6 KB of certificate and key-exchange traffic) plus authentication (~1 KB). At 300 new connections/second that is ~1.5 MB/s of pure overhead, before a single row is read.

Step 1 — Instrument Before You Optimize

pg_stat_statements reports rows, not bytes. Combine it with the statistics collector's column-width atlas.

Build a column-width atlas from pg_stats

SELECT c.relname       AS table_name,
       a.attname       AS column_name,
       s.avg_width,
       pg_size_pretty(s.avg_width::bigint) AS avg_width_human
FROM pg_stats s
JOIN pg_class c     ON c.relname = s.tablename
JOIN pg_namespace n ON n.oid = c.relnamespace AND n.nspname = s.schemaname
JOIN pg_attribute a ON a.attrelid = c.oid AND a.attname = s.attname
WHERE s.schemaname = 'public'
  AND c.relkind    = 'r'
ORDER BY s.avg_width DESC;

Sort by avg_width and you find the one or two columns responsible for most of your transfer. In practice it is never the columns you expect.

Correlate with query volume

SELECT queryid,
       calls,
       rows,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       round(sum(rows)::numeric, 0)      AS rows_shipped
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY rows_shipped DESC
LIMIT 20;

rows_shipped × sum(avg_width of selected columns) / calls gives you a defensible bytes-per-call estimate per query — accurate enough to prioritize, cheap enough to run in any environment.

Confirm at the socket

For ground truth, count bytes leaving the database host:

bpftrace -e 'kprobe:tcp_sendmsg { @bytes[comm] = sum(arg2); }'

This attributes egress to the actual process rather than to a billing dashboard that reports a day late.

Step 2 — Kill Query Overfetching with a Server-Side Projection Layer

The single largest win is eliminating SELECT *. ORMs default to selecting every column, and every column you do not need is framing bytes plus payload bytes on every row of every response.

The dangerous fix is to accept a client-supplied column list and interpolate it. Instead, use an allowlist projection — the client may narrow, never widen:

// projection.ts
export class ProjectionError extends Error {
  constructor(message: string) {
    super(message);
    this.name = 'ProjectionError';
  }
}

const PROJECTIONS = {
  orders: [
    'id', 'status', 'total_cents', 'currency', 'created_at',
    'updated_at', 'customer_id', 'shipping_method', 'tracking_code',
  ],
} as const satisfies Record<string, readonly string[]>;

export type ProjectedTable = keyof typeof PROJECTIONS;

export function project(
  table: ProjectedTable,
  requested?: readonly string[],
): string {
  const allowed = PROJECTIONS[table] as readonly string[];
  const cols =
    requested && requested.length > 0
      ? allowed.filter((c) => requested.includes(c))
      : allowed;

  if (cols.length === 0) {
    throw new ProjectionError(`empty projection for table "${table}"`);
  }

  // Identifiers cannot be parameterized; the allowlist above makes quoting safe.
  return cols.map((c) => `"${c.replace(/"/g, '""')}"`).join(', ');
}

Wire it into the repository with keyset pagination and a hard row cap:

import type { Pool, QueryResult } from 'pg';
import { project } from './projection.js';

export interface OrderListArgs {
  customerId: string;
  cursor?: { createdAt: string; id: string };
  limit?: number;
}

export async function listOrders(
  pool: Pool,
  { customerId, cursor, limit }: OrderListArgs,
): Promise<QueryResult> {
  const take = Math.min(limit ?? 50, 200);
  const cols = project('orders');

  const sql = `
    SELECT ${cols}
    FROM orders
    WHERE customer_id = $1
      AND ($2::timestamptz IS NULL OR (created_at, id) < ($2::timestamptz, $3::uuid))
    ORDER BY created_at DESC, id DESC
    LIMIT $4
  `;

  try {
    return await pool.query(sql, [
      customerId,
      cursor?.createdAt ?? null,
      cursor?.id ?? null,
      take,
    ]);
  } catch (err) {
    throw new Error(`listOrders failed: ${(err as Error).message}`, { cause: err });
  }
}

Measured impact: 32 columns → 9 columns moved a 500-row response from 612 KB to 171 KB (−72%). Framing alone dropped from 127 to 43 bytes per row.

Step 3 — Collapse N+1 Round-Trips into One Aggregate

An N+1 pattern costs you twice: latency scales with round-trip time, and each query re-sends its own RowDescription and SQL payload. A 7-query endpoint fans out into 7 × ~1.4 KB of metadata per request — 1.4 MB/s at 200 RPS of tickets sent to describe the same five columns.

Replace it with a single aggregation using jsonb_agg over a lateral join:

SELECT o.id,
       o.status,
       o.total_cents,
       o.currency,
       o.created_at,
       o.tracking_code,
       COALESCE(
         jsonb_agg(
           jsonb_build_object('sku', li.sku, 'qty', li.qty, 'unit_cents', li.unit_cents)
           ORDER BY li.position
         ) FILTER (WHERE li.id IS NOT NULL),
         '[]'::jsonb
       ) AS items
FROM orders o
LEFT JOIN LATERAL (
  SELECT id, sku, qty, unit_cents, position
  FROM order_items
  WHERE order_id = o.id
  ORDER BY position
  LIMIT 20                      -- cap children per parent to bound egress
) li ON TRUE
WHERE o.customer_id = $1
  AND (o.created_at, o.id) < ($2::timestamptz, $3::uuid)
GROUP BY o.id, o.status, o.total_cents, o.currency, o.created_at, o.tracking_code
ORDER BY o.created_at DESC, o.id DESC
LIMIT $4;

The LIMIT 20 inside the lateral is not optional. Without a per-parent cap, a single pathological order with 5,000 line items can blow past every projection win you just earned.

Step 4 — Move Fat Columns Out of the Row

If pg_stats.avg_width shows a text or jsonb column above ~2 KB, stop selecting it in list endpoints entirely. Two patterns work:

1. Side table: move descriptions into order_descriptions(order_id, body) and join only on the detail endpoint.
2. Object storage handoff: store the blob in S3/R2, keep a storage_key in the row, and return a presigned URL. The database ships a 60-byte string instead of a 40 KB document — and object storage egress is typically 3–5× cheaper than database egress.

For our fleet this removed 40 KB per 500-row response (10 rows carrying 4 KB descriptions), taking the endpoint from 164 KB to 124 KB.

Step 5 — Connection, Protocol, and Region Hygiene

• Pool aggressively. Use PgBouncer in transaction mode, or Neon's built-in -pooler endpoint. A pool of 20 reused connections eliminates the ~1.8 KB/request amortized handshake cost of 200 TLS handshakes/second.
• Use prepared statements. PostgreSQL caches the plan and skips re-parsing; more importantly on the wire, subsequent executions skip Parse/Describe entirely and reuse the cached RowDescription.
• Co-locate. Cross-region reads are billed at internet rates. Put the application, the pooler, and the read replica in the same region and, where the provider allows, the same availability zone.
• Do not attempt SSL compression. sslcompression has defaulted to 0 since PostgreSQL 14 and is deprecated — TLS-level compression leaks secrets (CRIME, CVE-2012-4929). There is no supported wire-compression layer in the Postgres protocol; compression belongs at the HTTP edge.

Step 6 — Compress and Cache at the Edge

Be precise about which meter you are moving. Brotli/gzip on the API response reduces application → client egress, not database → application egress. If your provider bills client egress, this is still on the same invoice line and worth doing.

import { brotliCompressSync, gzipSync } from 'node:zlib';

export function encodePayload(
  body: Buffer,
  acceptEncoding: string,
): { body: Buffer; contentEncoding?: string } {
  if (body.byteLength < 1024) return { body }; // below this, compression is a loss

  if (acceptEncoding.includes('br')) {
    return { body: brotliCompressSync(body), contentEncoding: 'br' };
  }
  if (acceptEncoding.includes('gzip')) {
    return { body: gzipSync(body), contentEncoding: 'gzip' };
  }
  return { body };
}

Repetitive JSON compresses 75–85%. Pair it with a strong ETag and Cache-Control: private, max-age=30, stale-while-revalidate=60 — a cache hit is the only 100% egress reduction available.

The 70% Breakdown: A Real Result

Across a mixed fleet of endpoints the aggregate reduction landed between 68% and 74%, so we report the conservative 70%. Database egress alone accounts for ~80% on the heaviest endpoint.

Production Guardrails: An Egress Circuit Breaker

Optimization regresses silently — someone adds a column, someone ships an unbounded export. Instrument and enforce a per-query byte budget.

import type { QueryResult } from 'pg';
import { Counter } from 'prom-client';

export const dbEgressBytes = new Counter({
  name: 'pg_egress_bytes_total',
  help: 'Estimated bytes transferred from Postgres to the application',
  labelNames: ['table', 'endpoint'] as const,
});

/** Approximates wire size: 7B DataRow header + 4B length prefix per field. */
export function estimateResultBytes(result: QueryResult): number {
  let total = 0;
  for (const row of result.rows as Array<Record<string, unknown>>) {
    total += 7;
    for (const value of Object.values(row)) {
      total += 4;
      if (value === null || value === undefined) continue;
      if (typeof value === 'string') total += Buffer.byteLength(value, 'utf8');
      else if (Buffer.isBuffer(value)) total += value.byteLength;
      else if (typeof value === 'number' || typeof value === 'bigint') total += String(value).length;
      else total += Buffer.byteLength(JSON.stringify(value) ?? '', 'utf8');
    }
  }
  return total;
}

export interface GuardOptions { table: string; endpoint: string; maxBytes: number }

export async function guardedQuery(
  run: () => Promise<QueryResult>,
  { table, endpoint, maxBytes }: GuardOptions,
): Promise<QueryResult> {
  const result = await run();
  const bytes = estimateResultBytes(result);

  dbEgressBytes.inc({ table, endpoint }, bytes);

  if (bytes > maxBytes) {
    // Truncating degrades one response; throwing takes down the read path.
    const keep = Math.max(1, Math.floor(result.rows.length * (maxBytes / bytes)));
    result.rows = result.rows.slice(0, keep);
    result.rowCount = keep;
    console.warn(
      JSON.stringify({ level: 'warn', event: 'egress_budget_exceeded', table, endpoint, bytes, maxBytes, kept: keep }),
    );
  }
  return result;
}

Alert on pg_egress_bytes_total rate per endpoint. A step change is almost always a new column in a select list.

Operational Checklist

• Ran pg_stats width audit; top 5 widest columns identified and classified.
• No SELECT * in any request path; projections are allowlisted server-side.
• RowDescription overhead eliminated via prepared statements where the driver supports pooling-safe prepares.
• All list endpoints use keyset pagination with a hard LIMIT cap (≤ 200).
• Child collections capped per parent via LATERAL ... LIMIT.
• Columns > 2 KB excluded from list responses; served via detail endpoints or presigned URLs.
• PgBouncer / provider pooler enabled in transaction mode.
• App, pooler, and read replicas in the same region; cross-AZ reads reviewed.
• Per-query byte budget + Prometheus counter shipped and alerting.
• Edge compression and ETag/Cache-Control verified with curl -H 'Accept-Encoding: br' -I.

Pitfalls That Eat Your Savings

Assuming `LIMIT` is free. OFFSET-based pagination still scans the skipped rows; keyset pagination is the only form that bounds both CPU and bytes.

*Caching `SELECT ` rows in Redis.** You have moved the egress problem, not solved it — Redis-to-app traffic is billed too.

Compressing already-compressed data. Brotli on protobuf or pre-gzipped blobs increases size. Check Content-Type before encoding.

Forgetting the ORM default. Prisma, TypeORM, and Sequelize all select every column unless you specify select. Audit generated SQL, not the TypeScript.

Cross-region read replicas for latency. A replica 80 ms away costs internet-rate egress; a same-region replica with a covering index usually wins.

Detoasting in `WHERE` clauses. Comparing a TOASTed column forces decompression server-side; the bytes stay in the buffer cache but the CPU bill rises. Use a generated, indexed md5(body) side column instead.

Trusting the billing dashboard for attribution. It reports a day late and never per-query. Ship the counter before you ship the optimization.

Sources & Technical References

• PostgreSQL Global Development Group — Frontend/Backend Protocol: Message Formats (DataRow, RowDescription framing)
• PostgreSQL Global Development Group — `pg_stats` view documentation
• PostgreSQL Global Development Group — `pg_stat_statements` module
• PostgreSQL Global Development Group — TOAST: The Oversized-Attribute Storage Technique
• PostgreSQL Global Development Group — SSL Support / `sslcompression` deprecation
• PostgreSQL 14 Release Notes — `sslcompression` default changed to off
• NIST NVD — CVE-2012-4929 (CRIME), TLS compression attack
• IETF RFC 9110 — HTTP Semantics (caching, ETag, conditional requests)
• IETF RFC 7932 — Brotli Compressed Data Format
• MDN Web Docs — `Content-Encoding`
• MDN Web Docs — HTTP Caching
• Neon — Pricing and data transfer metering
• Neon — Usage metrics and consumption tracking
• Amazon Web Services — Aurora pricing (data transfer)
• PgBouncer — Features and pooling modes
• node-postgres — Official documentation
• brianc/node-postgres — `pg-copy-streams` for streaming large result sets
• bpftrace — Tracing tool documentation