PostgreSQL 18 Cut My GIN Index Build from Months to Hours

Importing 91 million audio fingerprints was one problem. Building the GIN index afterwards is where PostgreSQL 17 nearly lost four months.

on , tagged , , . 9 min read

I have a self-hosted audio fingerprint database for Audiqa, a research project I am doing around large-scale audio matching. The job is to take huge numbers of audio files, turn them into fingerprints you can compare quickly, and then ask useful questions like “have I seen this recording before?” or “which tracks are likely the same audio despite different files or encodings?”

Those fingerprints come from Chromaprint, the acoustic fingerprinting algorithm behind AcoustID. You feed it audio, it extracts the perceptual features that matter, and it gives you back a long integer array that represents how that audio sounds.

I am storing 91 million of those fingerprints in PostgreSQL, then searching them via a GIN index1 on extracted integer arrays using the pg_acoustid extension. The table is 12 GB of heap data2 plus 325 GB of TOAST storage3 because the fingerprint arrays are large.

I imported the full dataset first and only built the GIN index afterwards. That order was deliberate. Bulk-loading 91 million rows and building the index once at the end is still the sane way to do this. Keeping a huge GIN index up to date during ingest would have been even worse.

That GIN index build was crawling. After 15 hours on PostgreSQL 17, pg_stat_progress_create_index showed it had processed 24.8% of the table’s blocks. Back-of-napkin math put the total build time at roughly 2.5 days on a good stretch, but during IO-contended periods the rate dropped so low that the extrapolation ballooned to 118 days. Four months. To build an index. The problem was not just the data size.

PostgreSQL 17 builds GIN indexes with a single process. maintenance_work_mem (the memory budget PostgreSQL uses for operations like CREATE INDEX) was set to 1 GB, so the sorts kept spilling to disk. The storage underneath was 4 plain old hard drives arranged as 2 ZFS mirror vdevs4.

What 2.8 TB of writes looks like

Checking /proc/<pid>/io on the index build process showed 2.8 TB written to disk. To build an index on a 12 GB table. That is not a typo.

That is not a bug, either. It is just how GIN indexes work. They extract many keys per row. Each fingerprint produces up to 120 integer keys after masking and deduplication. With 91 million rows, that means billions of index entries to sort. At 1 GB of maintenance_work_mem, PostgreSQL could only hold a fraction of that in memory, so it kept writing intermediate sort runs to disk, merging them back5, and writing them again. Each pass multiplied the write volume.

Here is what a row actually looks like. Each fingerprint is stored as an array of around 948 signed 32-bit integers, the raw Chromaprint output:

 fingerprint_id | num_hashes |                     first_5_hashes
----------------+------------+--------------------------------------------------------
       11777351 |        948 | {705564745,705038377,709232937,714214185,714279481}
       11777352 |        948 | {1647562544,1645474080,1665196325,1673580662,558154862}
       11777353 |        948 | {-2081620690,-2123498194,-2089943745,-2081948385,-2031618787}

To search these by similarity, acoustid_extract_query() masks and deduplicates each array down to about 120 integer keys. Those keys are what the GIN index stores, one posting list per unique key, pointing back to every fingerprint that contains it.

-- This is the index that was taking forever.
-- acoustid_extract_query() pulls about 120 integer keys from each fingerprint.
-- 91M rows times about 80 unique keys means billions of GIN entries to sort.
CREATE INDEX idx_fingerprint_hashes_query
ON fingerprint_hashes
USING gin (acoustid_extract_query(fingerprint) gin__int_ops);

PostgreSQL 18 changes the math

PostgreSQL 18, released in September 2025, added parallel CREATE INDEX for GIN indexes. This was a long-requested feature. B-tree indexes6 have had parallel builds since PostgreSQL 11, but GIN was left behind because the data structure is fundamentally different.

The parallel GIN build works by having multiple workers scan and sort portions of the table simultaneously, then merge the results. On large datasets, that can change the runtime substantially. Combined with more maintenance_work_mem, which means fewer sort spills, the improvement compounds.

That made upgrading more attractive than babysitting the old build and hoping memory tuning alone would save it.

The upgrade path

PostgreSQL’s pg_upgrade handles major version upgrades without reimporting data, so it was the obvious path. The tricky part here was the custom C extension, pg_acoustid, which needs to be compiled against both the old and new PostgreSQL headers.

The approach was to build a migration Docker image containing both PG 17 and PG 18 binaries, plus pg_acoustid compiled for each version. This is a one-time throwaway image.

# Build pg_acoustid against the old server version.
FROM postgres:17-alpine AS acoustid-17
RUN apk add --no-cache build-base git
RUN git clone -b v1.0.0 https://github.com/acoustid/pg_acoustid.git /tmp/pg_acoustid && \
    cd /tmp/pg_acoustid && make with_llvm=no && make with_llvm=no install

# Build the same extension against the new server version.
FROM postgres:18-alpine AS acoustid-18
RUN apk add --no-cache build-base git
RUN git clone -b v1.0.0 https://github.com/acoustid/pg_acoustid.git /tmp/pg_acoustid && \
    cd /tmp/pg_acoustid && make with_llvm=no && make with_llvm=no install

# Final image: PG 18 runtime plus PG 17 binaries for pg_upgrade.
FROM postgres:18-alpine
RUN apk add --no-cache postgresql17 postgresql17-contrib

# pg_upgrade insists that the extension is loadable for both clusters.
COPY --from=acoustid-17 /usr/local/lib/postgresql/acoustid.so /usr/lib/postgresql17/
COPY --from=acoustid-17 /usr/local/share/postgresql/extension/acoustid* /usr/share/postgresql17/extension/
COPY --from=acoustid-18 /usr/local/lib/postgresql/acoustid.so /usr/local/lib/postgresql/
COPY --from=acoustid-18 /usr/local/share/postgresql/extension/acoustid* /usr/local/share/postgresql/extension/

Three things that almost derailed it

The upgrade itself was straightforward. The environment around it was not.

Data checksums. PostgreSQL 18 defaults to enabling data checksums on new clusters on Debian and Ubuntu. My PG 17 cluster had them off. If the new cluster is initialized with checksums and the old one does not have them, pg_upgrade refuses. The fix was initdb --no-data-checksums on the new cluster.

Cross-device links. I ran pg_upgrade --link to use hardlinks, which is fast and avoids data copying, but the old and new data directories were on different ZFS datasets. Hardlinks cannot cross filesystem boundaries. ZFS supports reflinks, though, so pg_upgrade --clone worked. It creates copy-on-write clones of the data files, which is nearly instant and does not double the disk usage.

PG 18 Docker directory layout. The official PostgreSQL 18 Docker images changed the default data directory structure to use version-specific subdirectories. Mounting a PG 17 data directory at the old path makes the container refuse to start. The fix was setting PGDATA=/var/lib/postgresql/data explicitly in the compose environment to preserve the flat layout.

The actual upgrade

# 1. Cancel the in-progress index build.
psql -c "SELECT pg_cancel_backend(7811);"

# 2. Initialize a PG 18 cluster that matches the old one closely enough
# for pg_upgrade not to complain.
docker run ... audiqa-pg-upgrade:latest \
  initdb -D /var/lib/postgresql/data-new --no-data-checksums \
  --encoding=UTF8 --locale=en_US.utf8

# 3. Dry run. Always do this first.
docker run ... audiqa-pg-upgrade:latest \
  pg_upgrade \
    --old-datadir=/var/lib/postgresql/data-old \
    --new-datadir=/var/lib/postgresql/data-new \
    --old-bindir=/usr/libexec/postgresql17 \
    --new-bindir=/usr/local/bin \
    --check
# Output: "Clusters are compatible"

# 4. Real upgrade with --clone. On ZFS this uses reflinks and is nearly instant.
pg_upgrade --clone \
  --old-datadir=... --new-datadir=... \
  --old-bindir=/usr/libexec/postgresql17 \
  --new-bindir=/usr/local/bin
# Output: "Upgrade Complete"

The result

After swapping data directories, bumping maintenance_work_mem from 1 GB to 4 GB, and restarting PostgreSQL 18, I kicked off the same index build again:

-- Same CREATE INDEX statement. PG 18 uses parallel workers automatically
-- based on max_parallel_maintenance_workers, set here to 4.
ALTER SYSTEM SET maintenance_work_mem = '4GB';
ALTER SYSTEM SET max_parallel_maintenance_workers = 4;
SELECT pg_reload_conf();

CREATE INDEX idx_fingerprint_hashes_query
ON fingerprint_hashes
USING gin (acoustid_extract_query(fingerprint) gin__int_ops);

According to pg_stat_progress_create_index, the scan phase immediately ran at about 63 blocks per second, versus about 7.4 blocks per second on the PostgreSQL 17 run. The higher maintenance_work_mem also meant fewer sort runs and much less spilling to disk. PostgreSQL 18 parallelism fixed one part of the problem. Less ridiculous parameter tuning fixed the other.

The pg_upgrade itself took under a minute. Fifteen hours of index build cancelled, a major version upgrade, and the hard part was over in under sixty seconds. The actual data migration with --clone was effectively instant because ZFS reflinks only update metadata.

Total expected build time: 10-15 hours, down from 2.5 days in the optimistic case or 118 days during IO contention.

What I’d do differently

Look, the 1 GB maintenance_work_mem setting was the real bottleneck all along. I had 64 GB of RAM sitting there doing nothing and I gave PostgreSQL one gigabyte to sort billions of keys. It only affects maintenance operations like CREATE INDEX and VACUUM, not regular queries. The PG 17 build might have finished in a day or two with 4 GB of work memory, even without parallelism. That part is on me.

I would also treat storage more tactically next time. This database lives on TrueNAS at home on ZFS mirrors, which is exactly what you want for durability, but not what you want for a one-off, write-heavy index build. If I had planned it properly, I would have moved the database to a single SSD for the build, let PostgreSQL hammer that directly, then moved the finished result back onto the safer ZFS pool.

But the PG 18 upgrade was worth doing regardless. Parallel GIN builds are a permanent capability. The next time this index needs rebuilding, after a bulk import or once the table grows again, it will just be fast by default.

  • PostgreSQL 18's parallel GIN builds turned this from a single-process slog into a job that could actually use the machine
  • maintenance_work_mem mattered almost as much as the version upgrade because the real enemy was repeated sort spilling
  • pg_upgrade --clone on ZFS made the major upgrade cheap enough that cancelling the old build was the sensible move

  1. Generalized Inverted Index (GIN). Instead of one index entry per row, it stores one entry per extracted key, each pointing to all rows containing that key. Think of the index at the back of a textbook, except the textbook has 91 million pages. ↩︎

  2. Heap storage is the main table storage in PostgreSQL, the actual rows on disk, as opposed to indexes or TOAST data. It is where your data lives. Everything else is just trying to find it faster. ↩︎

  3. The Oversized-Attribute Storage Technique (TOAST). PostgreSQL’s mechanism for storing values too large to fit in a regular 8 KB page by moving them out-of-line into a separate TOAST table. Yes, they named a storage subsystem after breakfast. Database people do not get out much. ↩︎

  4. A ZFS mirror vdev is a redundancy configuration where two disks hold identical copies of the data. A vdev, or virtual device, is the basic building block of a ZFS pool. You lose half your storage capacity, but you get to sleep at night. ↩︎

  5. Merge sort is an external sorting algorithm that divides data into sorted chunks that fit in memory, writes them to disk, then repeatedly merges the chunks together until the entire dataset is sorted. Elegant in theory. 2.8 TB of writes in practice. ↩︎

  6. A B-tree index is PostgreSQL’s default index type. It is a balanced tree structure that works well for equality and range lookups on scalar values, but not for array containment or similarity searches. It is the sensible one. GIN is the unhinged cousin. ↩︎

If you’ve also watched CREATE INDEX try to ruin your weekend, come commiserate. I’m on Bluesky, Mastodon, Twitter X, and technically LinkedIn, though discussing GIN write amplification there feels optimistic. The rest of my self-hosted mistakes live on GitHub.