Embedding versions management, TOAST and bloating in PostgreSQL

If you are working with vector embeddings you probably already know the AI models including embedding models have their own lifecycle. When a new model is released by a service provider the old version will be eventually deprecated and removed from the service. You might have better control with models deployed on your own infrastructure like EmbeddingGemma in this codelab but eventually you might want the new model just because it works better.

What is the impact of refreshing all the vector embedding in your AlloyDB or PostgreSQL database? Let’s dive in.

Prepare the tests data

Let us prepare a test environment. In my tests I am going to use AlloyDB Omni since it is fully PostgreSQL compatible and in this case behaves exactly like any PostgreSQL database.

We create a test table with the vector data type, add some indexes, and generate 20k rows.

-- Install extensions
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- Create a function building random vectors
CREATE OR REPLACE FUNCTION random_vector(dim integer)
 RETURNS vector
LANGUAGE sql VOLATILE AS $$
SELECT array_agg(random()::real)::vector FROM generate_series(1, dim);
$$;

-- Create a demo table
DROP TABLE IF EXISTS demo_documents CASCADE;
CREATE TABLE demo_documents (
    id BIGSERIAL PRIMARY KEY,
    category_id INT NOT NULL,
    status VARCHAR(32) NOT NULL,
    title VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    -- Large text column (> 2.5 KB) to demonstrate TOAST interaction with uncompressed/compressed text
    content TEXT NOT NULL,
    -- 768-dim float vector (~3.08 KB) -> pushed to TOAST
    embedding vector(768)
);

-- Pause autovacuum temporarily
ALTER TABLE demo_documents SET (
    autovacuum_enabled = false
);

-- Add a few B-tree indexes
CREATE INDEX idx_docs_category_created ON demo_documents (category_id, created_at);
CREATE INDEX idx_docs_status ON demo_documents (status);
CREATE INDEX idx_docs_title ON demo_documents (title);

-- Create HNSQ index on the vectores
CREATE INDEX idx_docs_embedding_hnsw ON demo_documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- Generate some data
INSERT INTO demo_documents (category_id, status, title, created_at, content, embedding)
SELECT
    (random() * 50)::int + 1,
    (ARRAY['draft', 'published', 'archived', 'review'])[(random() * 3)::int + 1],
    'Document title #' || g,
    now() - (g || ' minutes')::interval,
    -- ~3,000 bytes of repetitive text (> 2 KB TOAST threshold)
    repeat('PostgreSQL TOAST and vector storage internals demo ' || g || ' ', 50),
    -- 768-dim vector
    random_vector(768)
FROM generate_series(1, 20000) AS g;

Check the size of all segments

Let’s establish a clear baseline for comparison. We will run an initial vacuum analyze and check all our relations.

-- Run an initial vacuum to start with clean baseline metrics
VACUUM ANALYZE demo_documents;

-- Check all the segments
SELECT
    c.relname AS object_name,
    CASE c.relkind
        WHEN 'r' THEN 'table'
        WHEN 't' THEN 'toast table'
        WHEN 'i' THEN 'index'
    END AS kind,
    pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM pg_class c
WHERE c.relname = 'demo_documents'
   OR c.oid IN (SELECT indexrelid FROM pg_index WHERE indrelid = 'demo_documents'::regclass)
   OR c.oid = (SELECT reltoastrelid FROM pg_class WHERE relname = 'demo_documents')
ORDER BY pg_relation_size(c.oid) DESC;

Here is what we get:

        object_name | kind | size
---------------------------+-------------+---------
 idx_docs_embedding_hnsw | index | 78 MB
 pg_toast_3177242 | toast table | 81 MB
 demo_documents | table | 4336 kB
 idx_docs_category_created | index | 1024 kB
 idx_docs_title | index | 1024 kB
 demo_documents_pkey | index | 512 kB
 idx_docs_status | index | 256 kB
(7 rows)

Our demo_documents table with indexes and TOAST occupies about 170 MB. It might be helpful also to check tuples (physical rows):

-- Dead/Live tuple stats
SELECT 'Main Table' AS component, table_len,tuple_count,tuple_percent,dead_tuple_count, free_percent FROM pgstattuple('demo_documents');
-- TOAST Table Dead/Live tuple stats
SELECT 'TOAST Table' AS component, table_len,tuple_count,tuple_percent,dead_tuple_count, free_percent FROM pgstattuple((SELECT reltoastrelid FROM pg_class WHERE relname = 'demo_documents'));

We see the main table and the TOAST have zero dead tuples and reasonable small free space percentage:

 component | table_len | tuple_count | tuple_percent | dead_tuple_count | free_percent
------------+-----------+-------------+---------------+------------------+--------------
 Main Table | 4440064 | 20000 | 90.92 | 0 | 1.57

  component | table_len | tuple_count | tuple_percent | dead_tuple_count | free_percent
-------------+-----------+-------------+---------------+------------------+--------------
 TOAST Table | 84492288 | 40000 | 74.52 | 0 | 21.82

Here is a basic graph to illustrate the layout:

Updating the vectors

We emulate the process of refreshing embeddings by updating all the vectors in the demo_documents table. For example we had all our embeddings created using Google text-embedding-004 model and we updated all of them using gemini-embedding-2 model. Google publishing life cycle for all its models in the documentation where you can see it and plan your maintenance in advance.

-- Update all vector embeddings
UPDATE demo_documents
SET embedding = random_vector(768);

After updating all the rows we can check the sizes of all objects again and see how they’ve changed:

        object_name | kind | size before update | size after update
---------------------------+-------------+--------------------+-------------------
 idx_docs_embedding_hnsw | index | 78 MB | 156 MB
 pg_toast_3177242 | toast table | 81 MB | 159 MB
 demo_documents | table | 4336 kB | 7376 kB
 idx_docs_category_created | index | 1024 kB | 2048 kB
 idx_docs_title | index | 1024 kB | 2048 kB
 demo_documents_pkey | index | 512 kB | 1024 kB
 idx_docs_status | index | 256 kB | 512 kB
(7 rows)

Almost all objects including table, TOAST and indexes doubled in size. The impact is most visible for the HNSW vector index and the demo_documents table itself. Here is information about dead tuples and space allocation for the main table and TOAST:

 component | table_len | tuple_count | tuple_percent | dead_tuple_count | free_percent
------------+-----------+-------------+---------------+------------------+--------------
 Main Table | 7585792 | 20000 | 47.84 | 20000 | 1.2

  component | table_len | tuple_count | tuple_percent | dead_tuple_count | free_percent
-------------+-----------+-------------+---------------+------------------+--------------
 TOAST Table | 166387712 | 40000 | 37.84 | 40000 | 22.21

Here is a diagram:

So, roughly half of the space is occupied by dead tuples. When I discuss it with some developers they mention the vacuuming process and at least some of them had expectations that it should fix the bloating. Let’s do some vacuuming and see if it changes it.

VACUUM (VERBOSE, ANALYZE) demo_documents;

The verbose output provides enough information about what was done during the vacuum process and everybody who is curious can check it in detail. We can have a look into the main stats about tuples:

 component | table_len | tuple_count | tuple_percent | dead_tuple_count | free_percent
------------+-----------+-------------+---------------+------------------+--------------
 Main Table | 7585792 | 20000 | 47.84 | 0 | 50.16

  component | table_len | tuple_count | tuple_percent | dead_tuple_count | free_percent
-------------+-----------+-------------+---------------+------------------+--------------
 TOAST Table | 166387712 | 40000 | 37.84 | 0 | 60.17

The vacuum process cleared up the dead tuples leaving the space allocation intact and it was exactly what we expected. We have free space which can be used by the new rows but the allocation on the disk didn’t change.

        object_name | kind | size before vacuum | size after vacuum
---------------------------+-------------+--------------------+-------------------
 pg_toast_3177242 | toast table | 159 MB | 159 MB
 idx_docs_embedding_hnsw | index | 156 MB | 156 MB
 demo_documents | table | 7408 kB | 7408 kB
 idx_docs_category_created | index | 2048 kB | 2048 kB
 idx_docs_title | index | 2048 kB | 2048 kB
 demo_documents_pkey | index | 1024 kB | 1024 kB
 idx_docs_status | index | 512 kB | 512 kB

Alternative layout

What if the bloating is a significant issue for your workload? In such a case you might consider a different layout and place the vectors in a separate table connecting to the main table using primary keys. Then you can use either a view or simple table join to work with the data. And when the vectors are required to be updated — create a new table with a new vector version.

Such an approach can save you some vacuum overhead and bloating. But of course the join of two (or more) tables might perform slightly differently than the single flat table approach. You need to test it with your data and with your vectors.

Conclusion

The final layout of your table might depend on multiple factors. In real life I very rarely saw a case when the search was performed only in one single table. In most cases it was always a combination of 2,3,4, or more tables with the resulting dataset. Considering other parts like filters on non-vector columns or full-text search combined with the semantic search, the real case might be much more complicated than a demo with one flat table where all the data is stored in one segment. And for a high-loaded scalable environment, the right relational model can be a key for success and requires vigorous testing.

In the next post I will try to evaluate the performance overhead of keeping the vectors in a separate table vs having them in the same table. Stay tuned!

---