How to Use PostgreSQL with pgvector and TimescaleDB Extensions in LabNow AI

LabNow AI provides a pre-configured PostgreSQL container image that ships with pgvector and TimescaleDB built-in, enabling vector similarity search and time-series analytics through environment variables or SQL commands.

The labnow-ai/lab-foundation repository maintains a specialized database layer that eliminates manual extension compilation. By using PostgreSQL with custom extensions like pgvector and timescaledb in LabNow AI, data teams can deploy production-ready vector stores and time-series databases without managing complex build dependencies or shared library configurations.

How LabNow AI Packages PostgreSQL Extensions

The foundation repository builds a custom PostgreSQL image through docker_db_postgres/postgres-ext.Dockerfile, which orchestrates multiple setup scripts to compile and register extensions directly into the container filesystem.

The Custom Dockerfile Architecture

The Dockerfile located at docker_db_postgres/postgres-ext.Dockerfile executes a multi-stage build process that copies a root filesystem and runs helper scripts during the image construction phase. The build invokes script-setup-pg-ext.sh and script-setup-pg-ext-mirror.sh between lines 11 and 27, ensuring compiled binaries are installed under /usr/share/postgresql/${PG_MAJOR}/extension/ before the final image is sealed.

pgvector Installation via setup_pgvectorscale

The pgvector extension (delivered through the pgvectorscale package providing both vector and vectorscale extensions) is installed by the setup_pgvectorscale() function in docker_db_postgres/rootfs/opt/utils/script-setup-pg-ext.sh. This function fetches the latest release from GitHub, extracts the distribution zip, and installs the resulting .deb files at lines 38 through 46. The script additionally references postgres-${PG_MAJOR}-pgvector from the auto-generated package list in install-list-pgext.tpl.apt at line 28.

TimescaleDB Repository Configuration

TimescaleDB is integrated through an APT repository configured by docker_db_postgres/rootfs/opt/utils/script-setup-pg-ext-mirror.sh. The add_apt_source function at lines 32 through 37 registers the TimescaleDB package source, allowing the Dockerfile's install_apt helper to pull the extension binaries during the build process.

Running the PostgreSQL Container with Extensions

Once built, the container supports two distinct methods for activating extensions: pre-loading at startup for global availability, or on-demand creation per database session.

Pre-loading Extensions with PG_PRELOAD_LIBS

For applications requiring extensions to be available in every connection without explicit CREATE EXTENSION commands, set the PG_PRELOAD_LIBS environment variable when starting the container. As documented in docker_db_postgres/README.md at lines 25 through 27, this variable accepts a comma-separated list of extension names to load into shared memory at server startup.

docker run -d \
    --name labnow-db \
    -p 15432:5432 \
    -e POSTGRES_DB=labnow \
    -e POSTGRES_PASSWORD=postgres \
    -e PG_PRELOAD_LIBS=pgvector,timescaledb \
    labnow/postgres-ext:latest

On-Demand Extension Creation

If you prefer granular control over extension availability, omit PG_PRELOAD_LIBS and create extensions manually after connecting. This approach allows different databases within the same server instance to load different extension sets.

CREATE EXTENSION IF NOT EXISTS vector;       -- Enables pgvector
CREATE EXTENSION IF NOT EXISTS timescaledb;  -- Enables TimescaleDB

Practical Examples for Vector and Time-Series Workloads

Build the image locally using the base namespace and PostgreSQL version arguments defined in the Dockerfile:

docker build -t labnow/postgres-ext \
    -f ./docker_db_postgres/postgres-ext.Dockerfile \
    --build-arg BASE_NAMESPACE=quay.io/labnow0dev \
    --build-arg BASE_IMG=postgres-16 .

Connect to the running container:

docker exec -it labnow-db psql -U postgres -d labnow

Create a vector similarity search table using the vector data type:

CREATE TABLE items (
    id   BIGSERIAL PRIMARY KEY,
    embedding vector(1536)
);

INSERT INTO items (embedding) VALUES
    ('[0.12,0.34,0.56,...]'), ('[0.56,0.78,0.90,...]');

-- L2 distance nearest neighbor search
SELECT id, embedding <=> '[0.10,0.30,0.50,...]' AS distance
FROM items
ORDER BY distance
LIMIT 5;

Implement time-series storage with automatic partitioning via hypertables:

CREATE TABLE sensor_data (
    ts      TIMESTAMPTZ NOT NULL,
    device  TEXT        NOT NULL,
    value   DOUBLE PRECISION
);

SELECT create_hypertable('sensor_data', 'ts');

INSERT INTO sensor_data (ts, device, value)
VALUES (NOW(), 'temp_1', 23.5);

-- Continuous aggregate for downsampled analytics
CREATE MATERIALIZED VIEW sensor_daily_avg
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 minute', ts) AS bucket,
       device,
       avg(value) AS avg_val
FROM sensor_data
GROUP BY bucket, device;

Summary

• LabNow AI distributes a purpose-built PostgreSQL image via docker_db_postgres/postgres-ext.Dockerfile that pre-installs pgvector and TimescaleDB during the build phase.
• The setup_pgvectorscale() function in script-setup-pg-ext.sh handles pgvector compilation from GitHub releases, while add_apt_source in script-setup-pg-ext-mirror.sh configures the TimescaleDB repository.
• Extensions can be pre-loaded globally using the PG_PRELOAD_LIBS environment variable or enabled per-database using standard CREATE EXTENSION SQL commands.
• The resulting container supports 1536-dimensional vector similarity search and automatic time-series partitioning without additional system-level configuration.

Frequently Asked Questions

How do I verify that pgvector and TimescaleDB are installed in my LabNow AI container?

Connect to the database using psql and query the pg_extension catalog table. If the extensions appear in SELECT * FROM pg_extension; or if CREATE EXTENSION vector; executes without error, the binaries are properly installed in /usr/share/postgresql/${PG_MAJOR}/extension/ as configured by the Dockerfile build scripts.

Can I use both pgvector and TimescaleDB in the same database simultaneously?

Yes. The LabNow AI image places both extension libraries in the shared PostgreSQL extension directory. You can load both via PG_PRELOAD_LIBS=pgvector,timescaledb or create them individually with CREATE EXTENSION. There are no conflicts between the vector data types and hypertable functionality.

What PostgreSQL versions does the LabNow AI extension image support?

The Dockerfile uses a PG_MAJOR build argument to target specific PostgreSQL versions (e.g., 16). The install-list-pgext.tpl.apt template dynamically references postgres-${PG_MAJOR}-pgvector, ensuring the correct extension binaries are pulled for your specified base image such as postgres-16.

Do I need to rebuild the image to add additional extensions beyond pgvector and TimescaleDB?

No, if the extension is available via APT. The script-setup-pg-ext-mirror.sh configures multiple repositories, and you can add package names to install-list-pgext.tpl.apt or modify the setup scripts before building. For extensions requiring compilation from source, extend the script-setup-pg-ext.sh with additional setup functions similar to setup_pgvectorscale().

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}