Extensions: pg_trgm, PostGIS, pg_stat_statements · SQL: Zero to Production

Extensions: how Postgres adds capabilities without bloating core

Postgres's extensibility is a real differentiator, and CREATE EXTENSION is the mechanism. An extension packages new types, functions, operators, and index support into an installable unit — you've already met one, btree_gist, powering the EXCLUDE overlap constraints in Module 10's index-types lesson; pgcrypto (home of gen_random_uuid() on older Postgres) is another common example. This lesson is a tour of three more, each solving a different real problem, so you recognize them by name and know when to reach for one.

SELECT * FROM pg_available_extensions ORDER BY name;   -- everything installable on this server
SELECT * FROM pg_extension;                              -- everything currently installed in this database

pg_stat_statements: query-level performance monitoring

Tracks every distinct query pattern executed on the server, with aggregated call counts, total/average execution time, and rows returned — the standard, first tool for answering "what's actually slow on this database," aggregated across all connections rather than one query's EXPLAIN ANALYZE at a time.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- After running various queries, this becomes the single most useful
-- starting point for "what's actually slow, in aggregate, over time":
SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

This lesson only introduces it — using it as a real operational monitoring tool is beyond this course's scope, and it needs a server-level configuration change (shared_preload_libraries) to actually start collecting data, not just CREATE EXTENSION alone.

PostGIS: geospatial data and queries

The de facto standard for storing and querying geographic/geometric data in Postgres — points, lines, polygons, distance/containment queries, coordinate system transforms. Far beyond this course's scope to teach properly, but worth recognizing by name: if you ever see a geometry or geography column type, or functions like ST_Distance/ ST_Contains, that's PostGIS. It builds on the same GiST indexing infrastructure from Module 10's other-index-types lesson — geospatial queries ("what's within 5km of this point") are exactly the kind of non-linear-ordering problem GiST was built for.

pg_trgm: fuzzy, typo-tolerant text search

Recall Module 10's B-tree limitation: no index can accelerate LIKE '%pattern%' with no anchor. pg_trgm (trigram matching) solves a related but different problem: fuzzy, similarity-based matching — tolerant of typos, partial matches, and "close enough" search, not exact substring matching.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Similarity score between two strings (0 = nothing alike, 1 = identical).
SELECT similarity('ACADEMY DINOSAUR', 'ACADMEY DINOSAUR');  -- high, despite the typo

-- % operator: "similar enough" match, using a configurable threshold.
SELECT title FROM film WHERE title % 'ACADMEY DINOSAUR';   -- finds it despite the typo

-- A GIN (or GiST) index on trigrams accelerates BOTH fuzzy matching
-- AND, unlike plain B-tree, arbitrary substring LIKE '%pattern%' queries:
CREATE INDEX idx_film_title_trgm ON film USING gin (title gin_trgm_ops);
EXPLAIN SELECT title FROM film WHERE title LIKE '%GOLDFINGER%';

This directly resolves Module 10's open question about LIKE '%pattern%' — a plain B-tree fundamentally cannot help (sort order doesn't expose "contains"), but a trigram index can, because it indexes overlapping 3-character fragments of every value, and "contains X" becomes "shares trigrams with X" — a genuinely different, matchable structure. The now-familiar caveat from Module 10 still applies, though: on pagila's 1000-row film table, the planner may well still choose a plain Seq Scan over the trigram index, because a sequential scan of 1000 rows is already cheap enough that the index's overhead isn't worth paying. Force it to confirm the index genuinely can serve this query (SET enable_seqscan = off, same trick from Module 10):

SET enable_seqscan = off;
EXPLAIN SELECT title FROM film WHERE title LIKE '%GOLDFINGER%';
-- Now: Bitmap Heap Scan + Bitmap Index Scan using idx_film_title_trgm
RESET enable_seqscan;

The lesson underneath the specific result, worth restating one more time since it's come up in nearly every indexing lesson this course: "an index exists and could theoretically help" is not the same claim as "the planner will actually use it for this query, on this data." Only EXPLAIN (forced or not) tells you which is true.

Choosing among these (and knowing they exist at all)

The real skill this lesson is building isn't deep expertise in any one extension — it's recognizing the shape of a problem well enough to know an extension probably already solves it, before reinventing something worse in application code. Fuzzy search, geospatial queries, and query-level performance monitoring are all common enough real-world needs that "is there already a battle-tested Postgres extension for this" is worth asking before writing custom logic.

Check yourself

What problem does pg_stat_statements solve that a single query's EXPLAIN ANALYZE cannot?
Why can a trigram (pg_trgm) index accelerate LIKE '%GOLDFINGER%' when a plain B-tree index cannot?
Name one real-world data shape PostGIS is built for, and one index type (from Module 10) it relies on.