How PostgreSQL and SQL Server Differ in Data Access Strategies

Sequential Scan

Both PostgreSQL and SQL Server can perform a full table (sequential) scan, reading every row in order. While this may seem inefficient for large tables, it is optimal for small tables or queries that need to retrieve a large proportion of rows (e.g., over 50%). The optimizer chooses a sequential scan when no suitable index exists or when filtering conditions are broad.

Index Scan

PostgreSQL’s index scan traverses a B‑tree index to locate tuple pointers, then fetches the corresponding rows from the heap in a single combined operation. SQL Server separates these steps: an Index Seek finds matching index entries, followed by a Key Lookup (for clustered tables) or RID Lookup (for heap tables) to retrieve missing columns. This separation provides clearer visibility into the cost of the heap access, aiding performance tuning.

Bitmap Heap Scan and Bitmap Index Scan

For queries with multiple predicates, PostgreSQL can build a bitmap from one or more indexes (Bitmap Index Scan) and then perform a Bitmap Heap Scan to fetch rows in batches, reducing random I/O. The bitmap may be exact or lossy. SQL Server does not have a direct equivalent, though it uses bitmap filtering in parallel plans.

Index‑Only Scan

PostgreSQL’s index‑only scan can satisfy a query entirely from the index without accessing the heap, provided all required columns are present in the index. This reduces I/O and improves performance for read‑heavy workloads. SQL Server achieves a similar effect with covering indexes that include additional columns, eliminating the need for key or RID lookups.

Parallel Query Execution

Both databases support parallel query execution. PostgreSQL can split a large table scan across multiple worker processes, reducing overall query time. SQL Server uses parallel scan operators and gather streams to distribute work across threads, integrating tightly with its optimizer for both OLTP and OLAP workloads.

Role of Clustered Indexes

SQL Server’s clustered index defines the physical order of rows, benefiting range queries and ordered results. PostgreSQL stores tables as unordered heaps and does not support clustered indexes; the CLUSTER command can reorder a table based on an index, but this is a manual, static operation. PostgreSQL’s design favors flexibility and the ability to maintain multiple indexes, which is advantageous for write‑heavy workloads.

Conclusion

PostgreSQL offers flexible data‑access techniques such as bitmap heap scans and index‑only scans, giving developers fine‑grained control over query execution, while lacking clustered indexes. SQL Server relies on clustered indexes for physical ordering and provides more explicit execution‑plan visibility. Understanding these differences helps DBAs choose the right platform and optimize queries accordingly.