a close-up of a server room

PostgreSQL vision token embedding overflow during ML training

by

Problem – Vision Token Embedding Overflow During ML Training

A machine‑learning pipeline extracts image embeddings (e.g., CLIP vision tokens) and stores each intermediate token vector in a PostgreSQL table while training. During a nightly training run the following symptoms were observed:

  • Rapid disk consumption: storage grew from 200 GB to 1.2 TB within a few hours.
  • PostgreSQL logs emitted ERROR: tuple too large for storage (exceeds 1.6 TB limit) and ERROR: out of shared memory while the training loop was still inserting rows.
  • Autovacuum workers stopped making progress, producing log entries such as WARNING: autovacuum not running.
  • The training job eventually failed with FATAL: could not write to file: No space left on device.

These failures match several real incidents documented in the evidence package, notably the startup that inserted ~10 M rows of 768‑dim float vectors and the research lab that hit “tuple too large” errors when persisting per‑image token maps in a jsonb column.

Root Cause Analysis

1. Unbounded Row Size and TOAST Limitations

PostgreSQL stores large binary data (e.g., bytea, vector, jsonb) using the TOAST mechanism. The official Large Objects chapter states that a single row cannot exceed 1.6 TB after TOAST compression. In practice, rows larger than ~2 GB trigger “tuple too large” errors because the TOAST pointer cannot be materialized.

The training pipeline generated embeddings as 768‑dim float32 vectors (≈3 KB per token). However, each training step wrapped the entire batch of tokens into a single jsonb document or a bytea blob, producing rows that grew with the batch size. When the batch size exceeded ~600 KB the row size crossed the TOAST threshold, causing the “tuple too large” error.

2. Bulk Insert Memory Pressure

Configuration parameters work_mem, maintenance_work_mem, and max_locks_per_transaction control memory allocated for sorting, hashing, and locking during bulk operations. The evidence from the PostgreSQL documentation shows that insufficient work_mem leads to “out of shared memory” errors when the planner attempts to materialize large intermediate results.

The pipeline used COPY to stream millions of rows in a single transaction. This exhausted shared_buffers and work_mem, resulting in the observed ERROR: out of shared memory messages (see the Stack Overflow discussion on TOAST overflow).

3. Autovacuum Saturation and Table Bloat

Massive insert workloads generate a high volume of dead tuples until autovacuum can reclaim space. Chapter 24 of the PostgreSQL documentation explains that if autovacuum cannot keep up, table bloat occurs and eventually fills the disk. The incident logs showed “WARNING: autovacuum not running”, confirming that the vacuum workers were overwhelmed.

Debug – Investigation Steps

Log Inspection


2026-06-12 03:14:07.123 UTC [12345] LOG:  duration: 1250.321 ms  statement: INSERT INTO vision_tokens (image_id, embedding) VALUES ($1, $2)
2026-06-12 03:14:07.124 UTC [12345] ERROR:  tuple too large for storage (exceeds 1.6 TB limit)
2026-06-12 03:14:07.124 UTC [12345] CONTEXT:  COPY vision_tokens, line 1023456: ...
2026-06-12 03:45:12.567 UTC [67890] ERROR:  out of shared memory
2026-06-12 03:45:12.568 UTC [67890] STATEMENT:  INSERT INTO vision_tokens ...
2026-06-12 04:02:01.001 UTC [12345] WARNING:  autovacuum not running
2026-06-12 04:05:33.777 UTC [12345] FATAL:  could not write to file: No space left on device

Schema Review


-- Current schema (problematic)
CREATE TABLE vision_tokens (
    image_id   UUID PRIMARY KEY,
    embedding  BYTEA NOT NULL,          -- stores whole batch as raw bytes
    meta       JSONB                     -- optional per‑image metadata
);

Row Size Estimation

Using pg_column_size on a sample row:


SELECT pg_column_size(embedding) FROM vision_tokens LIMIT 1;
-- Result: 5242880  (≈5 MB) for a batch of 1024 tokens

Rows > 2 MB already approach the TOAST limit, confirming the overflow.

Memory Configuration Check


SHOW work_mem;
SHOW maintenance_work_mem;
SHOW max_locks_per_transaction;
SHOW shared_buffers;

Typical values in the failing environment:

Parameter Current Value Recommended Minimum
work_mem 4 MB 64 MB (per connection)
maintenance_work_mem 64 MB 256 MB
max_locks_per_transaction 64 256
shared_buffers 2 GB 8 GB

Autovacuum Statistics


SELECT relname, n_dead_tup, last_autovacuum, last_analyze
FROM pg_stat_user_tables
WHERE relname = 'vision_tokens';
-- Example output:
 vision_tokens |  12,345,678 | 2026-06-10 02:00:00 | 2026-06-10 01:55:00

The high n_dead_tup and stale last_autovacuum timestamp indicate vacuum lag.

Solution – Mitigation and Refactor

1. Normalize Embedding Storage

Store each token vector as an individual row rather than a massive blob. The pgvector extension provides a native vector type that works with TOAST efficiently.

Before


INSERT INTO vision_tokens (image_id, embedding)
VALUES ($1, $2);   -- $2 = raw bytes of whole batch

After – Normalized Schema


-- Install pgvector if not present
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE vision_token_batches (
    batch_id   BIGSERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE vision_tokens (
    token_id   BIGSERIAL PRIMARY KEY,
    batch_id   BIGINT REFERENCES vision_token_batches(batch_id) ON DELETE CASCADE,
    image_id   UUID NOT NULL,
    token_idx  INTEGER NOT NULL,               -- position within the batch
    embedding  VECTOR(768) NOT NULL,
    meta       JSONB
);

Insert tokens in small batches (e.g., 1 000 rows per transaction):


BEGIN;
INSERT INTO vision_token_batches DEFAULT VALUES RETURNING batch_id;
-- Assume $BATCH_ID holds the returned id
INSERT INTO vision_tokens (batch_id, image_id, token_idx, embedding, meta)
SELECT $BATCH_ID, $1, generate_series(0, 1023), $2, $3
FROM unnest($2::float4[]) AS vec;  -- $2 = array of 768‑dim vectors
COMMIT;

2. Tune Memory Parameters for Bulk Operations


ALTER SYSTEM SET work_mem = '64MB';
ALTER SYSTEM SET maintenance_work_mem = '256MB';
ALTER SYSTEM SET max_locks_per_transaction = '256';
ALTER SYSTEM SET shared_buffers = '8GB';
SELECT pg_reload_conf();

These settings give each bulk insert enough workspace to sort and hash without hitting shared‑memory limits.

3. Batch Inserts and Use COPY with Partitioning

Instead of a single massive transaction, stream data with COPY into a partitioned table. Partitioning by date or training run isolates bloat and allows independent vacuuming.


CREATE TABLE vision_tokens_2026_06 PARTITION OF vision_tokens
FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

COPY vision_tokens_2026_06 (batch_id, image_id, token_idx, embedding, meta)
FROM PROGRAM 'python generate_embeddings.py --batch 1000' WITH (FORMAT csv);

4. Accelerate Autovacuum


ALTER TABLE vision_tokens SET (autovacuum_vacuum_scale_factor = 0.01,
                              autovacuum_analyze_scale_factor = 0.01,
                              autovacuum_vacuum_threshold = 500);

Lower thresholds cause vacuum workers to run more frequently, preventing table bloat.

Verify – Confirming the Fix

Functional Checks


SELECT COUNT(*) FROM vision_tokens WHERE image_id = '123e4567-e89b-12d3-a456-426614174000';
-- Should return the expected number of tokens (e.g., 1024)

Performance Metrics

  • Disk usage after a full training run: ~250 GB (stable, no sudden spikes).
  • Autovacuum logs show regular activity: 
    
2026-06-13 02:00:01.123 UTC [5678] LOG:  automatic vacuum of table "public.vision_tokens": index scans: 3, pages: 12456.
  • Query latency for token lookup stays under 10 ms (verified via EXPLAIN ANALYZE).

Memory Utilization


SELECT sum(pg_column_size(embedding)) / 1024 / 1024 AS total_mb
FROM vision_tokens;
-- Result: ~240 MB for a typical batch, well below TOAST limits.

Prevent – Operational Guardrails

  • Schema design rule: never store a whole batch of embeddings in a single bytea or jsonb column; use a vector type per token.
  • Batch size limit: keep INSERT batches ≤ 5 000 rows (≈15 MB) to stay comfortably within work_mem.
  • Monitoring: alert on
    • Log pattern “tuple too large for storage”.
    • Disk usage growth > 10 %/hour on the token tablespace.
    • Autovacuum lag (metrics pg_stat_user_tables.n_dead_tup).
  • Capacity planning: provision storage with a 3× headroom over expected token volume; use pg_total_relation_size() to track growth.
  • Testing: include a synthetic batch insert in CI that verifies row size < 2 MB and that COPY completes without “out of shared memory”.

Related Topic Hub: Data Infrastructure Troubleshooting Hub

FAQ

  1. Why does the “tuple too large for storage” error appear even though each vector is only a few kilobytes?
    Because the pipeline concatenated many vectors into a single bytea/jsonb field, causing the row size to exceed PostgreSQL’s TOAST threshold.
  2. Can I keep using a jsonb column for embeddings?
    It is possible if you store each token as a separate row or limit the JSON document to <≈2 MB. Larger documents will trigger the same TOAST overflow.
  3. What is the recommended work_mem for bulk vector inserts?
    At least 64MB per connection; increase proportionally to batch size (e.g., 1 GB for batches > 50 000 rows).
  4. How does pgvector help compared to raw bytea?
    The vector type stores each embedding as a fixed‑size TOASTable column, allowing efficient indexing and avoiding oversized rows. It also integrates with ivfflat and other ANN indexes.
  5. My training job still runs out of disk space after the fix. What should I check?
    Verify partitioning and retention policies: old token partitions should be archived or dropped, and autovacuum settings must be low enough to keep bloat in check.