Why Sequential Reads Beat Random Reads in Postgres at Scale

Short summary

When batch-reading hundreds of GB from Postgres, index-based pagination causes up to 2000 random disk reads per batch because rows are stored in insert order, not id order. Using a server-side cursor forces a sequential heap scan that reads each 8KB page exactly once with predictable prefetching. For long-running exports where cursor transactions would block vacuum, keyset pagination on a column correlated with physical layout is a practical compromise.

• •Index-based LIMIT/OFFSET pagination causes random disk reads at scale because heap order != id order
• •Server-side cursors force sequential heap scans, reading each page once with OS prefetch benefits
• •Keyset pagination is a practical alternative when long-lived cursor transactions are problematic

Generated with AI, which can make mistakes.