# 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`):
   ```bash
   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:
   ```bash
   apt-get remove -y postgresql-server-dev-${PG_MAJOR} ninja-build
   ```
3. The build completes by invoking `install__clean`.