Skip to content

Introduction

Core concepts of PostgreSQL extensions and the Pigsty extension ecosystem

Extensions are the soul of PostgreSQL. Pigsty includes 575 pre-compiled, out-of-the-box extension plugins, fully unleashing PostgreSQL’s potential.

What are Extensions

PostgreSQL extensions are a modular mechanism that allows enhancing database functionality without modifying the core code. An extension typically consists of three parts:

  • Control file (.control): Required, contains extension metadata
  • SQL scripts (.sql): Optional, defines functions, types, operators, and other database objects
  • Dynamic library (.so): Optional, provides high-performance functionality implemented in C

Extensions can add to PostgreSQL: new data types, index methods, functions and operators, foreign data access, procedural languages, performance monitoring, security auditing, and more.

Core Extensions

Among the extensions included in Pigsty, the following are most representative:

Extension Description
PostGIS Geospatial data types and indexes, de facto GIS standard
TimescaleDB Time-series database with continuous aggregates, columnar storage, auto-compression
PGVector Vector data type with HNSW/IVFFlat indexes, essential for AI applications
Citus Distributed database with horizontal sharding capabilities
pg_duckdb Embedded DuckDB analytical engine for OLAP acceleration
pg_search ParadeDB search extension, providing BM25 and full-text search capabilities
Apache AGE Graph database supporting OpenCypher query language
pg_graphql Native GraphQL query support

Most extensions can coexist and even be combined, creating synergistic effects far greater than the sum of their parts.

Extension Categories

Pigsty organizes extensions into 16 categories:

Category Alias Description Typical Extensions
Time-series time Time-series data processing timescaledb, pg_cron, periods
Geospatial gis Geospatial data postgis, h3, pgrouting
Vector rag Vector retrieval and AI pgvector, vchord, pg_vectorize
Search fts Full-text search pgroonga, zhparser, pg_bigm
Analytics olap OLAP and analytics pg_duckdb, pg_mooncake, citus
Feature feat Feature enhancements age, pg_graphql, hll, rum
Language lang Procedural languages plpython3u, pljava, plv8
Type type Data types hstore, ltree, ip4r
Utility util Utility tools http, pg_net, pgjwt
Function func Function libraries pg_uuidv7, topn, tdigest
Admin admin Operations management pg_repack, pg_squeeze, pgagent
Stat stat Monitoring statistics pg_stat_statements, pg_qualstats, auto_explain
Security sec Security auditing pgaudit, pgsodium, pg_tde
FDW fdw Foreign data access postgres_fdw, mysql_fdw, oracle_fdw
Compatibility sim Database compatibility orafce, babelfish
ETL etl Data synchronization pglogical, wal2json, decoderbufs

You can batch install an entire category of extensions using category aliases, for example: pg_extensions: [ pgsql-gis, pgsql-rag ].

Predefined Extension Stacks

Pigsty provides several predefined extension stacks for convenient scenario-based selection:

Stack Included Extensions
gis-stack postgis, pgrouting, pointcloud, h3, q3c, ogr_fdw
rag-stack pgvector, vchord, pgvectorscale, pg_similarity, pg_tiktoken
fts-stack pgroonga, pg_bigm, zhparser, hunspell
olap-stack pg_duckdb, pg_mooncake, timescaledb, pg_partman, plproxy
feat-stack age, hll, rum, pg_graphql, pg_jsonschema, jsquery
stat-stack pg_show_plans, pg_stat_kcache, pg_qualstats, pg_wait_sampling
supa-stack pg_graphql, pg_jsonschema, wrappers, pgvector, pgsodium, vault

Simply use these names in pg_extensions to install the entire stack.

Extension Resources