Extensions Worth Knowing

CREATE EXTENSION adds types, functions, operators, index methods and background workers to a running database. That single statement is why Postgres became a vector store, a job scheduler, a time-series engine and a geospatial platform without anyone forking the server. The first topic of this book called extensibility a catalogue property; this is where the property is spent.

Each entry on the list is also a dependency that your backups, your replicas, your restore host, your next major version and, if you ever move, your managed provider all have to agree with. Cartwheel found this out during the upgrade planning in the previous topic: four extensions were installed on pg-primary, one of them with no record of who added it or why, and each had to be confirmed as packaged for 18 before the window could be booked. Nothing in the catalogue records the reason an extension is there.

What CREATE EXTENSION Actually Does

An extension is a packaged set of SQL objects with a version number and a script that creates them. Installing it is per database, not per cluster: running the statement in cartwheel does nothing for any other database on pg-primary, which is the same lesson the template1 discussion in Chapter 1 arrived at from the other side. The objects land in one schema, chosen with the SCHEMA clause when the extension permits relocation, and CASCADE pulls in any extensions it depends on rather than failing with a message about a missing prerequisite.

SELECT extname, extversion, extnamespace::regnamespace
  FROM pg_extension ORDER BY 1;

ALTER EXTENSION pg_stat_statements UPDATE;   -- SQL objects only

Two facts hide behind that pair of statements. The first is that installing a new package on the operating system does not update the extension inside the database. The files on disk change, the catalogue does not, and ALTER EXTENSION … UPDATE is what runs the migration script that brings the SQL objects to the new version. Skipping it leaves a database whose extension version and library version disagree, which usually surfaces as a function that exists but behaves like the old one. The second is that some extensions are not just SQL. Those carrying a shared library that has to be loaded at server start (pg_stat_statements, auto_explain, pg_cron, citus) must be listed in shared_preload_libraries, and that is a restart rather than a statement. It is the difference between "install it now" and "install it at the next window".

The Operational Set

The extensions that make the rest of this book measurable are all in the standard contrib package. pg_stat_statements aggregates execution counts and timings per normalized query, which is what turns "the site is slow" into a ranked list. auto_explain logs the plan of any statement over a threshold, so the plan that was actually chosen at 09:14 on Saturday exists rather than being reconstructed later from a query run at a quiet moment. pg_buffercache reports what is currently in shared_buffers and which relation each page belongs to. pgstattuple counts live and dead tuples and free space by scanning the relation, which is the honest bloat number rather than an estimate. postgres_fdw queries another Postgres server as if its tables were local. hypopg creates an index that does not exist so that EXPLAIN can be asked whether it would use one, which tests an index on a 40-million-row table without building it.

One more belongs beside them without being contrib. pg_repack rebuilds a bloated table or index online, holding only brief exclusive locks at the start and end rather than the ACCESS EXCLUSIVE lock a VACUUM FULL holds for the whole rewrite. On a table that checkout reads continuously, that difference is the difference between a maintenance window and a Tuesday afternoon.

The Capability Set

The second group replaces systems rather than instrumenting one. pgvector adds a vector type with similarity operators and approximate-nearest-neighbour indexes, which is the reason a large number of teams that were about to run a dedicated vector database did not. PostGIS adds geometry and geography types with a full spatial function library and spatial indexing, and is old enough and complete enough that "Postgres for geospatial" needs no qualification. pg_trgm supports similarity and fuzzy matching on text and backs index-supported LIKE patterns that a B-tree cannot serve.

The heavier three change how the database is operated. TimescaleDB layers hypertables, columnar compression and continuous aggregates over ordinary tables for time-series work. Citus distributes tables across a cluster of nodes, which is the answer when one server genuinely is not enough. pg_cron runs scheduled jobs inside the database, and demonstrates the restart rule perfectly, since it needs a preloaded library and by default keeps its metadata in a single named database for the whole cluster. pg_partman automates the creation and retention of partitions, which is the tooling most teams eventually build by hand around a partitioned delivery_events.

pgcrypto, and Where the Key Lives

Column encryption is the extension people install for the wrong reason, so it is worth being precise about what it does. pgcrypto provides hashing, password hashing with crypt() and gen_salt(), symmetric and public-key PGP functions, and raw ciphers. All of them execute inside the server process, so the plaintext and the key both pass through the database. The manual states it directly: the data and passwords move between the extension and the client in clear text, so you must connect locally or over TLS and must trust the system and database administrators.

That single sentence prices the feature. A key stored in a table in the same database protects against nothing an attacker who reached the database cannot also reach. A key passed in from the application on every call is better, and it makes a compromised backup file genuinely unreadable, which is a real threat model worth defending. But it does not protect against a compromised superuser or a compromised server, and if that is the threat, the encryption has to happen in the application before the value is ever sent. Combine that with the indexing cost from the authentication topic and the honest recommendation is narrow: a small number of columns, read by primary key, with the key held outside the database.

Trusted Extensions, and Who May Install One

Installing an extension ordinarily requires whatever privileges creating its component objects would require, and for many of them that means superuser. Since 13, an extension whose control file marks it trusted can be installed by any role with CREATE on the database. The extension object itself is owned by the caller while the objects inside it are owned by the bootstrap superuser, so the caller can drop the extension without being able to modify its internals.

That distinction is technical on a self-managed box and structural on a managed one, where nobody has superuser at all. Which extensions a provider supports, at which versions, on which major release, is a hard constraint on the design rather than a detail to look up at deployment. Amazon RDS, Cloud SQL, Azure Database and the rest each publish their own list, the lists differ, and they differ again per major version.

What an Extension Costs Later

Every extension has to exist on every machine the database might ever run on. That is the replica, the restore target, the scratch cluster the upgrade is rehearsed on, and the new major version on the day the window is booked; the previous topic's pre-flight query exists because this is the item that most often stops an upgrade. An extension with a background worker also takes a process slot and shared memory, and one with a preloaded library makes every future install of it a restart.

The deeper cost is on the upgrade path. An unmaintained extension pins your major version to whenever its packaging catches up, and five extensions adopted in one quarter can produce a dependency graph that no single Postgres release satisfies. That is the entire argument for reaching for a core feature when one fits, jsonb instead of a document store or built-in full text before a search engine, and for keeping the list in the repository next to the migrations with a version and a named owner beside each line.