PGVector on CloudSQL for GCP

Manual cloud setup can sometimes be time-consuming and prone to inconsistencies—a missed configuration step might lead to unexpected behavior or security concerns. When building a vector search application, a RAG system, or a semantic recommendation engine for production, having a reliable infrastructure approach alongside your application code could be beneficial.

Many of us have experienced the initial satisfaction of setting up resources through the Google Cloud Console, only to later struggle when trying to recreate that environment or share the exact setup with teammates. Without version control or documentation, reproducing environments consistently can be challenging.

We've found that combining Terraform for infrastructure definition with Makefiles for operation simplification might help address some common challenges. This approach allows you to define your cloud environment as code and potentially automate deployments.

By turning your vector database stack into code, you get:

Comprehensive architecture showing repository structure, workflow, and GCP resources

Ready to build something cool? Let's get our hands dirty with a complete PostgreSQL setup that has pgvector extensions baked in. No more manual steps or forgotten configurations—just pure, repeatable infrastructure.

The repository is organized using a modular approach with separate environments, making it enterprise-ready from day one:

.
├── 📄 Makefile                # Main command interface for all operations
├── 📁 terraform-sql/          # Terraform configuration for Cloud SQL
│   ├── 📁 environments/       # Environment-specific configurations
│   │   ├── 📁 dev/            # Development environment
│   │   ├── 📁 preprod/        # Pre-production environment
│   │   └── 📁 prod/           # Production environment
│   └── 📁 modules/            # Reusable Terraform modules
│       ├── 📁 project/        # GCP project configuration
│       ├── 📁 sql/            # Cloud SQL instance configuration
│       └── 📁 vpc/            # Network configuration
└── 📁 how_to_tutorials/       # Example applications and demos
    └── 📁 semantic_demo/      # Semantic search demo for product recommendations

Visual overview of the repository structure and modular organization for Terraform-based PGVector deployment

The architecture aims to be straightforward while addressing common infrastructure needs:

The modular configuration in terraform-sql automates the following:

The repository implements different security configurations for each environment. Let's look at the networking module which includes thoughtful firewall rules:

# Allow PostgreSQL access from authorized sources
resource "google_compute_firewall" "allow_postgres" {
  name    = "${var.name_prefix}-allow-postgres"
  network = google_compute_network.vpc_network.self_link

  allow {
    protocol = "tcp"
    ports    = ["5432"]
  }

  # In development, this might be more permissive
  # In production, this should be limited to specific sources
  source_ranges = var.authorized_networks
  target_tags   = ["cloudsql"]
  description   = "Allow PostgreSQL access from authorized networks"
}

# Allow internal traffic within the VPC
resource "google_compute_firewall" "allow_internal" {
  name    = "${var.name_prefix}-allow-internal"
  network = google_compute_network.vpc_network.self_link

  allow {
    protocol = "all"
  }

  source_ranges = [var.subnet_cidr]
  description   = "Allow all internal traffic within the VPC"
}

The repository suggests a progressive security model that allows starting with more permissive settings in development environments while implementing stricter controls in production. We recommend carefully reviewing security settings before deploying to production.

One of the standout features of this project is its streamlined approach using Makefiles. You can deploy and manage your entire vector search infrastructure with simple commands:

# Copy the example environment file
cp .env.example .env
# Edit with your credentials

# Copy the example variables file
cp terraform-sql/environments/dev/terraform.tfvars.example terraform-sql/environments/dev/terraform.tfvars
# Edit terraform.tfvars with your specific configuration values

After your infrastructure is deployed, you need to set up the vector extension. Traditionally this would require multiple manual steps, but our Makefile simplifies this:

This single command connects to your database and runs:

CREATE EXTENSION IF NOT EXISTS vector;

-- Create a database for our application
CREATE DATABASE vector_search;
\c vector_search

-- Create a table for semantic product data
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    category TEXT,
    price DECIMAL(10, 2),
    embedding VECTOR(1536) -- For OpenAI embeddings, adjust as needed
);

-- Create an index for fast similarity search
CREATE INDEX ON products USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

This infrastructure can support various vector-based applications:

Ready to go beyond the demo and into production? Here are some hard-learned lessons to save you some late-night troubleshooting sessions:

Environment Differences

Deployment Steps