The production extension toolkit

conceptThe concept

Preloaded extensions allocate shared memory or hooks at startup, so adding them needs a restart, and a missing library file prevents the server from starting. Plan them with upgrades, not during incidents.

whyWhy it matters

These extensions turn common operational chores (nightly jobs, partition rotation, bloat fixes, cache warm-up after failover) into SQL that lives with the database and is visible to every DBA, instead of cron jobs on a single host that disappear with it.

Use caseReal-world scenario

A platform team's standard image preloads pg_stat_statements, auto_explain, pgaudit, pg_cron, pg_prewarm. pg_cron runs partman.run_maintenance_proc() hourly and a nightly VACUUM (ANALYZE) of a staging table; pg_repack is run from a runbook when pgstattuple shows more than 40% dead space; autoprewarm keeps failover warm-up under a minute.

Try itExamples

Run and edit these in the interactive lesson.

SELECT name, installed_version, default_version,
       installed_version IS DISTINCT FROM default_version AND installed_version IS NOT NULL AS needs_update
FROM pg_available_extensions
WHERE installed_version IS NOT NULL
ORDER BY name;
SELECT setting AS shared_preload_libraries FROM pg_settings WHERE name = 'shared_preload_libraries';

SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       pg_size_pretty(pg_table_size(relid)) AS size, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 5;

-- postgresql.conf: shared_preload_libraries = '...,pg_cron'   cron.database_name = 'shop'
CREATE EXTENSION pg_cron;
CREATE EXTENSION pg_partman SCHEMA partman;

-- daily partitions for events, 7 created ahead, 90 days kept
SELECT partman.create_parent(p_parent_table => 'public.events', p_control => 'at',
                             p_interval => '1 day', p_premake => 7);
UPDATE partman.part_config SET retention = '90 days', retention_keep_table = false
WHERE parent_table = 'public.events';

SELECT cron.schedule('partman-maintenance', '7 * * * *', $$CALL partman.run_maintenance_proc()$$);
SELECT cron.schedule('nightly-analyze', '30 2 * * *', $$VACUUM (ANALYZE) staging.import$$);

-- did it run?
SELECT jobid, status, return_message, start_time FROM cron.job_run_details ORDER BY start_time DESC LIMIT 10;

CREATE EXTENSION pgstattuple;
SELECT table_len, tuple_percent, dead_tuple_percent, free_percent FROM pgstattuple('orders');
SELECT * FROM pgstattuple_approx('events');              -- cheaper on big tables
SELECT avg_leaf_density, leaf_fragmentation FROM pgstatindex('orders_pkey');

-- shell: rebuild with only a brief exclusive lock at the end
-- pg_repack --dbname=shop --table=public.orders --jobs=2 --wait-timeout=60

CREATE EXTENSION pg_prewarm;
SELECT pg_prewarm('orders');                    -- read into shared_buffers now
SELECT pg_prewarm('orders_pkey');

-- automatic: shared_preload_libraries = '...,pg_prewarm'
-- pg_prewarm.autoprewarm = on                  (default when preloaded)
-- pg_prewarm.autoprewarm_interval = 300s       saves the block list every 5 minutes

CREATE EXTENSION hypopg;
SELECT * FROM hypopg_create_index('CREATE INDEX ON orders (customer_id, created_at)');
EXPLAIN SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 10;
--  Limit -> Index Scan Backward using "<13457>btree_orders_customer_id_created_at" on orders
SELECT hypopg_reset();

-- pg_hint_plan: last resort, documented and reviewed
LOAD 'pg_hint_plan';
/*+ Leading(c o) HashJoin(c o) IndexScan(o orders_customer_id_idx) */
SELECT ... FROM customers c JOIN orders o ON o.customer_id = c.id WHERE ...;

internalsUnder the hood

pg_cron runs a background worker in one database (cron.database_name) that launches jobs as new connections or background workers and records results in cron.job_run_details. pg_repack builds a new copy of the table while a trigger captures concurrent changes into a log table, replays them, then swaps files under a brief ACCESS EXCLUSIVE lock; it needs a primary key or a unique not-null index. pgstattuple reads every page for exact numbers; pgstattuple_approx skips all-visible pages using the visibility map. autoprewarm periodically writes the list of cached blocks to autoprewarm.blocks and reloads them after start. hypopg creates index definitions only in the session's memory, so plain EXPLAIN (not ANALYZE) can consider them.

Avoid / preferDo this, not that

-- incident at 3 a.m.
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements,pg_cron,pgaudit,pg_wait_sampling';
-- restart

-- add preload libraries in a planned change, after checking every package is installed on the primary and all replicas

/*+ IndexScan(o orders_customer_id_idx) */  -- added to 40 queries after one bad plan

-- fix statistics (ANALYZE, extended statistics, per-column targets), indexes or the query first;
-- hint only with a ticket and a review date

PitfallsCommon mistakes

• Installing an extension package on the primary but not on replicas, so a failover or a preload change breaks startup.
• Running pg_repack on a table without a primary key or with very high write rates without a wait timeout, so it holds up or fails at the swap.
• Scheduling pg_cron jobs in a database other than cron.database_name and wondering why they never run.
• Forgetting ALTER EXTENSION ... UPDATE in every database after upgrading extension packages.
• Trusting hypopg results without testing the real index: it only shows what the planner would choose, not the actual run time.

LimitsWhen not to use it

Do not install extensions "in case". Each one adds upgrade work, security surface and sometimes shared memory and hooks on every query. On managed services, use only the provider's supported list and versions, and check major-version compatibility before every upgrade.

SecuritySecurity notes

Extensions run code inside the server with high privileges. Install only trusted, maintained packages from your distribution or vendor, pin versions, restrict CREATE on the database so only administrators can install extensions, and review untrusted extensions (and their SECURITY DEFINER functions) before allowing them. pg_cron jobs run as the role that scheduled them: keep that role's privileges minimal.

ChallengeTry it yourself

SELECT name, installed_version, default_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL
  AND installed_version <> default_version
ORDER BY name;

InterviewQuestions you should be able to answer

QuizCheck your understanding

5 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