database extensions

PostgreSQL Database Extensions

This module documents how the PostgreSQL base image is extended with custom database extensions (such as vector databases, search engines, and graph databases) in docker_db_postgres.

---

1. Extension Architecture Overview

The postgres-ext image is built on top of the standard PostgreSQL image (postgres-16). It adds utility libraries and compiles advanced indexing/vector tools to support enterprise database search.

Key components of the setup:

• Base image: postgres:16 (or ${BASE_NAMESPACE:+$BASE_NAMESPACE/}postgres-16).
• Target folder permissions: Operates under postgres user account (user 999).
• Utility configs: Sourced helper files /opt/utils/script-utils.sh and /opt/utils/script-setup-pg-ext.sh.

---

2. Package Templates and Version Resolution

To handle PostgreSQL major version dependencies dynamically during package installation:

1. An APT package dependency template /opt/utils/install-list-pgext.tpl.apt contains version variables like postgresql-${PG_MAJOR}-pgvector.
2. The Dockerfile runs envsubst to replace ${PG_MAJOR} with the active version (e.g. 16):

   envsubst < /opt/utils/install-list-pgext.tpl.apt > /opt/utils/install-list-pgext.apt

3. Installs the dynamically generated APT list using install_apt.

---

3. Extension Management Stacks

Extensions are installed using three different methods:

Method A: PostgreSQL Package Managers

• pgxman: Runs pgxman install for quick package integrations.
• pgxnclient: Installed via pip; fetches, builds, and registers extensions via PGXN (PostgreSQL Extension Network).

Method B: Native APT Packages

Installs package-supported extensions (such as postgis, pgrouting, pg-cron, pgaudit, and plpython3) directly via system packages resolved using envsubst.

Method C: Custom Source Compiles

Advanced extensions that require custom compilations are managed by individual script functions:

• setup_pg_search: Installs Rust-based BM25 search extensions.
• setup_pgroonga: Compiles Roonga full-text search engine plugins.
• setup_pgvectorscale: Compiles high-performance vector search indexes.
• setup_apache_age: Compiles graph database query processors.
• setup_pg_duckdb: Integrates DuckDB query engine for analytical queries.
• setup_pg_net: Sets up asynchronous HTTP requests scheduler.

---

4. Compilation Cleanups

To keep final database image sizes clean and prevent layer bloat:

1. Compile dependencies like postgresql-server-dev-${PG_MAJOR} and ninja-build are installed temporarily.
2. Once custom extensions are compiled and their .so objects and .control descriptors are written to /usr/share/postgresql/*/extension/, the development headers are removed:

   apt-get remove -y postgresql-server-dev-${PG_MAJOR} ninja-build

3. The build completes by invoking install__clean.