Create Extensions

Quick Start

After installing PostgreSQL extensions, you can enable (create) them using the CREATE EXTENSION statement:

CREATE EXTENSION vector;      -- Enable vector database extension (no explicit loading required)
CREATE EXTENSION timescaledb; -- Enable time-series database extension (explicit loading required)

Some extensions have dependencies on other extensions. In such cases, you can either install the dependencies first or use the CREATE EXTENSION CASCADE command to install all dependencies at once.

CREATE EXTENSION documentdb CASCADE; -- create documentdb extension and all its dependencies

You can also specify the schema and specific version in the command.

Configure

Extensions (database logical objects) are logically part of PostgreSQL databases. In Pigsty, you can specify which extensions to be created in a database using pg_databases.

pg_databases:
  - name: postgres
    baseline: supabase.sql
    schemas: [ extensions ,auth ,realtime ,storage ,graphql_public ,supabase_functions ,_analytics ,_realtime ]
    extensions:                                 # Extensions to be enabled in the postgres database
      - { name: pgcrypto  ,schema: extensions } # cryptographic functions
      - { name: pg_net    ,schema: extensions } # async HTTP
      - { name: pgjwt     ,schema: extensions } # json web token API for postgres
      - { name: uuid-ossp ,schema: extensions } # generate universally unique identifiers (UUIDs)
      - { name: pgsodium        }               # pgsodium is a modern cryptography library for Postgres.
      - { name: supabase_vault  }               # Supabase Vault Extension
      - { name: pg_graphql      }               # pg_graphql: GraphQL support
      - { name: pg_jsonschema   }               # pg_jsonschema: Validate json schema
      - { name: wrappers        }               # wrappers: FDW collections
      - { name: http            }               # http: allows web page retrieval inside the database.
      - { name: pg_cron         }               # pg_cron: Job scheduler for PostgreSQL
      - { name: timescaledb     }               # timescaledb: Enables scalable inserts and complex queries for time-series data
      - { name: pg_tle          }               # pg_tle: Trusted Language Extensions for PostgreSQL
      - { name: vector          }               # pgvector: the vector similarity search
      - { name: pgmq            }               # pgmq: A lightweight message queue like AWS SQS and RSMQ

Here, the extensions in the database object is a list where each element can be:

• A simple string representing the extension name, such as vector
• A dictionary that may contain the following fields:
  • name: The only required field, specifying the extension name, which may differ from the extension package name.
  • schema: Specifies the schema for installing the extension, defaults to the first schema in the current dbsu search path, usually the default public.
  • version: Specifies the extension version, defaults to the latest version, rarely used.

If the database doesn’t exist yet, the extensions defined here will be automatically created when creating a cluster or creating a database through Pigsty.

If the database is already created, it’s recommended to manage extension through standard schema migration procedure. You can bookkeeping corresponding changes in the Pigsty inventory to help future migration tasks.

Default

Pigsty create several extensions by default for managed PostgreSQL databases. These extensions are created in the default template1 database and the postgres database. Any newly created database will inherit the extension from template1, so you don’t need extra configure.

You can modify default extensions list by overwritting the pg_default_extensions.

All default extensions are built-in Contrib extensions that come with PostgreSQL, with the sole exception of the pg_repack, which is a third-party extension from PGDG. and pg_repack is crucial for PostgreSQL bloat maintainance, so Pigsty installs it by default and enables it in all databases.

pg_default_extensions:
  - { name: pg_stat_statements ,schema: monitor }
  - { name: pgstattuple        ,schema: monitor }
  - { name: pg_buffercache     ,schema: monitor }
  - { name: pageinspect        ,schema: monitor }
  - { name: pg_prewarm         ,schema: monitor }
  - { name: pg_visibility      ,schema: monitor }
  - { name: pg_freespacemap    ,schema: monitor }
  - { name: postgres_fdw       ,schema: public  }
  - { name: file_fdw           ,schema: public  }
  - { name: btree_gist         ,schema: public  }
  - { name: btree_gin          ,schema: public  }
  - { name: pg_trgm            ,schema: public  }
  - { name: intagg             ,schema: public  }
  - { name: intarray           ,schema: public  }
  - { name: pg_repack } # <-- The only 3rd-party extension created by default

In Pigsty’s design, monitoring-related extensions are created in the monitor schema, while other functional extensions are created in the public schema.

Additionally, the vector database extension pgvector has a special status. It is installed by default in Pigsty (in the pgsql-main alias) and enabled in the placeholder meta database.

Finally, the key extension for implementing CDC (Change Data Capture), the wal2json, is also installed by default, but since it’s an “Extension without DDL”, so it doesn’t appear in pg_default_extensions.

Extensions without DDL

Not all extensions require the CREATE EXTENSION command to be enabled.

In principle, PostgreSQL extensions typically consist of three parts:

• Control file: Contains key metadata, required
• SQL file: Contains SQL statements, optional
• Library file: Contains binary shared libraries (.so, .dylib, .dll), optional

The SQL file is optional, so extensions without an SQL file typically don’t require executing the CREATE EXTENSION command to enable.

For example, the wal2json extension providing CDC extraction capabilities, the pg_stat_statements and auto_explain extensions providing slow query statistics. They only have shared library files and extension .control files, without SQL files, so they don’t need/cannot be enabled through the CREATE EXTENSION command.

Note that not having a CREATE EXTENSION command doesn’t affect whether an extension needs to be LOAD. Some extensions may not have SQL/DDL but still require explicit loading, such as some security, stat, audit-related extensions.

List of Extensions Without DDL

Below is a list of all extensions that don’t require CREATE EXTENSION DDL: