In this lesson, you will learn how PostgreSQL handles NULL values within B-tree indexes and how this affects your query performance and index utility.
How PostgreSQL Indexes NULLs
In a standard B-tree index, PostgreSQL treats NULL as a distinct value. Specifically, it stores NULL entries at the beginning or end of the index structure (depending on the NULLS FIRST or NULLS LAST setting). Because they are physically stored in the index, queries searching for NULL values—such as WHERE column IS NULL—can benefit from an Index Scan rather than falling back to a full table scan.
The Cost of Indexing Nothing
While indexing NULL values allows for faster IS NULL searches, there is a performance trade-off. If your table has a high percentage of NULL values, the index grows significantly in size. A bloated index increases the depth of the B-tree, requiring more I/O operations to traverse. Furthermore, if your application rarely queries for NULL values, these entries occupy space in the cache (the shared_buffers) without providing any tangible benefit to your most frequent SELECT queries.
Consider a scenario where you have a status_date column that is NULL for 90% of your rows:
-- Creating an index on a column with many NULLs
CREATE INDEX idx_status_date ON orders(status_date);
-- This query benefits from the index
SELECT * FROM orders WHERE status_date IS NULL;
If you find that your query patterns ignore the NULL values, you can reclaim space and improve performance using a Partial Index. This technique excludes the NULL values from the index entirely, keeping the index slim and fast:
-- A partial index ignores NULLs, keeping the index small
CREATE INDEX idx_status_date_active ON orders(status_date)
WHERE status_date IS NOT NULL;
By excluding the NULL entries, you reduce the index size, improve the efficiency of your Index Scan for non-null data, and minimize the maintenance overhead during INSERT and UPDATE operations.
Key Takeaway: While PostgreSQL includes NULLs in indexes to support IS NULL lookups, using partial indexes to exclude them is an essential strategy for preventing index bloat and keeping your scan paths optimized.