PostgreSQLExtensions

vector (pgvector)

TLDR: Adds a vector column type and distance operators for approximate nearest neighbor (ANN) search over embeddings.

When to use

• Fixed-length numeric vectors (embeddings, spectra fingerprints, latent states).
• “Find the k most similar rows” is a core query.
• Vectors in the same table as labels, timestamps, and JSON metadata.

When not to use

Billion-scale ANN with strict sub-10ms SLA — you may later shard or add a dedicated vector service. For thousands to low millions of rows, pgvector is often sufficient.

CREATE EXTENSION vector;

CREATE TABLE compounds (
  id serial PRIMARY KEY,
  smiles text,
  fingerprint vector(2048)
);

SELECT id, smiles, fingerprint <=> $1 AS distance
FROM compounds
ORDER BY distance
LIMIT 20;

age (Apache AGE)

TLDR: Adds a labeled property graph and openCypher inside PostgreSQL — graph pattern matching without a separate Neo4j server.

When to use

• Relationships are as important as entities (who traded with whom, what cites what, what reacts with what).
• Multi-hop queries: “friends of friends”, “supply chain depth 3”, “path from concept A to B”.
• Graph + SQL joins in one transaction.

When not to use

Pure trees (use ltree or adjacency list). Massive graph analytics at internet scale with dedicated ops team — consider a dedicated graph engine.

graph LR
  M1(("Molecule A"))
  R(("Reaction"))
  M2(("Molecule B"))
  M1 -->|REACTS_IN| R -->|PRODUCES| M2
            

CREATE EXTENSION age;
LOAD 'age';

SELECT * FROM cypher('science_graph', $$
  MATCH (a:Molecule {name: 'benzene'})-[:REACTS_IN*1..3]-(b:Molecule)
  RETURN DISTINCT b.name
$$) AS (name agtype);

Note: AGE implements a subset of openCypher. Complex graph algorithms (PageRank at huge scale) may still belong in batch frameworks — but traversal and pattern match often belong here.

pg_trgm

TLDR: Trigram similarity — match strings that are almost equal (typos, formatting differences).

When to use

• User-facing search over names, symbols, or labels with inconsistent spelling.
• Deduplication: “are these two instrument IDs the same?”
• Joining messy external data to a clean registry.

CREATE EXTENSION pg_trgm;

SELECT name, similarity(name, 'Hilbert space') AS sim
FROM papers
WHERE name % 'Hilbert space'
ORDER BY sim DESC
LIMIT 10;

Pitfall: Short tokens (nic, ion) can false-positive inside longer words — use word boundaries or minimum similarity thresholds in production.

btree_gin

TLDR: Enables GIN index patterns that combine equality and containment — especially powerful for JSONB keys and multi-column GIN.

When to use

• Heavy filtering on metadata->>'parameter' or nested JSON experiment configs.
• You run WHERE properties @> '{"solvent": "water"}' at scale.

CREATE EXTENSION btree_gin;
CREATE INDEX ON runs USING gin ((config->'solver'));

pg_stat_statements

TLDR: Records aggregate stats per normalized SQL statement — the standard way to find slow queries.

When to use: Any database that matters — notebook-driven chaos eventually becomes someone’s 3am incident.

CREATE EXTENSION pg_stat_statements;
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

pg_cron

TLDR: Cron-style job scheduler that runs SQL inside Postgres.

When to use

• Nightly aggregation, partition maintenance, embedding refresh.
• Schedules versioned next to schema, not scattered on host crontab.

CREATE EXTENSION pg_cron;
SELECT cron.schedule('nightly-rollup', '0 2 * * *', $$CALL rollup_daily()$$);

Security: Restrict who can create jobs; treat like superuser capability.

ltree

TLDR: Stores materialized paths in a tree (root.child.grandchild) with fast ancestor/descendant operators.

When to use

• Strict hierarchies: taxonomy, file paths, bill of materials.
• Simpler than a full graph when edges are only parent→child.

CREATE EXTENSION ltree;
SELECT * FROM nodes WHERE path <@ 'project.detector.run42';

vs AGE: ltree = trees; AGE = arbitrary graphs (cycles, many edge types).

citext

TLDR: Case-insensitive text type — comparisons ignore case without LOWER() everywhere.

When to use: Symbols, gene names, handles where case drift causes duplicate rows.

timescaledb

TLDR: First-class time-series — automatic partitioning by time, retention policies, compression.

When to use

• High ingest rate of (timestamp, value, tags) — market data, sensors, lab instruments.
• Queries are mostly “range over time” + aggregates.

CREATE EXTENSION timescaledb;
SELECT create_hypertable('sensor_readings', 'time');

Built-in full-text search (tsvector / tsquery)

TLDR: Keyword search with tokenization and ranking — no extension required.

When to use: Document bodies, abstracts, log messages — exact and stemmed word match, not embedding similarity.

CREATE INDEX ON papers USING gin (to_tsvector('english', abstract));
SELECT title FROM papers
WHERE to_tsvector('english', abstract) @@ plainto_tsquery('english', 'topological insulator');

rum / pg_bm25

TLDR: Full-text indexes with BM25-style relevance — often better ranking than ts_rank_cd.

When to use: Large corpora where keyword relevance quality matters (literature, regulations, patents).

hypopg

TLDR: Hypothetical indexes — planner considers them in EXPLAIN without building them.

When to use: Before adding heavy indexes on production tables; compare plan cost estimates.

pg_repack

TLDR: Rebuilds tables online to reclaim space from update/delete bloat.

When to use: Append-heavy or churn-heavy tables (event logs, archival stores) when VACUUM is not enough.

postgis

TLDR: Spatial types and predicates — distance, containment, intersections on Earth or projected CRS.

When to use: Field sampling sites, microscopy stage coordinates, geographic portfolios.

CREATE EXTENSION postgis;
SELECT id FROM sites
WHERE ST_DWithin(geom, ST_MakePoint(-73.98, 40.75)::geography, 5000);