OCI PostgreSQL Service provides fully managed PostgreSQL with automatic backups, point-in-time recovery, HA standby, and built-in connection pooling. It includes pgVector for storing and querying vector embeddings directly in Postgres, making it suitable for AI applications that need both relational and semantic search capabilities in the same database. This post covers Terraform provisioning, pgVector schema setup, and production monitoring.

Step 1: PostgreSQL DB System

resource "oci_psql_db_system" "orders_postgres" {
  compartment_id     = var.compartment_id
  display_name       = "orders-postgresql-production"
  db_version         = "16"
  shape              = "PostgreSQL.VM.Standard.E4.Flex.4.64GB"
  instance_count     = 2  # Primary + HA standby
  instance_memory_size_in_gbs = 64
  instance_ocpu_count         = 4

  db_configuration_override_collection {
    items {
      config_key   = "max_connections"
      overridden_config_value = "200"
    }
    items {
      config_key   = "shared_buffers"
      overridden_config_value = "16GB"
    }
    items {
      config_key   = "work_mem"
      overridden_config_value = "64MB"
    }
    items {
      config_key   = "wal_level"
      overridden_config_value = "logical"
    }
  }

  storage_details {
    system_type          = "OCI_OPTIMIZED_STORAGE"
    is_regionally_durable = true
    availability_domain  = "MULTI_AD"
    iops                 = 15000
  }

  network_details {
    subnet_id     = var.db_subnet_id
    nsg_ids       = [var.postgres_nsg_id]
    primary_db_endpoint_private_ip = "10.0.10.50"
  }

  credentials {
    username                    = "pgadmin"
    password_details {
      password_type = "VAULT_SECRET"
      secret_id     = var.postgres_admin_secret_id
      secret_version = 1
    }
  }

  management_policy {
    backup_policy {
      backup_start        = "02:00"
      days_of_the_week    = ["SUNDAY"]
      kind                = "WEEKLY"
      retention_days      = 35
    }
  }

  defined_tags = { "Operations.Environment" = "production", "Operations.ManagedBy" = "terraform" }
}

output "postgres_primary_fqdn" {
  value = oci_psql_db_system.orders_postgres.primary_db_endpoint.fqdn
}

Step 2: pgVector Schema for AI Embeddings

pgVector stores high-dimensional vector embeddings from embedding models alongside your relational data. You can run semantic similarity search, nearest-neighbor queries, and hybrid search combining SQL filters with vector distance in the same query. This eliminates the need for a separate vector database for most AI applications.

-- Enable pgVector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Store product embeddings for semantic search
CREATE TABLE product_embeddings (
  product_id      BIGINT PRIMARY KEY REFERENCES products(id),
  embedding       vector(1536),  -- OpenAI text-embedding-3-small dimensions
  embedded_at     TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- HNSW index for fast approximate nearest-neighbor search
CREATE INDEX product_embeddings_hnsw_idx
  ON product_embeddings
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

-- Hybrid search: combine vector similarity with SQL filters
SELECT
  p.id, p.name, p.price, p.category,
  1 - (pe.embedding
FROM product_embeddings pe
JOIN products p ON p.id = pe.product_id
WHERE
  p.category    = 'electronics'
  AND p.price   < 500
  AND p.in_stock = true
ORDER BY pe.embedding
LIMIT 10;

Step 3: Performance Alarm

resource "oci_monitoring_alarm" "postgres_cpu_high" {
  compartment_id        = var.compartment_id
  display_name          = "postgres-cpu-high"
  is_enabled            = true
  metric_compartment_id = var.compartment_id
  namespace             = "oci_postgresql"
  query                 = "CpuUtilization[5m]{dbSystemId = 'PSQL_OCID'}.mean() > 80"
  severity              = "WARNING"
  pending_duration      = "PT10M"
  destinations          = [var.ops_notification_topic_id]
  body                  = "PostgreSQL CPU above 80% for 10 minutes. Check pg_stat_activity for long-running queries or connection pile-ups."
}

Operational Notes

Use HNSW indexing for production pgVector workloads. IVFFlat is faster to build but slower to query. HNSW has a slower build time but delivers better query performance with tunable recall. Set ef_construction higher for better index quality at the cost of longer build time. For 1536-dimensional OpenAI embeddings at millions of rows, HNSW with m=16 and ef_construction=64 provides a good balance between build time and query latency.

OCI PostgreSQL uses connection pooling with PgBouncer built in. The max_connections PostgreSQL parameter limits backend processes, but PgBouncer allows thousands of client connections to share a smaller pool of backend connections. Set max_connections to match your expected peak backend load (not client connections) and let PgBouncer handle the client multiplexing.

Regards,
Osama

#OCI #OracleCloud #PostgreSQL #pgVector #Terraform #IaC #TechBlog #Oracle #CloudDatabase #AI #VectorDatabase #HighAvailability #OracleCloudInfrastructure #SemanticSearch #AIEmbeddings #HybridSearch #HNSW #NearestNeighbor #ManagedDatabase #GenAI