Skip to content

Default Extensions

PostgreSQL extensions installed by default in Pigsty
PostgreSQL extensions installed by default in Pigsty

Pigsty installs and enables some core extensions by default when initializing PostgreSQL clusters.

Default Installed Extensions

Extensions installed by default via pg_packages:

Extension Description
pg_repack Handle table bloat online, important maintenance tool
wal2json Logical decoding outputs JSON format changes, commonly used in CDC scenarios
pgvector Vector data type and indexes, installed with pgsql-main by default

The default value of pg_extensions is an empty array []. Declare additional extensions as needed, for example:

Extension Description
postgis Geospatial database extension
timescaledb Time-series database extension
pgvector Vector data type and indexes

Default Enabled Extensions

Extensions enabled by default in all databases via pg_default_extensions:

Extension Schema Description
pg_stat_statements monitor SQL statement execution statistics
pgstattuple monitor Tuple-level statistics
pg_buffercache monitor Buffer cache inspection
pageinspect monitor Page-level inspection
pg_prewarm monitor Relation prewarming
pg_visibility monitor Visibility map inspection
pg_freespacemap monitor Free space map inspection
postgres_fdw public PostgreSQL foreign data wrapper
file_fdw public File foreign data wrapper
btree_gist public B-tree GiST operator classes
btree_gin public B-tree GIN operator classes
pg_trgm public Trigram matching
intagg public Integer aggregator
intarray public Integer array functions
pg_repack repack Online table reorganization

These extensions provide basic monitoring, operations, and feature enhancement capabilities.

Default Preloaded Extensions

Extensions preloaded by default into shared_preload_libraries via pg_libs:

Extension Description
pg_stat_statements Track execution statistics of all SQL statements
auto_explain Automatically log execution plans for slow queries

These two extensions provide basic observability and are strongly recommended to keep.

Customize Default Extensions

You can customize default installed and enabled extensions by modifying configuration parameters:

all:
  vars:
    # Modify default extension packages
    pg_packages:
      - pgsql-main pgsql-common
      - pg_repack_$v* wal2json_$v*

    # Modify default installed extensions
    pg_extensions: [ postgis, timescaledb, pgvector ]

    # Modify default preloaded extensions
    pg_libs: 'timescaledb, pg_stat_statements, auto_explain'

    # Modify default enabled extensions
    pg_default_extensions:
      - { name: pg_stat_statements, schema: monitor }
      - { name: pg_repack }
      # ... add more

For detailed extension usage, please refer to: