Advanced20 min read

PostgreSQL Extensions

Extend PostgreSQL with pg_stat_statements, PostGIS, uuid-ossp, and custom extensions.

Extension Basics

CREATE EXTENSION loads bundled modules into database. Extensions ship SQL objects, functions, and background workers. Available extensions listed in pg_available_extensions.

Manage extensions per database. Version upgrades via ALTER EXTENSION ... UPDATE after PostgreSQL minor upgrades.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

SELECT uuid_generate_v4();

pg_stat_statements

Tracks normalized query text with call count and timing statistics—essential for performance tuning. Requires shared_preload_libraries in postgresql.conf and restart.

Reset stats with pg_stat_statements_reset(). Query pg_stat_statements view for top total_exec_time offenders.

Combine with auto_explain for plan capture on slow queries
Beware secrets in query text—mask in logs
Available on RDS after parameter group enable

SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

PostGIS

PostGIS adds geographic types and spatial indexes (GiST). ST_DWithin, ST_Contains, and ST_Distance power location queries.

Use geography type for lon/lat distance in meters; geometry for projected coordinate systems. Index with GIST for performant spatial filters.

CREATE EXTENSION postgis;

CREATE TABLE stores (
  id SERIAL PRIMARY KEY,
  name TEXT,
  location GEOGRAPHY(POINT, 4326)
);

SELECT name FROM stores
WHERE ST_DWithin(location, ST_MakePoint(-122.4, 37.8)::geography, 5000);

Other Common Extensions

citext provides case-insensitive text. hstore key-value (legacy vs JSONB). pg_trgm fuzzy text search with GIN indexes. btree_gin combines btree-equivalent ops in GIN for hybrid indexes.

crypto/pgcrypto for hashing and encryption. pgvector for embedding similarity search in AI applications.

CREATE EXTENSION citext;
CREATE TABLE users (email CITEXT UNIQUE);

Managing Extensions Safely

Pin extension versions in migrations. Test extension upgrades in staging—some require REINDEX or data migration steps. DROP EXTENSION ... CASCADE removes dependent objects—dangerous in production.

Cloud providers preinstall common extensions; self-hosted may need postgresql-contrib package. Document required extensions in application README and IaC.