max_index_keys has been counting things that aren’t keys since PostgreSQL 11. What it reports, 32, is the most columns an index can have, and since INCLUDE arrived a column in an index is not necessarily a key: a 31-key index carrying one INCLUDE column is at the limit, and so is a one-key index carrying 31 of them. The error message says columns (cannot use more than 32 columns in an index), pg_controldata says Maximum columns in an index:, and only the parameter’s name is still talking about keys.
It’s a preset, the same species as max_function_args and max_identifier_length. Context internal; SET, ALTER SYSTEM, and a line in postgresql.conf all answer parameter "max_index_keys" cannot be changed, and pg_settings gives it a min_val and max_val of 32, another range with one member. The number behind it is INDEX_MAX_KEYS in src/include/pg_config_manual.h. The 6.5 manual allowed seven index keys; 7.0 raised that to 16, 7.3 to 32, and there it has stayed. Until 8.1 it was also the function-argument limit, which is the other post’s story and isn’t retold here, except for one detail of the 2005 split: pg_control had been recording the shared value under the name funcMaxArgs, and the split replaced that field with indexMaxKeys. Of the two constants that used to be one number, this is the one that kept its seat in pg_control, next to the one NAMEDATALEN has held since 7.3.
What counts toward the 32: key columns, INCLUDE columns, and expressions, each expression being one column no matter how many table columns it reads. The check is in DefineIndex, so it applies to every access method that permits multiple columns (B-tree, GiST, GIN, BRIN) and to everything that creates an index under another name: PRIMARY KEY, UNIQUE, EXCLUDE. Foreign keys get the same 32, because the referential-integrity triggers keep their key arrays at RI_MAX_NUMKEYS, which is INDEX_MAX_KEYS under an alias, and ALTER TABLE refuses the 33rd column with cannot have more than 32 keys in a foreign key. Partition keys are also capped at 32, and the limits appendix in the docs lists both as raisable by recompiling, but PARTITION_MAX_KEYS is its own #define, a few lines below this one in the same header. Raising INDEX_MAX_KEYS does nothing for it.
You will not hit 32 by accident, because there’s a smaller limit standing in front of it. A B-tree index tuple has to fit in a third of a page, which on the standard 8 kB block is 2704 bytes under the version 4 format PostgreSQL 12 introduced, and 32 columns of anything but narrow types gets there first. Thirty-two text columns of 96 random bytes each produced index row size 3112 exceeds btree version 4 maximum 2704 on 18.6. Thirty-two int columns, 128 bytes of data, indexed without a murmur. So the column limit is reachable only with narrow types, and the way people reach it is INCLUDE: a “covering index” that includes every column of a forty-column table is not covering a query. It’s a second copy of the table with a sort order, and the error at column 33 is the closest PostgreSQL comes to asking whether you meant that.
The reason this is a preset rather than a postmaster setting, and the reason it can’t be raised the way FUNC_MAX_ARGS can, is that it’s on disk. An index tuple, in the format that B-tree, hash, GiST, and GIN all use, starts with an eight-byte header. If any column is null, a bitmap follows the header, one bit per column, and the bitmap is not sized to the index; it’s sized to INDEX_MAX_KEYS, four bytes, always, because (as the comment in access/itup.h explains) there’s no room in the header to record how many columns there are. Four bytes rounded up to MAXALIGN is eight, so the bitmap costs eight bytes in every index tuple that has a null in it, which pageinspect will show you:
1 postgres=# CREATE TABLE pair (a int, b int); 2 postgres=# INSERT INTO pair VALUES (1, 2), (3, NULL); 3 postgres=# CREATE INDEX pair_ab ON pair (a, b); 4 postgres=# SELECT itemoffset, itemlen, nulls FROM bt_page_items('pair_ab', 1); 5 itemoffset | itemlen | nulls 6 ------------+---------+------- 7 1 | 16 | f 8 2 | 24 | t
The tuple carrying less data is the bigger one. Every such index in the cluster was written with that bitmap width, so the width is recorded in pg_control and checked at startup. Here is 18.6 built from the official tarball with the constant changed to 64, being pointed at a data directory the PGDG 18.6 package had created:
1 FATAL: database files are incompatible with server 2 DETAIL: The database cluster was initialized with INDEX_MAX_KEYS 32, but the server was compiled with INDEX_MAX_KEYS 64. 3 HINT: It looks like you need to recompile or initdb.
pg_upgrade --check between the two builds stops at the same fence, with old and new pg_controldata maximum indexed columns are invalid or do not match, for the same reason, so it’s dump and restore or nothing. And before any of that, the PG_MODULE_MAGIC block covered in the max_function_args post records this constant too, so every C extension on the machine needs rebuilding as well; the 2006 commit that added the magic block singled this one out because changing it “breaks tsearch and anything using GiST.”
Sixty-four would cost the tuple nothing on a 64-bit build: an eight-byte bitmap rounds up to the same sixteen, and the 64-key build’s null tuples came out the same 24 bytes. The cost is elsewhere. The constant sizes fixed arrays in 32 source files, 145 of them, and nobody has made the case that widening all of those is free since Rod Taylor asked for 64 in 2003 on behalf of an OSDL benchmark and was told that 32 was where the discussion had ended.
The number is 32, has been since 2002, and won’t be anything else on a server you didn’t compile yourself. An index that needs a 33rd column is telling you something about the index. If it’s a covering index, include what the query reads, not what the table has. If it’s a uniqueness rule over 33 columns, that’s a row-level check spelled as an index; hash the columns and put the unique index on the hash, which is what the byte-limit HINT was going to suggest anyway. Then stop.