Postgres extension recommendations

Mortar uses Postgres as storage for the database primitive. The Postgres standard library is enough on its own, but a few extensions noticeably improve the quality of Mortar users’ applications. This page lists extensions the Mortar team has actively tested + recommends enabling, with each entry covering purpose, how to install, and when to use it.

Recommended extension list

Extension	Use	When to install
`pgvector`	Vector embeddings (RAG / semantic search)	Any AI application
`pg_partman`	Auto-partition time-series tables	Single table > 10M rows
`pg_search`	Full-text search (simpler alternative to `tsvector`)	FTS traffic < 10 QPS
`pg_cron`	In-database cron scheduling	When business logic is tightly bound to schema
`pg_stat_statements`	Slow-query analysis	Mandatory in every production environment
`pgaudit`	DDL / data-access audit	Cyber-security / SOC 2 compliance requirements
`pg_trgm`	Fuzzy matching (typo-tolerant search)	Mixed Chinese-Pinyin search
`zhparser`	Chinese tokenization (pairs with `pg_search` / `tsvector`)	Chinese FTS

How to enable

Mortar’s bundled RDS instance doesn’t auto-enable any extensions — turn them on as needed. The SaaS shared cluster (current default) lets users self-serve CREATE EXTENSION on these extensions. Dedicated RDS (planned, Large+ tier) + BYOC (planned) customers’ DBAs get fuller permissions.

-- Under your project tenant schema
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- pg_stat_statements requires superuser; the Mortar platform pre-installs it on every shared pool

Use cases + examples

pgvector — semantic search + RAG

See rag-cookbook for the full version. Short:

CREATE EXTENSION vector;
ALTER TABLE docs ADD COLUMN embedding vector(1536);
CREATE INDEX ON docs USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

-- top-K nearest
SELECT id, content FROM docs ORDER BY embedding <=> '[0.1, 0.2, ...]' LIMIT 10;

pg_partman — time-series partitioning

CREATE EXTENSION pg_partman;
SELECT partman.create_parent(
  'public.events',
  'created_at',
  'native',
  'monthly'
);

Fits insert-only tables queried by time, like events / audit_log / messages. Reduces index size, speeds up queries like WHERE created_at > now() - interval '7 days'.

pg_trgm — fuzzy search

CREATE EXTENSION pg_trgm;
CREATE INDEX ON users USING gin (display_name gin_trgm_ops);

SELECT * FROM users WHERE display_name % 'zhang san' ORDER BY similarity(display_name, 'zhang san') DESC LIMIT 10;

Friendly to mixed Chinese/English + typos. Combine with zhparser for Chinese searches.

zhparser — Chinese tokenization

CREATE EXTENSION zhparser;
CREATE TEXT SEARCH CONFIGURATION cn (PARSER = zhparser);
ALTER TEXT SEARCH CONFIGURATION cn ADD MAPPING FOR n,v,a,i,e,l WITH simple;

-- Usage
SELECT * FROM articles WHERE to_tsvector('cn', body) @@ to_tsquery('cn', '深度学习 & 神经网络');

Trade-offs vs pg_search: native tsvector is slow but simple; pg_search is fast but needs a dedicated worker. For medium scale (< 10M rows), pick native + zhparser.

pg_stat_statements — mandatory

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

The slow-query hit list. Install on every production environment without thinking.

Extension	Why not
`plv8`	JavaScript in Postgres — if you want to run business logic in JS, use Mortar’s `compute` primitive (Aliyun FC / Tencent SCF) instead of stuffing it into the database.
`postgres_fdw`	Cross-database joins — usually a data-architecture problem, not a SQL problem; slicing into proper primitive boundaries is more valuable.
`pg_hint_plan`	Forced query plans — most performance issues are missing indexes + wrong data shape, not planner bugs; run EXPLAIN first.

Planned extension support

Multi-region (planned): each region has its own Postgres; extension config syncs. Enterprise (planned): customers bring their own RDS and can install any extension; the Mortar control plane only inspects the pg_extension system view to detect what’s enabled, without enforcing versions.

References

rag-cookbook — the full pgvector RAG flow
full-text-search — FTS + zhparser
architecture § database primitive