pg_bigm

Overview

Version

Build

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

pig build pkg pg_bigm         # build RPM / DEB packages

Install

You can install pg_bigm 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 pg_bigm;          # Install for current active PG version

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

dnf install -y pg_bigm_18       # PG 18
dnf install -y pg_bigm_17       # PG 17
dnf install -y pg_bigm_16       # PG 16
dnf install -y pg_bigm_15       # PG 15
dnf install -y pg_bigm_14       # PG 14

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

Preload:

shared_preload_libraries = 'pg_bigm';

Create Extension:

CREATE EXTENSION pg_bigm;

Usage

pg_bigm Documentation | GitHub: pgbigm/pg_bigm

The pg_bigm module provides full text search capability in PostgreSQL. This module allows a user to create 2-gram (bigram) index for faster full text search.

pg_bigm is released under the PostgreSQL License, a liberal Open Source license, similar to the BSD or MIT licenses.

Features

• Bigram indexing: Creates 2-gram (bigram) GIN indexes for text columns
• Faster LIKE searches: Accelerates LIKE queries including prefix, suffix, and substring searches
• All language support: Works with any language including CJK (Chinese, Japanese, Korean) without additional configuration
• Simple API: Provides similarity search functions and operators

Functions and Operators

Functions

Operators

GUC Parameters

Examples

Basic Full Text Search

-- Create extension
CREATE EXTENSION pg_bigm;

-- Create a table with text data
CREATE TABLE documents (id serial PRIMARY KEY, content text);
INSERT INTO documents (content) VALUES
  ('PostgreSQL is a powerful database'),
  ('Full text search with bigram indexing'),
  ('Japanese text: 日本語テキスト検索');

-- Create a bigram index
CREATE INDEX docs_bigm_idx ON documents USING gin (content gin_bigm_ops);

-- Search using LIKE
SELECT * FROM documents WHERE content LIKE '%search%';

-- Search using likequery function
SELECT * FROM documents WHERE content LIKE likequery('database');

Similarity Search

-- Show bigrams for a string
SELECT show_bigm('PostgreSQL');

-- Similarity search
SET pg_bigm.similarity_limit = 0.2;
SELECT * FROM documents WHERE content =% 'database search';

Comparison with pg_trgm

pg_bigm has the following advantages over the built-in pg_trgm:

For detailed documentation including advanced usage and performance tuning, see the official pg_bigm documentation.