vgram

Overview

Collect corpus-specific q-gram statistics before creating the GIN index; upstream supports PostgreSQL 13+.

Version

Build

You can build the RPM / DEB packages for vgram using pig build:

pig build pkg vgram         # build RPM / DEB packages

Install

You can install vgram directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install vgram;          # Install for current active PG version

pig ext install -y vgram -v 18  # PG 18
pig ext install -y vgram -v 17  # PG 17
pig ext install -y vgram -v 16  # PG 16
pig ext install -y vgram -v 15  # PG 15
pig ext install -y vgram -v 14  # PG 14

dnf install -y vgram_18       # PG 18
dnf install -y vgram_17       # PG 17
dnf install -y vgram_16       # PG 16
dnf install -y vgram_15       # PG 15
dnf install -y vgram_14       # PG 14

apt install -y postgresql-18-vgram   # PG 18
apt install -y postgresql-17-vgram   # PG 17
apt install -y postgresql-16-vgram   # PG 16
apt install -y postgresql-15-vgram   # PG 15
apt install -y postgresql-14-vgram   # PG 14

Create Extension:

CREATE EXTENSION vgram;

Usage

Sources:

• Official README
• Official extension SQL
• Official GIN implementation

vgram accelerates LIKE and ILIKE substring searches with GIN indexes built from variable-length q-grams. Unlike fixed trigrams, it learns frequent short grams from the target corpus and indexes longer or rarer grams, which can improve selectivity for a particular data distribution.

Build a Corpus-Specific Index

Version 1.0 requires PostgreSQL 13 or later. In psql, collect frequent grams, save the result, and pass it to the vgram_gin_ops options:

CREATE EXTENSION vgram;

SELECT qgram_stat(title, 2, 4, 0.05) AS vgrams
FROM documents
\gset

CREATE INDEX documents_title_vgram_idx
ON documents USING gin (
  title vgram_gin_ops (minq=2, maxq=4, vgrams=:'vgrams')
);

SELECT *
FROM documents
WHERE title ILIKE '%indexing%';

minq and maxq bound extracted gram lengths. The threshold passed to qgram_stat() marks grams at or above that corpus frequency as too common to index. Use get_vgrams() to inspect extraction for a candidate value and learned gram array.

Objects and Advanced Statistics

• qgram_stat(text, int, int, float8) aggregates frequent q-grams.
• get_vgrams(text, int, int, text[]) shows the grams selected for text.
• vgram_gin_ops indexes ordinary text for LIKE and ILIKE.
• vgram_text is text-compatible but installs custom frequency statistics and selectivity estimation.
• vgram_gin_ops2 is the GIN operator class for vgram_text.

Maintenance Boundaries

The learned option array is embedded in the index definition and can be very large. It reflects the sample at build time; major language or distribution changes require new statistics, comparison of recall/plans, and usually REINDEX or a replacement index. GIN candidates are rechecked, but overly common or too-short patterns may still fall back to broad scans. Benchmark build time, index size, write amplification, pending-list behavior, VACUUM, collation, multibyte text, case folding, and planner estimates. Keep the training query and parameters reproducible so replicas and rebuilt environments use the same operator-class options.