pg_restore "exceeds btree version 4 maximum 2704 for index" with no data exceeding that



  • 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!



  • 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2