Extensions introduction

Extensions package optional features into a database—installed per database, not cluster-wide. Enable only what you need and pin versions in production.

Install an extension

CREATE EXTENSION IF NOT EXISTS citext;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email CITEXT NOT NULL UNIQUE
);

citext provides case-insensitive text; pg_trgm powers fuzzy search indexes.

Practice: Apply DDL in a throwaway practice database. Use \d table in psql to verify constraints and indexes.

Listing and removing

SELECT extname, extversion FROM pg_extension;
-- DROP EXTENSION pg_trgm;  -- only when unused

Managed cloud note

Providers allowlist extensions—verify availability before designing on exotic modules. PostGIS teaser appears in the wrap module.

Important interview questions and answers

1. Q: CREATE EXTENSION scope?
   A: Per database—other databases in the cluster need their own CREATE EXTENSION.
2. Q: Superuser requirement?
   A: Many extensions require elevated privileges—managed services expose allowed list.

Self-check

1. How do you see installed extensions?
2. What does citext help with?

Tip: CREATE EXTENSION is per database—repeat on each DB that needs citext or pg_trgm.