Extensions

Extensions add capabilities via `CREATE EXTENSION` without forking PostgreSQL. Popular ones:

| Extension | Purpose | |-----------|---------| | pg_trgm | Trigram similarity, fuzzy LIKE | | PostGIS | Geospatial types and queries | | uuid-ossp / pgcrypto | UUID and crypto helpers | | pgvector | Vector similarity for RAG | | citext | Case-insensitive text |

					CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_users_name_trgm ON users USING GIN (name gin_trgm_ops);

SELECT name FROM users
WHERE name % 'jon'  -- similarity operator
ORDER BY similarity(name, 'jon') DESC
LIMIT 10;
				

Extensions affect portability, backup/restore, managed-cloud allowlists, and upgrade paths. Pin versions in migrations and document operational ownership.

On interviews: name an extension tied to a real feature (geo, fuzzy search, vectors), and mention deployment constraints on RDS/Cloud SQL.

Common pitfalls: enabling extensions in prod without staging validation; assuming every host ships PostGIS; mixing extension types with app-level duplicates; no rollback plan when an extension blocks upgrades.

The trade-off is faster feature delivery inside Postgres versus dependency on DBA tooling, cloud support, and migration complexity.

Checklist:

• Map extension to a concrete query need.
• Verify availability on target hosting.
• Version-pin in migration scripts.
• Document restore and upgrade implications.