FG
💻 Software🤖 AI & LLMs

Logical replication of Vector column

Fresh5 days ago
Mar 14, 20260 views
Confidence Score53%
53%

Problem

Hi all, Are the Vector column types supported by logical replication? I am trying to replicate a Vector column on Postgres 15.5, but it is failing with: [code block] As workaround, I am able to replicate the vector as text and cast it back to vector. Thank you!

Error Output

ERROR: malformed vector literal: "[0.024066104"

Unverified for your environment

Select your OS to check compatibility.

1 Fix

Canonical Fix
Unverified Fix
New Fix – Awaiting Verification

Implement Vector Column Replication Workaround

Medium Risk

PostgreSQL's logical replication does not support the pgvector extension's Vector column type directly, leading to errors when attempting to replicate these columns. The error 'malformed vector literal' indicates that the replication process cannot interpret the Vector data format correctly.

Awaiting Verification

Be the first to verify this fix

  1. 1

    Create a Text Representation of the Vector

    Modify the table schema to include an additional text column that will store the vector data as a string. This allows for logical replication to occur without errors.

    sql
    ALTER TABLE your_table ADD COLUMN vector_text TEXT;
  2. 2

    Insert Vector Data as Text

    When inserting data into the table, convert the Vector column to text format and store it in the new text column. This ensures that the data can be replicated without issues.

    sql
    INSERT INTO your_table (vector_text) VALUES (vector::TEXT);
  3. 3

    Set Up Logical Replication

    Configure logical replication on the modified table. Ensure that the replication includes the new text column instead of the original Vector column.

    sql
    CREATE PUBLICATION my_publication FOR TABLE your_table;
  4. 4

    Replicate and Cast Back to Vector

    On the subscriber side, create a new column to store the Vector data and cast the text representation back to the Vector type during insertion.

    sql
    ALTER TABLE your_table ADD COLUMN vector_vector VECTOR;
INSERT INTO your_table (vector_vector) VALUES (vector_text::VECTOR);
  5. 5

    Test the Replication Process

    Perform a test by inserting new records into the publisher and verifying that they appear correctly in the subscriber with the Vector data properly cast back.

    sql
    SELECT * FROM your_table;

Validation

To confirm the fix worked, check the subscriber database to ensure that the Vector data is replicated correctly and that no errors are thrown during the process. Validate the integrity of the data by comparing the original Vector values with the replicated ones.

Sign in to verify this fix

Environment

Submitted by

AC

Alex Chen

2450 rep

Tags

pgvectorembeddingsvector-search