btree_gist improvements in PostgreSQL 18

The btree_gist improvements in PostgreSQL 18 are one of the most practical performance wins shipping in this release for teams that depend on exclusion constraints, composite indexes, and nearest-neighbour search. The btree_gist extension lives in PostgreSQL contrib and implements btree-like operators and behavior on top of the GiST framework. With PostgreSQL 18, index builds that rely on btree_gist are dramatically faster, and the resulting indexes are better organized for query time as well. In this guide we explain what changed, why it matters, and how to validate the gains on your own workloads. This article walks through the motivation behind the change, the engineering behind the patch, reproducible benchmarks, and clear guidance on when to adopt PostgreSQL 18 for workloads that lean on btree_gist. Whether you run heavy bulk imports, maintain large exclusion constraints, or tune GiST indexes for latency, the btree_gist improvements in PostgreSQL 18 deserve a place on your upgrade checklist.

What is btree_gist and why it matters

GiST stands for Generalized Search Tree. It is a framework used to implement advanced, specialized indexes for a wide range of use cases. Full-text indexing and spatial index types are classic examples of specialized index implementations built on top of GiST. The btree_gist extension uses this same framework to provide btree-like operators — equality, ordering, and range behavior — inside a GiST index. On the surface that may sound redundant, because standard btree indexes already handle scalar equality and ordering efficiently. The value of btree_gist becomes obvious the moment you need to combine ordinary scalar types with GiST-only capabilities in a single index. Two use cases stand out in modern PostgreSQL deployments.

Nearest-neighbour search

The btree_gist improvements in PostgreSQL 18 make this pattern more efficient at scale. GiST indexes support ordering operators that let PostgreSQL answer nearest-neighbour queries efficiently. By exposing btree-style operators through GiST, btree_gist allows scalar columns to participate in these ordered scans alongside geometric or range data.

Exclusion constraints

This is arguably the most important use case. Exclusion constraints let you enforce advanced business rules that a simple UNIQUE constraint cannot express. Suppose you want to combine a standard scalar type such as bigint or uuid with a range type. You need btree_gist to create that constraint, because otherwise the required combination of those types cannot be used together inside a single GiST index — and a GiST index is exactly what an exclusion constraint requires here.

The problem before PostgreSQL 18: buffered index builds

To understand the btree_gist improvements in PostgreSQL 18, you first need to understand how GiST indexes are built. PostgreSQL offers two methods for building a GiST index: buffered and sorted. The sorted method is selected when the operator classes for a GiST index implement sortsupport. If sortsupport is not available, PostgreSQL falls back to the buffered method. That distinction turned out to be the crux of the performance story. The buffered method follows a one-by-one approach. Each value is inserted individually as the index is built, which is considerably slower than feeding sorted input. Because every insertion triggers an individual lookup within the growing index, the whole procedure is slow and, just as importantly, produces a suboptimally organized index afterward. Sorted input, by contrast, makes it far easier to build the index and to keep an optimal internal structure. Here is the catch. Up to and including PostgreSQL 17, btree_gist operator classes did not provide sortsupport. As a result, every btree_gist index build was forced to use the slower buffered method. For a small table this is barely noticeable, but for tables with millions of rows the difference is enormous — both in build time and in resulting index quality.

The patch behind the btree_gist improvements in PostgreSQL 18

With the PostgreSQL 18 release, btree_gist was extended to use sortsupport by default when building these indexes. That single change flips the default build strategy from the slow buffered path to the fast sorted path. The patch has a longer history than the release notes suggest. The work started with Andrey Borodin back in 2020, but that initial effort was reverted because of unresolved problems handling specific data types. The idea was later revived by Christoph Heiss and Bernd Helmle, and after extensive review the remaining issues were finally solved — landing the feature in PostgreSQL 18. The practical upshot is simple: if your schema uses btree_gist for composite indexes or exclusion constraints, upgrading to PostgreSQL 18 gives you faster index builds and better index quality with zero changes to your DDL.

Setting up the benchmark schema

To measure the btree_gist improvements in PostgreSQL 18, we start from a simple, reproducible schema. The benchmarks in this article are based on a real dataset provided by a customer who was struggling with int4range range values and the time required to rebuild indexes while importing data. The simplified schema used throughout is shown below.

CREATE EXTENSION btree_gist;

CREATE TABLE token (
    id    uuid,
    range int4range NOT NULL
);

CREATE INDEX ON token USING gist(id, range);

This same table layout is reused for every benchmark below. A quick look at four sample rows shows the shape of the data: a UUID paired with an integer range.

SELECT * FROM token LIMIT 4;

                  id                  |        range
--------------------------------------+---------------------
 e44e4063-db4c-e8f7-afa3-fd4308e30085 | [10773122,10773138)
 7a32d965-983d-9e2f-1baa-b07595c562b0 | [10741736,10741751)
 e2dd8b78-a655-464b-3bcb-b1e3921ce645 | [10773122,10773153)
 7304d2d3-6e39-fa01-2ef0-cdb8818202fb | [10741750,10741751)

Benchmark configuration

The benchmarks that follow quantify the btree_gist improvements in PostgreSQL 18 across realistic dataset sizes. The build benchmarks were run on a lightly tuned instance of PostgreSQL 17.5 and PostgreSQL 18beta2. Only a handful of settings were adjusted from the defaults, and they are shown below so you can reproduce a comparable environment.

shared_buffers = 4GB
maintenance_work_mem = 1GB
max_wal_size = 6GB
# effective_cache_size is already 4GB by default and was left untouched

Keeping the two instances identical apart from the major version is what makes the comparison fair. The only meaningful difference between the runs is the presence of sortsupport in the PostgreSQL 18 btree_gist operator classes.

Benchmark 1: CREATE INDEX build time

Build time is where the btree_gist improvements in PostgreSQL 18 are most visible. The first benchmark measures how long it takes to build a btree_gist index on the token table for 1 million, 5 million, and 10 million rows. This is the headline number that captures the btree_gist improvements in PostgreSQL 18. Across all three dataset sizes, the CREATE INDEX timings on PostgreSQL 18beta2 with sortsupport are significantly lower than on PostgreSQL 17.5. In other words, the sorted build path is doing exactly what it promised: turning a slow, one-insertion-at-a-time build into a fast, bulk-friendly operation. For workloads that routinely rebuild large btree_gist indexes during data imports, this alone can reshape maintenance windows.

Benchmark 2: building an exclusion constraint

Exclusion constraints inherit the btree_gist improvements in PostgreSQL 18 directly. The second benchmark illustrates the improvement you get when btree_gist works behind the scenes to create an exclusion constraint. Here we add a constraint to the test table that guarantees a given UUID does not have any overlapping integer ranges. This is expressed with the && overlaps operator, which int4range supports.

ALTER TABLE token
    ADD CONSTRAINT excl_id_range
    EXCLUDE USING gist(id WITH =, range WITH &&);

Adding this constraint forces PostgreSQL to build a GiST index under the hood, so it inherits the same build-time behavior we measured above. Benchmarking the statement for 1 million, 5 million, and 10 million tuples shows the same pattern: PostgreSQL 18 with sortsupport is dramatically faster than PostgreSQL 17. Workloads that import and index large volumes of data — and that need btree_gist specifically for the features described earlier — are the biggest beneficiaries.

Benchmark 3: query throughput with pgbench

Read performance is another dimension of the btree_gist improvements in PostgreSQL 18 worth measuring. Faster index builds are welcome, but the more interesting question is whether the resulting indexes are actually better for query time. The patch primarily targets build performance, yet a physically better-organized index should also improve read performance. To test that hypothesis, a query benchmark was built using pgbench and its scripting feature, based on the 10 million row dataset. The initialization script below creates a fresh test_dataset table, populates it with 10,000 random keys drawn from the 10 million row token_10m table, and adds a unique index on the surrogate key.

-- init.sql

BEGIN;

DROP TABLE IF EXISTS test_dataset;

CREATE TABLE test_dataset(
    keyid integer not null,
    id    uuid not null,
    range int4range
);

CREATE TEMP SEQUENCE testset_seq;

INSERT INTO test_dataset
SELECT nextval('testset_seq'), id, range
FROM token_10m ORDER BY random() LIMIT 10000;

CREATE UNIQUE INDEX ON test_dataset(keyid);

COMMIT;

The pgbench script picks a random key, reads the matching token, and then queries the 10 million row table for tokens whose range overlaps the selected one using the && operator. This is the exact access pattern the btree_gist index is meant to accelerate.

\set keyid random(1, 10000)
SELECT id, range FROM test_dataset WHERE keyid = :keyid \gset
SELECT id, range FROM token_10m WHERE id = ':id' AND range && ':range';

The benchmark is executed three times, and the average transactions per second across the runs is reported. Each run lasts 60 seconds, uses exactly one connection, and records latency as well. The driver loop below runs the initialization script and then the pgbench workload on each iteration.

for i in $(seq 1 3);
do
    psql -qXf init.sql && pgbench -n -r -c 1 -T 60 -f pgbench.tokens;
done

The result is striking. Selecting tokens that overlap with the && operator shows a clear throughput improvement on PostgreSQL 18beta2. The better index quality translates into roughly three times higher transaction throughput per second than the same workload on PostgreSQL 17. That is a substantial read-side win that comes essentially for free with the upgrade.

Digging deeper: why lookups are faster

Understanding page access explains why the btree_gist improvements in PostgreSQL 18 help read-heavy workloads. To explain why lookup queries run faster, a dedicated benchmark tool was created to trace the difference. It performs the same random-access workload as the pgbench test, but wraps every query in EXPLAIN to capture runtime metrics such as the number of pages each query touches. The tool selects a configurable number of tokens (10,000 by default), then reads each selected token back from the table inside a loop that repeats ten times by default — roughly 100,000 queries in total. For each query, the execution plan is analyzed and execution time, shared page hits, shared page reads, and I/O time are collected. When the run finishes, the metrics are written to a CSV file and a summary is printed to the console. Because the queries are repeated many times, the index becomes fully cached, which isolates the effect of index structure from disk I/O. The key finding concerns average shared buffer page hits. Running the same table and btree_gist index on both versions, PostgreSQL 17 reads about ten more pages on average per lookup than PostgreSQL 18beta2. The benchmark was repeated several times and the figures stayed stable. Fewer pages touched per query means less work per lookup, which directly explains the higher throughput. This confirms the expectation that sortsupport yields a better-organized index that also improves read performance.

How to adopt the btree_gist improvements in PostgreSQL 18

The best part of this feature is that it requires no application changes. The sortsupport path is used by default in PostgreSQL 18, so existing DDL that creates btree_gist indexes or exclusion constraints benefits automatically. A few practical recommendations will help you get the most out of the upgrade. First, plan to rebuild affected indexes after upgrading. A fresh CREATE INDEX or REINDEX on PostgreSQL 18 is what lets an existing index inherit the improved sorted-build structure and its query-time benefits. Second, revisit your maintenance windows for large imports; the faster build path can materially shorten the time spent rebuilding btree_gist indexes during bulk loads. Third, keep maintenance_work_mem generous during index builds, since sorted builds still benefit from adequate working memory. A minimal rebuild after an upgrade looks like the statement below, which recreates the composite btree_gist index so it is stored using the new sorted layout.

REINDEX INDEX CONCURRENTLY token_id_range_idx;

Frequently asked questions

These answers cover the questions teams ask most about the btree_gist improvements in PostgreSQL 18.

Do I need to change my SQL to benefit?

A common question about the btree_gist improvements in PostgreSQL 18 is whether any code changes are needed. No. The btree_gist improvements in PostgreSQL 18 are enabled by default through sortsupport. Your existing CREATE INDEX and exclusion constraint definitions work unchanged; you simply get faster builds and better-organized indexes.

Will existing indexes automatically use the new layout?

Indexes built on earlier versions keep their old structure until you rebuild them. Run REINDEX (ideally CONCURRENTLY to avoid blocking) after upgrading so that each btree_gist index is regenerated with the sorted-build layout.

Which workloads benefit most?

The btree_gist improvements in PostgreSQL 18 reward data-intensive workloads the most. Workloads that import large volumes of data and rely on btree_gist for exclusion constraints, composite GiST indexes, or nearest-neighbour search see the largest gains — both in build time and in query throughput.

Conclusion

The new sortsupport in the btree_gist extension delivers much better performance than any previous PostgreSQL major version. Even though it is "just" an extension, btree_gist underpins important capabilities: composite indexes over data types that GiST does not natively support, and exclusion constraints that enforce rich business rules. The btree_gist improvements in PostgreSQL 18 make those capabilities faster to build and faster to query. If your systems depend on these features, PostgreSQL 18 is worth testing as soon as possible. Validate the build-time and query-time gains against your own data, rebuild your btree_gist indexes to pick up the sorted layout, and enjoy the free performance headroom that comes with the upgrade.

Key takeaways on the btree_gist improvements in PostgreSQL 18

To summarize the btree_gist improvements in PostgreSQL 18: the extension now provides sortsupport, so GiST index builds switch from the slow buffered path to the fast sorted path by default. Build times for large btree_gist indexes drop sharply, exclusion constraints are created far faster, and query throughput improves thanks to better index quality. In short, the btree_gist improvements in PostgreSQL 18 benefit both write-heavy index maintenance and read-heavy lookups. Teams running large PostgreSQL fleets should test these btree_gist improvements in PostgreSQL 18 against their own datasets before rolling out the upgrade broadly.

Further reading and references

For the authoritative details on the extension and the operators discussed here, see the official PostgreSQL btree_gist documentation and the GiST index access method documentation maintained by the PostgreSQL Global Development Group. To go deeper on tuning GiST and other index types, explore more PostgreSQL performance tuning resources on MinervaDB and our related notes on index maintenance and exclusion constraints.