skip to content

Extensibility

How PostgreSQL grows past its core: extensions installed per database, and foreign data wrappers that query other servers as local tables. Interviewers probe upgrades, privileges and pushdown.

part ofPostgreSQLread it →
on this pageshow

explore

questions

12

In PostgreSQL, what does CREATE EXTENSION actually do, and what must already exist on the server before it can succeed?

level: juniorimportance: must knowfreq 55%

answer

  1. a package, not loose objects
  2. files on disk come first
  3. control file plus versioned script
  4. per database, recorded in pg_extension

basics

~20 s

CREATE EXTENSION runs an extension's install script in the current database and records every object it creates as a member of one package. The control file, SQL scripts and any C library must already be installed on the server.

solid answer

~40 s

`CREATE EXTENSION ltree` finds `ltree.control` (in `SHAREDIR/extension`, or a directory listed in PostgreSQL 18's new `extension_control_path`). It picks `default_version` unless you pass `VERSION`, and runs the matching `ltree--<version>.sql` script inside the current transaction. The extension gets a row in `pg_extension`, and every object the script creates is recorded as a member in `pg_depend`. That is why `DROP EXTENSION` removes them all together and `pg_dump` writes one `CREATE EXTENSION` line. The command acts on one database only. It does not download anything: an OS package or a build must already have put the control file, scripts and shared library on the server and on every standby. If they are missing, PostgreSQL 18 answers `extension "postgis" is not available`. `pg_available_extensions` lists what is on disk, and `\dx` lists what is installed.

code

sql · 18 lines
sql
-- on disk vs installed in this database
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE name IN ('ltree', 'postgis');

CREATE EXTENSION IF NOT EXISTS ltree;

CREATE TABLE product_category (
  category_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  path        ltree NOT NULL UNIQUE      -- e.g. 'grocery.dairy.cheese'
);
CREATE INDEX product_category_path_gist ON product_category USING gist (path);

-- every category under grocery.dairy, including itself
SELECT path FROM product_category WHERE path <@ 'grocery.dairy';

-- fails (RESTRICT): product_category.path depends on the ltree type
DROP EXTENSION ltree;

go deeper

for a junior

Know that an extension is files on the server plus objects in one database, that CREATE EXTENSION installs it per database, and that pg_available_extensions and \dx show what exists.

for a middle

Explain the control file, versioned scripts and update chains, pg_extension membership, and why DROP EXTENSION defaults to RESTRICT.

for a senior

Show operational care: packages on every standby before CREATE EXTENSION, read CASCADE dependency lists before dropping, and know that 18's extension_control_path suits immutable images.

for a principal

Discuss which extensions a platform should allow at all, how their packages are versioned and rolled out across environments, and who owns installing them.

## What an extension is An **extension** is how PostgreSQL packages related SQL objects (functions, data types, operators, operator classes, sometimes configuration tables) together with optional C code. The contrib modules that ship with PostgreSQL, such as `ltree`, `hstore`, `pgcrypto` and `amcheck`, are extensions. So are external projects such as PostGIS and Citus. Packaging matters because PostgreSQL then *knows* the objects belong together: it can drop them together, upgrade them together and dump them as one line. An extension has two parts: **files on the server** and **catalog state inside one database**. The files come first: | File | Example | Location | Installed by | |---|---|---|---| | Control file | `ltree.control` | `SHAREDIR/extension` (`pg_config --sharedir`) | OS package or `make install` | | Install script | `ltree--1.1.sql` | next to the control file | same | | Update scripts | `ltree--1.2--1.3.sql` | next to the control file | same | | Shared library | `ltree.so` | `$libdir` (`pg_config --pkglibdir`) | same | The control file is a small `key = value` file. Its settings include `default_version`, `module_pathname`, `relocatable`, `requires`, `superuser` and `trusted`. ## What CREATE EXTENSION does, step by step 1. **Finds `<name>.control`** on the extension control path. If there is no such file, PostgreSQL 18 stops with `extension "<name>" is not available`, plus a hint that the extension must first be installed on the system where PostgreSQL is running. 2. **Picks the version**: the `VERSION` clause if you gave one, otherwise `default_version` from the control file. 3. **Checks privileges**: you must be a superuser, unless the control file says `trusted = true` and you hold `CREATE` on the database. 4. **Runs the scripts** inside the current transaction (so they cannot contain `VACUUM` or `COMMIT`). If only an older base script exists, it follows the update chain. Installing `ltree` 1.3 runs `ltree--1.1.sql`, then `ltree--1.1--1.2.sql`, then `ltree--1.2--1.3.sql`. 5. **Records the result**: a row in `pg_extension` (`extname`, `extversion`, `extnamespace`, `extowner`), plus a membership dependency (`deptype = 'e'` in `pg_depend`) for each object the script created. The C library is usually **not** loaded at this point. A backend loads it the first time one of its functions is called. Libraries that must be in memory before any backend starts are listed in `shared_preload_libraries`, which is a separate step. ## Scope: files per installation, objects per database - The files serve the whole installation, but `CREATE EXTENSION` acts on **the current database only**. Each database that needs `ltree` runs it, unless that database was created from a template such as `template1` that already had the extension. - An extension's name is unique within the database, and the extension itself does not live in a schema; its member objects do. `CREATE EXTENSION ltree SCHEMA ext` puts the objects in `ext`, unless the control file pins its own `schema`. - `CASCADE` first installs any extensions named in `requires`. `IF NOT EXISTS` turns a duplicate into a notice, but it never checks that the existing extension matches the files on disk. - **Physical standbys** receive the catalog rows through WAL, but never the files. Install the same package on every standby, or calls into the library fail there. ## Seeing what exists - `pg_available_extensions` lists every control file found on disk, with its `default_version` and its `installed_version` (NULL if it is not installed in this database). - `pg_available_extension_versions` has one row per installable version, with the `superuser`, `trusted`, `relocatable` and `requires` flags. - `pg_extension` lists what is installed in this database. psql's `\dx` shows the same. In PostgreSQL 18, `\dx` also gains a *Default version* column, so you can see at a glance when an installed version lags behind the files. ## Removing an extension `DROP EXTENSION` removes every member object. It defaults to `RESTRICT`: if a table column uses the `ltree` type, the command fails with *cannot drop extension ltree because other objects depend on it*. `CASCADE` drops those dependents too, including that column and its data, so read the list of dependents first. You cannot drop a single member on its own. Changes to members belong in an update script, which you apply with `ALTER EXTENSION ... UPDATE`. ## PostgreSQL 18: extension_control_path Before 18, the control files had to live in the compiled-in `SHAREDIR/extension` directory. PostgreSQL 18 adds `extension_control_path`, a superuser-only setting that defaults to `'$system'` (that compiled-in directory). It takes a colon-separated list of directories, each expected to contain an `extension` subdirectory. This lets extensions live outside a read-only installation tree, such as an immutable container image. It is normally set together with `dynamic_library_path`, which points at the matching library directory. If two directories hold an extension with the same name, the first one on the path wins.

  • Why does a PostgreSQL streaming standby need the extension's package installed even though CREATE EXTENSION ran only on the primary?
    Physical replication ships WAL. WAL carries the catalog rows (`pg_extension`, `pg_proc`, the new types) but never files outside the data directory. The control file, scripts and shared library live in the installation's share and lib directories. A standby without the package therefore has catalog entries that point at a library it cannot load, and the first query that calls one of its C functions fails. Install the same package version on every standby before running `CREATE EXTENSION`.
  • What does PostgreSQL's DROP EXTENSION ... CASCADE remove that a plain DROP EXTENSION refuses to?
    Plain `DROP EXTENSION` means `RESTRICT`: it drops the members only if nothing outside the extension depends on them. If `product_category.path` has type `ltree`, the command errors and lists that column. `CASCADE` also drops the dependents, which here means the column and its data along with any index on it. On a production database, read the dependency list before adding `CASCADE`.

Installing an extension is like flat-pack furniture: the OS package delivers the boxed parts to the building's storeroom (files on the server), and CREATE EXTENSION assembles one set in one room (a database). Each room that wants the furniture assembles its own set, and DROP EXTENSION takes the whole set out of that room only.

  • CREATE EXTENSION downloads and builds the extension from the internet.
  • Running CREATE EXTENSION once makes it available in every database of the cluster.
  • An extension lives inside a schema, so two schemas can each install their own copy.
  • DROP EXTENSION by default also drops the tables and columns that use its types.
  • A streaming standby gets the extension's shared library through WAL replay.

Assumes PostgreSQL 18: the extension_control_path setting and the Default version column in psql's \dx are new in 18.

open as a page

In PostgreSQL, why do some extensions need shared_preload_libraries and a server restart while others work right after CREATE EXTENSION?

level: middleimportance: must knowfreq 62%

basics

~20 s

Libraries that reserve shared memory, start background workers or install global hooks must be loaded by the postmaster at startup through shared_preload_libraries, which takes a restart. Libraries that only provide functions load on first call, so CREATE EXTENSION is enough.

open as a page

With PostgreSQL's postgres_fdw, which objects do you create to query another server's tables, and which object holds the host and which the password?

level: middleimportance: must knowfreq 55%

basics

~20 s

After CREATE EXTENSION postgres_fdw, a foreign server holds host, port and dbname; a user mapping holds each local role's remote user and password; foreign tables, usually made by IMPORT FOREIGN SCHEMA, name the remote tables.

open as a page

With PostgreSQL's postgres_fdw, how do you see which parts of a query run on the remote server, and what stops a WHERE clause from being pushed down?

level: seniorimportance: must knowfreq 48%

basics

~20 s

EXPLAIN VERBOSE shows each foreign scan's Remote SQL. postgres_fdw ships a WHERE clause only when its functions, operators and types are built-in or from a listed extension, immutable and collation-safe; otherwise rows cross the network and are filtered locally.

open as a page

In PostgreSQL, what is a foreign table, and how does querying one differ from querying an ordinary local table?

level: juniorimportance: should knowfreq 38%

basics

~20 s

A foreign table is a local definition whose rows live elsewhere; a foreign data wrapper such as postgres_fdw fetches them at query time. It has no local storage or indexes, and PostgreSQL assumes its declared constraints hold without enforcing them.

open as a page

Using PostGIS in PostgreSQL for a delivery-zone feature, how do you store zones and addresses and query them so a GiST index is used?

level: middleimportance: should knowfreq 47%

basics

~20 s

Install PostGIS, store zones and points as geography in SRID 4326, index the columns with GiST, and filter with index-aware predicates such as ST_Covers and ST_DWithin. A comparison like ST_Distance(...) < 3000 cannot use the index.

open as a page

In PostgreSQL, why can a non-superuser application role run CREATE EXTENSION ltree but get permission denied for amcheck?

level: middleimportance: should knowfreq 38%

basics

~20 s

ltree's control file says trusted = true, so any role with CREATE privilege on the current database may install it, and its script runs as the bootstrap superuser. amcheck is not marked trusted, so only a superuser can install it.

open as a page

After pg_upgrade moves a PostgreSQL 17 cluster to 18, why does amcheck still lack gin_index_check, and how should extension versions be managed across upgrades?

level: seniorimportance: should knowfreq 44%

basics

~20 s

pg_upgrade recreates each extension at its old SQL version, so amcheck stays at 1.4 while the 18 files offer 1.5 with gin_index_check. Running pg_upgrade's update_extensions.sql, or ALTER EXTENSION amcheck UPDATE in each database, brings the SQL objects forward.

open as a page

A PostgreSQL finance report over postgres_fdw foreign tables picks poor join plans and spends most of its time on the network; which postgres_fdw options do you tune, and what does each cost?

level: seniorimportance: should knowfreq 35%

basics

~20 s

Fix estimates with use_remote_estimate (a remote EXPLAIN during planning) or a scheduled ANALYZE of the foreign tables, plus fdw_startup_cost and fdw_tuple_cost. Cut round trips with fetch_size (100 rows per FETCH by default) and batch_size (1 row per INSERT).

open as a page

When one PostgreSQL transaction writes to local tables and to postgres_fdw foreign tables, what isolation and commit guarantees does the remote side actually get?

level: seniorimportance: should knowfreq 32%

basics

~20 s

postgres_fdw opens a remote transaction at REPEATABLE READ (SERIALIZABLE if the local one is) and sends the remote COMMIT just before the local one. With no two-phase commit, a failure between those steps leaves one side committed and the other not.

open as a page

In PostgreSQL, what do you get by attaching postgres_fdw foreign tables as partitions of a partitioned table, and where does that do-it-yourself sharding break down?

level: seniorimportance: should knowfreq 26%

basics

~20 s

A partitioned table whose partitions are foreign tables on different servers gives local partition pruning, concurrent remote scans with async_capable, and per-shard aggregate pushdown. It lacks global unique constraints, atomic cross-shard writes and one consistent snapshot.

open as a page

After an OS upgrade on a PostgreSQL 18 host, how would you use amcheck to check product_category's B-tree indexes for corruption without blocking writes?

level: seniorimportance: nice to knowfreq 21%

basics

~20 s

As a superuser, install amcheck and run bt_index_check on each B-tree index, optionally with heapallindexed => true. It takes only an AccessShareLock, so writes continue. Avoid bt_index_parent_check on a busy primary, because its ShareLock blocks writes.

open as a page