Vector database for AI

quick-vector-db-for-ai.md

Quick Setup: PostgreSQL with pgvector for AI Projects

If you need a working vector database for your AI project in no time, this guide is for you. Setting up PostgreSQL with pgvector is super easy, and Docker makes it even more convenient.

Step 1: Run PostgreSQL with pgvector

PostgreSQL is an excellent database, and Docker images make it easy to spin up a containerized version. To get PostgreSQL with pgvector installed and ready to go, run the following command:

docker run -d --rm \
    -e POSTGRES_DB=ai \
    -e POSTGRES_USER=ai \
    -e POSTGRES_PASSWORD=ai \
    -e PGDATA=/var/lib/postgresql/data/pgdata \
    -v pgvolume:/var/lib/postgresql/data \
    -p 5532:5432 \
    --name pgvector \
    phidata/pgvector:16

This command launches a PostgreSQL instance with pgvector on your localhost, accessible on port 5532. You can connect to it using the connection string:

PG_CONNECTION_STRING=postgresql://ai:ai@localhost:5532/ai

The Phidata team created this image to simplify the setup process. However, if you need additional extensions like PostGIS, you can use another image.

Step 2: Use an Extended PostgreSQL Image

For those needing more functionality, including a variety of useful extensions like PostGIS, you can use the following image:

docker run -d --rm \
    -e POSTGRES_DB=ai \
    -e POSTGRES_USER=ai \
    -e POSTGRES_PASSWORD=ai \
    -e PGDATA=/var/lib/postgresql/data/pgdata \
    -v pgvolume:/var/lib/postgresql/data \
    -p 5532:5432 \
    --name pgvector \
    --platform linux/amd64 \
    ivanlonel/postgis-with-extensions

This image provides the same PostgreSQL setup, but with additional extensions. After running the container, you can enable the extensions with the following SQL commands:

-- Enable pgvector
CREATE EXTENSION vector;

-- Enable PostGIS
CREATE EXTENSION postgis;

-- Verify PostGIS versions
SELECT PostGIS_version();
SELECT PostGIS_full_version();

Step 3: Monitoring PostgreSQL Logs

To keep an eye on what's happening in your PostgreSQL instance, you can follow the logs with this command:

docker logs --follow pgvector

By default, only errors are logged. To log everything, you can start your container with the log_statement=all parameter:

docker run -d --rm \
    -e POSTGRES_DB=ai \
    -e POSTGRES_USER=ai \
    -e POSTGRES_PASSWORD=ai \
    -e PGDATA=/var/lib/postgresql/data/pgdata \
    -v pgvolume:/var/lib/postgresql/data \
    -p 5532:5432 \
    --name pgvector \
    --platform linux/amd64 \
    ivanlonel/postgis-with-extensions \
    -c log_statement=all

Cool, after this is now running use the above docker command to follow all the logs.