How do you query a billion-row transaction table without falling over?
Bound every access by a key range, keep the access path matched to an existing index, and stop treating COUNT(*) as free.
At that size the query plan is the whole story. Some rules I hold to:
Never scan when you can seek. Every query is bounded by a range on an indexed key — usually time plus an entity key. If the plan shows a full scan, the query is wrong, not the table.
Match existing indexes rather than adding new ones. Each added index is a tax on every write, and on a write-heavy transaction table that tax compounds. Rewriting the predicate is usually cheaper than the index that would rescue it.
Paginate by keyset, not by OFFSET. OFFSET 10000000 reads ten million rows
to discard them. WHERE id > :last reads what you asked for.
Treat exact counts as a feature request. An exact COUNT(*) is a scan.
Where the product will accept an estimate or a maintained counter, that is a
better answer than a faster scan.
Archive on a schedule. The cheapest query against cold data is the one against a table that no longer holds it.