Reducing data transfer

Every byte a query returns is serialised by Postgres, pushed through the connection, and deserialised by your driver, before your code decides whether it needed it. Most applications discard a large fraction of that. Cutting it is unusual in that it makes the application faster and cheaper at the same time - there is no trade to make.

CapyDB does not meter egress, so this is about latency and throughput, not a bill.

Four places it goes

Selecting more columns than you use

SELECT * returns every column, including the TEXT blob and the JSONB document nobody on this code path reads. It also defeats index-only scans: an index containing everything the query needs can be answered without touching the heap at all, and * guarantees it cannot.

-- Reads the heap for every row, returns columns nobody uses.
SELECT * FROM posts WHERE author_id = $1 ORDER BY created_at DESC LIMIT 20;

-- Can be answered from an index on (author_id, created_at, id, title).
SELECT id, title, created_at FROM posts WHERE author_id = $1 ORDER BY created_at DESC LIMIT 20;

ORMs make this easy to get wrong, because the default of every one of them is to hydrate the full model.

Returning more than you inserted

Most ORMs return the complete row after a write. Inserting two columns and getting fifty back doubles the transfer for no benefit:

-- Returns the whole row including the JSONB payload you just sent.
INSERT INTO posts (title, body) VALUES ($1, $2) RETURNING *;

-- Returns what you actually need.
INSERT INTO posts (title, body) VALUES ($1, $2) RETURNING id;

The same applies to UPDATE ... RETURNING *. Name the columns.

Fetching the same thing repeatedly

If every page view runs the same query for data that changes hourly, that is a cache, not a query. This is the largest win available in most applications and it is not a database change.

Paginating with OFFSET

OFFSET 100000 makes Postgres produce 100,020 rows and throw away 100,000 of them. The work grows linearly with the page number. Keyset pagination does not:

-- Cost grows with the page number.
SELECT id, title FROM posts ORDER BY created_at DESC OFFSET 100000 LIMIT 20;

-- Constant cost, any page.
SELECT id, title FROM posts
WHERE created_at < $1              -- the last row of the previous page
ORDER BY created_at DESC
LIMIT 20;

Measuring it

EXPLAIN estimates the width of a row in bytes, which multiplied by the row count is a decent first approximation:

EXPLAIN SELECT * FROM posts WHERE author_id = 1;
--  Index Scan using posts_author_idx on posts  (cost=... rows=560 width=116)
--                                                          ^^^^^^^^^ ^^^^^^^^^

width is an estimate and is unreliable for variable-length columns - a TEXT column averaging 8 KB is where the real transfer is hiding, and the planner's guess will not tell you that. Measure the actual sizes:

SELECT pg_size_pretty(avg(pg_column_size(payload))::bigint) AS avg_payload,
       pg_size_pretty(max(pg_column_size(payload))::bigint) AS max_payload
FROM posts;

Then rank query patterns by total time in your project's observability view - the heavy transfer is usually already near the top, because moving bytes is not free on the database side either.

Large JSONB

A JSONB document of a few hundred kilobytes is fine to store and expensive to return on every request. Two options, and they compose:

Extract only the keys you need, in the database:

SELECT id, payload -> 'summary' AS summary FROM posts WHERE id = $1;

That trades network bytes for a little CPU on the database, which is usually the right way round - but it is a trade, so measure rather than assume.

Or split the column: keep the small, frequently read fields in their own columns and the large document in a side table joined only when something actually needs it. Postgres already does a version of this automatically (values over ~2 KB move to TOAST storage), but it still fetches and decompresses them when you select the column. Not selecting it is free.