Skip to content
Tutorials Center
My learning

Topics

Lesson 56 of 90 ยท Extensions and Ecosystem

pg_trgm, hstore, citext, pgcrypto and uuid-ossp

Meet five handy PostgreSQL extensions: fuzzy search with pg_trgm, key-value hstore, case-insensitive citext, hashing with pgcrypto and UUIDs with uuid-ossp.

Five contrib extensions solve problems that almost every application meets sooner or later: finding text despite typos, storing a loose bag of attributes, treating [email protected] and [email protected] as the same email, hashing and random bytes, and generating identifiers. All five are shipped with PostgreSQL, and all five are trusted extensions (see Extensions), so a role with CREATE privilege on the database can install them.

This lesson shows what each one does, with the real PostgreSQL syntax and a note on when to reach for something else. The extensions themselves need a PostgreSQL server, so the PostgreSQL blocks are read-only. Where an idea can be demonstrated honestly in the browser, the live editors use SQLite stand-ins: a hand-built trigram similarity, a case-insensitive unique column and a key-value lookup with JSON.

pg_trgm: fuzzy matching and fast LIKE

A trigram is a group of three consecutive characters. pg_trgm splits text into trigrams and compares the sets. Two strings that share many trigrams are similar, even when a letter is missing or swapped.

SQL
CREATE EXTENSION IF NOT EXISTS pg_trgm;

SELECT show_trgm('cat');
-- {"  c"," ca","at ","cat"}

SELECT similarity('postgres', 'postgre');   -- a number between 0 and 1
SELECT 'postgres' % 'postgrse';             -- true if similarity >= the threshold (default 0.3)

The rules are simple: text is lowercased, split into words, and each word is padded with two spaces in front and one behind, so the start and end of a word count as features. The similarity is the number of shared trigrams divided by the number of distinct trigrams in either string.

You can build the same calculation yourself in SQLite, which makes the idea concrete. The next editor generates the trigrams of a few words with a recursive query, then scores each one against postgres. The padding and the division are the same ones described above.

SQL Trigram similarity, by hand

Press Run (or Ctrl + Enter) to see the result here.

postgre scores 0.7 and postgresql 0.667, while progress scores only 0.2 and mysql shares nothing. That ranking is exactly what you want for "did you mean" suggestions.

Operators and indexes

The extension adds operators you will use in queries:

Operator or function Meaning
a % b True if similarity(a, b) is at or above pg_trgm.similarity_threshold
similarity(a, b) Score from 0 to 1
a <-> b Distance, which is 1 - similarity(a, b)
word_similarity(a, b) Best match of a against any part of b, good for short words inside longer text
show_trgm(text) The trigram set of a string

A search-as-you-type query looks like this:

SQL
SELECT title, similarity(title, 'postgrse tutorial') AS score
FROM articles
WHERE title % 'postgrse tutorial'
ORDER BY title <-> 'postgrse tutorial'
LIMIT 10;

Without an index, PostgreSQL computes the similarity for every row. With a GIN or GiST index using the trigram operator classes, it only looks at promising rows:

SQL
CREATE INDEX articles_title_trgm_idx
    ON articles USING gin (title gin_trgm_ops);

The same index also makes LIKE '%word%', ILIKE '%word%' and regular-expression matches fast, which a normal B-tree index cannot do because the pattern starts with a wildcard. A GiST index (gist_trgm_ops) is better when you sort by distance with <-> and want the nearest few. GIN and GiST Indexes explains how the two differ.

pg_trgm is for typos and partial words. For real word-based searching with stemming and ranking, use Full-Text Search; the two work well together.

hstore: a simple key-value column

hstore stores a set of text keys and text values in one column. It is the older, simpler cousin of jsonb.

SQL
CREATE EXTENSION IF NOT EXISTS hstore;

CREATE TABLE products (
    id    integer PRIMARY KEY,
    name  text NOT NULL,
    attrs hstore
);

INSERT INTO products VALUES
  (1, 'Desk lamp', 'color => black, watts => 9, dimmable => yes'),
  (2, 'Mug',       'color => blue, size => 350ml');

SELECT name, attrs -> 'color' AS color FROM products;        -- get a value
SELECT name FROM products WHERE attrs ? 'dimmable';          -- does the key exist?
SELECT name FROM products WHERE attrs @> 'color => blue';    -- contains these pairs?
SELECT akeys(attrs), avals(attrs) FROM products WHERE id = 1;
UPDATE products SET attrs = attrs || 'color => white' WHERE id = 1;  -- merge or overwrite
UPDATE products SET attrs = delete(attrs, 'watts') WHERE id = 1;

A GIN index on the column (USING gin (attrs)) speeds up ? and @>. The big limit is that every value is text, and values cannot be nested or typed. For anything new, JSON and JSONB is usually the better choice, because it supports numbers, booleans, arrays and nesting, and has a path query language. Choose hstore when you want something tiny and flat, or when you inherit a schema that already uses it. The idea of a key-value lookup is easy to practice with SQLite's JSON functions:

SQL Key-value lookups with JSON (hstore stand-in)

Press Run (or Ctrl + Enter) to see the result here.

citext: case-insensitive text

Emails, usernames and tags are usually case-insensitive in practice: [email protected] and [email protected] should be the same person. The citext extension adds a column type that compares as if both sides were lowercased.

SQL
CREATE EXTENSION IF NOT EXISTS citext;

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

INSERT INTO accounts (email) VALUES ('[email protected]');
INSERT INTO accounts (email) VALUES ('[email protected]');
-- ERROR:  duplicate key value violates unique constraint "accounts_email_key"

SELECT * FROM accounts WHERE email = '[email protected]';   -- finds the row

The stored text keeps its original capitalization, so you can still display [email protected] as the user typed it, but equality, UNIQUE, ORDER BY, LIKE and the regex operators all ignore case. The usual B-tree index works on a citext column.

There are trade-offs. citext compares with the database's locale rules, so it is not a full Unicode case folding solution, and it adds a small cost to every comparison. PostgreSQL also supports nondeterministic ICU collations, a core feature that can make a whole column case-insensitive without an extension, but pattern matching on such columns was limited in older releases. Alternatively, a plain text column with an index on lower(email) (see Partial and Expression Indexes) works everywhere and keeps the type simple. Text Types and Collations compares all the options.

SQLite has a built-in way to do the same job, which lets you try the behavior live:

SQL A case-insensitive unique column

Press Run (or Ctrl + Enter) to see the result here.

INSERT OR IGNORE skips the duplicate quietly; without it you would see a constraint error, just like PostgreSQL's duplicate key value message.

pgcrypto: hashing, randomness and encryption

pgcrypto brings cryptographic functions into SQL. Four are used most:

SQL
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Random bytes (up to 1024 per call), good for tokens and salts
SELECT encode(gen_random_bytes(16), 'hex');          -- 32 hex characters

-- A hash of some data: digest() returns bytea
SELECT encode(digest('hello', 'sha256'), 'hex');
-- 2cf24dba5fb0a30e26e83b2ac5b9e29e1b161e5c1fa7425e73043362938b9824

-- A keyed hash, for signing
SELECT encode(hmac('payload', 'secret-key', 'sha256'), 'hex');

-- Password hashing with a random salt (bcrypt, cost 12)
INSERT INTO users (email, password_hash)
VALUES ('[email protected]', crypt('correct horse', gen_salt('bf', 12)));

-- Verifying: hash the attempt using the stored hash as the salt
SELECT id FROM users
WHERE email = '[email protected]'
  AND password_hash = crypt('correct horse', password_hash);

gen_salt('bf', 12) creates a bcrypt salt with a cost of 12; raising the cost makes each hash slower to compute, which slows down attackers too. The crypt() function stores the salt inside the hash text, which is why the verify query passes the stored hash as the second argument.

There is also a family of pgp_sym_encrypt and pgp_pub_encrypt functions for encrypting column values. Be careful here: the key must be passed to the server in the SQL text, so it can end up in logs, pg_stat_statements and query history. For most applications it is safer to hash passwords and encrypt data in the application, with a library designed for that, and to keep keys out of SQL. Authentication, TLS and Encryption goes deeper.

Never use plain digest() or md5() for passwords. Fast hashes can be guessed at billions per second. Use a slow, salted algorithm such as bcrypt (crypt with gen_salt('bf')), or better, a dedicated password hashing function such as Argon2 in your application code.

uuid-ossp: generating UUIDs

A UUID is a 128-bit identifier that is practically unique without coordination. The uuid-ossp extension generates several versions of it:

SQL
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";   -- the name has a hyphen, so it needs quotes

SELECT uuid_generate_v4();                              -- random
SELECT uuid_generate_v1();                              -- timestamp + machine based
SELECT uuid_generate_v5(uuid_ns_dns(), 'example.com');  -- the same input always gives the same UUID

The fixed-input versions (v3 with MD5, v5 with SHA-1) are useful when you want a stable ID derived from a name, such as an imported record's natural key.

For ordinary random UUIDs you no longer need this extension. Since PostgreSQL 13 the core function gen_random_uuid() generates a random (version 4) UUID, and PostgreSQL 18 adds uuidv7(), which produces time-ordered UUIDs that are friendlier to indexes. Prefer those when your version has them. UUIDs, Serials and Identity Columns compares key choices in detail.

SQL
CREATE TABLE sessions (
    id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),   -- core since PostgreSQL 13
    created_at timestamptz NOT NULL DEFAULT now()
);

Picking the right one

Which extension for which job?

pg_trgm Typos, suggestions, LIKE '%x%'.
hstore Flat text key-value pairs.
citext Emails and usernames.
pgcrypto Hashes, salts, random bytes.
1 / 2

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which index lets PostgreSQL answer WHERE name ILIKE '%lamp%' without scanning the whole table?

    A B-tree cannot be used when the pattern starts with a wildcard. A trigram GIN (or GiST) index breaks the text into trigrams and can serve LIKE, ILIKE and regex searches.
  2. 2. How does pg_trgm score the similarity of two strings?

    Similarity is shared trigrams over the union of both trigram sets, a value from 0 (nothing in common) to 1 (identical sets).
  3. 3. What is the main limitation of hstore compared with jsonb?

    hstore stores flat text keys and text values. jsonb supports numbers, booleans, arrays, nesting and path queries, so it is usually the better choice for new designs.
  4. 4. A citext column has a UNIQUE constraint and contains '[email protected]'. What happens when you insert '[email protected]'?

    citext compares values case-insensitively, so the unique index sees both values as equal and rejects the second insert.
  5. 5. Why should you not store passwords with a plain digest(password, 'sha256')?

    Password storage needs a slow, salted algorithm such as bcrypt or Argon2, so that guessing is expensive. A fast hash makes brute force cheap.

Summary

  • pg_trgm compares trigram sets: similarity(), %, <->, and GIN/GiST indexes that also speed up LIKE '%x%' and regex searches.
  • hstore is a flat text key-value type with ->, ?, @> and ||; prefer jsonb for new designs.
  • citext makes a column case-insensitive, including in UNIQUE constraints; an index on lower(column) is the portable alternative.
  • pgcrypto gives you gen_random_bytes, digest, hmac and bcrypt through crypt and gen_salt; keep encryption keys out of SQL where you can.
  • uuid-ossp generates several UUID versions, but gen_random_uuid() (core since 13) and uuidv7() (18) cover most needs.
  • All five are trusted extensions, installed with CREATE EXTENSION.

Next: pgvector and PostGIS, two big third-party extensions for embeddings and maps.