PostgreSQL Extensions: pg_trgm, pgcrypto, pg_stat_statements and Others

← Back to the section

PostgreSQL is one of the few databases you can extend from the inside. Instead of baking everything into the core, the developers moved a lot of useful functionality into extensions: separate modules that install with a single command and add new functions, data types, and index algorithms.

What an extension is and how to enable it

An extension is a package of SQL objects (functions, types, operators, index methods) that are added to a specific database with a single command:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

The IF NOT EXISTS keyword protects against an error if the extension is already installed. The operation itself is safe and fast.

To see what's already installed:

live example

SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL;

Most standard extensions ship with PostgreSQL: the files are already on the server, and all that's left is to enable them in the right database. The words "in the database" matter here — the command applies to one database, not to the whole cluster.

Three extensions worth enabling on any project

There are three extensions that cost almost nothing in resources but are needed regularly:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

It's best to enable them right away.

pg_stat_statements — see which queries are slow

Without this extension, finding a slow query on a live server is extremely hard: PostgreSQL doesn't keep query history by default. pg_stat_statements fixes this: it accumulates statistics for each unique query — how many times it ran, how much total time it took, how much data it read.

One caveat: the extension requires a single additional setting in postgresql.conf and a cluster restart:

shared_preload_libraries = 'pg_stat_statements'

After the restart, enable the extension and you can start looking at the statistics:

CREATE EXTENSION pg_stat_statements;

-- top 10 queries by total execution time
SELECT query, calls, total_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

This is the first tool to reach for when investigating performance problems.

pgcrypto — UUIDs, password hashes, cryptographic functions

The pgcrypto extension adds cryptographic functions directly to SQL.

Generating UUIDs. Starting with PostgreSQL 13, the gen_random_uuid() function is available without any extension, but for compatibility with older versions it's convenient to have pgcrypto:

-- on PostgreSQL 13 and newer this works without the extension
SELECT gen_random_uuid();
-- e.g. a9f1a2b3-1c2d-4e5f-8a9b-0c1d2e3f4a5b

Hashing passwords. pgcrypto supports bcrypt — an algorithm specifically designed for storing passwords (deliberately slow, to make brute-forcing harder):

-- store a password
INSERT INTO account (email, password_hash)
VALUES ('user@example.com', crypt('plaintext', gen_salt('bf', 10)));

-- verify the password on login
SELECT id FROM account
WHERE email = 'user@example.com'
  AND password_hash = crypt('plaintext', password_hash);

In practice, hashing is more often done on the application side so the plaintext password never reaches the database.

Data hashes. You can compute SHA-256 and other hashes right in a query — the result comes back as bytea:

-- the hash is computed inside the database
SELECT digest('some text', 'sha256');

An important caveat: encrypting columns directly in the database via pgp_sym_encrypt is not a great idea. The database then knows the keys, which creates an extra vector for leaks. For sensitive data, encrypt on the application side or use disk encryption.

pg_trgm — substring search and fuzzy search

A search for a piece of a string — LIKE '%tit%' — cannot lean on a B-tree index: that index is ordered by the beginning of the string, while the fragment you look for may sit anywhere. The database is forced to scan every row, and on large tables this is very slow. The query itself looks innocent:

live example

SELECT id, first_name, last_name
FROM customer
WHERE last_name ILIKE '%tit%';

pg_trgm solves the problem with trigrams: the text is split into three-character fragments, and a GIN index is built on them. The query doesn't need rewriting — only its plan changes:

CREATE EXTENSION pg_trgm;

CREATE INDEX ix_customer_last_name_trgm
    ON customer USING gin (last_name gin_trgm_ops);

All the arithmetic behind the extension fits into a few lines: PostgreSQL pads a word with two spaces at the start and one at the end, cuts it into triples of characters, and measures closeness as the share of shared triples among all distinct ones.

live example

import java.util.LinkedHashSet;
import java.util.Locale;
import java.util.Set;

public class Trigram {

    static Set<String> split(String text) {
        String padded = "  " + text.toLowerCase(Locale.ROOT) + " ";
        Set<String> parts = new LinkedHashSet<>();
        for (int i = 0; i + 3 <= padded.length(); i++) {
            parts.add(padded.substring(i, i + 3));
        }
        return parts;
    }

    static double similarity(String a, String b) {
        Set<String> shared = new LinkedHashSet<>(split(a));
        shared.retainAll(split(b));
        Set<String> all = new LinkedHashSet<>(split(a));
        all.addAll(split(b));
        return (double) shared.size() / all.size();
    }

    public static void main(String[] args) {
        System.out.println("ivanov -> " + split("ivanov"));
        System.out.printf(Locale.ROOT, "ivanov ~ ivannov = %.2f%n", similarity("ivanov", "ivannov"));
        System.out.printf(Locale.ROOT, "ivanov ~ petrov  = %.2f%n", similarity("ivanov", "petrov"));
    }
}

It prints the set [ i, iv, iva, van, ano, nov, ov ] and two scores: 0.67 for the typo and 0.08 for a different surname. That is exactly what the similarity function does — it returns a number between 0 and 1, where 1 means a full match. The index stores the same triples as GIN keys.

-- find similar surnames, even with a typo
SELECT last_name, similarity(last_name, 'ivannov') AS score
FROM customer
WHERE similarity(last_name, 'ivannov') > 0.4
ORDER BY score DESC
LIMIT 10;

This is useful for autocomplete forms and fuzzy lookups against reference tables. There is a shorter form too — the % operator, whose threshold comes from the pg_trgm.similarity_threshold setting (0.3 by default).

btree_gist — non-overlap constraint

PostgreSQL has an EXCLUDE mechanism: an integrity constraint that forbids rows from "overlapping" on a given condition. The classic example is bookings: one room cannot be booked twice for an overlapping period.

By default, EXCLUDE works only with geometric and range types via a GiST index. btree_gist adds support for ordinary scalar types (integers, text) to the same index, which lets you combine them with ranges:

CREATE EXTENSION btree_gist;

CREATE TABLE booking (
    id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id bigint NOT NULL,
    period  tstzrange NOT NULL,
    EXCLUDE USING gist (room_id WITH =, period WITH &&)
);

This database-level constraint guarantees that for the same room_id there won't be two records with an overlapping period.

citext — case-insensitive text

A typical problem with email addresses: a user signed up as User@Example.com but logs in as user@example.com. For the comparison to work correctly, you usually have to write LOWER(email) = LOWER($1) everywhere or force the address into lowercase before storing.

citext (case-insensitive text) is a data type that makes comparisons case-insensitive automatically:

CREATE EXTENSION citext;

CREATE TABLE account (
    id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email  citext NOT NULL UNIQUE
);

-- both queries return the same row
SELECT * FROM account WHERE email = 'user@example.com';
SELECT * FROM account WHERE email = 'USER@EXAMPLE.COM';

The unique index is case-insensitive too: User@Example.com and user@example.com are treated as one value.

pgstattuple — how much space is wasted

Over time, "dead" rows accumulate in PostgreSQL tables — deleted or updated records that VACUUM hasn't cleaned up yet. This takes up disk space and slows down queries.

pgstattuple lets you precisely measure how "bloated" a table or index is:

CREATE EXTENSION pgstattuple;

-- table statistics: how many live rows, how many dead, how much free space
SELECT * FROM pgstattuple('orders');

-- index statistics: leaf page density
SELECT * FROM pgstatindex('ix_orders_customer');

-- fast approximate estimate (doesn't read the whole table)
SELECT * FROM pgstattuple_approx('orders');

When dead_tuple_percent is high or an index's avg_leaf_density is low — it's time to run VACUUM or consider a rebuild.

unaccent — search that ignores diacritics

Diacritical marks (accent marks) are umlauts, accents, and similar symbols in European languages: é, ü, ñ. unaccent strips them, reducing the text to basic ASCII:

CREATE EXTENSION unaccent;

SELECT unaccent('Naïve café résumé');
-- result: Naive cafe resume

This is useful when you need a search that finds cafe for the query café and vice versa. The extension is usually used together with full-text search:

live example

SELECT id, title FROM products
WHERE search @@ plainto_tsquery('russian', unaccent('наушники'));

pg_partman — automating partitions

Partitioning splits a large table into physical pieces — by month, for example. PostgreSQL does this natively, but creating new partitions by hand every month is inconvenient.

pg_partman automates the process: it creates future partitions ahead of time and, if needed, drops old ones. Version 5 keeps native partitioning only, and create_parent takes three required arguments — table, column and interval:

CREATE SCHEMA partman;
CREATE EXTENSION pg_partman SCHEMA partman;

SELECT partman.create_parent(
    p_parent_table => 'public.event_log',
    p_control      => 'occurred_at',
    p_interval     => '1 month',
    p_premake      => 4   -- create 4 partitions ahead
);

-- run on a schedule (for example, via pg_cron)
SELECT partman.run_maintenance('public.event_log');

Without pg_partman, the same thing requires manual DDL or writing maintenance scripts.

pg_repack — reorganize a table without stopping the database

VACUUM FULL removes table bloat by rebuilding the table completely — but it holds an exclusive lock while doing so. For a large table this means several minutes when nobody can read or write.

pg_repack does the same thing but without the long lock: it builds a new copy of the table in the background and quickly switches over to it at the end:

pg_repack -d mydb -t order_doc        # reorganize the table
pg_repack -d mydb -i ix_order_status  # rebuild the index

This is an external utility: it is installed on the server separately.

Common mistakes

Enabling everything "just in case". Each extension registers objects in the database schema and slightly increases the complexity of the environment. Only enable what you actually need.

Using hstore in new code. hstore predates JSONB and stores flat string-to-string pairs only: no nesting, no numbers, no arrays. The extension is alive and maintained, but for new code use jsonb — it can do more.

Using uuid_generate_v4() from uuid-ossp. This extension was the standard before PostgreSQL 13. Nowadays, for UUID v4, use gen_random_uuid() from pgcrypto (or the built-in function in PG 13+). For UUID v7 (with time-based ordering) you used to generate the value on the application side; PostgreSQL 18 added the built-in uuidv7() function.

Adding pg_stat_statements without shared_preload_libraries. Without this setting the extension will be created but won't work — it silently collects nothing until you restart with the correct config.

In short

• Extensions are enabled with CREATE EXTENSION IF NOT EXISTS <name> — and apply to one database, not the whole cluster: in a neighbouring database on the same server the command has to be repeated.
• On any project it's worth enabling pg_stat_statements (see slow queries), pgcrypto (UUIDs, bcrypt), and pg_trgm (indexed substring search).
• pg_stat_statements requires shared_preload_libraries in the config and a cluster restart — do it once when starting the project.
• pg_trgm cuts text into triples of characters and stores them in GIN; similarity is the share of shared triples, and the % operator compares it against the 0.3 threshold.
• btree_gist is needed for EXCLUDE constraints with ranges (bookings), citext removes the constant LOWER() for emails and logins.
• pg_partman creates partitions on a schedule, pg_repack reorganizes bloated tables without locking — unlike VACUUM FULL.

What to read next

• Index types in PostgreSQL — how pg_trgm and btree_gist relate to GIN and GiST.
• Partitioning — how partitions work and when you need them.
• VACUUM and table bloat — pgstattuple and pg_repack in the context of maintenance.
• Monitoring slow queries — pg_stat_statements in more detail.