Mastering Postgresql

PostgreSQL Python Development

Build search, vector similarity, and data-intensive applications with PostgreSQL and Python.

Quick Reference

When NOT to Use This Skill

• DBA tasks: Backup strategies, replication setup, user management, security hardening
• Other databases: MySQL, MongoDB, Redis, Elasticsearch-specific queries
• Schema design: Normalization theory, data modeling patterns
• Stored procedures: PL/pgSQL function development
• Application frameworks: Django ORM specifics, FastAPI integration details

Quick Start Checklist

Copy this checklist to track progress:

Setup Progress:
- [ ] Docker environment running (docker-compose up -d)
- [ ] Connected to database (psql or Python)
- [ ] Extensions created (pgvector, pg_trgm)
- [ ] Table created with search_vector and embedding columns
- [ ] GIN index on search_vector created
- [ ] HNSW index on embedding created
- [ ] Test full-text query returns results
- [ ] Test vector query returns results

Quick Start: Search + Vectors in 5 Minutes

1. Start PostgreSQL with pgvector

# docker-compose.yml
services:
  postgres:
    image: pgvector/pgvector:pg17
    environment:
      POSTGRES_PASSWORD: devpass
    ports: ["5432:5432"]
    volumes: [pgdata:/var/lib/postgresql/data]
volumes:
  pgdata:

docker-compose up -d

# Verify container is running:
docker-compose ps
# Expected: postgres service with status "Up"

2. Enable Extensions

CREATE EXTENSION vector;      -- pgvector for embeddings
CREATE EXTENSION pg_trgm;     -- Trigram for fuzzy search

-- Verify extensions installed:
SELECT extname, extversion FROM pg_extension 
WHERE extname IN ('vector', 'pg_trgm');
-- Expected: 2 rows with version numbers

3. Create Searchable Table with Vectors

CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT,
    embedding vector(1536),
    search_vector tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(content, '')), 'B')
    ) STORED
);

-- Create indexes
CREATE INDEX idx_docs_search ON documents USING GIN (search_vector);
CREATE INDEX idx_docs_embedding ON documents USING hnsw (embedding vector_cosine_ops);

-- Verify indexes created:
SELECT indexname FROM pg_indexes WHERE tablename = 'documents';
-- Expected: idx_docs_search, idx_docs_embedding, documents_pkey

4. Query from Python

import asyncpg

async def search(pool, query: str, embedding: list[float], limit: int = 10):
    return await pool.fetch("""
        SELECT id, title,
               ts_rank(search_vector, websearch_to_tsquery('english', $1)) AS text_rank,
               embedding <=> $2::vector AS vector_dist
        FROM documents
        WHERE search_vector @@ websearch_to_tsquery('english', $1)
        ORDER BY vector_dist
        LIMIT $3
    """, query, embedding, limit)

# Verify connection works:
# pool = await asyncpg.create_pool('postgresql://postgres:devpass@localhost/postgres')
# rows = await pool.fetch("SELECT 1 AS test")
# assert rows[0]['test'] == 1

Decision Trees

Which Search Approach?

Need search? ─┬─► Exact keyword match ──────► B-tree index + WHERE =
              │
              ├─► Full-text search (FTS) ───► tsvector + GIN + ts_rank
              │
              ├─► Relevance like Google ────► pg_search BM25 (ParadeDB)
              │
              ├─► Typo tolerance ───────────► pg_trgm + similarity()
              │
              ├─► Semantic/AI search ───────► pgvector + embeddings
              │
              └─► Hybrid (keywords + semantic) ► Combine tsvector + pgvector

Which Vector Index?

Vector index? ─┬─► Dataset < 100K rows ────► No index (exact search OK)
               │
               ├─► Need best recall ────────► HNSW (slower build, fast query)
               │
               ├─► Fast index build ────────► IVFFlat (needs data first)
               │
               ├─► On AlloyDB ──────────────► ScaNN (Google optimized)
               │
               ├─► On Azure ────────────────► pg_diskann (32x less memory)
               │
               ├─► Billions of vectors ─────► VectorChord vchordrq (self-host)
               │
               └─► Dimensions > 2000 ───────► halfvec or binary quantization

Which Python Library?

Python lib? ──┬─► Sync, simple, stable ─────► psycopg2
              │
              ├─► Async + modern features ──► psycopg3
              │
              ├─► Max async performance ────► asyncpg
              │
              └─► ORM needed ───────────────► SQLAlchemy + asyncpg/psycopg

Which Index Type for Column?

Column type? ─┬─► Scalar (int, text, timestamp) ─► B-tree (default)
              │
              ├─► JSONB ────────────────────────┬► GIN (general queries)
              │                                 └► GIN jsonb_path_ops (@> only)
              │
              ├─► Array ────────────────────────► GIN
              │
              ├─► tsvector ─────────────────────► GIN (or GiST for updates)
              │
              ├─► vector ───────────────────────► HNSW or IVFFlat
              │
              └─► Range / Geometric ────────────► GiST

Common Patterns

For implementation details, see the reference files:

Index Tuning Quick Reference

HNSW Parameters

CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops) WITH (m=16, ef_construction=100);
SET hnsw.ef_search = 100;  -- Before querying

-- Verify setting applied:
SHOW hnsw.ef_search;

IVFFlat Parameters

CREATE INDEX ON docs USING ivfflat (embedding vector_l2_ops) WITH (lists=100);
SET ivfflat.probes = 10;

Troubleshooting Quick Reference

For detailed troubleshooting, see search-vectors-json.md.

Script Usage

pip install -r scripts/requirements.txt  # Install dependencies first

Example:

python scripts/setup_extensions.py --host localhost --dbname mydb
python scripts/create_search_tables.py --host localhost --dbname mydb
python scripts/health_check.py --host localhost --dbname mydb

Cloud Quick Reference

Serverless options: Neon (scale-to-zero, instant branching) and Supabase (BaaS with auth/real-time) are ideal for dev/test and startups. See cloud-serverless.md.

BM25 Options:

• pg_search (ParadeDB): Production-ready, self-host or ParadeDB managed service
• pg_textsearch (TigerData): Preview status, available on managed PostgreSQL services

See provider-specific files for setup commands: AWS | GCP | Azure

Reference Files

Load these for detailed implementation guidance:

AWS RDS and Aurora Reference

PostgreSQL deployment on AWS RDS and Aurora with pgvector support.

Contents

• Create RDS PostgreSQL Instance
• Create Aurora PostgreSQL Cluster
• Enable pgvector on RDS/Aurora
• RDS Proxy Setup
• Connection Strings

---

Create RDS PostgreSQL Instance

# Create parameter group for extensions
aws rds create-db-parameter-group \
  --db-parameter-group-name pg-vector-params \
  --db-parameter-group-family postgres16 \
  --description "PostgreSQL with vector extensions"

# Modify for pg_stat_statements
aws rds modify-db-parameter-group \
  --db-parameter-group-name pg-vector-params \
  --parameters "ParameterName=shared_preload_libraries,ParameterValue=pg_stat_statements,ApplyMethod=pending-reboot"

# Create RDS instance
aws rds create-db-instance \
  --db-instance-identifier mydb-postgres \
  --db-instance-class db.r6g.large \
  --engine postgres \
  --engine-version 16.4 \
  --allocated-storage 100 \
  --storage-type gp3 \
  --storage-throughput 125 \
  --master-username postgres \
  --master-user-password 'YourSecurePassword123!' \
  --db-parameter-group-name pg-vector-params \
  --vpc-security-group-ids sg-xxxxxxxx \
  --db-subnet-group-name mydb-subnet-group \
  --multi-az \
  --backup-retention-period 7 \
  --publicly-accessible false

---

Create Aurora PostgreSQL Cluster

# Aurora Serverless v2 (recommended for variable workloads)
aws rds create-db-cluster \
  --db-cluster-identifier mydb-aurora \
  --engine aurora-postgresql \
  --engine-version 16.4 \
  --master-username postgres \
  --master-user-password 'YourSecurePassword123!' \
  --serverless-v2-scaling-configuration MinCapacity=0.5,MaxCapacity=16 \
  --vpc-security-group-ids sg-xxxxxxxx \
  --db-subnet-group-name mydb-subnet-group

# Add instance to cluster
aws rds create-db-instance \
  --db-instance-identifier mydb-aurora-instance-1 \
  --db-cluster-identifier mydb-aurora \
  --db-instance-class db.serverless \
  --engine aurora-postgresql

---

Enable pgvector on RDS/Aurora

-- Connect to database
CREATE EXTENSION vector;
CREATE EXTENSION pg_trgm;
CREATE EXTENSION pg_stat_statements;

-- Verify
SELECT extname, extversion FROM pg_extension;

---

RDS Proxy Setup

# Create secret for credentials
aws secretsmanager create-secret \
  --name mydb-credentials \
  --secret-string '{"username":"postgres","password":"YourSecurePassword123!"}'

# Create RDS Proxy
aws rds create-db-proxy \
  --db-proxy-name mydb-proxy \
  --engine-family POSTGRESQL \
  --auth Description="Proxy auth",AuthScheme=SECRETS,SecretArn=arn:aws:secretsmanager:... \
  --role-arn arn:aws:iam::123456789:role/rds-proxy-role \
  --vpc-subnet-ids subnet-xxx subnet-yyy \
  --require-tls

# Register target
aws rds register-db-proxy-targets \
  --db-proxy-name mydb-proxy \
  --db-instance-identifiers mydb-postgres

---

Connection Strings

# Direct connection
conn_string = "postgresql://postgres:password@mydb-postgres.xxxxx.us-east-1.rds.amazonaws.com:5432/mydb"

# Via RDS Proxy
conn_string = "postgresql://postgres:password@mydb-proxy.proxy-xxxxx.us-east-1.rds.amazonaws.com:5432/mydb"

# With SSL (recommended)
conn_string = "postgresql://postgres:password@host:5432/mydb?sslmode=require"

---

Instance Sizing

---

Related References

• cloud-common.md - Extension matrix, pooling, production config
• cloud-gcp.md - GCP Cloud SQL and AlloyDB
• cloud-azure.md - Azure Flexible Server

Azure Database for PostgreSQL Reference

PostgreSQL deployment on Azure Flexible Server with pgvector and pg_diskann support.

Contents

• Create Flexible Server
• Enable Extensions
• pg_diskann Index (Azure Exclusive)
• Enable Built-in PgBouncer
• Connection Strings

---

Create Flexible Server

# Create resource group
az group create --name mydb-rg --location eastus

# Create flexible server
az postgres flexible-server create \
  --resource-group mydb-rg \
  --name mydb-postgres \
  --location eastus \
  --admin-user postgres \
  --admin-password 'YourSecurePassword123!' \
  --sku-name Standard_D4s_v3 \
  --tier GeneralPurpose \
  --storage-size 128 \
  --version 16 \
  --high-availability ZoneRedundant

---

Enable Extensions

# Allow extensions
az postgres flexible-server parameter set \
  --resource-group mydb-rg \
  --server-name mydb-postgres \
  --name azure.extensions \
  --value "vector,pg_trgm,pg_stat_statements"

Then in SQL:

CREATE EXTENSION vector;
CREATE EXTENSION pg_trgm;

---

pg_diskann Index (Azure Exclusive)

DiskANN is Microsoft's disk-based vector index, now GA on Azure PostgreSQL.

Advantages over HNSW:

• 32x lower memory footprint (stores index on SSD)
• Up to 10x lower latency at 95% recall
• 4x lower cost due to reduced compute requirements
• Scales to billions of vectors without RAM constraints

Enable DiskANN

# Enable in Azure Portal: Server parameters -> azure.extensions
# Add both: VECTOR,DISKANN

-- Enable both extensions
CREATE EXTENSION vector;
CREATE EXTENSION diskann;

-- Create DiskANN index
CREATE INDEX idx_docs_diskann ON documents
  USING diskann (embedding vector_cosine_ops);

-- Query uses same syntax as pgvector
SELECT id, title, embedding <=> $1::vector AS distance
FROM documents
ORDER BY embedding <=> $1::vector
LIMIT 10;

HNSW vs DiskANN Comparison

---

Enable Built-in PgBouncer

Azure Flexible Server includes built-in PgBouncer for connection pooling.

# Enable PgBouncer
az postgres flexible-server parameter set \
  --resource-group mydb-rg \
  --server-name mydb-postgres \
  --name pgbouncer.enabled \
  --value true

# Configure pool mode
az postgres flexible-server parameter set \
  --resource-group mydb-rg \
  --server-name mydb-postgres \
  --name pgbouncer.default_pool_size \
  --value 50

---

Connection Strings

# Direct connection (port 5432)
conn_string = "postgresql://postgres:password@mydb-postgres.postgres.database.azure.com:5432/mydb?sslmode=require"

# Via PgBouncer (port 6432)
conn_string = "postgresql://postgres:password@mydb-postgres.postgres.database.azure.com:6432/mydb?sslmode=require"

---

Instance Sizing

---

Related References

• cloud-common.md - Extension matrix, pooling, production config
• cloud-aws.md - AWS RDS and Aurora
• cloud-gcp.md - GCP Cloud SQL and AlloyDB

Cloud Common Reference

Extension availability, connection pooling patterns, production configuration, and cost optimization.

Contents

• Extension Availability Matrix
• Connection Pooling
• Production Configuration
• Monitoring Queries
• Cost Optimization

---

Extension Availability Matrix

Notes:

• pg_search/BM25 requires self-hosted PostgreSQL, ParadeDB, Neon, or Supabase
• pg_diskann (Azure) and alloydb_scann (GCP) are cloud-specific optimized vector indexes

---

Connection Pooling

Pooling Options by Platform

PgBouncer Configuration (Self-Managed)

# pgbouncer.ini
[databases]
mydb = host=db-host port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5

Application-Side Pooling

# asyncpg pool (always recommended)
pool = await asyncpg.create_pool(
    dsn,
    min_size=5,
    max_size=20,
    max_inactive_connection_lifetime=300,
    command_timeout=60
)

# When using PgBouncer, disable prepared statements
pool = await asyncpg.create_pool(
    dsn,
    min_size=5,
    max_size=20,
    statement_cache_size=0  # Required for PgBouncer transaction mode
)

---

Production Configuration

Recommended Parameters

-- Memory (adjust based on instance size)
ALTER SYSTEM SET shared_buffers = '4GB';          -- 25% of RAM
ALTER SYSTEM SET effective_cache_size = '12GB';   -- 75% of RAM
ALTER SYSTEM SET work_mem = '256MB';
ALTER SYSTEM SET maintenance_work_mem = '1GB';

-- Connections
ALTER SYSTEM SET max_connections = 200;

-- Vector workloads
ALTER SYSTEM SET max_parallel_workers_per_gather = 4;
ALTER SYSTEM SET max_parallel_maintenance_workers = 4;

-- WAL
ALTER SYSTEM SET wal_buffers = '64MB';
ALTER SYSTEM SET checkpoint_completion_target = 0.9;

-- Reload
SELECT pg_reload_conf();

Memory Sizing Guidelines

---

Monitoring Queries

Active Connections

SELECT datname, state, count(*)
FROM pg_stat_activity
GROUP BY datname, state;

Slow Queries

SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Index Usage

SELECT relname, seq_scan, idx_scan,
       round(100.0 * idx_scan / nullif(seq_scan + idx_scan, 0), 2) AS idx_pct
FROM pg_stat_user_tables
WHERE n_live_tup > 1000
ORDER BY seq_scan DESC;

Table Bloat

SELECT relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       n_dead_tup,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

---

Cost Optimization

Instance Sizing Guidelines

Cost-Saving Strategies

1. Use Reserved Instances - 30-60% savings for 1-3 year commitment 2. Aurora Serverless v2 - Scale to zero during low usage 3. Read Replicas - Offload read queries 4. Storage Optimization - Use gp3 on AWS, adjust IOPS as needed 5. Right-size Connections - Reduce max_connections if not needed 6. Neon/Supabase for dev - Scale-to-zero eliminates idle costs

Storage Costs

Vector Index Storage Estimate

HNSW index size ≈ rows × dimensions × 4 bytes × 1.5 (overhead)

Example: 1M rows × 1536 dims × 4 × 1.5 = ~9.2 GB

IVFFlat index size ≈ rows × dimensions × 4 bytes × 1.1

DiskANN: Uses disk storage, minimal RAM overhead

---

Related References

• cloud-aws.md - AWS RDS and Aurora
• cloud-gcp.md - GCP Cloud SQL and AlloyDB
• cloud-azure.md - Azure Flexible Server
• cloud-serverless.md - Neon and Supabase

GCP Cloud SQL and AlloyDB Reference

PostgreSQL deployment on GCP Cloud SQL and AlloyDB with pgvector and ScaNN support.

Contents

• Cloud SQL Instance
• Enable Extensions on Cloud SQL
• Cloud SQL Proxy
• AlloyDB Cluster
• ScaNN Index (AlloyDB Exclusive)
• Connection Strings

---

Cloud SQL Instance

# Create instance
gcloud sql instances create mydb-cloudsql \
  --database-version=POSTGRES_16 \
  --tier=db-custom-4-16384 \
  --region=us-central1 \
  --availability-type=REGIONAL \
  --storage-type=SSD \
  --storage-size=100GB \
  --storage-auto-increase \
  --backup-start-time=02:00 \
  --maintenance-window-day=SUN \
  --maintenance-window-hour=03

# Set root password
gcloud sql users set-password postgres \
  --instance=mydb-cloudsql \
  --password='YourSecurePassword123!'

# Create database
gcloud sql databases create mydb --instance=mydb-cloudsql

---

Enable Extensions on Cloud SQL

# Enable pgvector via database flags
gcloud sql instances patch mydb-cloudsql \
  --database-flags=cloudsql.enable_pgvector=on

# For pg_stat_statements
gcloud sql instances patch mydb-cloudsql \
  --database-flags=cloudsql.enable_pg_stat_statements=on

Then in SQL:

CREATE EXTENSION vector;
CREATE EXTENSION pg_trgm;

---

Cloud SQL Proxy

# Install proxy
curl -o cloud-sql-proxy https://storage.googleapis.com/cloud-sql-connectors/cloud-sql-proxy/v2.8.0/cloud-sql-proxy.linux.amd64
chmod +x cloud-sql-proxy

# Run proxy
./cloud-sql-proxy --port 5432 PROJECT_ID:REGION:INSTANCE_NAME

# Or with IAM authentication
./cloud-sql-proxy --auto-iam-authn PROJECT_ID:REGION:INSTANCE_NAME

---

AlloyDB Cluster

AlloyDB offers superior vector performance with ScaNN indexes.

# Create cluster
gcloud alloydb clusters create mydb-alloydb \
  --region=us-central1 \
  --password='YourSecurePassword123!' \
  --network=default

# Create primary instance
gcloud alloydb instances create mydb-primary \
  --cluster=mydb-alloydb \
  --region=us-central1 \
  --instance-type=PRIMARY \
  --cpu-count=4

Enable pgvector and ScaNN on AlloyDB

-- pgvector is pre-installed, just enable
CREATE EXTENSION vector;

-- Enable ScaNN (AlloyDB optimized index)
CREATE EXTENSION alloydb_scann CASCADE;

---

ScaNN Index (AlloyDB Exclusive)

ScaNN provides 10x faster index builds and 4x faster queries compared to HNSW.

-- Create ScaNN index
CREATE INDEX ON documents
  USING scann (embedding cosine)
  WITH (num_leaves=100);

-- With automatic tuning
CREATE INDEX ON documents
  USING scann (embedding cosine)
  WITH (mode='AUTO');

Vector Query (Same syntax as pgvector)

SELECT id, title, embedding <=> $1::vector AS distance
FROM documents
ORDER BY embedding <=> $1::vector
LIMIT 10;

---

Connection Strings

Cloud SQL

# Via Cloud SQL Proxy (localhost)
conn_string = "postgresql://postgres:password@127.0.0.1:5432/mydb"

# Direct private IP (within VPC)
conn_string = "postgresql://postgres:password@10.x.x.x:5432/mydb"

# With Cloud SQL Python Connector
from google.cloud.sql.connector import Connector

connector = Connector()
conn = connector.connect(
    "project:region:instance",
    "asyncpg",
    user="postgres",
    password="password",
    db="mydb"
)

AlloyDB

# Via AlloyDB Auth Proxy
./alloydb-auth-proxy "projects/PROJECT/locations/REGION/clusters/CLUSTER/instances/INSTANCE"

conn_string = "postgresql://postgres:password@127.0.0.1:5432/mydb"

# With Python Connector
from google.cloud.alloydb.connector import Connector

connector = Connector()
conn = await connector.connect_async(
    "projects/project/locations/region/clusters/cluster/instances/instance",
    "asyncpg",
    user="postgres",
    password="password",
    db="mydb"
)

---

Instance Sizing

---

Related References

• cloud-common.md - Extension matrix, pooling, production config
• cloud-aws.md - AWS RDS and Aurora
• cloud-azure.md - Azure Flexible Server

Serverless PostgreSQL Reference

Neon and Supabase for developer-focused PostgreSQL with scale-to-zero and instant branching.

Contents

• Neon
• Supabase
• Serverless vs Traditional Managed
• When to Choose Serverless

---

Neon

Scale-to-zero PostgreSQL with instant database branching.

Connection

# Connect via connection string from Neon console
psql "postgresql://user:pass@ep-cool-name-123456.us-east-2.aws.neon.tech/neondb?sslmode=require"

Enable Extensions

-- Enable pgvector (pre-installed)
CREATE EXTENSION vector;

-- Standard pgvector queries work as-is
SELECT * FROM documents ORDER BY embedding <=> $1::vector LIMIT 10;

Key Features

Best For

• Development and testing environments
• Bursty workloads with idle periods
• CI/CD workflows (branch per PR)
• Cost-sensitive projects

---

Supabase

Backend-as-a-Service with PostgreSQL, auth, real-time, and storage.

Connection

# Connect via connection string from Supabase dashboard
psql "postgresql://postgres:[password]@db.[project-ref].supabase.co:5432/postgres"

Enable Extensions

-- Enable pgvector
CREATE EXTENSION vector;

-- Supabase includes real-time subscriptions on tables
ALTER TABLE documents REPLICA IDENTITY FULL;

Key Features

Best For

• Full-stack applications
• Rapid prototyping
• Apps needing auth + real-time out of the box
• Teams wanting managed backend infrastructure

---

Serverless vs Traditional Managed

---

When to Choose Serverless

Choose Neon when:

• You need scale-to-zero for cost savings
• CI/CD requires database branches per PR
• Workloads are bursty with idle periods
• Development/staging environments

Choose Supabase when:

• You need auth, real-time, storage bundled
• Building full-stack apps quickly
• Want auto-generated REST/GraphQL APIs
• Need edge functions alongside database

Choose Traditional Managed when:

• Enterprise compliance requirements (SOC2, HIPAA)
• Predictable, high-volume workloads
• Need cloud-specific features (ScaNN, DiskANN)
• Existing cloud infrastructure integration

---

Related References

• cloud-common.md - Extension matrix, pooling, production config
• cloud-aws.md - AWS RDS and Aurora
• cloud-gcp.md - GCP Cloud SQL and AlloyDB
• cloud-azure.md - Azure Flexible Server

Python Drivers Reference

Driver selection, connection patterns, pooling, and SQLAlchemy integration for PostgreSQL.

Contents

• Library Selection
• Connection Patterns
• SQLAlchemy Integration
• Error Handling
• Performance Optimization

---

Library Selection

Installation

# psycopg2 (binary for easy install)
pip install psycopg2-binary

# psycopg3
pip install "psycopg[binary,pool]"

# asyncpg
pip install asyncpg

# SQLAlchemy with async
pip install sqlalchemy[asyncio] asyncpg

# pgvector support
pip install pgvector

---

Connection Patterns

psycopg2 (Sync)

import psycopg2
from psycopg2.extras import RealDictCursor
from contextlib import contextmanager

# Single connection
conn = psycopg2.connect(
    host="localhost",
    dbname="mydb",
    user="user",
    password="pass"
)

# Context manager pattern
@contextmanager
def get_cursor(commit=True):
    conn = psycopg2.connect("postgresql://user:pass@localhost/mydb")
    try:
        with conn.cursor(cursor_factory=RealDictCursor) as cur:
            yield cur
        if commit:
            conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()

# Usage
with get_cursor() as cur:
    cur.execute("SELECT * FROM documents WHERE id = %s", (1,))
    result = cur.fetchone()

psycopg2 Connection Pool

from psycopg2 import pool

# Step 1: Create pool
connection_pool = pool.ThreadedConnectionPool(
    minconn=5,
    maxconn=20,
    host="localhost",
    dbname="mydb",
    user="user",
    password="pass"
)

# Step 2: Use pool
conn = connection_pool.getconn()
try:
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM documents")
        results = cur.fetchall()
    conn.commit()
finally:
    connection_pool.putconn(conn)

psycopg3 (Sync and Async)

import psycopg
from psycopg.rows import dict_row

# Sync connection
with psycopg.connect("postgresql://user:pass@localhost/mydb", row_factory=dict_row) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM documents WHERE id = %s", (1,))
        result = cur.fetchone()

# Async connection
import asyncio
from psycopg import AsyncConnection

async def query_async():
    async with await AsyncConnection.connect(
        "postgresql://user:pass@localhost/mydb",
        row_factory=dict_row
    ) as conn:
        async with conn.cursor() as cur:
            await cur.execute("SELECT * FROM documents")
            return await cur.fetchall()

psycopg3 Connection Pool

from psycopg_pool import ConnectionPool, AsyncConnectionPool

# Step 1: Create sync pool
pool = ConnectionPool(
    "postgresql://user:pass@localhost/mydb",
    min_size=5,
    max_size=20
)

# Step 2: Use sync pool
with pool.connection() as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT 1")

# Async pool
async def main():
    # Step 1: Create async pool
    pool = AsyncConnectionPool(
        "postgresql://user:pass@localhost/mydb",
        min_size=5,
        max_size=20
    )
    await pool.open()
    
    # Step 2: Use async pool
    async with pool.connection() as conn:
        async with conn.cursor() as cur:
            await cur.execute("SELECT 1")
    
    # Step 3: Close when done
    await pool.close()

psycopg3 Pool with pgvector Type Registration

Use the configure callback to register pgvector types on every connection:

from psycopg_pool import AsyncConnectionPool
from pgvector.psycopg import register_vector

async def configure_connection(conn):
    """Configure every connection with pgvector types."""
    await register_vector(conn)

pool = AsyncConnectionPool(
    "postgresql://user:pass@localhost/mydb",
    min_size=5,
    max_size=20,
    configure=configure_connection,
    open=False  # Required for async pools
)

FastAPI Lifespan Integration

The recommended pattern for managing pool lifecycle in FastAPI:

from contextlib import asynccontextmanager
from fastapi import FastAPI, Depends
from psycopg_pool import AsyncConnectionPool
from pgvector.psycopg import register_vector

async def configure_conn(conn):
    await register_vector(conn)

pool = AsyncConnectionPool(
    conninfo="postgresql://user:pass@localhost/mydb",
    min_size=5,
    max_size=20,
    configure=configure_conn,
    open=False
)

@asynccontextmanager
async def lifespan(app: FastAPI):
    """Open pool on startup, close on shutdown."""
    await pool.open()
    yield
    await pool.close()

app = FastAPI(lifespan=lifespan)

async def get_db():
    """Dependency for route handlers."""
    async with pool.connection() as conn:
        yield conn

@app.get("/search")
async def search(q: str, conn=Depends(get_db)):
    return await conn.fetch("SELECT * FROM docs WHERE ...")

Pool Health Checking (psycopg3 3.2+)

For production reliability, configure pools to verify connections before serving:

from psycopg_pool import ConnectionPool, AsyncConnectionPool

# Sync pool with built-in health check
pool = ConnectionPool(
    "postgresql://user:pass@localhost/mydb",
    min_size=5,
    max_size=20,
    check=ConnectionPool.check_connection,  # Validates before serving
    max_lifetime=3600.0,   # Close connections after 1 hour
    max_idle=600.0         # Close idle connections after 10 minutes
)

# Async pool with health check
async_pool = AsyncConnectionPool(
    "postgresql://user:pass@localhost/mydb",
    min_size=5,
    max_size=20,
    check=AsyncConnectionPool.check_connection,
    max_lifetime=3600.0,
    max_idle=600.0,
    open=False
)

When to use health checks:

• Production deployments with load balancers
• Databases with idle_session_timeout configured
• Environments where connections may be dropped (cloud, firewalls)

asyncpg (Async Only)

import asyncpg

# Single connection
conn = await asyncpg.connect("postgresql://user:pass@localhost/mydb")
row = await conn.fetchrow("SELECT * FROM documents WHERE id = $1", 1)
await conn.close()

# Connection pool (recommended)
# Step 1: Create pool
pool = await asyncpg.create_pool(
    "postgresql://user:pass@localhost/mydb",
    min_size=5,
    max_size=20,
    command_timeout=60,
    statement_cache_size=100
)

# Step 2: Use pool
async with pool.acquire() as conn:
    rows = await conn.fetch("SELECT * FROM documents LIMIT 10")

# Step 3: Close when done
await pool.close()

# Verify pool is working:
# async with pool.acquire() as conn:
#     result = await conn.fetchval("SELECT 1")
#     assert result == 1

---

SQLAlchemy Integration

Setup with pgvector

from sqlalchemy import Column, Integer, String, Text, create_engine
from sqlalchemy.dialects.postgresql import JSONB, ARRAY, TSVECTOR
from sqlalchemy.orm import declarative_base, sessionmaker
from pgvector.sqlalchemy import Vector

Base = declarative_base()

class Document(Base):
    __tablename__ = 'documents'
    
    id = Column(Integer, primary_key=True)
    title = Column(String(255), nullable=False)
    content = Column(Text)
    metadata = Column(JSONB, default={})
    tags = Column(ARRAY(String), default=[])
    embedding = Column(Vector(1536))
    search_vector = Column(TSVECTOR)

# Sync engine
engine = create_engine("postgresql+psycopg2://user:pass@localhost/mydb")

# Async engine
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
async_engine = create_async_engine("postgresql+asyncpg://user:pass@localhost/mydb")

Vector Queries with SQLAlchemy

from sqlalchemy import select
from sqlalchemy.orm import Session

def find_similar(session: Session, embedding: list, limit: int = 10):
    stmt = (
        select(Document)
        .order_by(Document.embedding.cosine_distance(embedding))
        .limit(limit)
    )
    return session.scalars(stmt).all()

# Async version
from sqlalchemy.ext.asyncio import AsyncSession

async def find_similar_async(session: AsyncSession, embedding: list, limit: int = 10):
    stmt = (
        select(Document)
        .order_by(Document.embedding.cosine_distance(embedding))
        .limit(limit)
    )
    result = await session.execute(stmt)
    return result.scalars().all()

Full-Text Search with SQLAlchemy

from sqlalchemy import func

def search_documents(session: Session, query: str, limit: int = 20):
    ts_query = func.websearch_to_tsquery('english', query)
    stmt = (
        select(
            Document,
            func.ts_rank(Document.search_vector, ts_query).label('rank')
        )
        .where(Document.search_vector.op('@@')(ts_query))
        .order_by(func.ts_rank(Document.search_vector, ts_query).desc())
        .limit(limit)
    )
    return session.execute(stmt).all()

Bulk Operations with SQLAlchemy

from sqlalchemy.dialects.postgresql import insert

# Bulk upsert
stmt = insert(Document).values([
    {"title": "Doc 1", "content": "Content 1"},
    {"title": "Doc 2", "content": "Content 2"},
])
stmt = stmt.on_conflict_do_update(
    index_elements=['title'],
    set_={"content": stmt.excluded.content}
)
session.execute(stmt)
session.commit()

---

Error Handling

Retry Pattern

import asyncio
from functools import wraps
import asyncpg

def with_retry(max_attempts=3, delay=1.0, backoff=2.0):
    """Decorator for automatic retry on connection errors."""
    def decorator(func):
        @wraps(func)
        async def wrapper(*args, **kwargs):
            last_exception = None
            for attempt in range(max_attempts):
                try:
                    return await func(*args, **kwargs)
                except (asyncpg.PostgresConnectionError, 
                        asyncpg.InterfaceError) as e:
                    last_exception = e
                    if attempt < max_attempts - 1:
                        await asyncio.sleep(delay * (backoff ** attempt))
            raise last_exception
        return wrapper
    return decorator

@with_retry(max_attempts=3)
async def query_with_retry(pool, query, *args):
    async with pool.acquire() as conn:
        return await conn.fetch(query, *args)

Connection Recovery

class DatabasePool:
    """Self-healing connection pool wrapper."""
    
    def __init__(self, dsn: str):
        self.dsn = dsn
        self.pool = None
    
    async def get_pool(self):
        if self.pool is None or self.pool._closed:
            self.pool = await asyncpg.create_pool(
                self.dsn,
                min_size=5,
                max_size=20
            )
        return self.pool
    
    async def execute(self, query: str, *args):
        pool = await self.get_pool()
        try:
            async with pool.acquire() as conn:
                return await conn.fetch(query, *args)
        except asyncpg.InterfaceError:
            # Pool corrupted, recreate
            await self.pool.close()
            self.pool = None
            return await self.execute(query, *args)

---

Performance Optimization

Prepared Statements

# asyncpg auto-caches prepared statements
# Explicit preparation for hot paths:
stmt = await conn.prepare("SELECT * FROM documents WHERE id = $1")
for doc_id in doc_ids:
    row = await stmt.fetchrow(doc_id)

Pipeline Mode (psycopg3)

# Reduce round trips for multiple queries
async with conn.pipeline():
    await conn.execute("INSERT INTO logs (msg) VALUES ($1)", ("msg1",))
    await conn.execute("INSERT INTO logs (msg) VALUES ($1)", ("msg2",))
    await conn.execute("INSERT INTO logs (msg) VALUES ($1)", ("msg3",))
# All executed in single round trip

Connection Pool Sizing

Rule of thumb:
max_connections = (core_count * 2) + effective_spindle_count

For SSD:
- Web app: 10-20 connections per app instance
- Background workers: 2-5 per worker

Total: Keep under PostgreSQL max_connections (default 100)

Query Optimization Tips

# 1. Use LIMIT with ORDER BY for vector search
# Bad: fetches all then limits in Python
rows = await conn.fetch("SELECT * FROM docs ORDER BY embedding <=> $1", emb)
results = rows[:10]

# Good: limits in query (uses index)
rows = await conn.fetch("""
    SELECT * FROM docs ORDER BY embedding <=> $1 LIMIT 10
""", emb)

# 2. Avoid SELECT * with large columns (like embedding)
# Bad
rows = await conn.fetch("SELECT * FROM docs WHERE id = $1", doc_id)

# Good
rows = await conn.fetch("""
    SELECT id, title, created_at FROM docs WHERE id = $1
""", doc_id)

# 3. Use EXISTS for presence checks
# Bad
count = await conn.fetchval("SELECT COUNT(*) FROM docs WHERE user_id = $1", uid)
exists = count > 0

# Good
exists = await conn.fetchval("""
    SELECT EXISTS(SELECT 1 FROM docs WHERE user_id = $1)
""", uid)

---

Related References

• python-queries.md — Bulk inserts, FTS queries, vector queries, JSONB operations

Python Query Patterns Reference

Bulk inserts, full-text search (FTS), vector similarity queries, and JSONB/array operations.

Contents

• Bulk Insert Strategies
• Full-Text Search Queries
• Vector Similarity Queries
• JSONB and Array Operations

---

Bulk Insert Strategies

Performance Comparison

psycopg2: execute_values

from psycopg2.extras import execute_values

data = [
    ("Title 1", "Content 1", [0.1, 0.2, 0.3]),
    ("Title 2", "Content 2", [0.4, 0.5, 0.6]),
    # ... thousands of rows
]

with conn.cursor() as cur:
    execute_values(
        cur,
        """INSERT INTO documents (title, content, embedding) 
           VALUES %s ON CONFLICT (id) DO NOTHING""",
        data,
        template="(%s, %s, %s::vector)",
        page_size=1000
    )
conn.commit()

# Verify insert count:
# cur.execute("SELECT COUNT(*) FROM documents")
# print(f"Inserted: {cur.fetchone()[0]} rows")

psycopg2: COPY Protocol

from io import StringIO
import csv

# Step 1: Prepare data as TSV
buffer = StringIO()
writer = csv.writer(buffer, delimiter='\t')
for row in data:
    writer.writerow(row)
buffer.seek(0)

# Step 2: Copy to table
with conn.cursor() as cur:
    cur.copy_from(buffer, 'documents', columns=('title', 'content'))
conn.commit()

psycopg3: COPY with Binary

async with conn.cursor() as cur:
    async with cur.copy("COPY documents (title, content) FROM STDIN (FORMAT BINARY)") as copy:
        for title, content in data:
            await copy.write_row((title, content))

asyncpg: copy_records_to_table

# Fastest method for asyncpg
records = [
    ("Title 1", "Content 1"),
    ("Title 2", "Content 2"),
]

await conn.copy_records_to_table(
    'documents',
    records=records,
    columns=['title', 'content']
)

# Verify:
# count = await conn.fetchval("SELECT COUNT(*) FROM documents")

asyncpg: executemany (For Complex Queries)

# Use when you need RETURNING or complex logic
await conn.executemany(
    """INSERT INTO documents (title, content) VALUES ($1, $2)
       ON CONFLICT (title) DO UPDATE SET content = EXCLUDED.content""",
    [("Title 1", "Content 1"), ("Title 2", "Content 2")]
)

---

Full-Text Search Queries

Basic FTS (All Libraries)

# psycopg2/psycopg3 (uses %s placeholders)
cur.execute("""
    SELECT id, title, ts_rank(search_vector, query) AS rank,
           ts_headline('english', content, query) AS snippet
    FROM documents, websearch_to_tsquery('english', %s) query
    WHERE search_vector @@ query
    ORDER BY rank DESC
    LIMIT %s
""", (search_term, limit))

# asyncpg (uses $1, $2 placeholders)
rows = await conn.fetch("""
    SELECT id, title, ts_rank(search_vector, query) AS rank,
           ts_headline('english', content, query) AS snippet
    FROM documents, websearch_to_tsquery('english', $1) query
    WHERE search_vector @@ query
    ORDER BY rank DESC
    LIMIT $2
""", search_term, limit)

FTS with Filters

rows = await conn.fetch("""
    SELECT id, title, ts_rank(search_vector, query) AS rank
    FROM documents, websearch_to_tsquery('english', $1) query
    WHERE search_vector @@ query
      AND created_at > $2
      AND metadata @> $3::jsonb
    ORDER BY rank DESC
    LIMIT $4
""", search_term, since_date, json.dumps({"status": "published"}), limit)

# Verify index is used:
# EXPLAIN (ANALYZE) should show "Bitmap Index Scan"

BM25 Search (pg_search)

# ParadeDB only
rows = await conn.fetch("""
    SELECT id, title, paradedb.score(id) AS score,
           paradedb.snippet(content, $1) AS snippet
    FROM documents
    WHERE content @@@ $1
    ORDER BY paradedb.score(id) DESC
    LIMIT $2
""", search_term, limit)

---

Vector Similarity Queries

Step 1: Register pgvector Types

# psycopg2
from pgvector.psycopg2 import register_vector
register_vector(conn)

# psycopg3
from pgvector.psycopg import register_vector
register_vector(conn)

# asyncpg
from pgvector.asyncpg import register_vector
await register_vector(conn)

Step 2: Similarity Search

import numpy as np

# Generate or load embedding
query_embedding = np.array([0.1, 0.2, ...])  # 1536 dims for OpenAI

# asyncpg query
rows = await conn.fetch("""
    SELECT id, title, embedding <=> $1 AS distance
    FROM documents
    ORDER BY embedding <=> $1
    LIMIT $2
""", query_embedding, limit)

# Convert distance to similarity (cosine distance is 0-2, lower is better)
results = [
    {"id": r["id"], "title": r["title"], "similarity": 1 - r["distance"]}
    for r in rows
]

# Verify index is used:
# EXPLAIN should show "Index Scan using idx_docs_embedding"

Filtered Vector Search

# Filter then search (efficient with partial index or pre-filtering)
rows = await conn.fetch("""
    SELECT id, title, embedding <=> $1 AS distance
    FROM documents
    WHERE category = $2
    ORDER BY embedding <=> $1
    LIMIT $3
""", embedding, category, limit)

Hybrid Search Function

async def hybrid_search(
    pool: asyncpg.Pool,
    query_text: str,
    query_embedding: list[float],
    text_weight: float = 0.3,
    vector_weight: float = 0.7,
    limit: int = 20
) -> list[dict]:
    """
    Combine FTS and vector search with weighted scoring.
    
    Args:
        pool: asyncpg connection pool
        query_text: Search keywords for FTS
        query_embedding: Vector for similarity search
        text_weight: Weight for text score (default 0.3)
        vector_weight: Weight for vector score (default 0.7)
        limit: Max results to return
    
    Returns:
        List of documents with combined_score
    """
    async with pool.acquire() as conn:
        rows = await conn.fetch("""
            WITH text_matches AS (
                SELECT id, ts_rank(search_vector, query) AS text_score
                FROM documents, websearch_to_tsquery('english', $1) query
                WHERE search_vector @@ query
            ),
            vector_matches AS (
                SELECT id, 1 - (embedding <=> $2::vector) AS vector_score
                FROM documents
                ORDER BY embedding <=> $2::vector
                LIMIT 100
            )
            SELECT d.id, d.title, d.content,
                   COALESCE(t.text_score, 0) * $3 + 
                   COALESCE(v.vector_score, 0) * $4 AS combined_score
            FROM documents d
            LEFT JOIN text_matches t ON d.id = t.id
            LEFT JOIN vector_matches v ON d.id = v.id
            WHERE t.id IS NOT NULL OR v.id IS NOT NULL
            ORDER BY combined_score DESC
            LIMIT $5
        """, query_text, query_embedding, text_weight, vector_weight, limit)
        
        return [dict(r) for r in rows]

Reciprocal Rank Fusion (RRF)

RRF is the industry-standard algorithm for hybrid search. Unlike weighted scoring, RRF uses rank positions which normalizes across different score scales.

Formula: score = 1 / (k + rank) where k=60 is standard.

async def hybrid_search_rrf(
    pool: asyncpg.Pool,
    query_text: str,
    query_embedding: list[float],
    k: int = 60,
    limit: int = 20
) -> list[dict]:
    """
    Hybrid search using Reciprocal Rank Fusion (RRF).

    RRF normalizes scores by rank position, making it more robust
    than weighted scoring when combining different search methods.

    Args:
        pool: asyncpg connection pool
        query_text: Keywords for full-text search
        query_embedding: Vector for semantic search
        k: RRF constant (default 60, industry standard)
        limit: Max results to return
    """
    async with pool.acquire() as conn:
        rows = await conn.fetch("""
            WITH semantic AS (
                SELECT id, RANK() OVER (ORDER BY embedding <=> $1::vector) as rank
                FROM documents
                ORDER BY embedding <=> $1::vector
                LIMIT 50
            ),
            keyword AS (
                SELECT id, RANK() OVER (
                    ORDER BY ts_rank_cd(search_vector, query) DESC
                ) as rank
                FROM documents, plainto_tsquery('english', $2) query
                WHERE search_vector @@ query
                LIMIT 50
            )
            SELECT
                COALESCE(s.id, k.id) as id,
                d.title,
                d.content,
                (COALESCE(1.0 / ($3 + s.rank), 0.0) +
                 COALESCE(1.0 / ($3 + k.rank), 0.0)) as rrf_score
            FROM semantic s
            FULL OUTER JOIN keyword k ON s.id = k.id
            JOIN documents d ON d.id = COALESCE(s.id, k.id)
            ORDER BY rrf_score DESC
            LIMIT $4
        """, query_embedding, query_text, k, limit)

        return [dict(r) for r in rows]

When to use RRF vs Weighted:

---

JSONB and Array Operations

JSONB Queries

import json

# Containment query (uses GIN index)
rows = await conn.fetch("""
    SELECT * FROM products
    WHERE data @> $1::jsonb
""", json.dumps({"category": "electronics", "in_stock": True}))

# Extract and filter
rows = await conn.fetch("""
    SELECT id, data->>'name' AS name, (data->>'price')::numeric AS price
    FROM products
    WHERE (data->>'price')::numeric < $1
      AND data ? 'name'
    ORDER BY price
""", max_price)

# Update JSONB field
await conn.execute("""
    UPDATE products
    SET data = jsonb_set(data, '{status}', $1::jsonb)
    WHERE id = $2
""", json.dumps("active"), product_id)

# Verify update:
# row = await conn.fetchrow("SELECT data->>'status' FROM products WHERE id = $1", product_id)
# assert row[0] == "active"

Array Queries

# Find by tag overlap (posts with ANY of these tags)
rows = await conn.fetch("""
    SELECT * FROM posts
    WHERE tags && $1::text[]
""", ['python', 'postgresql'])

# Find by tag containment (posts with ALL of these tags)
rows = await conn.fetch("""
    SELECT * FROM posts
    WHERE tags @> $1::text[]
""", ['python', 'postgresql'])

# Add tag to array (avoid duplicates)
await conn.execute("""
    UPDATE posts
    SET tags = array_append(tags, $1)
    WHERE id = $2 AND NOT ($1 = ANY(tags))
""", 'new-tag', post_id)

# Remove tag from array
await conn.execute("""
    UPDATE posts
    SET tags = array_remove(tags, $1)
    WHERE id = $2
""", 'old-tag', post_id)

---

Related References

• python-drivers.md — Driver selection, connection patterns, pools, SQLAlchemy

Full-Text Search Reference

Native PostgreSQL full-text search, BM25 ranking with pg_search, and trigram fuzzy matching.

Contents

• Native Full-Text Search
• BM25 with pg_search
• Trigram Fuzzy Search

---

Native Full-Text Search

Core Concepts

Creating tsvector Columns

-- Option 1: Generated column (recommended)
ALTER TABLE documents ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(content, '')), 'B')
) STORED;

-- Verify it works:
SELECT search_vector FROM documents LIMIT 1;
-- Expected: tsvector with weighted terms

-- Option 2: Trigger-maintained (for complex logic)
CREATE OR REPLACE FUNCTION documents_search_trigger() RETURNS trigger AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('english', coalesce(NEW.title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(NEW.content, '')), 'B');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tsvector_update BEFORE INSERT OR UPDATE
ON documents FOR EACH ROW EXECUTE FUNCTION documents_search_trigger();

Query Functions

-- websearch_to_tsquery supports:
-- "quoted phrases", -negation, OR operator
SELECT * FROM documents
WHERE search_vector @@ websearch_to_tsquery('english', '"database index" -mysql');

Ranking Functions

-- ts_rank: term frequency based
SELECT title, ts_rank(search_vector, query) AS rank
FROM documents, to_tsquery('english', 'postgresql') query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- ts_rank_cd: cover density (phrase proximity)
SELECT title, ts_rank_cd(search_vector, query) AS rank
FROM documents, phraseto_tsquery('english', 'full text search') query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- Normalization options (bitmask)
-- 1: divides by 1 + log(document length)
-- 2: divides by document length
-- 4: divides by mean harmonic distance between extents
SELECT ts_rank(search_vector, query, 1) AS normalized_rank
FROM documents, to_tsquery('english', 'search') query;

Highlighting Results

SELECT title,
       ts_headline('english', content, query,
           'StartSel=<b>, StopSel=</b>, MaxWords=35, MinWords=15'
       ) AS snippet
FROM documents, websearch_to_tsquery('english', 'postgresql search') query
WHERE search_vector @@ query;

Weight Configuration

-- Custom weight array [D, C, B, A]
SELECT ts_rank('{0.1, 0.2, 0.4, 1.0}', search_vector, query) AS rank
FROM documents, to_tsquery('postgresql') query;

Full-Text Search Verification

-- Verify index exists and is used
EXPLAIN (ANALYZE) SELECT * FROM documents 
WHERE search_vector @@ to_tsquery('postgresql');
-- Should show: Index Scan using idx_documents_search

-- Debug tokenization
SELECT to_tsvector('english', 'PostgreSQL full-text searching');
-- Output: 'full':2 'full-text':2 'postgresql':1 'search':3 'text':3

---

BM25 with pg_search

BM25 (Best Match 25) provides relevance scoring that considers term frequency and inverse document frequency. Available via pg_search extension in ParadeDB.

Installation

-- ParadeDB image only
CREATE EXTENSION pg_search;

-- Verify installation
SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_search';
-- Expected: pg_search with version number

Creating BM25 Index

-- Basic index
CREATE INDEX idx_products_search ON products
USING bm25 (id, title, description, category)
WITH (key_field='id');

-- With custom tokenizer
CREATE INDEX idx_docs_search ON documents
USING bm25 (id, title, content)
WITH (
    key_field='id',
    text_fields='{"title": {"tokenizer": "en_stem"}, "content": {"tokenizer": "en_stem"}}'
);

-- Verify index created
SELECT indexname FROM pg_indexes WHERE indexname LIKE '%bm25%';

BM25 Queries

-- Basic search with @@@ operator
SELECT title, description, paradedb.score(id) AS score
FROM products
WHERE description @@@ 'wireless keyboard'
ORDER BY paradedb.score(id) DESC;

-- Phrase search
SELECT * FROM products
WHERE description @@@ '"mechanical keyboard"';

-- Boolean operators
SELECT * FROM products
WHERE description @@@ 'wireless AND (keyboard OR mouse)';

-- Fuzzy matching
SELECT * FROM products
WHERE id @@@ paradedb.match('title', 'keybord', distance => 1);

-- Field boosting
SELECT * FROM products
WHERE id @@@ paradedb.boost(
    paradedb.match('title', 'keyboard'),
    2.0
);

Highlighting with BM25

SELECT title,
       paradedb.snippet(description, 'wireless keyboard') AS snippet
FROM products
WHERE description @@@ 'wireless keyboard';

BM25 vs ts_rank Comparison

Alternative: pg_textsearch (TigerData)

Note: pg_textsearch is in preview status as of December 2025.

TigerData's pg_textsearch provides BM25 ranking optimized for hybrid AI search workflows.

-- TigerData/Timescale pg_textsearch (preview)
CREATE EXTENSION pg_textsearch;

-- Create BM25 index
CREATE INDEX ON products USING bm25 (title, description);

-- Query with <@> operator
SELECT title, bm25_score(products) AS score
FROM products
WHERE description <@> 'wireless keyboard'
ORDER BY score DESC;

---

Trigram Fuzzy Search

pg_trgm enables similarity-based matching for typo tolerance.

Setup

CREATE EXTENSION pg_trgm;

-- Verify installation
SHOW pg_trgm.similarity_threshold;
-- Default: 0.3

-- Index for similarity queries
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);

-- Index for LIKE/ILIKE optimization
CREATE INDEX idx_products_desc_trgm ON products USING GIN (description gin_trgm_ops);

Similarity Functions

-- similarity(): 0-1 score
SELECT name, similarity(name, 'Postgre') AS sim
FROM products
WHERE similarity(name, 'Postgre') > 0.3
ORDER BY sim DESC;

-- % operator: uses pg_trgm.similarity_threshold
SET pg_trgm.similarity_threshold = 0.4;
SELECT * FROM products WHERE name % 'Postgre';

-- word_similarity(): best matching substring
SELECT name, word_similarity('SQL', name) AS sim
FROM products
WHERE 'SQL' <% name;  -- word similarity threshold

LIKE/ILIKE Optimization

-- GIN index on gin_trgm_ops accelerates wildcard queries
SELECT * FROM products WHERE name ILIKE '%keyboard%';
SELECT * FROM products WHERE name LIKE '%key%board%';

-- Verify index is used
EXPLAIN SELECT * FROM products WHERE name ILIKE '%keyboard%';
-- Should show: Bitmap Index Scan on idx_products_name_trgm

---

Related References

• search-vectors-json.md — pgvector, JSONB indexing, array indexing, maintenance

Vectors, JSONB & Index Management Reference

pgvector similarity search, JSONB/array indexing strategies, and index maintenance.

Contents

• pgvector Deep Dive
• JSONB Indexing
• Array Indexing
• Index Type Selection
• Index Maintenance
• Troubleshooting

---

pgvector Deep Dive

Vector Column Creation

-- Fixed dimensions
CREATE TABLE items (
    id BIGSERIAL PRIMARY KEY,
    embedding vector(1536)  -- OpenAI ada-002
);

-- Verify dimensions
SELECT vector_dims(embedding) FROM items LIMIT 1;

Distance Operators

-- Cosine similarity (most common)
SELECT id, title, embedding <=> $1::vector AS distance
FROM documents
ORDER BY embedding <=> $1::vector
LIMIT 10;

-- Convert distance to similarity
SELECT id, title, 1 - (embedding <=> $1::vector) AS similarity
FROM documents
ORDER BY embedding <=> $1::vector
LIMIT 10;

-- Verify pgvector is working
SELECT '[1,2,3]'::vector <=> '[4,5,6]'::vector AS test_distance;
-- Expected: ~5.196 (Euclidean distance)

HNSW Index

Hierarchical Navigable Small World - best for query performance.

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- Verify index created
SELECT indexname, indexdef FROM pg_indexes 
WHERE indexdef LIKE '%hnsw%';

Query-time tuning:

SET hnsw.ef_search = 100;  -- Default 40. Higher = better recall, slower

-- Verify setting
SHOW hnsw.ef_search;

Filtered query optimization (pgvector 0.7+):

-- Enable iterative scan for filtered queries
SET hnsw.iterative_scan = strict_order;  -- or 'relaxed_order' for better recall
SET hnsw.max_scan_tuples = 50000;        -- Limit tuples scanned
SET hnsw.scan_mem_multiplier = 2;        -- Memory multiplier for scans (default: 1)

-- Query with filter uses iterative scan
SELECT * FROM documents
WHERE category = 'tutorial'
ORDER BY embedding <=> $1::vector
LIMIT 10;

Build memory:

SET maintenance_work_mem = '2GB';  -- More = faster build
SET max_parallel_maintenance_workers = 7;  -- Parallel build

-- Monitor build progress
SELECT * FROM pg_stat_progress_create_index;

IVFFlat Index

Inverted File with Flat compression - faster build, requires data.

-- Must have data before creating index
CREATE INDEX ON documents USING ivfflat (embedding vector_l2_ops)
WITH (lists = 100);

Query-time tuning:

SET ivfflat.probes = 10;  -- Default 1. Higher = better recall

Index Selection Guide

Recommendation: Use HNSW unless build time is critical.

VectorChord (Large-Scale Alternative)

For datasets exceeding 100M vectors, VectorChord offers significant performance improvements over pgvector while maintaining API compatibility.

-- Install VectorChord (self-hosted only)
CREATE EXTENSION vchord CASCADE;

-- Create vchordrq index (IVF + RaBitQ quantization)
CREATE INDEX ON documents
USING vchordrq (embedding vector_l2_ops)
WITH (options = $$
    residual_quantization = true
    [build.internal]
    lists = [4096]
$$);

-- Query uses same syntax as pgvector
SELECT id, title, embedding <-> $1::vector AS distance
FROM documents
ORDER BY embedding <-> $1::vector
LIMIT 10;

When to consider VectorChord:

• Datasets > 100M vectors
• Cost-sensitive deployments (400K vectors per $1 vs 15K for pgvector)
• Self-hosted infrastructure acceptable

Note: Added to Thoughtworks Technology Radar (April 2025) as "Assess" category.

Dimension Limits

Hybrid Search Pattern

-- Combine keyword + vector search
WITH keyword_matches AS (
    SELECT id, ts_rank(search_vector, query) AS text_rank
    FROM documents, websearch_to_tsquery('english', $1) query
    WHERE search_vector @@ query
),
vector_matches AS (
    SELECT id, 1 - (embedding <=> $2::vector) AS vector_score
    FROM documents
    ORDER BY embedding <=> $2::vector
    LIMIT 100
)
SELECT d.id, d.title,
       COALESCE(k.text_rank, 0) * 0.3 + COALESCE(v.vector_score, 0) * 0.7 AS combined_score
FROM documents d
LEFT JOIN keyword_matches k ON d.id = k.id
LEFT JOIN vector_matches v ON d.id = v.id
WHERE k.id IS NOT NULL OR v.id IS NOT NULL
ORDER BY combined_score DESC
LIMIT 20;

---

JSONB Indexing

Index Types for JSONB

GIN Index (Default)

CREATE INDEX idx_data ON products USING GIN (data);

-- Supports these queries:
SELECT * FROM products WHERE data ? 'price';           -- Key exists
SELECT * FROM products WHERE data ?| array['a','b'];   -- Any key exists
SELECT * FROM products WHERE data ?& array['a','b'];   -- All keys exist
SELECT * FROM products WHERE data @> '{"status":"active"}';  -- Contains

-- Verify index is used
EXPLAIN SELECT * FROM products WHERE data @> '{"status":"active"}';
-- Should show: Bitmap Index Scan on idx_data

GIN jsonb_path_ops

CREATE INDEX idx_data_path ON products USING GIN (data jsonb_path_ops);

-- Only supports containment:
SELECT * FROM products WHERE data @> '{"category":"electronics"}';

Expression Index

-- Index specific field for equality/range queries
CREATE INDEX idx_price ON products ((data->>'price')::numeric);

-- Query uses index
SELECT * FROM products WHERE (data->>'price')::numeric < 100;

-- Index nested path
CREATE INDEX idx_category ON products ((data#>>'{metadata,category}'));

JSONB Query Patterns

-- Access operators
data->'key'          -- Returns JSON
data->>'key'         -- Returns text
data#>'{a,b}'        -- Path access, returns JSON
data#>>'{a,b}'       -- Path access, returns text

-- Array element access
data->0              -- First array element
data->>-1            -- Last array element (text)

-- Containment (index-friendly)
data @> '{"a":1}'    -- data contains {"a":1}
data <@ '{"a":1}'    -- data is contained by {"a":1}

---

Array Indexing

GIN Index for Arrays

CREATE INDEX idx_tags ON posts USING GIN (tags);

-- Verify
SELECT indexname FROM pg_indexes WHERE indexname = 'idx_tags';

intarray Extension (Integer Arrays)

For integer arrays, the intarray extension provides an optimized operator class:

-- Enable extension
CREATE EXTENSION intarray;

-- Create optimized index for integer arrays
CREATE INDEX idx_labels ON items USING GIN (label_ids gin__int_ops);

-- Queries use same operators but with better performance
SELECT * FROM items WHERE label_ids @> ARRAY[1, 5, 10];
SELECT * FROM items WHERE label_ids && ARRAY[1, 2, 3];

Array Operators (Index-Supported)

-- Find posts with all these tags
SELECT * FROM posts WHERE tags @> ARRAY['python', 'postgresql'];

-- Find posts with any of these tags
SELECT * FROM posts WHERE tags && ARRAY['python', 'go', 'rust'];

-- Check specific element (not index-optimized)
SELECT * FROM posts WHERE 'python' = ANY(tags);

---

Index Type Selection

Decision Matrix

Partial Indexes

-- Index only active records
CREATE INDEX idx_active_orders ON orders (created_at)
WHERE status = 'active';

-- Query must include the WHERE clause
SELECT * FROM orders WHERE status = 'active' AND created_at > '2024-01-01';

Covering Indexes

-- Include columns to enable index-only scans
CREATE INDEX idx_users_email ON users (email) INCLUDE (name, created_at);

-- This query can be satisfied from index alone
SELECT email, name, created_at FROM users WHERE email = 'test@example.com';

-- Verify index-only scan
EXPLAIN SELECT email, name, created_at FROM users WHERE email = 'test@example.com';
-- Should show: Index Only Scan

---

Index Maintenance

Autovacuum Settings for Vector Tables

HNSW and GIN indexes generate significant bloat during updates. Configure aggressive autovacuum for vector-heavy tables:

-- Aggressive settings for vector tables
ALTER TABLE documents SET (
    autovacuum_vacuum_scale_factor = 0.01,   -- Trigger at 1% changed (default 20%)
    autovacuum_vacuum_threshold = 50,         -- Minimum rows before trigger
    autovacuum_analyze_scale_factor = 0.01,   -- Keep statistics fresh
    autovacuum_vacuum_cost_delay = 2          -- Faster vacuum execution
);

-- Verify settings
SELECT relname, reloptions
FROM pg_class
WHERE relname = 'documents';

Why aggressive settings for vectors:

• HNSW graph structure creates many dead tuples on updates
• Bloated indexes degrade query performance significantly
• Default 20% threshold is too high for vector workloads

Monitor Index Usage

-- Unused indexes (candidates for removal)
SELECT schemaname, relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelname NOT LIKE '%pkey%'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Index hit ratio (should be > 0.99)
SELECT relname,
       round(100.0 * idx_scan / nullif(seq_scan + idx_scan, 0), 2) AS idx_ratio,
       seq_scan, idx_scan
FROM pg_stat_user_tables
WHERE n_live_tup > 10000
ORDER BY idx_ratio ASC;

Reindex Operations

-- Rebuild specific index (locks table)
REINDEX INDEX idx_documents_search;

-- Concurrent rebuild (no lock, slower)
REINDEX INDEX CONCURRENTLY idx_documents_search;

-- Rebuild all indexes on table
REINDEX TABLE documents;

-- Verify reindex completed
SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass)) 
FROM pg_indexes WHERE tablename = 'documents';

Index Bloat Detection

-- Estimate bloat ratio
SELECT nspname, relname,
       round(100 * pg_relation_size(indexrelid) / 
             nullif(pg_relation_size(indrelid), 0)) AS index_ratio
FROM pg_index
JOIN pg_class ON pg_class.oid = pg_index.indexrelid
JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(indexrelid) DESC;

---

Troubleshooting

Index Not Used

-- Check query plan
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM documents WHERE ...;

-- If Seq Scan appears:
-- 1. Update statistics
ANALYZE documents;

-- 2. Verify index exists
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'documents';

-- 3. Check operator class matches query
-- vector_cosine_ops for <=>, vector_l2_ops for <->

Poor Full-Text Results

-- Check what tokens are generated
SELECT to_tsvector('english', 'your document text');

-- Verify query parsing
SELECT websearch_to_tsquery('english', 'your search query');

-- Check if they match
SELECT to_tsvector('english', 'text') @@ to_tsquery('english', 'query');

Vector Search Quality Issues

Common Error Messages

---

Related References

• search-fulltext.md — Full-text search, BM25, trigram fuzzy search

Setup and Docker Reference

Local development environment, extension installation, and PostgreSQL configuration for search and vector workloads.

Contents

• Docker Compose Configurations
• Extension Installation
• PostgreSQL Configuration
• Development Workflow
• psql Quick Reference
• Troubleshooting Setup

---

Docker Compose Configurations

pgvector Development Environment

# docker-compose-pgvector.yml
version: '3.8'

services:
  postgres:
    image: pgvector/pgvector:pg17
    container_name: postgres-dev
    environment:
      POSTGRES_USER: devuser
      POSTGRES_PASSWORD: devpass
      POSTGRES_DB: devdb
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./init:/docker-entrypoint-initdb.d
    shm_size: '512mb'  # Production: increase to 2gb for parallel queries
    command: >
      postgres
      -c shared_buffers=256MB
      -c work_mem=16MB
      -c maintenance_work_mem=512MB
      -c max_parallel_workers_per_gather=2
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U devuser -d devdb"]
      interval: 10s
      timeout: 5s
      retries: 5

  pgadmin:
    image: dpage/pgadmin4:latest
    container_name: pgadmin
    environment:
      PGADMIN_DEFAULT_EMAIL: admin@local.dev
      PGADMIN_DEFAULT_PASSWORD: admin
    ports:
      - "8080:80"
    depends_on:
      postgres:
        condition: service_healthy

volumes:
  postgres_data:

ParadeDB Environment (BM25 Support)

# docker-compose-paradedb.yml
version: '3.8'

services:
  paradedb:
    image: paradedb/paradedb:latest
    container_name: paradedb-dev
    environment:
      POSTGRES_USER: devuser
      POSTGRES_PASSWORD: devpass
      POSTGRES_DB: devdb
    ports:
      - "5432:5432"
    volumes:
      - paradedb_data:/var/lib/postgresql/data
    shm_size: '512mb'
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U devuser -d devdb"]
      interval: 10s
      timeout: 5s
      retries: 5

volumes:
  paradedb_data:

Initialization Script

Create init/01-extensions.sql:

-- Enable extensions on database creation
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- For ParadeDB image only:
-- CREATE EXTENSION IF NOT EXISTS pg_search;

Docker Commands

# Start environment
docker-compose -f docker-compose-pgvector.yml up -d

# View logs
docker-compose logs -f postgres

# Connect via psql
docker exec -it postgres-dev psql -U devuser -d devdb

# Stop and preserve data
docker-compose down

# Stop and remove data
docker-compose down -v

---

Extension Installation

pgvector

-- Check availability
SELECT * FROM pg_available_extensions WHERE name = 'vector';

-- Install
CREATE EXTENSION vector;

-- Verify
SELECT extversion FROM pg_extension WHERE extname = 'vector';
-- Should return: 0.8.0 or higher

pg_trgm (Fuzzy Search)

CREATE EXTENSION pg_trgm;

-- Verify
SHOW pg_trgm.similarity_threshold;
-- Default: 0.3

pg_search (BM25) - ParadeDB Only

-- Only available in ParadeDB image
CREATE EXTENSION pg_search;

-- Verify
SELECT * FROM pg_extension WHERE extname = 'pg_search';

pg_stat_statements (Query Monitoring)

-- Requires postgresql.conf: shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION pg_stat_statements;

Extension Dependencies

---

PostgreSQL Configuration

Search-Optimized postgresql.conf

# Memory - adjust based on available RAM
shared_buffers = 256MB              # 25% of RAM for dedicated server
work_mem = 64MB                     # Per-operation memory for sorts/hashes
maintenance_work_mem = 512MB        # For index builds, VACUUM
effective_cache_size = 1GB          # Estimate of OS cache available

# Parallelism
max_parallel_workers_per_gather = 4
max_parallel_maintenance_workers = 4
max_parallel_workers = 8

# Planner - SSD settings
random_page_cost = 1.1              # 1.1 for SSD, 4.0 for HDD
effective_io_concurrency = 200      # 200 for SSD, 2 for HDD

# WAL - for better write performance
wal_buffers = 16MB
checkpoint_completion_target = 0.9

# Logging - development
log_min_duration_statement = 100    # Log queries over 100ms
log_statement = 'none'              # Set to 'all' for debugging
log_line_prefix = '%t [%p]: db=%d,user=%u '

# Statistics
default_statistics_target = 100     # Increase for complex queries

Vector Workload Tuning

# For large vector index builds
maintenance_work_mem = 2GB          # More memory = faster HNSW build
max_parallel_maintenance_workers = 7

# Monitor progress
# SELECT * FROM pg_stat_progress_create_index;

Full-Text Search Tuning

# Custom text search configuration (optional)
default_text_search_config = 'pg_catalog.english'

Applying Configuration

# Docker: mount custom config
docker run -v ./postgresql.conf:/etc/postgresql/postgresql.conf \
  pgvector/pgvector:pg17 \
  postgres -c config_file=/etc/postgresql/postgresql.conf

# Or use -c flags in docker-compose command section

---

Development Workflow

Initial Setup Checklist

[ ] 1. Start Docker environment
[ ] 2. Verify PostgreSQL is healthy
[ ] 3. Create extensions
[ ] 4. Create application schema
[ ] 5. Create indexes
[ ] 6. Load sample data
[ ] 7. Test queries
[ ] 8. Verify index usage with EXPLAIN

Sample Schema Creation

-- Documents with full-text search and vectors
CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT,
    metadata JSONB DEFAULT '{}',
    tags TEXT[] DEFAULT '{}',
    embedding vector(1536),
    search_vector tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(content, '')), 'B')
    ) STORED,
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

-- Indexes
CREATE INDEX idx_docs_search ON documents USING GIN (search_vector);
CREATE INDEX idx_docs_embedding ON documents USING hnsw (embedding vector_cosine_ops);
CREATE INDEX idx_docs_metadata ON documents USING GIN (metadata jsonb_path_ops);
CREATE INDEX idx_docs_tags ON documents USING GIN (tags);
CREATE INDEX idx_docs_created ON documents (created_at DESC);

-- Updated timestamp trigger
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER documents_updated_at
    BEFORE UPDATE ON documents
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

Data Volume Management

# Backup volume
docker run --rm -v postgres_data:/data -v $(pwd):/backup \
  alpine tar czf /backup/postgres_backup.tar.gz /data

# Restore volume
docker run --rm -v postgres_data:/data -v $(pwd):/backup \
  alpine tar xzf /backup/postgres_backup.tar.gz -C /

---

psql Quick Reference

Common commands for exploring PostgreSQL schemas and debugging.

Meta-Commands

Schema Exploration

-- Check extensions and versions
\dx

-- Inspect table with indexes
\d+ documents

-- List all indexes on a table
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'documents';

-- Check index sizes
SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE relname = 'documents';

Query Analysis

-- Basic explain
EXPLAIN SELECT * FROM documents WHERE id = 1;

-- With execution stats (actually runs query)
EXPLAIN ANALYZE SELECT * FROM documents
WHERE search_vector @@ to_tsquery('postgresql');

-- Full analysis with buffers
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM documents
ORDER BY embedding <=> '[0.1,0.2,0.3]'::vector
LIMIT 10;

Reading EXPLAIN output:

• Seq Scan: Full table scan (may need index)
• Index Scan: Using index (good)
• Bitmap Index Scan: GIN/multiple conditions
• actual time: Real execution time in ms
• rows: Actual vs estimated row count

Running Scripts

# Execute SQL file
psql -f schema.sql "postgresql://user:pass@localhost/mydb"

# Run single command
psql -c "SELECT version();" "postgresql://user:pass@localhost/mydb"

# Interactive with connection string
psql "postgresql://user:pass@localhost:5432/mydb"

---

Troubleshooting Setup

Extension Installation Failures

Connection Issues

Performance Issues

Verifying Setup

-- Check extensions
SELECT extname, extversion FROM pg_extension;

-- Check table indexes
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'documents';

-- Check index sizes
SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes WHERE relname = 'documents';

-- Test vector operations
SELECT '[1,2,3]'::vector <=> '[4,5,6]'::vector AS distance;

-- Test full-text search
SELECT to_tsvector('english', 'PostgreSQL is great') @@ 
       to_tsquery('english', 'postgresql');

Container Health Check

# Check container status
docker-compose ps

# Check PostgreSQL logs for errors
docker-compose logs postgres | grep -i error

# Interactive shell for debugging
docker exec -it postgres-dev bash