Home/Operations/Architectures/POSTGRESQL

postgresql Reference Architecture

⚡ Performance25 min

RPO: 0 (Sync Standby ile) / < 1s (Async DR)

RTO: < 15 saniye (Patroni Otomatik Failover)

PostgreSQL pgvector HNSW: Production RAG Blueprint & Tuning Guide

Deploy and scale pgvector HNSW safely in production: maintenance_work_mem sizing, WAL generation, Patroni replication, lock mitigation, hybrid search, and diagnostic queries.

Components:PostgreSQL 17pgvector 0.8.xPatroni HAetcd v3halfvec(1536)HNSW Indexpg_prewarmpg_buffercache

#postgresql#pgvector#hnsw#rag#vector-search#replication#patroni#performance-tuning

Architecture Topology & Flow Diagram

📌 PostgreSQL 17 + pgvector HNSW + Patroni Production RAG Reference Architecture Diagram (Raft Quorum, DCS, TOAST, Sync/Async WAL Stream)

Comprehensive Production Engineering Guide

1. Introduction and Architectural Overview

pgvector allows you to store embeddings directly alongside operational data within identical transaction boundaries, eliminating the need to sync data to dedicated vector stores (such as Pinecone, Qdrant, or Milvus). This removes dual-write overhead, consistency reconciliation lag, and distributed transaction complexity. However, this architectural consolidation comes with a direct trade-off: your vector indices now share identical system resources with PostgreSQL's buffer manager, WAL pipeline, checkpointer, autovacuum, and physical replication stream. Vector search is no longer an isolated subsystem—it becomes one of the heaviest consumers of your database's memory and I/O budget.

In this guide, we examine the lifecycle of HNSW indices across four operational axes:

•Build: The in-memory graph is constructed inside maintenance_work_mem. If the graph exceeds this memory ceiling, pgvector falls back to on-disk incremental insertion; build duration and generated WAL volume escalate dramatically.
•Search: Graph traversal begins at the entry-point node, iteratively evaluating neighbor lists at each step. Access patterns are inherently random I/O; if the index cannot fit across shared_buffers and the OS page cache, query latency becomes strictly bounded by NVMe random read IOPS.
•DML Writes: Every non-HOT UPDATE and every INSERT mandates a full graph insertion (a search traversal of depth ef_construction plus neighbor list re-linking). These writes contend heavily on buffer content LWLocks.
•Replication: Every byte of modified index pages streams as WAL to standby nodes. In synchronous replication topologies, this directly inflates the commit latency of all primary transactions.

Mimari Akış & Topoloji Şeması (Mermaid)

Mermaid şeması çiziliyor...

2. Scenario and Production Environment

Environment Specification:

Component	Specification
Hardware	32 vCPU, 256 GB RAM, NVMe RAID10
Software	PostgreSQL 17, pgvector 0.8.x, Patroni (1 primary, 1 sync standby, 1 async DR standby)
Dataset	Multi-tenant RAG platform, ~450 tenants, 12 million document chunks
Embeddings	`text-embedding-3-small`, 1536 dimensions, stored as `halfvec(1536)`
Distribution	Top 5 tenants hold 60% of data; hundreds of small tenants have only a few thousand chunks

The Incident: The engineering team initiated a new HNSW index during a nightly maintenance window using standard CREATE INDEX and default maintenance_work_mem = 64MB. The fallout:

1Because the graph exceeded the memory allocation, pgvector transitioned to on-disk incremental insertion after just tens of thousands of tuples. The build took 9 hours.
2CREATE INDEX acquired an exclusive ShareLock on the table, blocking all ingestion pipeline INSERT statements; the upstream message queue ballooned until morning.
3In on-disk mode, each inserted tuple emitted individual WAL records, generating ~70 GB of WAL. Due to synchronous replication, primary p99 commit latency spiked from 4 ms to 180 ms. The async DR standby lagged by 40 minutes.
4Once the index completed, smaller tenants querying with LIMIT 5 began receiving only 0–2 results.
5The retrieval service updated hit_count and last_accessed_at counters on the same table on every search. Within one week, index bloat reached 35%, and autovacuum spent hours re-linking HNSW graph nodes.

The following sections detail a battle-tested blueprint to eliminate every failure point identified above.

3. Step-by-Step Implementation & Configuration

Mimari Akış & Topoloji Şeması (Mermaid)

Mermaid şeması çiziliyor...

▸3.1. postgresql.conf Parameters

Baseline production sizing for a 256 GB RAM host:

ini

# --- Memory Sizing ---
shared_buffers = 64GB                   # 25% of RAM; paired with huge_pages
huge_pages = on                         # Reduces TLB cache miss pressure for 64GB buffer pool
effective_cache_size = 180GB            # shared_buffers + expected OS page cache
work_mem = 32MB                         # Multiplied by active connections for hybrid search sorts
maintenance_work_mem = 2GB              # Global default; elevated PER SESSION during HNSW build

# --- Worker Parallelism ---
max_worker_processes = 32
max_parallel_workers = 24
max_parallel_maintenance_workers = 8    # HNSW parallel build workers (pgvector 0.6.0+)
max_parallel_workers_per_gather = 4     # Accelerates exact filtered seq/bitmap scans

# --- Storage & I/O ---
random_page_cost = 1.1                  # Optimized for NVMe storage (graph traversal is random I/O)
effective_io_concurrency = 200

# --- WAL & Checkpoints ---
wal_compression = zstd                  # PG15+; radically shrinks full-page image (FPI) volume
max_wal_size = 64GB                     # Prevents checkpoint storms during large index builds
min_wal_size = 8GB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9

# --- Standby Tuning ---
hot_standby_feedback = on               # Mitigates query cancellation on read replicas
max_standby_streaming_delay = 30s

# --- Extensions ---
shared_preload_libraries = 'pg_stat_statements,pg_prewarm'
pg_prewarm.autoprewarm = on             # Restores HNSW buffer residency across restarts/failovers

Parameter	Default	Production Recommended	Sizing Rationale
`shared_buffers`	128MB	25%–40% of RAM	Hot HNSW index pages + active heap must fit within `shared_buffers` plus OS page cache.
`maintenance_work_mem`	64MB	Global 1–2 GB, Session = Projected graph size	Sized based on empirical sample footprint rather than arbitrary fixed caps (see Section 3.3).
`hnsw.ef_search`	40	40–200, workload-dependent	Recall/latency trade-off. If query `LIMIT` exceeds `ef_search`, pgvector returns at most `ef_search` candidates.
`max_parallel_maintenance_workers`	2	Cores − 2, cap at 8	A leader process coordinates workers; total active execution processes = workers + 1.
`max_parallel_workers_per_gather`	2	4	HNSW traversal is single-threaded; this speeds up metadata-filtered bitmap/seq scans.
`effective_cache_size`	4GB	50%–75% of RAM	Informs query planner cost model; does not allocate physical memory.
`wal_compression`	off	`zstd` or `lz4`	HNSW graph writes contain high full-page image ratios; compression relieves replication network.

Under Patroni, persist configuration changes dynamically through the DCS rather than manually altering local files:

bash

patronictl -c /etc/patroni/patroni.yml edit-config \
  -p 'shared_buffers=64GB' \
  -p 'huge_pages=on' \
  -p 'effective_cache_size=180GB' \
  -p 'maintenance_work_mem=2GB' \
  -p 'max_parallel_maintenance_workers=8' \
  -p 'wal_compression=zstd' \
  -p 'max_wal_size=64GB' \
  -p "shared_preload_libraries='pg_stat_statements,pg_prewarm'" \
  --force

# shared_buffers, huge_pages and shared_preload_libraries mandate a rolling restart
patronictl -c /etc/patroni/patroni.yml list
patronictl -c /etc/patroni/patroni.yml restart rag-cluster --pending --force

Extension settings like hnsw.ef_search should be applied at database or role scopes:

sql

ALTER DATABASE ragdb SET hnsw.ef_search = 100;
ALTER ROLE rag_batch_reader SET hnsw.ef_search = 200; -- offline batch evaluation jobs

▸3.2. Schema Design: Isolate Rapidly Updated Columns

Ensure the postgresql-17-pgvector binary package is installed across all Patroni cluster nodes before enabling the extension on the primary.

bash

# On all primary and standby hosts (Debian/Ubuntu PGDG repository)
sudo apt-get install -y postgresql-17-pgvector

sql

CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pg_buffercache;
CREATE EXTENSION IF NOT EXISTS pg_prewarm;
SELECT extname, extversion FROM pg_extension WHERE extname IN ('vector','pg_buffercache','pg_prewarm');

sql

-- Primary relational table: embeddings and immutable text content
CREATE TABLE documents_rag (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id integer NOT NULL,
    document_id uuid NOT NULL,
    chunk_no integer NOT NULL,
    chunk_text text NOT NULL,
    metadata jsonb,
    embedding halfvec(1536) NOT NULL, -- 2 x 1536 + 8 = 3080 bytes/row
    embedding_model text NOT NULL DEFAULT 'text-embedding-3-small',
    created_at timestamptz NOT NULL DEFAULT now()
) WITH (fillfactor = 90);

-- Rapidly mutated metrics table: never touches the HNSW vector index
CREATE TABLE documents_rag_stats (
    chunk_id bigint PRIMARY KEY REFERENCES documents_rag(id) ON DELETE CASCADE,
    hit_count bigint NOT NULL DEFAULT 0,
    last_accessed_at timestamptz
) WITH (fillfactor = 70); -- Ample page headroom for in-place HOT updates

CREATE INDEX CONCURRENTLY idx_documents_rag_tenant_doc
    ON documents_rag (tenant_id, document_id);

-- Aggressive autovacuum tuning: dead tuples exhaust candidate slots during HNSW traversal
ALTER TABLE documents_rag SET (
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_analyze_scale_factor = 0.01,
    autovacuum_vacuum_cost_limit = 2000
);
ALTER TABLE documents_rag_stats SET (autovacuum_vacuum_scale_factor = 0.01);

A halfvec(1536) vector occupies ~3 KB, surpassing the 2 KB TOAST threshold; the embedding is stored out-of-line in TOAST tables, preserving compact heap tuples. Confirm storage strategy (e for EXTERNAL uncompressed storage is expected for floating-point arrays):

sql

SELECT attname, atttypid::regtype, attstorage
FROM pg_attribute
WHERE attrelid = 'documents_rag'::regclass AND attname = 'embedding';

Dimension Limits: HNSW supports up to 2,000 dimensions for standard vector and up to 4,000 dimensions for halfvec. For 3072-dimension models (such as text-embedding-3-large), halfvec is an architectural requirement.

▸3.3. Empirically Sizing the HNSW Index via Sampling

Fixed rules for maintenance_work_mem are inadequate for HNSW. The deciding factor is total in-memory graph volume, roughly equal to on-disk index size:

Total Rows × (2 × Dimensions + 8 + 12 × m + ~100 bytes page/tuple overhead)

For 12M rows, 1536 dimensions, and m = 24, estimated size is 12M × 3.5 KB ≈ 42 GB; with page filling factors, expect 45–50 GB. Measure empirically on an UNLOGGED sample table:

sql

CREATE UNLOGGED TABLE rag_sample AS
SELECT * FROM documents_rag TABLESAMPLE SYSTEM (1);

SET maintenance_work_mem = '4GB';
SET max_parallel_maintenance_workers = 4;

CREATE INDEX rag_sample_hnsw
    ON rag_sample USING hnsw (embedding halfvec_cosine_ops)
    WITH (m = 24, ef_construction = 128);

SELECT
    (SELECT count(*) FROM rag_sample) AS sample_rows,
    pg_size_pretty(pg_relation_size('rag_sample_hnsw')) AS sample_index_size,
    pg_size_pretty(
        pg_relation_size('rag_sample_hnsw')
        * (SELECT reltuples FROM pg_class WHERE relname = 'documents_rag')
        / (SELECT count(*) FROM rag_sample)
    ) AS projected_full_size;

DROP TABLE rag_sample;

Parameter selection: m = 24 (default 16) improves high-dimension recall with negligible footprint overhead (~100 bytes per neighbor list). ef_construction = 128 (default 64) builds higher quality graph connectivity, scaling build time approximately linearly.

▸3.4. Pre-flight Operational Verification

bash

PGDATA=$(psql -Atc "SHOW data_directory")

# Ensure pg_wal has at least 1.5x projected index size in free space
df -h "$PGDATA/pg_wal"
du -sh "$PGDATA/pg_wal"

sql

-- Replication health: never start heavy builds during replication lag
SELECT application_name, state, sync_state,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_gap,
       replay_lag
FROM pg_stat_replication;

-- CREATE INDEX CONCURRENTLY must wait for all transactions holding old snapshots
SELECT pid, usename, application_name, state,
       now() - xact_start AS xact_age,
       backend_xmin,
       left(query, 80) AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
  AND pid <> pg_backend_pid()
ORDER BY xact_start NULLS LAST
LIMIT 10;

-- Stale replication slots retain WAL files and cause disk exhaustion
SELECT slot_name, active, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

▸3.5. Building the Index CONCURRENTLY

CREATE INDEX CONCURRENTLY cannot execute inside a transaction block, leaving an INVALID index if disconnected. Run within a persistent tmux session with ON_ERROR_STOP and an identifiable application_name on the Patroni leader:

bash

patronictl -c /etc/patroni/patroni.yml list # Verify leader node

tmux new -s hnsw_build

PGAPPNAME=hnsw_build psql -d ragdb -v ON_ERROR_STOP=1 <<'SQL'
\timing on
SET maintenance_work_mem = '48GB'; -- Projected sample size + safety margin
SET max_parallel_maintenance_workers = 7; -- 7 workers + 1 leader
SET statement_timeout = 0;

SELECT pg_current_wal_lsn() AS wal_start \gset

CREATE INDEX CONCURRENTLY idx_documents_rag_embedding_hnsw
    ON documents_rag
    USING hnsw (embedding halfvec_cosine_ops)
    WITH (m = 24, ef_construction = 128);

SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), :'wal_start')) AS wal_generated,
       pg_size_pretty(pg_relation_size('idx_documents_rag_embedding_hnsw')) AS index_size;
SQL

If graph construction exceeds allocated maintenance_work_mem, pgvector emits a runtime NOTICE. If seen, consider aborting and increasing memory:

text

NOTICE: hnsw graph no longer fits into maintenance_work_mem after 4183519 tuples
DETAIL: Building will take significantly more time.
HINT: Increase maintenance_work_mem to speed up builds.

Note on Spill: Contrary to common misconception, pgvector does *not* spill graph buffers into pgsql_tmp temporary files. When memory overflows, it writes directly to the index relation page-by-page. Monitor the NOTICE and progress metrics rather than temp_bytes.

Container Environments: Dynamic shared memory is used for parallel index builds. Docker's default 64 MB /dev/shm causes *"could not resize shared memory segment"* errors. Size --shm-size or Kubernetes emptyDir.medium: Memory adequately.

▸3.6. Cleaning Up Failed Builds

sql

SELECT indexrelid::regclass AS index_name, indisvalid, indisready, indislive
FROM pg_index
WHERE indrelid = 'documents_rag'::regclass
  AND NOT indisvalid;

-- INVALID indices are updated on DML writes but unused by planner
DROP INDEX CONCURRENTLY IF EXISTS idx_documents_rag_embedding_hnsw;

▸3.7. Buffer Cache Pre-warming

sql

-- Warm cache on primary
SELECT pg_prewarm('idx_documents_rag_embedding_hnsw');

bash

# Warm cache on read-only standby replicas (pg_prewarm functions in read-only mode)
for host in pg-standby-1 pg-standby-2; do
    psql -h "$host" -d ragdb -Atc "SELECT pg_prewarm('idx_documents_rag_embedding_hnsw');"
done

▸3.8. Query Tuning: ef_search & Iterative Index Scans

Standard RAG similarity retrieval with session-scoped ef_search:

sql

BEGIN;
SET LOCAL hnsw.ef_search = 100;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id,
       chunk_text,
       1 - (embedding <=> '[0.012,-0.023,...]'::halfvec(1536)) AS similarity
FROM documents_rag
ORDER BY embedding <=> '[0.012,-0.023,...]'::halfvec(1536)
LIMIT 5;
COMMIT;

When metadata filters are appended, HNSW finds ef_search candidates before applying the filter. For a tenant holding 1% of rows, ef_search = 40 yields only 0.4 expected candidates. pgvector 0.8.0 iterative index scan expands traversal dynamically until sufficient matches are satisfied:

sql

BEGIN;
SET LOCAL hnsw.ef_search = 100;
SET LOCAL hnsw.iterative_scan = strict_order; -- Exact distance order
SET LOCAL hnsw.max_scan_tuples = 50000;       -- Upper bound tuple scan limit

SELECT id, chunk_text
FROM documents_rag
WHERE tenant_id = 42
ORDER BY embedding <=> '[0.012,-0.023,...]'::halfvec(1536)
LIMIT 5;
COMMIT;

Using relaxed_order scans fewer tuples; re-order candidates via a materialized CTE:

sql

BEGIN;
SET LOCAL hnsw.iterative_scan = relaxed_order;

WITH ann AS MATERIALIZED (
    SELECT id, chunk_text,
           embedding <=> '[0.012,-0.023,...]'::halfvec(1536) AS distance
    FROM documents_rag
    WHERE tenant_id = 42
    ORDER BY distance
    LIMIT 20
)
SELECT id, chunk_text, distance
FROM ann
ORDER BY distance
LIMIT 5;
COMMIT;

For severe tenant data skew, maintain partial HNSW indices for large tenants and exact B-tree scans for small tenants:

sql

-- Dedicated index for dominant tenant
CREATE INDEX CONCURRENTLY idx_rag_hnsw_tenant_42
    ON documents_rag USING hnsw (embedding halfvec_cosine_ops)
    WITH (m = 24, ef_construction = 128)
    WHERE tenant_id = 42;

Prepared Statement Caveat: Generic query plans (tenant_id = $1) bypass partial indices. Force custom plan evaluations using SET plan_cache_mode = force_custom_plan for these query endpoints.

4. Monitoring & Diagnostic Queries

▸4.1. Index Build Progress

sql

SELECT p.pid,
       a.application_name,
       p.phase,
       p.tuples_done,
       p.tuples_total,
       round(100.0 * p.tuples_done / nullif(p.tuples_total, 0), 1) AS pct_done,
       now() - a.query_start AS elapsed,
       a.wait_event_type,
       a.wait_event
FROM pg_stat_progress_create_index p
JOIN pg_stat_activity a USING (pid);

▸4.2. Detecting Transactions Blocking CIC

sql

SELECT a.pid,
       pg_blocking_pids(a.pid) AS blocked_by,
       a.wait_event_type,
       a.wait_event,
       left(a.query, 60) AS query
FROM pg_stat_activity a
WHERE a.application_name = 'hnsw_build'
   OR a.pid = ANY (
       SELECT unnest(pg_blocking_pids(pid))
       FROM pg_stat_activity
       WHERE application_name = 'hnsw_build'
   );

▸4.3. Index Footprint and Scan Activity

sql

SELECT s.indexrelname AS index_name,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
       s.idx_scan,
       s.idx_tup_read,
       s.idx_tup_fetch,
       s.last_idx_scan -- PG16+
FROM pg_stat_user_indexes s
WHERE s.relname = 'documents_rag'
ORDER BY pg_relation_size(s.indexrelid) DESC;

▸4.4. Index Residency in shared_buffers

sql

SELECT c.relname,
       count(*) AS buffers,
       pg_size_pretty(count(*) * current_setting('block_size')::bigint) AS cached,
       pg_size_pretty(pg_relation_size(c.oid)) AS total,
       round(100.0 * count(*) * current_setting('block_size')::bigint
             / nullif(pg_relation_size(c.oid), 0), 2) AS pct_cached,
       round(avg(b.usagecount), 2) AS avg_usagecount
FROM pg_buffercache b
JOIN pg_class c
  ON b.relfilenode = pg_relation_filenode(c.oid)
 AND b.reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database())
WHERE c.relname IN ('idx_documents_rag_embedding_hnsw', 'documents_rag')
GROUP BY c.relname, c.oid;

▸4.5. Index Cache Hit Ratio

sql

SELECT indexrelname,
       idx_blks_hit,
       idx_blks_read,
       round(100.0 * idx_blks_hit / nullif(idx_blks_hit + idx_blks_read, 0), 2) AS hit_pct
FROM pg_statio_user_indexes
WHERE relname = 'documents_rag';

▸4.6. HOT Update Ratio & Dead Tuple Accumulation

sql

SELECT relname,
       n_tup_upd,
       n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct,
       n_tup_newpage_upd, -- PG16+
       n_live_tup,
       n_dead_tup,
       last_autovacuum,
       autovacuum_count
FROM pg_stat_user_tables
WHERE relname IN ('documents_rag', 'documents_rag_stats');

▸4.7. WAL Generation & Replication Lag

sql

-- Primary: streaming lag per replica
SELECT application_name,
       sync_state,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)) AS send_gap,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_gap,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

-- Cumulative WAL statistics
SELECT wal_records, wal_fpi, pg_size_pretty(wal_bytes) AS wal_bytes, stats_reset
FROM pg_stat_wal;

sql

-- Standby query conflict monitoring
SELECT datname, confl_snapshot, confl_lock, confl_bufferpin, confl_deadlock
FROM pg_stat_database_conflicts
WHERE datname = 'ragdb';

▸4.8. LWLock Contention (Parallel Ingestion)

sql

SELECT wait_event_type, wait_event, count(*) AS sessions
FROM pg_stat_activity
WHERE state = 'active'
  AND backend_type = 'client backend'
GROUP BY 1, 2
ORDER BY 3 DESC
\watch 1

▸4.9. Slow Vector Query Analytics

sql

SELECT calls,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       round(max_exec_time::numeric, 2) AS max_ms,
       shared_blks_hit,
       shared_blks_read,
       round(100.0 * shared_blks_hit
             / nullif(shared_blks_hit + shared_blks_read, 0), 2) AS hit_pct,
       left(query, 100) AS query
FROM pg_stat_statements
WHERE query ILIKE '%<=>%' OR query ILIKE '%<->%' OR query ILIKE '%<#>%'
ORDER BY mean_exec_time DESC
LIMIT 10;

▸4.10. Exact vs Approximate Recall Benchmarking

sql

SELECT embedding::text AS qv
FROM documents_rag TABLESAMPLE SYSTEM (0.01)
LIMIT 1 \gset

SET enable_indexscan = off;
SELECT array_agg(id) AS exact_ids
FROM (SELECT id FROM documents_rag
      ORDER BY embedding <=> :'qv'::halfvec(1536)
      LIMIT 10) s \gset
RESET enable_indexscan;

SET hnsw.ef_search = 100;
SELECT cardinality(ARRAY(
    SELECT unnest(:'exact_ids'::bigint[])
    INTERSECT
    SELECT id FROM (SELECT id FROM documents_rag
                    ORDER BY embedding <=> :'qv'::halfvec(1536)
                    LIMIT 10) ann
)) / 10.0 AS recall_at_10;

▸4.11. OS-Level Memory and I/O Telemetry

bash

# 1. NVMe queue depth and read latency (r_await)
iostat -xz 1 nvme0n1

# 2. PostgreSQL major page fault rates (majflt/s = reading from disk)
pidstat -r -p "$(pgrep -d',' -u postgres postgres)" 2 5

# 3. HNSW index residency in OS page cache
PGDATA=$(psql -d ragdb -Atc "SHOW data_directory")
REL=$(psql -d ragdb -Atc "SELECT pg_relation_filepath('idx_documents_rag_embedding_hnsw')")
vmtouch -v "$PGDATA/$REL" "$PGDATA/$REL".[0-9]* 2>/dev/null | tail -n 5

# 4. Monitor pg_wal growth during build
watch -n 5 "du -sh $PGDATA/pg_wal"

5. Production Best Practices & Gotchas

▸5.1. Strategic Decision Matrix

Scenario	Recommendation
Initial bulk ingestion	Load rows via `COPY` first, construct HNSW index at the end. One-shot build is exponentially faster than incremental tuple inserts.
Few thousand rows per tenant	HNSW is unnecessary; `(tenant_id)` B-tree index + exact scan offers 100% recall with sub-millisecond response.
Severe tenant data skew	Deploy partial HNSW indices for dominant tenants, exact scan or list partitioning for remaining tenants.
Embedding model migration	Never execute in-place `UPDATE embedding = ...`. Implement new column + new index + dual-write + cutover + drop old column. In-place updates trigger non-HOT heap rewrite, re-inserting entire graph and producing massive bloat.
IVFFlat vs HNSW	IVFFlat builds faster with lower RAM footprint, but list centroids are static to build data. In continuously growing RAG corpora, HNSW provides far greater recall stability.
Working set exceeds RAM	Sub-10ms latency is unattainable on a single node without RAM residency. Evaluate `halfvec`, binary quantization pre-filtering, or table partitioning.
High-traffic operational hours	While `CONCURRENTLY` does not lock DML, CPU, I/O and WAL overhead still impacts primary throughput. Schedule builds during off-peak windows.

▸5.2. Locking Matrix & Concurrency

Operation	Table Lock Mode	Blocks DML?	Production Gotcha
`CREATE INDEX`	`ShareLock`	Yes, blocks all `INSERT`/`UPDATE`/`DELETE`	`SELECT` continues. Avoid in production due to multi-hour build times.
`CREATE INDEX CONCURRENTLY`	`ShareUpdateExclusiveLock` (full duration)	No	Conflicts with `VACUUM`, `ANALYZE`, concurrent CIC, and most `ALTER TABLE` ops. Waits for old snapshots.
`REINDEX INDEX CONCURRENTLY`	`ShareUpdateExclusiveLock`	No	Temporarily keeps two indices concurrently, doubling disk space and WAL requirements.
`DROP INDEX CONCURRENTLY`	`ShareUpdateExclusiveLock`	No	Cannot run inside an explicit transaction block.
`VACUUM` (including HNSW)	`ShareUpdateExclusiveLock`	No	HNSW vacuum re-links deleted node neighbors; for heavily bloated indices, `REINDEX CONCURRENTLY` is faster.
`ALTER TABLE ... ADD COLUMN embedding halfvec(1536)`	`AccessExclusiveLock` (brief)	Yes, momentary	Nullable column without default is instantaneous. Always wrap in `SET lock_timeout = '3s'` with retry loop.
`INSERT` / non-HOT `UPDATE`	`RowExclusiveLock`	No	Graph buffer pages experience `BufferContent` LWLock contention under extreme concurrency.

▸5.3. Replication Dynamics

•WAL Volume and Synchronous Commit Latency: When graph construction fits within maintenance_work_mem, pgvector bulk-writes finalized index pages into WAL at completion. If memory spills, each tuple emits individual WAL records, multiplying volume. Under synchronous replication, every primary transaction blocks on standby WAL flush.
•Standby Query Cancellations: Standby cancellations stem from recovery conflicts managed by max_standby_streaming_delay. hot_standby_feedback = on reduces cancellations but delays primary vacuum cleanup.
•Cold Cache Post-Failover: Standby instances only load updated pages during WAL replay. An promoted standby holds a cold HNSW index. Configure pg_prewarm.autoprewarm = on and warm standby caches periodically.
•Extension Version Parity: Ensure identical pgvector binary versions across all nodes before executing ALTER EXTENSION vector UPDATE.

▸5.4. HOT Update Violations & Index Bloat

An UPDATE is HOT-qualified only if no indexed columns change and sufficient space exists in the current heap page. Every non-HOT update inserts a new tuple ctid into all secondary indices—requiring a full ef_construction graph traversal.

Dead tuples remain in the graph until vacuumed, consuming ef_search candidate slots and causing queries to return fewer rows than LIMIT.

•Isolate rapidly updated counters into dedicated companion tables.
•Set fillfactor = 90 on the base table to guarantee intra-page HOT space.
•Under severe bloat, execute REINDEX INDEX CONCURRENTLY to rebuild graph connectivity.

▸5.5. Pre-Production Checklist

Identical pgvector package version installed across all Patroni primary and standby nodes.
High-frequency counters separated from the main embedding table into a satellite stats table.
HNSW footprint measured via sample table and maintenance_work_mem sized appropriately per session.
pg_wal disk headroom (1.5x projected index), replication lag, slots, and active transactions validated.
Index built with CONCURRENTLY inside tmux with an identifiable application_name.
Post-build indisvalid verified and index prewarmed across primary and read replicas.
ef_search tuned against empirical recall/latency matrices; iterative index scan enabled for filtered queries.
Generic plan de-optimizations on partial indices mitigated using plan_cache_mode = force_custom_plan.

Cluster Health & Triage CLI

-- HNSW İndeks Durumu ve Boyutu
SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes WHERE relname = 'documents_rag';

-- Buffer Cache Yerleşimi
SELECT c.relname, count(*) AS buffers, pg_size_pretty(count(*) * 8192) AS cached, round(100.0 * count(*) * 8192 / nullif(pg_relation_size(c.oid), 0), 2) AS pct_cached FROM pg_buffercache b JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid) AND b.reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database()) WHERE c.relname IN ('idx_documents_rag_embedding_hnsw', 'documents_rag') GROUP BY c.relname, c.oid;

Topology Blueprint

3-Node Patroni Cluster (1 Primary + 1 Sync Standby + 1 Async DR) + pgvector 0.8.x + halfvec(1536)

Pros & Strengths

•Eliminates dedicated vector database licensing and dual-write reconciliation overhead
•Guarantees ACID consistency by keeping relational records and vector embeddings in identical transactions
•Leverages pgvector 0.8.x iterative scan and halfvec for optimal RAM, CPU, and cache residency

Cons & Constraints

•Graph construction demands substantial maintenance_work_mem and heavy WAL throughput
•High WAL volume can elevate primary commit latencies in synchronous replication topologies
•Non-HOT updates trigger index bloat and degrade ef_search candidate recall

Pre-Prod Checklist

pgvector package must be installed with identical versions across all Patroni nodes.
Frequently updated counter fields (hit_count, last_accessed_at) must be isolated into a dedicated table.
Index footprint must be measured via sampling and maintenance_work_mem sized accordingly per session.
pg_wal disk space, replication lag, replication slots, and long-running transactions must be verified pre-flight.
Build must run with CONCURRENTLY inside tmux with an identifiable application_name.
Post-build indisvalid state must be verified, and index prewarmed on primary and standby instances.
ef_search must be calibrated against measured recall/latency matrices; enable iterative scan for filtered queries.
Mitigate generic plan de-optimizations on partial indices using plan_cache_mode = force_custom_plan.

Other HA Topologies

POSTGRESQL

PostgreSQL + Patroni + etcd + HAProxy Enterprise HA

MYSQL

MySQL InnoDB Cluster + Group Replication + MySQL Router Topology

SQLSERVER

SQL Server Always On Availability Groups (AG) Topology