lthieu's notes

  • SF Pro
  • SF Rounded
  • Source Serif
  • MonoLisa
  • Script

Code

  • MonoLisa
  • JetBrains Mono

postgres-extensions-ecosystem

4 min read

Postgres Extensions Ecosystem — Map

Cheatsheet: tên extension → mục đích → khi nào dùng. Bookmark nếu làm Postgres serious.

Tags: attachment postgres extensions Liên quan: All Postgres tuần chapters

1. Bundled (ship with Postgres core)

Standard contrib/ extensions, available without extra install.

ExtensionPurposeTuần liên quan
pg_stat_statementsQuery attribution + perf statsTuan-09-DB-Observability-Tuning
pgcryptoEncryption functionsTuan-Bonus-DB-Security-Compliance
pg_trgmTrigram fuzzy text matchingTuan-03-Indexing-Mastery
btree_ginB-tree ops in GINTuan-03-Indexing-Mastery
btree_gistB-tree ops in GiSTTuan-03-Indexing-Mastery
hstoreKey-value typeTuan-02-Schema-Design-Normalization
citextCase-insensitive textTuan-02-Schema-Design-Normalization
uuid-osspUUID generationTuan-02-Schema-Design-Normalization
pgstattupleTuple-level stats (bloat)Tuan-01-DB-Internals-Refresh
pg_buffercacheInspect buffer poolTuan-01-DB-Internals-Refresh
pageinspectInspect page contentsTuan-01-DB-Internals-Refresh
pg_visibilityVM inspectionTuan-09-DB-Observability-Tuning
tablefunccrosstab, normal_rand—
intarrayInteger array ops—
ltreeHierarchical tree pathsTuan-02-Schema-Design-Normalization
cubeMulti-dim cube type—
earthdistanceLat/lon distance (deprecated → PostGIS)—
tsm_system_rowsTABLESAMPLE method—
unaccentRemove accents (search)Tuan-13-Search-Engines-ES
dblinkCross-DB queries (legacy)—
postgres_fdwForeign data wrapper to PG—
file_fdwRead files as tables—
auto_explainAuto log slow query plansTuan-09-DB-Observability-Tuning
pg_freespacemapFSM inspection—

Install:

CREATE EXTENSION pg_stat_statements;
-- Some need shared_preload_libraries

2. Geospatial

ExtensionPurposeNotes
PostGISFull GIS — points, polygons, routing, rasterThe gold standard; OSS
pgroutingRouting on PostGIS networksNetwork analysis
h3-pgH3 hexagonal grid (Uber)Spatial indexing
mobilitydbMoving objectsNiche

PostGIS is must-have if any spatial query. Install:

CREATE EXTENSION postgis;

3. Time-series

ExtensionPurposeNotes
TimescaleDBHypertables, continuous aggregatesTuan-Bonus-Time-Series-DB
pg_partmanTime-based partition automationTuan-Bonus-Postgres-Partitioning
timescaledb_toolkitHyperfunctions (statistical)With TimescaleDB

4. Vector / AI

ExtensionPurposeNotes
pgvectorVector type + HNSW/IVF indexTuan-15-Vector-DB-AI
pgvectorscaleFaster vector index (StreamingDiskANN)Timescale fork
pgaiLLM-in-DB callsTimescale, experimental
vectorOpenAI embeddings inlineTuned for AI workloads

5. Search / Text

ExtensionPurposeNotes
pg_trgmTrigram (bundled)Fuzzy match
unaccentAccent normalize (bundled)Search prep
RUMBetter full-text + rankingFaster than GIN for FTS
pg_searchElasticSearch-like FTSParadeDB; modern alternative to ES for small data

6. Performance / Observability

ExtensionPurposeNotes
pg_stat_statementsBundled — query statsMust-have
auto_explainBundled — auto plan logMust-have prod
pgauditCompliance audit logHIPAA/SOX/PCI
pg_stat_kcacheCPU + I/O per queryLinux only
pg_wait_samplingWait event samplingMore detail than pg_stat_activity
pgsentinelActive Session History (Oracle-like)Wait sampling + history
hypopgHypothetical indexes (test before create)Save build time
pg_hint_planForce planner hintsControversial; emergency only
pg_qualstatsTrack WHERE clause statsSuggest indexes
powaPerformance Workload Analyzer (UI for many extensions)All-in-one
pg_stat_monitorAggregated query stats (Percona)Enhanced pg_stat_statements

7. Operations / Maintenance

ExtensionPurposeNotes
pg_repackOnline table reorganizationBloat fix without lock
pg_squeezeOnline table reorganization (alternative)—
pg_partmanPartition managementTuan-Bonus-Postgres-Partitioning
pg_cronIn-DB cron schedulerSchedule maintenance
pg_jobsJob schedulingAlternative to pg_cron
pgBackRestBackup tool (external, not extension)Tuan-07-Backup-PITR-DR
pg_dirtyreadRead dead tuples (recovery)Emergency only
pgttGlobal temp tablesOracle-like behavior

8. Compatibility / Migration

ExtensionPurposeNotes
orafceOracle compat functionsMigration aid
tds_fdwFDW for SQL Server / SybaseMigration
mysql_fdwFDW for MySQLFederation
oracle_fdwFDW for OracleFederation
redis_fdwFDW for Redis—
mongo_fdwFDW for MongoDB—
parquet_fdwRead Parquet filesLakehouse integration
pg_sqliteRead SQLite—

9. Replication / Distribution

ExtensionPurposeNotes
pglogicalLogical replication (richer than core)Selective replication
CitusDistributed Postgres (sharding)Microsoft; horizontal scale
BDR (2nd Quadrant)Bi-Directional ReplicationEDB commercial

10. Security

ExtensionPurposeNotes
pgcryptoBundled — encryptionColumn encryption
pgauditAudit loggingCompliance
pg_tleTrusted Language Extensions (AWS)Safely install user extensions
anonData anonymizationGDPR
set_userAudit role changesTrack admin actions

11. Specialized Workloads

ExtensionPurposeNotes
hllHyperLogLog cardinality estimationApproximate counts
topnTop-N aggregateApproximate top
tdigestt-digest percentile estimationStreaming percentiles
bloomBloom filter indexMulti-column equality
segFloating-point interval typeNumeric ranges
prefixPrefix range typeURL prefix matching
pgmqMessage queue (like SQS/Kafka)DB-native queue
pg_graphqlGraphQL endpoint from schemaSupabase
pgRoutingRouting graph algorithmsWith PostGIS
pg_ivmIncremental materialized viewsReal-time CAGG (community)

12. Cloud / Vendor-specific

AWS RDS for Postgres

  • aws_s3, aws_lambda, aws_commons — AWS integration
  • pg_tle — safe user extensions
  • Standard extensions OK

Google Cloud SQL

  • google_columnar_engine (experimental)
  • Standard extensions OK

Azure Database for Postgres

  • Standard extensions

Supabase

  • pg_graphql, pg_jsonschema, pgsodium, pgmq, pgvector — bundled
  • Plus standard extensions

13. Extension Decision Tree

flowchart TD
    Need[Need extension for?] --> Q1{Query monitoring?}
    Q1 -->|Yes| E1[pg_stat_statements + auto_explain]

    Need --> Q2{Geospatial?}
    Q2 -->|Yes| E2[PostGIS]

    Need --> Q3{Time-series?}
    Q3 -->|Yes| E3[TimescaleDB]

    Need --> Q4{Vector / AI?}
    Q4 -->|Yes| E4[pgvector]

    Need --> Q5{Audit / compliance?}
    Q5 -->|Yes| E5[pgaudit]

    Need --> Q6{Sharding?}
    Q6 -->|Yes| E6[Citus]

    Need --> Q7{Backup?}
    Q7 -->|Yes| E7[pgBackRest external tool]

    Need --> Q8{Partition automation?}
    Q8 -->|Yes| E8[pg_partman]

    Need --> Q9{Reduce bloat online?}
    Q9 -->|Yes| E9[pg_repack]

    Need --> Q10{In-DB cron?}
    Q10 -->|Yes| E10[pg_cron]

    Need --> Q11{Test index before create?}
    Q11 -->|Yes| E11[hypopg]

    Need --> Q12{HLL counting?}
    Q12 -->|Yes| E12[hll]

14. Setting up Extensions

14.1 Some require shared_preload_libraries

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements,auto_explain,pgaudit,timescaledb'

Restart needed.

14.2 CREATE EXTENSION

CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pgvector;
CREATE EXTENSION postgis WITH VERSION '3.4';
 
-- Specific schema
CREATE EXTENSION pg_trgm WITH SCHEMA tools;

14.3 Update extensions

ALTER EXTENSION postgis UPDATE TO '3.4.2';
\dx  -- list installed extensions
\dx+ extension_name  -- detail

14.4 Cloud constraints

Managed services (RDS, Cloud SQL, Azure):

  • Limited extension list (must be on vendor’s approved list)
  • Cannot install custom extensions (until pg_tle)
  • Cannot modify shared_preload_libraries freely

→ Check vendor’s supported extension list before designing.

15. Anti-patterns

PatternWhy bad
Many extensions for funOperational complexity
Custom extensions on managed cloudWon’t install; lock-in pain
Forget shared_preload_libraries requirementExtension won’t load
Old extension versions on PG upgradeCompatibility issues
Mix Timescale + native partitioningConflicts
pg_hint_plan in productionHides root cause
Extension dependencies not documentedNew env setup confusion
Extensions in non-public schema without search_pathFunctions not found

For typical web app:

shared_preload_libraries = 'pg_stat_statements,auto_explain,pgaudit'
CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION auto_explain;
CREATE EXTENSION pgaudit;
CREATE EXTENSION pgcrypto;
CREATE EXTENSION pg_trgm;
CREATE EXTENSION btree_gin;
CREATE EXTENSION citext;
CREATE EXTENSION pgstattuple;
CREATE EXTENSION pg_buffercache;
-- Optional: pgvector if AI, postgis if geo, timescaledb if TS

Cập nhật: 2026-05-16. Postgres ecosystem rich — invest in knowing it.

Graph View

Backlinks