PostgreSQL Extensions: pg_stat_statements, pg_trgm, pgcrypto, citext, unaccent and Extensions on Managed Services

Version note: The examples in this post were run on PostgreSQL 18.7 (the official postgres:18 Docker image), in the Bookstore lab we set up in post 02; the pg_cron 1.6.8 and pg_upgrade experiments ran in separate, throwaway containers.

Guess First

Emre wants to add two extensions to Bookstore, the online bookstore used as the shared example across this site's series: citext for comparing e-mail addresses and pg_buffercache for looking into shared buffers. He connects as bookstore_owner, the role that owns the database in our lab but is not a superuser. The same day a teammate installs pg_stat_statements, which we used in post 07, on a freshly started PostgreSQL 18 container, this time as the superuser:

/* Emre, as bookstore_owner */
CREATE EXTENSION citext;
CREATE EXTENSION pg_buffercache;

/* teammate, as postgres on a new container */
CREATE EXTENSION pg_stat_statements;
SELECT count(*) FROM pg_stat_statements;

Which of the four statements fail? Both are "install an extension" commands; why would one work and the other not?

CREATE EXTENSION
ERROR:  permission denied to create extension "pg_buffercache"
HINT:  Must be superuser to create this extension.

CREATE EXTENSION
ERROR:  pg_stat_statements must be loaded via "shared_preload_libraries"

citext was installed and pg_buffercache was refused. pg_stat_statements was installed but could not be used. Three different outcomes come from three separate rules of the extension mechanism: the trusted/untrusted split, the libraries that must be loaded when the server starts, and the fact that CREATE EXTENSION only creates SQL objects. In this post we look at the mechanism behind the extensions we used one by one in this series, the everyday set, pg_cron, the licensing differences of the big extensions and the restrictions on managed services.

What an Extension Is: Package, Version and Dependencies

An extension is a package on the server's file system: a control file (name, default version, the shared library if there is one), an SQL script for each version and, most of the time, a .so library written in C. CREATE EXTENSION runs that script in the current database and ties the resulting functions, types and operators to the extension. So installation is per database; an extension you created in bookstore does not exist in the postgres database.

The extensions shipped with PostgreSQL are called contrib modules; they are all present in the official Docker image. Third-party extensions such as pg_cron, PostGIS and pgvector have to be installed separately. Here is what has accumulated in the lab since post 07, and what the server offers:

docker exec -it bookstore-pg psql -U bookstore_owner -d bookstore -f /lab/13-extensions.sql

        Name        | Version | Default version |   Schema   |                              Description
--------------------+---------+-----------------+------------+------------------------------------------------------------------------
 btree_gist         | 1.8     | 1.8             | public     | support for indexing common datatypes in GiST
 pg_stat_statements | 1.12    | 1.12            | public     | track planning and execution statistics of all SQL statements executed
 pg_trgm            | 1.6     | 1.6             | public     | text similarity measurement and index searching based on trigrams
 pgstattuple        | 1.5     | 1.5             | public     | show tuple-level statistics
 plpgsql            | 1.0     | 1.0             | pg_catalog | PL/pgSQL procedural language
 unaccent           | 1.1     | 1.1             | public     | text search dictionary that removes accents

 available | trusted
-----------+---------
        46 |      21

The extension version and the library version are two different things. A minor update (18.6 to 18.7) brings a new .so file, but the SQL objects in the database stay at the old version until you run ALTER EXTENSION ... UPDATE. We saw this in a throwaway database by installing an old version of pg_trgm:

CREATE EXTENSION pg_trgm VERSION '1.3';   /* 37 objects */
ALTER EXTENSION pg_trgm UPDATE;           /* 1.3 -> 1.4 -> 1.5 -> 1.6, 47 objects */
SELECT source, target, path FROM pg_extension_update_paths('pg_trgm')
WHERE target = '1.6' AND path IS NOT NULL ORDER BY source;

 source | target |               path
--------+--------+-----------------------------------
 1.0    | 1.6    | 1.0--1.1--1.2--1.3--1.4--1.5--1.6
 1.1    | 1.6    | 1.1--1.2--1.3--1.4--1.5--1.6
 1.2    | 1.6    | 1.2--1.3--1.4--1.5--1.6
 1.3    | 1.6    | 1.3--1.4--1.5--1.6
 1.4    | 1.6    | 1.4--1.5--1.6
 1.5    | 1.6    | 1.5--1.6

An update runs the scripts between versions one after another; going from 1.3 to 1.6 added ten objects. The query that lists rows where installed_version <> default_version (it is in the script) should be the first item on every pre-upgrade checklist. Objects that depend on an extension are protected as well; while the trigram index from post 10 exists, pg_trgm cannot be dropped:

ERROR:  cannot drop extension pg_trgm because other objects depend on it
DETAIL:  index ix_books_title_trgm depends on operator class gin_trgm_ops for access method gin
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Here CASCADE would drop the index too; never follow that hint in production without reading it. You can choose the schema for an extension's objects with CREATE EXTENSION ... SCHEMA extensions. In the lab they are all in public; a separate schema keeps extension functions apart from application tables but requires adding that schema to search_path.

Trusted and Untrusted: Who Can Install?

Since PostgreSQL 13 extensions come in two kinds. A trusted extension can be installed by a non-superuser role that has the CREATE privilege on the database; the script runs with superuser rights, but because the extension is considered safe those rights do not leak. Untrusted ones (those that can expose server memory, the file system or other databases' data) can only be installed by a superuser. Of the 46 extensions in the lab, 21 are trusted:

btree_gin, btree_gist, citext, cube, dict_int, fuzzystrmatch, hstore, intarray, isn, lo, ltree, pg_trgm, pgcrypto,
plpgsql, seg, tablefunc, tcn, tsm_system_rows, tsm_system_time, unaccent, uuid-ossp

That answers the first half of the guess: citext is on the list, pg_buffercache is not. pg_stat_statements, pgstattuple, pageinspect and pg_buffercache, which we used in this series, are untrusted, so we installed them as the superuser; the owner role could install pg_trgm, unaccent and btree_gist itself. The application role bookstore_app cannot install even a trusted extension, because it has no CREATE privilege on the database:

ERROR:  permission denied to create extension "hstore"
HINT:  Must have CREATE privilege on current database to create this extension.

And that is how it should be: installing an extension is a schema change and belongs with the migrations, run by the owner role. If you use Entity Framework Core, the provider's modelBuilder.HasPostgresExtension("pg_trgm") adds the statement that installs the extension to the migration; since we run migrations as the owner role (post 12), trusted extensions cause no trouble.

Extensions That Need shared_preload_libraries

The second half of the guess: CREATE EXTENSION pg_stat_statements only creates views and functions. The code that counts queries is a library; it must be loaded when the server starts so that it can see every backend's queries and reserve room in shared memory. The setting is shared_preload_libraries, and only a restart applies a change. That is why we restarted the lab container in post 07:

ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
/* docker restart bookstore-pg */

This group is mostly extensions that run in the background or touch every query: pg_stat_statements, pg_cron, TimescaleDB, pg_partman's background worker. Library-only modules such as auto_explain need no CREATE EXTENSION; in post 07 we enabled it with session_preload_libraries so that it is loaded in new sessions. The setting is a single list, so do not overwrite the current value when you add a second library: write 'pg_stat_statements,pg_cron'. If the list names a library that does not exist, the server will not start; check the value twice before the restart.

The Everyday Set: pg_trgm, unaccent, citext, pgcrypto, btree_gist

pg_trgm (posts 06 and 10) gives ILIKE '%...%' searches a GIN index and does similarity search; unaccent (post 10) handles searches typed without Turkish characters; btree_gist (post 04) lets an EXCLUDE constraint combine = and a range in one GiST index. We have used these in the series. Two are new:

citext is a case-insensitive text type; it applies lower() to both sides before comparing. It is handy for a unique constraint on an e-mail column:

CREATE TABLE newsletter_subscribers (email citext PRIMARY KEY, city citext);
INSERT INTO newsletter_subscribers VALUES ('Ada@Example.com', 'İSTANBUL');
INSERT INTO newsletter_subscribers VALUES ('ada@example.com', 'istanbul');
SELECT city = 'istanbul' AS istanbul, 'IŞIK'::citext = 'ışık' AS isik FROM newsletter_subscribers;

ERROR:  duplicate key value violates unique constraint "newsletter_subscribers_pkey"
DETAIL:  Key (email)=(ada@example.com) already exists.
 istanbul | isik
----------+------
 t        | f

For e-mail it worked as expected. For Turkish text, lower() uses the database locale (en_US.utf8 in the lab), so "IŞIK" becomes "işik" and does not match "ışık". The nondeterministic ICU collation from post 03 is more flexible here; the PostgreSQL documentation itself suggests considering nondeterministic collations instead of citext for new designs. You will mostly see citext in older schemas.

pgcrypto provides hashing and encryption functions (digest, hmac, crypt, pgp_sym_encrypt). If an old project installed it only to generate UUIDs, note that gen_random_uuid() has been in core since PostgreSQL 13 (\df gen_random_uuid shows the schema pg_catalog). When encrypting in the database, the thing to watch is the key: if the key travels inside the SQL text, it ends up in the log as well. We tried it in a superuser session with log_min_duration_statement = 0:

LOG:  duration: 1.273 ms  statement: SELECT length(pgp_sym_encrypt('Grace Hopper, +90 555 000 00 00', 'example-key')) > 0 AS encrypted;

pg_stat_statements normalizes constants to $1, $2, so it does not show them, but the slow query log writes the value and the key in plain text. Parameterized queries reduce this (parameter values can still show up in the log); the real fix is to encrypt in the application: in .NET with ASP.NET Core Data Protection or a key vault. As for passwords, do not hash them in the database at all; ASP.NET Core Identity does that on the application side.

Scheduled Jobs with pg_cron

pg_cron is a background worker that runs SQL on a cron schedule. It is not in the official image; we tried it with a small image that installs it from PostgreSQL's own Debian package repository (PGDG, which the official image already uses):

# PostgreSQL 18 + pg_cron from the PGDG apt repository the official image already uses
FROM postgres:18
RUN apt-get update \
    && apt-get install -y --no-install-recommends postgresql-18-cron \
    && rm -rf /var/lib/apt/lists/*

docker build -t bookstore/postgres-cron:18 db/13-pg-cron
docker run -d --name bookstore-cron -e POSTGRES_PASSWORD=<password> bookstore/postgres-cron:18 `
    -c shared_preload_libraries=pg_cron -c cron.database_name=bookstore -c cron.timezone=UTC

pg_cron keeps its metadata only in the database named by cron.database_name, and preloading is mandatory; Azure's documentation also notes that CREATE EXTENSION fails if the library was not preloaded. cron.sql creates a small copy of the outbox from post 09 and defines a job that deletes processed messages older than seven days:

SELECT cron.schedule('purge-processed-outbox', '* * * * *',
    $$DELETE FROM outbox_messages WHERE processed_at < now() - interval '7 days'$$);

 jobid |        jobname         | schedule  | database  | username | active
-------+------------------------+-----------+-----------+----------+--------
     1 | purge-processed-outbox | * * * * * | bookstore | postgres | t

 jobid |  status   | return_message | start_time
-------+-----------+----------------+------------
     1 | succeeded | DELETE 900     | 03:00:00

A minute later cron.job_run_details showed that 900 rows had been deleted. pg_cron is good for maintenance close to the data: deleting old rows, refreshing a materialized view, dropping a partition. It also prevents a job from overlapping with itself; the next run does not start until the previous one has finished. But if the work is an HTTP call, an e-mail or a flow that needs retries and monitoring, it belongs in the application: the Hangfire and Quartz.NET comparison in the Background Job'lar ve Scheduled Job'lar series covers that decision in detail. One more note: cron.job_run_details is not cleaned up automatically, so you need a job that does that as well.

The Big Extensions: PostGIS, pgvector, TimescaleDB, Citus

These four make PostgreSQL behave like another product, and each is a topic of its own; here is only what you need to know when choosing.

PostGIS adds geometry/geography types, spatial (GiST) indexes and hundreds of functions for geographic data; if Bookstore ever needs something like "the nearest pickup point", it is the place to start. pgvector stores embeddings and runs similarity search; its setup and the HNSW index are covered in the pgvector post of the RAG series. Both have permissive licenses similar to PostgreSQL's, and every major managed service offers them.

With TimescaleDB and Citus, licensing needs a conversation. TimescaleDB's source code has two licenses: the core is Apache 2.0, while some features such as compression are under the Timescale License (TSL). According to the Azure Database for PostgreSQL documentation, Azure offers only the Apache 2.0 edition, so a feature that works locally may be missing on the managed service. Citus, which distributes tables across several nodes, is released under AGPL-3.0. Amazon RDS for PostgreSQL's extension list for PostgreSQL 18 includes neither TimescaleDB nor Citus. Sharding and Citus are topics of the Veritabanı Performansı ve Scaling series.

Extensions on Managed Services: Azure and RDS

The information in this section comes from Microsoft Learn and the AWS documentation; no cloud account was created for this post and the commands were not run. Neither service gives you a superuser, and the extensions you can install are limited to the ones the service has built and offers.

On Azure Database for PostgreSQL flexible server an extension must first be added to the allow list with the azure.extensions server parameter; for an extension that is not on the list, CREATE EXTENSION, ALTER EXTENSION and DROP EXTENSION fail. Extensions that need preloading are also added to the shared_preload_libraries parameter, followed by a server restart. Installation is done by a member of the azure_pg_admin role. The CLI command from the documentation:

az postgres flexible-server parameter set `
    --resource-group <resource_group> --server-name <server> `
    --name azure.extensions --value "pg_trgm,unaccent,btree_gist,pg_stat_statements"

On Amazon RDS for PostgreSQL the equivalent is the rds.allowed_extensions parameter. Its default is *, meaning every extension the service offers can be installed; once you narrow the list, CREATE EXTENSION is refused for anything not on it, with a hint that the extension is "not specified in rds.allowed_extensions". It is a dynamic parameter and needs no restart. On RDS 13 and later, a user with the CREATE privilege can install trusted extensions, while the others require the rds_superuser role. Extensions that need preloading, such as pg_cron, are added to shared_preload_libraries in the parameter group and the instance is restarted. The AWS documentation also states that an engine upgrade does not update extensions; you do that manually after the upgrade.

The practical takeaway: before building a design that depends on an extension, check the target service's list for that major version. The local Docker image lets you install anything; finding out late that an extension cannot be installed in production is expensive.

Production Notes

Make extensions part of your migrations: CREATE EXTENSION IF NOT EXISTS, run by the owner role, in the same place as the schema changes. An extension installed by hand in one environment and forgotten in another is one of the most common reasons for "it works locally".

After a minor update, run the installed_version <> default_version query and plan the necessary ALTER EXTENSION ... UPDATE statements. Release notes sometimes ask for an update or a reindex for a specific extension.

Extensions that run in the background, such as pg_stat_statements and pg_cron, have a cost: shared memory, background worker slots (max_worker_processes) and their own tables. Write down why you installed each extension.

Common Pitfalls

Installing an unvetted extension in production and blocking the major upgrade. pg_upgrade requires every library the old cluster uses to exist in the new version as well. We tried to move a PostgreSQL 17 cluster with pg_cron installed to an 18 installation without the pg_cron package:

Checking for presence of required libraries                   fatal

Your installation references loadable libraries that are missing from the
new installation.  You can add these libraries to the new installation,
or remove the functions using them from the old installation.

could not load library "$libdir/pg_cron": ERROR:  could not access file "$libdir/pg_cron": No such file or directory
In database: bookstore

Locally the fix is to install the package; on a managed service you either wait until the extension is supported on the new version or remove it. The Azure documentation states that in-place major upgrades do not support some extensions, such as TimescaleDB, dblink and postgres_fdw, on every version. A little-known extension can postpone an upgrade for months.

Making the application role a superuser for an untrusted extension. If you need pg_buffercache, install it as the superuser and grant rights on its functions separately; for pg_stat_statements the pg_read_all_stats role is enough, as in post 07.

Seeing uuid-ossp or pgcrypto used only for UUIDs in an old project and carrying it into a new one: gen_random_uuid() and 18's uuidv7() are in core.

A Small Experiment

★☆☆ In the lab, create CREATE SCHEMA extensions as bookstore_owner, install hstore into that schema and run 'a=>1'::hstore without changing search_path. Which error did you get, and how did you fix it with ALTER ROLE ... SET search_path?

★★☆ In the pg_cron container, add a second job that deletes rows older than three days from cron.job_run_details. Move the job to 03:30 every night with cron.alter_job and verify the change in cron.job.

★★★ Compare the PostgreSQL 18 extension list of a cloud service you use (Azure or RDS) with the \dx output in this post. If one of the extensions Bookstore needs were missing, how would you change the design?

In the next post we move on to security and operations basics: the role model and GRANTs, pg_hba.conf, SCRAM and TLS, backups with pg_dump and testing the restore, going back to a point in time with the WAL archive, and major upgrades with pg_upgrade.