You are an expert in PostgreSQL extensions and their integration into production database systems.

## Key Points

- Always use `IF NOT EXISTS` when creating extensions in migration scripts to ensure idempotency.
- Pin extension versions in production and test upgrades in staging first.
- Use `pg_stat_statements` in every production database for query performance visibility.
- Place extensions in a dedicated schema to keep the public schema clean.
- Review extension dependencies before upgrading (`SELECT * FROM pg_depend WHERE ...`).
- For pgvector, choose HNSW indexes for better recall and IVFFlat for faster index builds on very large datasets.
- **Forgetting shared_preload_libraries**: Extensions like `pg_stat_statements` and `pg_cron` require entries in `shared_preload_libraries` in `postgresql.conf` and a server restart.
- **Extension ownership**: Extensions are owned by the installing superuser. Use `ALTER EXTENSION ... OWNER TO` if needed.
- **pgvector dimension limits**: Vectors are limited to 2000 dimensions by default. For larger vectors, consider dimensionality reduction.
- **PostGIS SRID mismatches**: Always ensure consistent SRIDs across geometry columns and queries. Mixing SRIDs silently produces wrong results.
- **pg_cron running on replica**: pg_cron only runs on the primary; jobs will not execute on standby servers.

Extensions — PostgreSQL

You are an expert in PostgreSQL extensions and their integration into production database systems.

Core Philosophy

Overview

PostgreSQL's extension system is one of its most powerful features, allowing the database to be extended with new data types, functions, operators, index types, and more without modifying the core engine. Extensions are installed per-database and managed through a clean API.

Core Concepts

Extension Management

Extensions are installed, upgraded, and removed with DDL commands:

-- Install an extension
CREATE EXTENSION IF NOT EXISTS pgvector;

-- Install into a specific schema
CREATE EXTENSION postgis SCHEMA public;

-- Upgrade an extension to the latest available version
ALTER EXTENSION pgvector UPDATE;

-- Check installed extensions
SELECT extname, extversion FROM pg_extension;

-- Check available extensions
SELECT * FROM pg_available_extensions WHERE name LIKE 'pg%';

pgvector — Vector Similarity Search

pgvector adds vector data types and similarity search, essential for AI/ML embedding workloads:

CREATE EXTENSION vector;

-- Create a table with a vector column (1536 dimensions for OpenAI embeddings)
CREATE TABLE documents (
    id bigserial PRIMARY KEY,
    content text NOT NULL,
    embedding vector(1536)
);

-- Create an HNSW index for fast approximate nearest neighbor search
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

-- Query nearest neighbors using cosine distance
SELECT id, content, embedding <=> '[0.1, 0.2, ...]'::vector AS distance
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 10;

-- Use inner product for normalized vectors
SELECT id, content, (embedding <#> '[0.1, 0.2, ...]'::vector) * -1 AS similarity
FROM documents
ORDER BY embedding <#> '[0.1, 0.2, ...]'::vector
LIMIT 10;

-- IVFFlat index (faster build, slightly less accurate)
CREATE INDEX ON documents USING ivfflat (embedding vector_l2_ops)
    WITH (lists = 100);

PostGIS — Geospatial Data

PostGIS adds geographic object support, spatial indexing, and hundreds of geospatial functions:

CREATE EXTENSION postgis;

-- Create a table with geometry column
CREATE TABLE locations (
    id serial PRIMARY KEY,
    name text NOT NULL,
    geom geometry(Point, 4326)  -- SRID 4326 = WGS 84
);

-- Insert a point (longitude, latitude)
INSERT INTO locations (name, geom)
VALUES ('Office', ST_SetSRID(ST_MakePoint(-73.9857, 40.7484), 4326));

-- Find locations within 5km of a point
SELECT name, ST_Distance(
    geom::geography,
    ST_SetSRID(ST_MakePoint(-73.9857, 40.7484), 4326)::geography
) AS distance_meters
FROM locations
WHERE ST_DWithin(
    geom::geography,
    ST_SetSRID(ST_MakePoint(-73.9857, 40.7484), 4326)::geography,
    5000  -- 5000 meters
)
ORDER BY distance_meters;

-- Create a spatial index
CREATE INDEX idx_locations_geom ON locations USING GIST (geom);

pg_cron — Job Scheduling

pg_cron enables cron-style job scheduling directly inside PostgreSQL:

CREATE EXTENSION pg_cron;

-- Schedule a job to vacuum a table daily at 3 AM
SELECT cron.schedule('nightly-vacuum', '0 3 * * *',
    $$VACUUM ANALYZE orders$$
);

-- Schedule a job to delete old records every hour
SELECT cron.schedule('cleanup-old-logs', '0 * * * *',
    $$DELETE FROM logs WHERE created_at < now() - interval '30 days'$$
);

-- Schedule a job to refresh a materialized view every 15 minutes
SELECT cron.schedule('refresh-dashboard', '*/15 * * * *',
    $$REFRESH MATERIALIZED VIEW CONCURRENTLY dashboard_stats$$
);

-- List scheduled jobs
SELECT * FROM cron.job;

-- Unschedule a job
SELECT cron.unschedule('nightly-vacuum');

Other Essential Extensions

-- pg_stat_statements: query performance tracking
CREATE EXTENSION pg_stat_statements;
SELECT query, calls, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

-- uuid-ossp or pgcrypto: UUID generation
CREATE EXTENSION "uuid-ossp";
SELECT uuid_generate_v4();

-- pgcrypto: cryptographic functions
CREATE EXTENSION pgcrypto;
SELECT crypt('password', gen_salt('bf'));

-- pg_trgm: trigram-based text similarity and fuzzy search
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_name_trgm ON users USING GIN (name gin_trgm_ops);
SELECT name FROM users WHERE name % 'johm';  -- fuzzy match

-- hstore: key-value store in a single column
CREATE EXTENSION hstore;

Implementation Patterns

Extension Version Pinning

-- Install a specific version
CREATE EXTENSION pgvector VERSION '0.7.0';

-- Check upgrade path
SELECT * FROM pg_extension_update_paths('pgvector');

-- Controlled upgrade
ALTER EXTENSION pgvector UPDATE TO '0.8.0';

Schema Isolation

-- Keep extensions in a dedicated schema
CREATE SCHEMA extensions;
CREATE EXTENSION postgis SCHEMA extensions;

-- Add to search path for convenience
ALTER DATABASE mydb SET search_path = public, extensions;

Best Practices

• Always use IF NOT EXISTS when creating extensions in migration scripts to ensure idempotency.
• Pin extension versions in production and test upgrades in staging first.
• Use pg_stat_statements in every production database for query performance visibility.
• Place extensions in a dedicated schema to keep the public schema clean.
• Review extension dependencies before upgrading (SELECT * FROM pg_depend WHERE ...).
• For pgvector, choose HNSW indexes for better recall and IVFFlat for faster index builds on very large datasets.

Common Pitfalls

• Forgetting shared_preload_libraries: Extensions like pg_stat_statements and pg_cron require entries in shared_preload_libraries in postgresql.conf and a server restart.
• Extension ownership: Extensions are owned by the installing superuser. Use ALTER EXTENSION ... OWNER TO if needed.
• pgvector dimension limits: Vectors are limited to 2000 dimensions by default. For larger vectors, consider dimensionality reduction.
• PostGIS SRID mismatches: Always ensure consistent SRIDs across geometry columns and queries. Mixing SRIDs silently produces wrong results.
• pg_cron running on replica: pg_cron only runs on the primary; jobs will not execute on standby servers.

Anti-Patterns

Over-engineering for hypothetical scale. Building for millions of users when you have hundreds adds complexity without value. Solve today's problems first.

Ignoring the existing ecosystem. Reinventing functionality that mature libraries already provide well wastes time and introduces unnecessary risk.

Premature abstraction. Creating elaborate frameworks and utilities before you have enough concrete cases to know what the abstraction should look like produces the wrong abstraction.

Neglecting error handling at boundaries. Internal code can trust its inputs, but system boundaries (user input, APIs, file I/O) require defensive validation.

Skipping documentation for obvious code. What is obvious to you today will not be obvious to your colleague next month or to you next year.