PostgreSQL vector search malformed JSON response after RAG pipeline update

Problem Description

After a routine update to the Retrieval‑Augmented Generation (RAG) pipeline, the citation extraction step began returning malformed JSON objects. Downstream the LLM serializer throws errors such as:

JSON parsing error: Unexpected token '}' at position 127
ERROR: invalid input syntax for type jsonb

Observed symptoms in production logs:

• API responses missing source_id fields in the citations array.
• Occasional empty citations arrays despite successful vector matches.
• High‑traffic gateway (≈15 k RPS) showing spikes in pgBouncer connection pool exhaustion warnings.
• PostgreSQL logs containing:

2024-07-30 14:02:18.123 UTC [12345] LOG:  canceling statement due to statement timeout
2024-07-30 14:02:18.124 UTC [12345] ERROR:  canceling statement due to statement timeout (SQLSTATE 57014)
2024-07-30 14:02:18.125 UTC [12345] LOG:  could not obtain lock on row in relation "pg_pool" (SQLSTATE 55P03)
2024-07-30 14:02:18.126 UTC [12345] FATAL:  remaining connection slots are reserved for non-replication superuser users (SQLSTATE 53300)

These errors correlate with the RAG citation format failures, causing broken reference links in the generated text.

Root Cause Analysis

The failure is a convergence of three interacting factors:

1. Connection‑pool exhaustion – The API gateway uses pgBouncer with max_pool_size set to 200. Under 15 k RPS the pool hits the limit, producing the SQLSTATE 55P03 lock error. When a client cannot obtain a connection, the query is cancelled, and the application falls back to a partial result set.
2. Statement timeout truncation – Hybrid vector+metadata queries (vector similarity + WHERE filters) often exceed the default statement_timeout = 5s. PostgreSQL aborts the query mid‑execution, leaving jsonb_agg with an incomplete aggregation. This matches the “canceling statement due to statement timeout” error (SQLSTATE 57014) reported in the logs.
3. JSON construction bug under high concurrency – The query builds citations with jsonb_build_object and jsonb_agg. As documented in PostgreSQL JSON Functions, if any row is omitted (e.g., because source_id is NULL or the row is dropped by timeout), the resulting JSON array is malformed. The pgvector issue #112 confirms that large result sets under load can produce truncated JSON when the aggregator is interrupted.

Combined, these issues cause the RAG pipeline to receive either an empty citations array or objects missing the mandatory source_id, leading to serialization failures.

Investigation and Debugging

Step‑by‑step diagnostics that reproduced the failure:

1. Check connection pool metrics via pgbouncer stats:

SHOW POOLS;
+------------+------+--------+----------+--------+----------+------------+
| database   | user | cl_active | cl_waiting | sv_active | sv_idle | maxwait |
+------------+------+--------+----------+--------+----------+------------+
| rag_db     | api  | 200    | 45     | 200      | 0      | 12.3s   |
+------------+------+--------+----------+--------+----------+------------+

Active connections constantly hit the max_pool_size, confirming exhaustion.

2. Inspect PostgreSQL statement timeout:

SELECT name, setting FROM pg_settings WHERE name = 'statement_timeout';
 name             | setting
------------------+---------
 statement_timeout| 5000

3. Reproduce the query with EXPLAIN ANALYZE to capture timing and row counts:

EXPLAIN ANALYZE
SELECT jsonb_agg(
         jsonb_build_object(
           'source_id', source_id,
           'text', chunk_text,
           'score', similarity
         )
       ) AS citations
FROM (
  SELECT id AS source_id,
         text AS chunk_text,
         embedding <=> query_vector AS similarity
  FROM documents
  WHERE metadata->>'category' = 'policy'
  ORDER BY embedding <=> query_vector
  LIMIT 50
) sub;

Typical output under load:

Aggregate  (cost=12345.67..12345.68 rows=1 width=0) (actual time=5023.456..5023.456 rows=0 loops=1)
  ->  Subquery Scan on sub  (cost=0.00..12345.66 rows=50 width=0) (actual time=5000.123..5000.123 rows=30 loops=1)
        Filter: (metadata->>'category' = 'policy'::text)
        Rows Removed by Filter: 120
Planning Time: 0.123 ms
Execution Time: 5023.600 ms

The execution exceeds the 5 s timeout, causing cancellation after ~5 s and returning only 30 rows instead of the requested 50. The resulting jsonb_agg is incomplete.

4. Capture the malformed JSON payload from the application log:

2024-07-30T14:02:18.210Z ERROR RAGPipeline - Citation payload malformed: [{"source_id":123,"text":"..."},"}"]

5. Validate the SELECT list – a recent schema change added the column source_id but the retrieval query was not updated, causing NULL values in some rows (see LlamaIndex forum thread). This matches the “null for source_id” symptom observed in the streaming media incident.

Resolution

Three categories of fixes were applied:

1. Connection‑pool scaling

Increase pgBouncer limits and reserve slots for critical traffic.

Parameter	Before	After
max_pool_size	200	400
reserve_pool_size	5	20
max_db_connections	300	500

Configuration snippet (pgbouncer.ini):

# Before
max_pool_size = 200
reserve_pool_size = 5

# After
max_pool_size = 400
reserve_pool_size = 20

2. Statement timeout adjustment

Raise the timeout to accommodate hybrid ANN queries and add a per‑query override.

# postgresql.conf
statement_timeout = 15000      # 15 s globally

# Application‑side override for the retrieval query
SET statement_timeout = 12000;  -- 12 s for RAG searches

3. Robust JSON construction

Rewrite the citation query to guarantee that every row contributes a well‑formed object, even when source_id is NULL. Use COALESCE and a sub‑query that filters out rows with missing identifiers before aggregation.

Before:

SELECT jsonb_agg(
         jsonb_build_object(
           'source_id', source_id,
           'text', chunk_text,
           'score', similarity
         )
       ) AS citations
FROM (
  SELECT id AS source_id,
         text AS chunk_text,
         embedding <=> query_vector AS similarity
  FROM documents
  WHERE metadata->>'category' = 'policy'
  ORDER BY embedding <=> query_vector
  LIMIT 50
) sub;

After:

SET statement_timeout = 12000;  -- ensure query can finish

WITH ranked AS (
  SELECT
    id AS source_id,
    text AS chunk_text,
    embedding <=> $1::vector AS similarity,
    row_number() OVER (ORDER BY embedding <=> $1::vector) AS rn
  FROM documents
  WHERE metadata->>'category' = 'policy'
    AND id IS NOT NULL            -- filter out missing identifiers
)
SELECT jsonb_agg(
         jsonb_build_object(
           'source_id', source_id,
           'text', chunk_text,
           'score', similarity
         )
       ) AS citations
FROM ranked
WHERE rn <= 50;

Using a CTE with row_number() guarantees deterministic ordering and allows the WHERE rn <= 50 clause to be applied after the vector similarity calculation, preventing the planner from pushing the LIMIT into the index scan and truncating the aggregation.

4. Deploy changes

All modifications were rolled out via a rolling restart of the API gateway and a zero‑downtime pgbouncer configuration reload (RELOAD command). The migration script added a NOT NULL constraint on source_id after back‑filling missing values, eliminating future NULL rows.

Verification

Post‑fix validation steps:

1. Run the retrieval query manually and confirm full JSON array:

SELECT jsonb_pretty(citations) FROM (
  -- query from the “After” block
) t;
[
  {
    "source_id": 101,
    "text": "Policy clause ...",
    "score": 0.9234
  },
  ...
  {
    "source_id": 150,
    "text": "Policy amendment ...",
    "score": 0.8912
  }
]

2. Execute an integration test that invokes the RAG endpoint with a known query and asserts that the citations array contains exactly 50 objects, each with a non‑null source_id.
3. Monitor pgBouncer metrics for cl_waiting and maxwait. After the change they remained < 5 % of capacity for 15 k RPS load.
4. Check PostgreSQL logs for the absence of statement timeout and connection pool exhaustion messages over a 30‑minute high‑traffic window.

Prevention and Best Practices

• Capacity planning for connection pools – Size pgBouncer pools based on peak RPS × avg_concurrent_queries. Reserve at least 10 % of slots for burst traffic.
• Per‑query timeout controls – Use SET statement_timeout only around long‑running hybrid searches; keep the global default low to protect other workloads.
• Guard against NULL identifiers – Enforce NOT NULL on primary identifier columns and add a migration that back‑fills missing values.
• Paginate large result sets – Instead of aggregating 1000+ rows in a single JSON array, page results (e.g., 50 per page) and concatenate on the application side.
• Observability – Export pg_stat_activity and pgbouncer metrics to Prometheus. Alert on:
  • statement timeout rate > 1 % of queries.
  • pgBouncer cl_waiting > 20 % of max_pool_size.
  • JSON aggregation errors from the application logs.

Related Topic Hub: Data Infrastructure Troubleshooting Hub

FAQ

1. Why does the citation JSON break only under high load?
   Because high concurrency exhausts the connection pool, causing query cancellations. Canceled queries leave jsonb_agg incomplete, which produces malformed JSON.
2. Can I keep the global statement_timeout low and still run hybrid searches?
   Yes. Apply a per‑session override (e.g., SET statement_timeout = 12000;) before the retrieval query, then reset it after execution.
3. What is the safest way to add new columns (e.g., source_id) without breaking existing JSON builds?
   Add the column with a default, back‑fill missing values, then add a NOT NULL constraint. Update the SELECT list to reference the new column and use COALESCE if necessary.
4. How can I detect truncated JSON arrays before they reach the LLM?
   Validate the size of the JSON array server‑side: jsonb_array_length(citations) = expected_count. If the length is lower, retry the query or fall back to a cached result.
5. Is paging the JSON aggregation a better approach for large corpora?
   Yes. Aggregating thousands of rows in a single jsonb_agg is prone to timeouts and memory pressure. Use LIMIT/OFFSET or keyset pagination and combine the pages in the application layer.