Error: Index Row Size Exceeds Btree Maximum

Issue

A connection fails with the following error:

ERROR: index row size 5176 exceeds btree version 4 maximum 2704 for index "<index_name>". Detail: Index row references tuple in relation "<table_name>". Hint: Values larger than 1/3 of a buffer page cannot be indexed.

Environment

Destination: PostgreSQL

Resolution

To resolve this issue, review and modify the affected index in your PostgreSQL destination:

1. Identify the index and table mentioned in the error.

2. Determine whether the index is required:

   • If the index is not necessary, drop it.
   • If the index is required, modify it to support large values.

3. Recreate the index so that it doesn't directly index large text fields. For example, use a function-based index to hash the column:

   CREATE INDEX idx_example ON table_name (md5(column_name));
   

   Alternatively, use:

   • Full-text indexing, if you need to search large text values.
   • Trigram indexing, if you need pattern-based searches.

4. In Fivetran, gp to the relevant connection overview page and click Sync in the top right.

Cause

This issue occurs when PostgreSQL inserts or updates a value in a B-tree index that exceeds the maximum allowed index row size, which is approximately 2704 bytes. PostgreSQL doesn't allow index entries larger than one-third of a buffer page. If one or more indexed values exceed this limit, the sync fails.