PostgreSQL Extensions: pg_trgm, pgcrypto, and More

Shane

2/14/2026

21 min read

A practical guide to essential PostgreSQL extensions including pg_trgm for fuzzy search, pgcrypto for encryption, pg_stat_statements for monitoring, and integration with Node.js.

postgresql database security search extensions pg_trgm pgcrypto

PostgreSQL Extensions: pg_trgm, pgcrypto, and More

Overview

PostgreSQL ships with a powerful extension system that lets you bolt on capabilities most databases charge enterprise licensing fees for — fuzzy text search, cryptographic hashing, geospatial queries, and query performance analysis, all without leaving your database. Extensions are one of the strongest reasons to choose PostgreSQL over MySQL or SQL Server for serious production workloads. This article covers the extensions I reach for most frequently, how to install and manage them, and how to wire them into a Node.js application with real working code.

Prerequisites

PostgreSQL 14 or later installed and running
Node.js 16+ with npm
Basic SQL knowledge (CREATE TABLE, SELECT, INSERT)
Familiarity with Express.js and node-postgres (pg module)
Superuser or CREATE privilege on your PostgreSQL database

What Are PostgreSQL Extensions?

An extension is a packaged bundle of SQL objects — functions, operators, data types, index methods — that extends PostgreSQL's core functionality. Unlike stored procedures you write yourself, extensions are compiled C code that runs inside the database engine at native speed. They go through PostgreSQL's review and packaging process, which means they integrate with the planner, the executor, and the index subsystem properly.

The extension system was introduced in PostgreSQL 9.1. Before that, people loaded raw SQL scripts and had no clean way to version or uninstall add-on functionality. The modern system tracks dependencies, supports versioning, and handles upgrades cleanly.

Every PostgreSQL installation ships with a set of "contrib" extensions in the contrib/ directory. These are maintained by the core team and are available on virtually every PostgreSQL deployment, including managed services like DigitalOcean, AWS RDS, and Supabase.

Installing and Managing Extensions

Checking Available Extensions

Before you can use an extension, check what is available on your server:

SELECT name, default_version, comment
FROM pg_available_extensions
ORDER BY name;

Output (abbreviated):

         name          | default_version |                       comment
-----------------------+-----------------+-----------------------------------------------------
 citext                | 1.6             | data type for case-insensitive character strings
 hstore                | 1.8             | data type for storing sets of (key, value) pairs
 pg_stat_statements    | 1.10            | track planning and execution statistics of all SQL
 pg_trgm               | 1.6             | text similarity measurement and index searching
 pgcrypto              | 1.3             | cryptographic functions
 postgis               | 3.4.0           | PostGIS geometry and geography spatial types
 tablefunc             | 1.0             | functions that manipulate whole tables
 uuid-ossp             | 1.1             | generate universally unique identifiers (UUIDs)
(8 rows)

Installing an Extension

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

The IF NOT EXISTS clause is important. Without it, running the command twice throws an error. Always use it in migration scripts.

Checking Installed Extensions

SELECT extname, extversion FROM pg_extension;

     extname      | extversion
------------------+------------
 plpgsql          | 1.0
 pg_trgm          | 1.6
 pgcrypto         | 1.3
(3 rows)

Upgrading and Removing Extensions

-- Upgrade to latest available version
ALTER EXTENSION pg_trgm UPDATE;

-- Remove an extension (CASCADE drops dependent objects)
DROP EXTENSION pg_trgm;
DROP EXTENSION pg_trgm CASCADE;

Be extremely careful with CASCADE. It will silently drop indexes, triggers, and functions that depend on the extension. In production, always run DROP EXTENSION without CASCADE first to see what would break.

pg_trgm: Fuzzy Text Search and Similarity

This is the extension I use most. pg_trgm breaks text into trigrams — sequences of three consecutive characters — and uses them to measure how similar two strings are. It is the fastest path to "did you mean?" functionality and typo-tolerant search in PostgreSQL.

How Trigrams Work

The string "Hello" produces these trigrams: " h", " he", "hel", "ell", "llo", "lo ". PostgreSQL compares the overlap between two sets of trigrams to produce a similarity score between 0 and 1.

SELECT show_trgm('Hello');
-- {"  h"," he",ell,hel,llo,"lo "}

SELECT similarity('PostgreSQL', 'Postgressql');
-- 0.6153846

SELECT similarity('PostgreSQL', 'MySQL');
-- 0.0

Creating Trigram Indexes

The real power comes from GIN and GiST indexes on trigram operators. Without an index, similarity searches do a full table scan. With one, PostgreSQL can use the index to narrow candidates before computing exact similarity.

-- GIN index: faster lookups, slower writes, larger on disk
CREATE INDEX idx_products_name_trgm ON products USING gin (name gin_trgm_ops);

-- GiST index: balanced read/write, supports distance operator
CREATE INDEX idx_products_name_gist ON products USING gist (name gist_trgm_ops);

I default to GIN indexes for read-heavy workloads. GiST is better when you need the <-> distance operator for ORDER BY queries.

Fuzzy Search Queries

-- Find products similar to a misspelled search term
SELECT name, similarity(name, 'Postgrss') AS score
FROM products
WHERE name % 'Postgrss'
ORDER BY score DESC
LIMIT 10;

-- Using the distance operator (requires GiST index)
SELECT name, name <-> 'Postgrss' AS distance
FROM products
ORDER BY name <-> 'Postgrss'
LIMIT 10;

-- Adjust the similarity threshold (default is 0.3)
SET pg_trgm.similarity_threshold = 0.2;

The % operator returns true when similarity exceeds the threshold. The <-> operator returns a distance value (1 minus similarity) and is what you want for ranked results.

pgcrypto: Hashing, Encryption, and UUIDs

pgcrypto provides cryptographic functions directly inside PostgreSQL. I use it for three things: password hashing with bcrypt, generating UUIDs, and occasionally encrypting sensitive columns.

Password Hashing with bcrypt

-- Hash a password
SELECT crypt('my_secret_password', gen_salt('bf', 10));
-- $2a$10$K7GzGq3hF8xJ5vN2mR1wAOYz5K8L3nP4qR7tU9wX1yZ2aB3cD4eF

-- Verify a password against a stored hash
SELECT (stored_hash = crypt('my_secret_password', stored_hash)) AS is_valid
FROM users
WHERE email = '[email protected]';

The gen_salt('bf', 10) creates a bcrypt salt with a work factor of 10. Each increment roughly doubles the computation time. I use 10 for web applications — it takes about 100ms, which is fast enough for login flows but slow enough to resist brute force.

Generating UUIDs

-- Generate a random UUID (v4)
SELECT gen_random_uuid();
-- a1b2c3d4-e5f6-4a7b-8c9d-0e1f2a3b4c5d

-- Use as a default column value
CREATE TABLE sessions (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    user_id INTEGER NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);

Symmetric Encryption

-- Encrypt data with AES
SELECT pgp_sym_encrypt('sensitive data', 'encryption_key');

-- Decrypt it
SELECT pgp_sym_decrypt(
    pgp_sym_encrypt('sensitive data', 'encryption_key'),
    'encryption_key'
);

A word of caution: column-level encryption adds complexity and kills query performance on encrypted columns. You cannot index encrypted values usefully. Use it only when regulations demand data-at-rest encryption beyond what disk encryption provides.

uuid-ossp vs pgcrypto for UUIDs

Both uuid-ossp and pgcrypto can generate UUIDs. Here is the practical difference:

Feature	uuid-ossp	pgcrypto
UUID v1 (time-based)	Yes	No
UUID v4 (random)	Yes	Yes
Function name	`uuid_generate_v4()`	`gen_random_uuid()`
Availability	Contrib extension	Contrib extension
PostgreSQL 13+ built-in	No	`gen_random_uuid()` is core

Starting with PostgreSQL 13, gen_random_uuid() is available without any extension. If you only need v4 UUIDs and you are on PostgreSQL 13 or later, you do not need either extension. For UUID v1 (time-based, includes MAC address), you still need uuid-ossp.

My recommendation: use gen_random_uuid() and skip both extensions unless you have a specific need for v1 UUIDs.

hstore: Key-Value Storage

hstore gives you a key-value data type. It predates PostgreSQL's JSON support and is still useful when you need lightweight, indexable key-value pairs without the overhead of JSONB.

CREATE EXTENSION IF NOT EXISTS hstore;

CREATE TABLE product_attributes (
    product_id INTEGER PRIMARY KEY,
    attrs hstore
);

INSERT INTO product_attributes VALUES (
    1,
    'color => red, size => large, material => cotton'
);

-- Query by key
SELECT attrs -> 'color' AS color FROM product_attributes WHERE product_id = 1;
-- red

-- Check if key exists
SELECT * FROM product_attributes WHERE attrs ? 'color';

-- GIN index for fast lookups
CREATE INDEX idx_attrs ON product_attributes USING gin (attrs);

In practice, I reach for JSONB over hstore in new projects. JSONB supports nested structures, has better tooling, and the performance difference is negligible for most workloads. But if you are dealing with flat key-value data and want the most compact storage, hstore still has an edge.

pg_stat_statements: Query Performance Analysis

This extension tracks execution statistics for every SQL statement your database runs. It is the single most valuable tool for identifying slow queries and performance regressions.

Setup

pg_stat_statements requires a server configuration change because it hooks into the query executor at a low level:

# Add to postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

Then restart PostgreSQL and create the extension:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Querying Statistics

-- Top 10 slowest queries by total time
SELECT
    substring(query, 1, 80) AS query_snippet,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

                  query_snippet                   | calls | total_ms  | mean_ms | rows
--------------------------------------------------+-------+-----------+---------+------
 SELECT * FROM products WHERE name % $1 ORDER BY  |  4521 | 128450.32 |   28.41 | 45210
 INSERT INTO orders (user_id, product_id, qty) VA |  1890 |  15230.11 |    8.06 |  1890
 SELECT u.*, p.* FROM users u JOIN purchases p ON |   320 |  12100.55 |   37.81 |  9600
(3 rows)

Resetting Statistics

SELECT pg_stat_statements_reset();

I reset statistics after every deployment so I can measure the impact of schema or query changes cleanly.

citext: Case-Insensitive Text

The citext type stores text that compares case-insensitively. It is far cleaner than littering your queries with LOWER() calls and works correctly with indexes.

CREATE EXTENSION IF NOT EXISTS citext;

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email CITEXT UNIQUE NOT NULL
);

INSERT INTO users (email) VALUES ('[email protected]');

-- This finds the row without LOWER()
SELECT * FROM users WHERE email = '[email protected]';

The UNIQUE constraint on a citext column prevents both [email protected] and [email protected] from being inserted. This is exactly what you want for email addresses and usernames.

tablefunc: Crosstab Queries

tablefunc provides the crosstab() function for pivot table queries. This is useful for reporting dashboards where you need to turn row values into columns.

CREATE EXTENSION IF NOT EXISTS tablefunc;

-- Monthly sales by category as a pivot table
SELECT * FROM crosstab(
    'SELECT category, extract(month from sale_date)::int, sum(amount)
     FROM sales
     WHERE sale_date >= ''2026-01-01''
     GROUP BY 1, 2
     ORDER BY 1, 2',
    'SELECT generate_series(1, 12)'
) AS ct(
    category TEXT,
    jan NUMERIC, feb NUMERIC, mar NUMERIC,
    apr NUMERIC, may NUMERIC, jun NUMERIC,
    jul NUMERIC, aug NUMERIC, sep NUMERIC,
    oct NUMERIC, nov NUMERIC, dec NUMERIC
);

The syntax is ugly and the function signature is rigid. For anything complex, I usually pivot in application code or use a reporting tool. But for quick admin dashboards, crosstab gets the job done without extra infrastructure.

PostGIS Basics for Geospatial Data

PostGIS is arguably the most powerful PostgreSQL extension. It adds geometry and geography types, spatial indexes, and hundreds of geospatial functions. Full coverage would fill a book, but here are the essentials.

CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    coords GEOGRAPHY(POINT, 4326)
);

-- Insert a point (longitude, latitude)
INSERT INTO locations (name, coords)
VALUES ('Grizzly Peak', ST_MakePoint(-122.2426, 37.8810));

-- Find locations within 5km of a point
SELECT name, ST_Distance(coords, ST_MakePoint(-122.2590, 37.8716)::geography) AS distance_m
FROM locations
WHERE ST_DWithin(coords, ST_MakePoint(-122.2590, 37.8716)::geography, 5000)
ORDER BY distance_m;

-- Spatial index for fast proximity queries
CREATE INDEX idx_locations_coords ON locations USING gist (coords);

The key distinction is GEOMETRY vs GEOGRAPHY. Geography types use a spherical earth model and return distances in meters. Geometry types use a flat coordinate system and are faster but less accurate over long distances. For anything involving real-world locations, use GEOGRAPHY.

Integrating Extensions with Node.js

All of these extensions work seamlessly with node-postgres because the results come back as standard SQL types. The pg driver handles UUID, numeric, and text types automatically.

var pg = require('pg');

var pool = new pg.Pool({
    connectionString: process.env.DATABASE_URL
});

// Fuzzy search with pg_trgm
function searchProducts(term, callback) {
    var query = {
        text: 'SELECT id, name, similarity(name, $1) AS score FROM products WHERE name % $1 ORDER BY score DESC LIMIT 20',
        values: [term]
    };
    pool.query(query, function(err, result) {
        if (err) return callback(err);
        callback(null, result.rows);
    });
}

// Hash a password with pgcrypto
function hashPassword(password, callback) {
    var query = {
        text: "SELECT crypt($1, gen_salt('bf', 10)) AS hash",
        values: [password]
    };
    pool.query(query, function(err, result) {
        if (err) return callback(err);
        callback(null, result.rows[0].hash);
    });
}

// Verify a password
function verifyPassword(email, password, callback) {
    var query = {
        text: "SELECT id, email FROM users WHERE email = $1 AND password_hash = crypt($2, password_hash)",
        values: [email, password]
    };
    pool.query(query, function(err, result) {
        if (err) return callback(err);
        callback(null, result.rows[0] || null);
    });
}

Performance Impact of Extensions

Extensions have real costs. Here is what I have measured in production:

pg_trgm GIN index: Adds 30-50% to index size compared to a standard btree index on the same column. Write performance drops by roughly 15% because the trigram index must be updated on every INSERT/UPDATE.
pg_stat_statements: Adds 1-3% overhead to query execution due to statistics tracking. The pg_stat_statements.max setting controls how many distinct queries are tracked (default 5000). Going much higher increases shared memory usage.
pgcrypto bcrypt: A bcrypt hash with work factor 10 takes ~100ms. This is intentional but means you should never hash passwords inside a transaction that holds locks.
PostGIS: Spatial indexes are large. A GiST index on a geography column with 10 million rows can exceed the table size. Plan storage accordingly.
hstore/citext: Negligible overhead. These are lightweight type extensions.

The general rule: install only the extensions you actually use. Each one adds shared memory overhead and shows up in pg_dump output.

Complete Working Example

Here is a full Node.js application that combines pg_trgm for fuzzy product search, pgcrypto for password hashing, and pg_stat_statements for query monitoring.

Database Schema

-- Enable extensions
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Users table with bcrypt password hashing
CREATE TABLE users (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    email CITEXT UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);

-- Products table with trigram index for fuzzy search
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    price NUMERIC(10, 2) NOT NULL,
    category TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);

-- Trigram index for fuzzy name search
CREATE INDEX idx_products_name_trgm ON products USING gin (name gin_trgm_ops);

-- Standard index for category filtering
CREATE INDEX idx_products_category ON products (category);

-- Seed data
INSERT INTO products (name, description, price, category) VALUES
    ('PostgreSQL Administration Cookbook', 'Recipes for database management', 49.99, 'books'),
    ('Redis In Action', 'Practical guide to Redis', 39.99, 'books'),
    ('Mechanical Keyboard Cherry MX Blue', 'Tactile clicky switches', 129.99, 'electronics'),
    ('USB-C Hub Adapter', 'Multi-port hub for laptops', 34.99, 'electronics'),
    ('Node.js Design Patterns', 'Advanced patterns for Node', 44.99, 'books'),
    ('Ergonomic Standing Desk', 'Adjustable height desk', 599.99, 'furniture'),
    ('PostgreSQL High Performance', 'Tuning and optimization', 54.99, 'books'),
    ('Noise Cancelling Headphones', 'Over-ear wireless headphones', 299.99, 'electronics'),
    ('Portable SSD 1TB', 'Fast external storage', 89.99, 'electronics'),
    ('MongoDB: The Definitive Guide', 'Complete MongoDB reference', 42.99, 'books');

Application Code

var express = require('express');
var pg = require('pg');
var bodyParser = require('body-parser');

var app = express();
app.use(bodyParser.json());

var pool = new pg.Pool({
    connectionString: process.env.DATABASE_URL || 'postgresql://localhost:5432/extension_demo'
});

// ============================================================
// Fuzzy Product Search (pg_trgm)
// ============================================================

app.get('/api/products/search', function(req, res) {
    var term = req.query.q;
    var minScore = parseFloat(req.query.min_score) || 0.15;

    if (!term) {
        return res.status(400).json({ error: 'Query parameter "q" is required' });
    }

    var query = {
        text: [
            'SELECT id, name, description, price, category,',
            '       similarity(name, $1) AS score',
            'FROM products',
            'WHERE similarity(name, $1) > $2',
            'ORDER BY score DESC',
            'LIMIT 20'
        ].join('\n'),
        values: [term, minScore]
    };

    pool.query(query, function(err, result) {
        if (err) {
            console.error('Search error:', err.message);
            return res.status(500).json({ error: 'Search failed' });
        }
        res.json({
            query: term,
            min_score: minScore,
            count: result.rows.length,
            results: result.rows
        });
    });
});

// ============================================================
// User Registration with bcrypt hashing (pgcrypto)
// ============================================================

app.post('/api/users/register', function(req, res) {
    var email = req.body.email;
    var password = req.body.password;

    if (!email || !password) {
        return res.status(400).json({ error: 'Email and password are required' });
    }

    if (password.length < 8) {
        return res.status(400).json({ error: 'Password must be at least 8 characters' });
    }

    var query = {
        text: [
            "INSERT INTO users (email, password_hash)",
            "VALUES ($1, crypt($2, gen_salt('bf', 10)))",
            "RETURNING id, email, created_at"
        ].join('\n'),
        values: [email, password]
    };

    pool.query(query, function(err, result) {
        if (err) {
            if (err.code === '23505') {
                return res.status(409).json({ error: 'Email already registered' });
            }
            console.error('Registration error:', err.message);
            return res.status(500).json({ error: 'Registration failed' });
        }
        res.status(201).json(result.rows[0]);
    });
});

// ============================================================
// User Login with password verification (pgcrypto)
// ============================================================

app.post('/api/users/login', function(req, res) {
    var email = req.body.email;
    var password = req.body.password;

    if (!email || !password) {
        return res.status(400).json({ error: 'Email and password are required' });
    }

    var query = {
        text: [
            'SELECT id, email, created_at',
            'FROM users',
            'WHERE email = $1 AND password_hash = crypt($2, password_hash)'
        ].join('\n'),
        values: [email, password]
    };

    pool.query(query, function(err, result) {
        if (err) {
            console.error('Login error:', err.message);
            return res.status(500).json({ error: 'Login failed' });
        }
        if (result.rows.length === 0) {
            return res.status(401).json({ error: 'Invalid email or password' });
        }
        res.json({ message: 'Login successful', user: result.rows[0] });
    });
});

// ============================================================
// Query Performance Monitor (pg_stat_statements)
// ============================================================

app.get('/api/admin/slow-queries', function(req, res) {
    var limit = parseInt(req.query.limit) || 10;

    var query = {
        text: [
            'SELECT',
            '    queryid,',
            '    substring(query, 1, 120) AS query_snippet,',
            '    calls,',
            '    round(total_exec_time::numeric, 2) AS total_ms,',
            '    round(mean_exec_time::numeric, 2) AS mean_ms,',
            '    round(min_exec_time::numeric, 2) AS min_ms,',
            '    round(max_exec_time::numeric, 2) AS max_ms,',
            '    rows',
            'FROM pg_stat_statements',
            'WHERE query NOT LIKE \'%pg_stat_statements%\'',
            'ORDER BY total_exec_time DESC',
            'LIMIT $1'
        ].join('\n'),
        values: [limit]
    };

    pool.query(query, function(err, result) {
        if (err) {
            console.error('Stats error:', err.message);
            return res.status(500).json({ error: 'Failed to fetch query stats' });
        }
        res.json({
            count: result.rows.length,
            queries: result.rows
        });
    });
});

app.post('/api/admin/reset-stats', function(req, res) {
    pool.query('SELECT pg_stat_statements_reset()', function(err) {
        if (err) {
            console.error('Reset error:', err.message);
            return res.status(500).json({ error: 'Failed to reset stats' });
        }
        res.json({ message: 'Query statistics reset' });
    });
});

// ============================================================
// Start server
// ============================================================

var port = process.env.PORT || 3000;
app.listen(port, function() {
    console.log('Extension demo running on port ' + port);
});

Testing the Endpoints

# Register a user
curl -X POST http://localhost:3000/api/users/register \
  -H "Content-Type: application/json" \
  -d '{"email": "[email protected]", "password": "securepass123"}'

# Response:
# {"id":"a1b2c3d4-e5f6-4a7b-8c9d-0e1f2a3b4c5d","email":"[email protected]","created_at":"2026-02-13T10:30:00.000Z"}

# Login
curl -X POST http://localhost:3000/api/users/login \
  -H "Content-Type: application/json" \
  -d '{"email": "[email protected]", "password": "securepass123"}'

# Response:
# {"message":"Login successful","user":{"id":"a1b2c3d4-...","email":"[email protected]",...}}

# Fuzzy search - note the typo "Postgre" still finds PostgreSQL books
curl "http://localhost:3000/api/products/search?q=Postgre"

# Response:
# {"query":"Postgre","min_score":0.15,"count":2,"results":[
#   {"id":1,"name":"PostgreSQL Administration Cookbook","score":0.52,...},
#   {"id":7,"name":"PostgreSQL High Performance","score":0.48,...}
# ]}

# Fuzzy search with a misspelling
curl "http://localhost:3000/api/products/search?q=Keybord"

# Response:
# {"query":"Keybord","min_score":0.15,"count":1,"results":[
#   {"id":3,"name":"Mechanical Keyboard Cherry MX Blue","score":0.24,...}
# ]}

# Check slow queries
curl "http://localhost:3000/api/admin/slow-queries?limit=5"

Common Issues and Troubleshooting

1. Extension Not Available

ERROR:  could not open extension control file "/usr/share/postgresql/14/extension/pg_trgm.control": No such file or directory

The contrib package is not installed. On Debian/Ubuntu:

sudo apt-get install postgresql-contrib-14

On Amazon RDS or DigitalOcean Managed Databases, most contrib extensions are pre-installed. If you get this error on a managed service, the extension may not be supported — check the provider's documentation.

2. Permission Denied for CREATE EXTENSION

ERROR:  permission denied to create extension "pg_stat_statements"
HINT:  Must be superuser to create this extension.

pg_stat_statements requires superuser because it modifies shared_preload_libraries. On managed databases, use the cloud provider's dashboard to enable it. On RDS, add it to a custom parameter group. On DigitalOcean, it is enabled by default.

For extensions like pg_trgm and pgcrypto, you can grant creation rights:

GRANT CREATE ON DATABASE mydb TO myuser;

Or, as superuser, create the extension in a specific schema and grant usage:

CREATE EXTENSION pg_trgm SCHEMA public;
GRANT USAGE ON SCHEMA public TO myuser;

3. pg_stat_statements Returns Zero Rows

SELECT count(*) FROM pg_stat_statements;
-- 0

This means shared_preload_libraries was not configured or PostgreSQL was not restarted after the configuration change. Verify:

SHOW shared_preload_libraries;
-- Should include: pg_stat_statements

If it shows empty or does not include pg_stat_statements, update postgresql.conf and restart the server. A SELECT pg_reload_conf() is not sufficient — this extension requires a full restart.

4. Trigram Similarity Returns 0 for Short Strings

SELECT similarity('go', 'golang');
-- 0

pg_trgm struggles with very short strings because there are too few trigrams to generate meaningful overlap. Strings shorter than 3 characters produce minimal or no trigrams. Workarounds:

Pad short inputs: concatenate with a space or prefix
Use LIKE or ILIKE as a fallback for short queries
Combine pg_trgm with full-text search (tsvector) for comprehensive search

// Application-level fallback for short search terms
function buildSearchQuery(term) {
    if (term.length < 3) {
        return {
            text: 'SELECT id, name, price FROM products WHERE name ILIKE $1 LIMIT 20',
            values: ['%' + term + '%']
        };
    }
    return {
        text: 'SELECT id, name, price, similarity(name, $1) AS score FROM products WHERE name % $1 ORDER BY score DESC LIMIT 20',
        values: [term]
    };
}

5. bcrypt Hash Comparison Fails Silently

-- This looks right but always returns false
SELECT password_hash = crypt('password', password_hash) FROM users WHERE email = '[email protected]';
-- f

Common causes: the password was hashed with a different library (e.g., bcryptjs in Node.js) that uses a slightly different hash format, or the password column was truncated. Ensure the column is TEXT or at minimum VARCHAR(72) — bcrypt output is 60 characters, but being generous avoids silent truncation bugs.

6. Extension Version Mismatch After Upgrade

WARNING:  extension "pg_trgm" has no update path from version "1.4" to version "1.6"

This happens when you upgrade PostgreSQL major versions without running ALTER EXTENSION UPDATE. Run the update after each major version upgrade:

ALTER EXTENSION pg_trgm UPDATE TO '1.6';

Check available update paths:

SELECT * FROM pg_extension_update_paths('pg_trgm') WHERE source = '1.4';

Best Practices

Install extensions in a dedicated schema when working with shared databases. CREATE EXTENSION pg_trgm SCHEMA extensions; keeps contrib objects separate from your application tables and avoids naming collisions.
Track extensions in your migration files. Every CREATE EXTENSION should be in a numbered migration, not run manually. This ensures consistent environments across development, staging, and production.
Set explicit similarity thresholds per use case. The default 0.3 is too high for product search (misses reasonable matches) and too low for duplicate detection (produces false positives). Tune per feature and store the threshold in configuration, not in SQL.
Never store encryption keys in the database. When using pgp_sym_encrypt, pass the key from your application via parameterized queries. If the key is stored in the same database as the encrypted data, you have gained nothing.
Monitor pg_stat_statements weekly. Set a calendar reminder. Query regressions creep in gradually. The queries that dominate total_exec_time after a week of traffic are the ones worth optimizing first.
Run bcrypt hashing outside of transactions. A bcrypt hash with work factor 10 holds a database connection for ~100ms. Inside a transaction with row locks, this blocks other operations. Hash the password, then start the transaction for the INSERT.
Use gen_random_uuid() instead of application-level UUID generation. It avoids a round-trip, reduces application code, and guarantees the UUID is generated with PostgreSQL's cryptographically secure random number generator.
Test extension availability in your application startup. Query pg_extension on boot and log warnings for missing extensions. This catches deployment issues before users hit errors.

function checkExtensions(callback) {
    var required = ['pg_trgm', 'pgcrypto'];
    var query = "SELECT extname FROM pg_extension WHERE extname = ANY($1)";
    pool.query(query, [required], function(err, result) {
        if (err) return callback(err);
        var installed = result.rows.map(function(r) { return r.extname; });
        var missing = required.filter(function(ext) {
            return installed.indexOf(ext) === -1;
        });
        if (missing.length > 0) {
            console.warn('Missing PostgreSQL extensions:', missing.join(', '));
        }
        callback(null, missing);
    });
}

Keep extension versions pinned in CI/CD. If your test database uses PostgreSQL 14 but production runs 16, extension behavior may differ. Use the same PostgreSQL version and extension versions everywhere.

PostgreSQL Extensions: pg_trgm, pgcrypto, and More

Overview

Prerequisites

What Are PostgreSQL Extensions?

Installing and Managing Extensions

Checking Available Extensions

Installing an Extension

Checking Installed Extensions

Upgrading and Removing Extensions

pg_trgm: Fuzzy Text Search and Similarity

How Trigrams Work

Creating Trigram Indexes

Fuzzy Search Queries

pgcrypto: Hashing, Encryption, and UUIDs

Password Hashing with bcrypt

Generating UUIDs

Symmetric Encryption

uuid-ossp vs pgcrypto for UUIDs

hstore: Key-Value Storage

pg_stat_statements: Query Performance Analysis

Setup

Querying Statistics

Resetting Statistics

citext: Case-Insensitive Text

tablefunc: Crosstab Queries

PostGIS Basics for Geospatial Data

Integrating Extensions with Node.js

Performance Impact of Extensions

Complete Working Example

Database Schema

Application Code

Testing the Endpoints

Common Issues and Troubleshooting

1. Extension Not Available

2. Permission Denied for CREATE EXTENSION

3. pg_stat_statements Returns Zero Rows

4. Trigram Similarity Returns 0 for Short Strings

5. bcrypt Hash Comparison Fails Silently

6. Extension Version Mismatch After Upgrade

Best Practices

References

Quick Links

Need Expert Help?