Tune PostgreSQL for pgvector

What this covers

Which PostgreSQL and pgvector settings change vector-search latency and memory use on Azure Database for PostgreSQL flexible server, what each one does, safe starting values, how to set them at server, session or transaction level, and how to measure the effect with EXPLAIN and Azure Monitor.

Exam weight: Develop AI solutions by using Azure data management services (30–35%)

Skills measured

• "Configure compute, memory, and storage resources to support vector workloads"
• "Implement indexing strategies, including optimizing query latency and reducing pgvector compute overhead"

Key concepts

Why vector queries are different

One cosine distance on 1,536 dimensions costs about 1,536 multiply-adds. Brute force over a million rows is about 1.5 billion operations per query, so vector search is CPU-bound without an index and memory-bound with one. HNSW traversal is fast only while graph pages stay in RAM. Cost per comparison grows linearly with dimensions: 3,072 dims means about 2× the work and 2× the storage of 1,536 dims. Shortened embeddings (for example 768 or 1,024) cut both.

Operator cost: inner product (<#>) is cheapest. Cosine (<=>) adds normalization. For unit-length embeddings (OpenAI models) inner product gives the same ranking as cosine, so <#> with vector_ip_ops is a cheap win.

Memory parameters

Planner and parallelism

• random_page_cost: default 4.0 assumes spinning disks. On Azure SSDs 1.1–1.5 encourages index scans.
• effective_io_concurrency: around 200 on SSD for bitmap-heap prefetching.
• max_parallel_workers_per_gather: lets exact (unindexed) scans run in parallel; look for Workers Launched in plans.
• max_parallel_maintenance_workers: parallel index builds (HNSW supports them; increase max_worker_processes if needed, which needs a restart).
• parallel_setup_cost / parallel_tuple_cost (defaults 1000 / 0.1): lower values make parallel plans more likely.

pgvector knobs

Set them per transaction with SET LOCAL so pooled connections don't keep someone else's settings.

Measuring

• EXPLAIN (ANALYZE, BUFFERS): look for Index Scan using <vector index>, actual times, and shared hit vs read buffers.
• ANALYZE <table> after bulk loads so the planner's statistics are current.
• pg_stat_statements and Query Store show the slowest normalized queries.
• Azure Monitor metrics: CPU percent, Memory percent, Storage IO percent / Disk IOPS consumed, Active connections. Alert before they saturate.

Best practice: baseline first, change one setting at a time, test with production-sized data, and record what changed and why.

How it works

Hands-on

Azure portal

1. Server, Server parameters: search work_mem, maintenance_work_mem, random_page_cost, effective_io_concurrency; edit and Save (dynamic parameters apply without restart).
2. Static parameters such as shared_buffers show Restart required. Restart from Overview.
3. Monitoring > Metrics: add CPU percent, Memory percent and Storage IO percent to one chart and pin it.

Azure CLI

az postgres flexible-server parameter set -g <resource-group> --server-name <server-name> \
  --name random_page_cost --value 1.1
az postgres flexible-server parameter set -g <resource-group> --server-name <server-name> \
  --name maintenance_work_mem --value 2097152   # in KB = 2 GB
az postgres flexible-server parameter show -g <resource-group> --server-name <server-name> \
  --name shared_buffers --query "{value:value, unit:unit, restart:isConfigPendingRestart}"

SHOW shared_buffers;

-- Cache hit ratio
SELECT sum(heap_blks_hit) / NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0) AS cache_hit_ratio
FROM pg_statio_user_tables;

-- Per-query tuning inside one transaction
BEGIN;
SET LOCAL hnsw.ef_search = 100;
SET LOCAL work_mem = '256MB';
SELECT id, name, embedding <=> $1 AS distance FROM products ORDER BY embedding <=> $1 LIMIT 10;
COMMIT;

SET ivfflat.probes = 10;
EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM products ORDER BY embedding <=> $1 LIMIT 10;
ANALYZE products;
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'products';

Python

import time
import psycopg

def benchmark(conn: psycopg.Connection, qvec, settings=(20, 40, 100, 200), runs=20):
    """Compare latency for several hnsw.ef_search values; measure recall separately."""
    results = {}
    for ef in settings:
        start = time.perf_counter()
        for _ in range(runs):
            with conn.transaction():
                conn.execute(f"SET LOCAL hnsw.ef_search = {int(ef)}")
                conn.execute("SELECT id FROM products ORDER BY embedding <=> %s LIMIT 10",
                             (qvec,)).fetchall()
        results[ef] = (time.perf_counter() - start) / runs * 1000
    return results  # ms per query

Exam traps

Quick review

• Vector cost grows linearly with dimensions; shorter embeddings are cheaper.
• Normalized embeddings: use <#> for the cheapest exact-equivalent ranking.
• Keep the vector index in memory: shared_buffers and enough RAM.
• work_mem is per operation per connection; scope it with SET LOCAL.
• random_page_cost 1.1–1.5 suits Azure SSD storage.
• ivfflat.probes default 1; hnsw.ef_search default 40.
• Measure with EXPLAIN (ANALYZE, BUFFERS) and Azure Monitor metrics.

Practice

Practice: compute and memory questions · Practice: indexing questions

Official references

• AI-200 study guide
• Unit: Tune PostgreSQL for pgvector
• Optimize performance when using pgvector
• Microsoft Learn