Unconventional PostgreSQL Optimizations: Beyond the Index

When it comes to database optimization, developers often reach for the same old tools: rewrite the query slightly differently, slap an index on a column, denormalize, analyze, vacuum, cluster, repeat. Conventional techniques are effective, but sometimes being creative can really pay off. PostgreSQL, with its rich feature set, offers several unconventional optimization techniques that can yield substantial benefits in specific scenarios.

Eliminating Full Table Scans with Check Constraints

Consider a common scenario: a users table with a plan column that can only be 'free' or 'pro', enforced by a check constraint. An analyst queries for users with the plan 'Pro' (note the capital 'P'), which doesn't exist. PostgreSQL, by default, performs a full table scan to determine this, scanning all 100,000 rows to return zero results.

The solution lies in the constraint_exclusion parameter. Setting it to 'on' allows PostgreSQL to examine check constraints during query planning. When the planner sees a condition that contradicts a constraint (like querying for 'Pro' when only 'free' and 'pro' are allowed), it can skip the table scan entirely, returning an empty result instantly.

This parameter isn't enabled by default because the planning overhead can outweigh the benefits for simple, well-formed queries. However, in reporting and BI environments where ad-hoc queries are common and mistakes happen, enabling constraint_exclusion can prevent unnecessary full table scans, saving significant resources.

Optimizing for Lower Cardinality with Function-Based Indexes

A sales table with 10 million records stores timestamps with full precision. Analysts need daily reports, so a standard B-tree index on the sold_at timestamp column seems logical. While this reduces query time from 627ms to 187ms, the index consumes 214MB—nearly half the table's size.

The insight here is that the analysts don't need millisecond precision; they need daily aggregation. By creating a function-based index on just the date part of the timestamp (date_trunc('day', sold_at AT TIME ZONE 'UTC')::date), the index size drops to 66MB. This smaller index not only saves storage but also improves query performance to 145ms, as PostgreSQL can deduplicate the fewer distinct values.

The challenge with function-based indexes is discipline: the query must use the exact same expression. A slight variation, like using ::date directly instead of date_trunc, will prevent index usage. This is where virtual generated columns (introduced in PostgreSQL 14, with virtual support in version 18) come in. By adding a virtual column that materializes the date expression, you create a single source of truth. Users can query the virtual column directly, guaranteeing index usage without requiring strict adherence to a specific expression pattern.

Enforcing Uniqueness with Hash Indexes

For tables storing large, unique values like URLs, a unique B-tree index ensures no duplicates. However, B-tree indexes store the actual values in leaf blocks, leading to massive index sizes—154MB for a table of 160MB in the example.

Hash indexes, which store hash values instead of the actual data, offer a more compact alternative. While PostgreSQL doesn't support unique hash indexes directly, exclusion constraints can bridge this gap. By adding an exclusion constraint using a hash index (EXCLUDE USING HASH (url WITH =)), you can enforce uniqueness while benefiting from the smaller footprint of hash indexes.

The hash index in the example is only 32MB—five times smaller than the B-tree equivalent. Queries using equality conditions can leverage this index efficiently. However, there are trade-offs: foreign keys cannot reference exclusion constraints, and INSERT ... ON CONFLICT has limitations. For upsert operations, the MERGE command provides a workaround.

The Philosophy of Unconventional Optimization

These techniques share a common thread: they move beyond generic solutions to address specific constraints and access patterns. The constraint-based scan elimination leverages schema semantics to avoid wasted work. Function-based indexing and virtual columns align the index structure with the actual query patterns, not just the underlying data. Hash indexes trade some functionality for storage efficiency in scenarios where large, unique values dominate.

Each approach requires understanding the trade-offs. Enabling constraint_exclusion adds planning overhead. Function-based indexes demand precise expression matching, mitigated by virtual columns. Hash indexes sacrifice foreign key support and full conflict resolution. The key is recognizing which problem you're solving and which trade-offs are acceptable.

In a world where databases are increasingly treated as black boxes, these optimizations remind us that deep knowledge of the system's internals can yield disproportionate benefits. They encourage looking beyond the obvious index and considering the data's shape, the query's intent, and the database's hidden capabilities.

For developers and DBAs, the lesson is to question defaults and explore the feature set. PostgreSQL's flexibility allows for creative solutions that can dramatically improve performance and reduce resource usage in the right contexts. The next time a query seems slow or an index feels bloated, consider whether an unconventional approach might be the key.

For further reading on PostgreSQL's advanced features, consult the official documentation on constraints, function-based indexes, and exclusion constraints.