0

Currently trying to migrate a Azure Postgres instance from pg11 to pg13, using pg_dump and pg_restore.

However there's a problematic table that is complaining about a large index b-tree size, I suspect this is because of the postgres12 B-Tree byte size decrease, however when looking at the data I cannot find any data that goes past 2704.

PG Restore failed for database 'postgres' with exit code '1' and error message 'error: COPY failed for table "table": ERROR: index row size 2712 exceeds btree version 4 maximum 2704 for index "table_reach_id_idx"'.

When looking at the table:

postgres=> select length(reach), pg_column_size(reach), octet_length(reach) from public.table where length(reach) >= 2000 ORDER BY length(reach) DESC LIMIT 10;
 length | pg_column_size | octet_length
--------+----------------+--------------
   2698 |           2698 |         2698
   2690 |           2690 |         2690
   2690 |           2690 |         2690
   2690 |           2690 |         2690
   2690 |           2690 |         2690
   2690 |           2690 |         2690
   2690 |           2690 |         2690
   2671 |           2671 |         2671
   2671 |           2671 |         2671
   2671 |           2671 |         2671
(10 rows)

It's possible that the table previously had larger data, so I've ran a REINDEX on the table, as well as a VACUUM FULL with no luck.

This table is fairly thick, so while I could drop the index and re-add it, it could impact applications that use it in the interim.

Any ideas are welcome, thanks!

1
  • What's the type of this column? Is the result shown obtained from pg11 or pg13? Commented Feb 4, 2022 at 11:19

1 Answer 1

0

Rather than believing that PostgreSQL lied to you, I'd assume that you made a mistake and there really are index keys that exceed the limit.

When you measure the size of the rows, don't forget that PostgreSQL TOASTs large values either by compressing them or storing them out of line.

1
  • As you said, the limits have changed. Did you alsu change the encoding? Commented Feb 4, 2022 at 8:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.