PGVector on CloudSQL for GCP

📋 Why Infrastructure as Code May Help with Vector Search

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. While manual setup works well for quick experiments, teams often look for more systematic approaches when moving toward production.

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, which could help maintain consistency across environments and reduce time spent on manual configuration.

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

Comprehensive architecture showing repository structure, workflow, and GCP resources

✨ Key Features

Our PGVector on CloudSQL solution provides several key advantages:

🏗️ Terraform Architecture for PostgreSQL Vector Search

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:

1. 🏝️ A dedicated GCP project created by Terraform providing some isolation between projects
2. 🔒 Custom VPC with private network connectivity for enhanced security
3. 🌐 Cloud SQL PostgreSQL instance with configurable access options (public access limited to dev environments)
4. 🧩 Vector database configuration for storing embeddings
5. 🚦 Environment separation for dev, preprod, and prod following common DevOps practices

📊 What Does the Terraform Setup Do?

The modular configuration in terraform-sql automates the following:

🔐 Security Considerations

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—security considerations are an essential part of any deployment strategy.

🚦 Simplified Deployment: Using the Makefile

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

# Initialize Terraform for your chosen environment
make tf-init ENV=dev

# Review what will be created
make tf-plan ENV=dev

# Apply the changes
make tf-apply ENV=dev

# Create database schema with pgvector extension
make setup-db ENV=dev

# Add sample product data
make add-sample-data ENV=dev

# Verify database setup
make check-db ENV=dev

# Run simple demo queries
make run-simple-demo ENV=dev

# Check current environment settings
make env

# Connect directly to the database
make connect-db ENV=dev

# Clean up when done
make tf-destroy ENV=dev

This Makefile approach attempts to simplify complex commands, which might help team members collaborate more effectively without needing to memorize every technical detail.

🧑‍💻 Post-Deployment: Setting Up pgvector

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:

make setup-db ENV=dev

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);

🤔 Potential Use Cases

This infrastructure can support various vector-based applications:

🧬 The Power of Reproducibility

One potential benefit of Infrastructure as Code is improved reproducibility. Many of us have faced challenges with environment inconsistencies or struggled to remember exact setup steps.

With the Terraform and Makefile approach described here, you might find it easier to:

• ⏱️
  Create similar testing environments more efficiently

  Spin up identical environments in minutes rather than hours of manual configuration

• 🌍
  Deploy to different regions using the same configuration base

  Multi-region deployment becomes a simple parameter change

• 🔁
  Recover from accidental resource deletions by reapplying your configuration

  Disaster recovery becomes a matter of running terraform apply

• 👋
  Onboard new team members by sharing code rather than lengthy setup instructions

  New developers can be productive in hours, not days

This approach could potentially reduce documentation overhead and make collaboration more straightforward, though every team's experience will vary based on their specific needs and existing workflows.

🏆 Production-Ready Deployment Tips

Ready to go beyond the demo and into production? The repository is already structured to support this journey, with separate environments for dev, preprod, and prod. Here are some hard-learned lessons to save you some late-night troubleshooting sessions:

Environment Differences

Deployment Steps

1. Environment progression (follow the path to production):
   • Start with dev environment to test your configuration 🧪
   • Validate in preprod with production-like settings 🔍
   • Apply strict security rules only in prod 🛡️
2. Secure your database (because security matters):
   • In the prod environment variables, restrict authorized_networks to specific IP ranges 🔒
   • Enable Private Service Connect for production deployments 🔐
   • Enable Cloud SQL IAM authentication by setting enable_iam_auth = true in your tfvars 👤
3. Optimize for performance (vector search gets hungry):
   • The repository supports different instance sizes per environment:
     • db-f1-micro for dev testing
     • db-custom-2-7680 for preprod
     • db-custom-4-15360 for production workloads 🏋️
   • Vector search loves RAM—use the high-memory options in the variables 🧠
   • Benchmark with real data in preprod before going to production ⏳
4. Manage secrets properly (no passwords in GitHub, please):
   • The repository includes .gitignore for .tfvars files 🚫
   • Use environment variables for sensitive data in CI/CD pipelines 🔑
   • Consider using the google_secret_manager_secret resource 🗝️
   • Use Terraform's sensitive = true marking for outputs 🙈
5. Use the environment-specific Makefiles:
   • Follow the principle of "make it easy to do the right thing" 🎯
   • Take advantage of the input validation built into the modules 🔍
6. Monitoring saves lives (or at least your weekend):
   • Set up Cloud Monitoring alerts before you need them 🚨
   • Enable the automated backups using the backup_configuration in the SQL module 💾
   • Consider read replicas for high-traffic applications by setting read_replica_size 📚

🎉 Conclusion

By automating your vector search infrastructure with Terraform and Makefiles, you might find your workflow becoming more streamlined and reproducible. Rather than clicking through console interfaces and trying to remember all the steps, this approach offers a way to document and version your infrastructure setup.

When your database configuration is defined in code, it becomes something you can collaboratively work on, review, and improve over time – a shared resource for your team.

Infrastructure as code can be a valuable approach that helps bridge the gap between experimental demos and production-ready systems. For vector search applications where configuration details can significantly impact performance, this approach might save you considerable time and reduce potential errors.

📚 Additional Resources

• Official PGVector Documentation
• Cloud SQL Documentation
• Terraform Documentation
• View Complete Project Repository

We'd welcome your feedback, suggestions, or contributions to help improve this project.