Default Extensions

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:

Quick Start: Overview of the extension usage process
Extension Introduction: Core concepts of extensions
Install Extensions: How to install extensions
Configure Extensions: Preloading and parameter configuration
Create Extensions: Creating extensions in databases

Default Extensions

Default Installed Extensions

Default Enabled Extensions

Default Preloaded Extensions

Customize Default Extensions

Was this page helpful?