Useful extensions: pgcrypto, citext, hstore, ltree and btree_gist

conceptThe concept

• pgcrypto: crypt(password, gen_salt('bf')) produces a bcrypt hash; verifying is stored = crypt(attempt, stored). pgp_sym_encrypt and pgp_sym_decrypt encrypt values with a key.
• citext: a text type whose comparisons ignore case, so a UNIQUE constraint on an email column treats Ann@x.com and ann@X.com as duplicates.
• hstore: a flat map of text keys to text values. Today JSONB is usually the better choice, but hstore remains common in older schemas.
• ltree: label paths such as shop.home.lighting with operators for ancestors (@>) and descendants (<@) and GiST index support.
• btree_gist: GiST operator classes for plain types, needed when an exclusion constraint combines = on a column with && on a range.

whyWhy it matters

Each of these replaces application code that is easy to get subtly wrong: case-insensitive uniqueness checks with race conditions, recursive tree queries, or double-booking checks done with SELECT-then-INSERT.

Use caseReal-world scenario

A meeting-room service stores bookings with a room id and a tstzrange. One exclusion constraint guarantees no two bookings for the same room overlap, even under concurrent inserts.

Try itExamples

Run and edit these in the interactive lesson.

BEGIN;
CREATE EXTENSION pgcrypto;
CREATE TEMP TABLE app_users (email text PRIMARY KEY, pw_hash text NOT NULL);
INSERT INTO app_users VALUES ('ann@example.com', crypt('correct horse', gen_salt('bf', 8)));
SELECT left(pw_hash, 7) AS algorithm_and_cost,
       pw_hash = crypt('correct horse', pw_hash) AS right_password,
       pw_hash = crypt('wrong', pw_hash) AS wrong_password
FROM app_users;
ROLLBACK;

BEGIN;
CREATE EXTENSION citext;
CREATE TEMP TABLE subscribers (email citext PRIMARY KEY);
INSERT INTO subscribers VALUES ('Ann@Example.com');
SELECT email, email = 'ann@example.COM' AS matches FROM subscribers;
INSERT INTO subscribers VALUES ('ann@example.com');

BEGIN;
CREATE EXTENSION ltree;
CREATE TEMP TABLE categories (path ltree PRIMARY KEY);
INSERT INTO categories VALUES ('shop'), ('shop.home'), ('shop.home.lighting'), ('shop.home.kitchen'),
  ('shop.sports'), ('shop.sports.cycling'), ('shop.sports.cycling.helmets');
SELECT path, nlevel(path) AS depth FROM categories WHERE path <@ 'shop.sports' ORDER BY path;
SELECT path FROM categories WHERE path @> 'shop.sports.cycling.helmets' ORDER BY nlevel(path);
ROLLBACK;

BEGIN;
CREATE EXTENSION btree_gist;
CREATE TEMP TABLE bookings (
  room int NOT NULL,
  during tstzrange NOT NULL,
  EXCLUDE USING gist (room WITH =, during WITH &&)
);
INSERT INTO bookings VALUES (1, '[2025-11-10 09:00+00, 2025-11-10 10:00+00)'), (2, '[2025-11-10 09:30+00, 2025-11-10 10:30+00)');
INSERT INTO bookings VALUES (1, '[2025-11-10 09:30+00, 2025-11-10 11:00+00)');

BEGIN;
CREATE EXTENSION hstore;
SELECT h -> 'color' AS color, h ? 'size' AS has_size, hstore_to_jsonb(h) AS as_jsonb
FROM (SELECT 'color=>red, size=>XL, fit=>slim'::hstore AS h) s;
ROLLBACK;

internalsUnder the hood

bcrypt with cost 8 to 12 takes milliseconds to hundreds of milliseconds per hash by design, which is why it resists brute force. ltree paths are stored as a compact label array; GiST indexes on them support ancestor and descendant searches without recursive CTEs.

Avoid / preferDo this, not that

INSERT INTO users (email, pw)
VALUES ($1, md5($2));

INSERT INTO users (email, pw_hash)
VALUES ($1, crypt($2, gen_salt('bf', 12)));

-- check, then insert: races under concurrency
SELECT 1 FROM bookings WHERE room = $1 AND during && $2;
INSERT INTO bookings ...;

EXCLUDE USING gist (room WITH =, during WITH &&)

PitfallsCommon mistakes

• Sending plain passwords or encryption keys in SQL that is logged by log_statement or pg_stat_statements.
• Using hstore for new nested data; JSONB supports nesting, types and SQL/JSON path.
• Forgetting a GiST index on ltree columns and querying a large tree with sequential scans.

LimitsWhen not to use it

In-database encryption with pgcrypto protects data at rest from someone reading table files or backups, but the key travels with every query, so it does not protect against a DBA or anyone who can see query logs. For strong guarantees use application-side or client-side encryption with a key management service.

SecuritySecurity notes

Set log_statement and log_min_duration_statement so they do not capture statements containing secrets, and remember pg_stat_statements stores normalised query text without literal values. Prefer hashing passwords in the application with a modern algorithm (argon2id or bcrypt); if you hash in the database, use bcrypt with a cost of at least 10.

ChallengeTry it yourself

BEGIN;
CREATE EXTENSION ltree;
CREATE TEMP TABLE org AS
WITH RECURSIVE t AS (
  SELECT id, name, id::text AS p FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, t.p || '.' || e.id FROM employees e JOIN t ON e.manager_id = t.id
)
SELECT id, name, p::ltree AS path FROM t;
SELECT name, nlevel(path) - nlevel('1.2') AS depth_below
FROM org WHERE path <@ '1.2' AND path <> '1.2'
ORDER BY path;
ROLLBACK;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• CREATE EXTENSION
• Additional supplied modules
• Packaging related objects