max_identifier_length reports a number, 63, and the number is the least interesting thing about it. Every relational database caps the length of a name. What is unusual about PostgreSQL is what happens when you go over: MySQL, SQL Server and Oracle reject the statement, while PostgreSQL cuts the name off at 63 bytes, mentions it in a NOTICE, and carries on as though that were what you meant. Nearly every real problem involving this parameter traces back to that one decision.
The parameter itself is a read-only preset, a sibling of block_size, integer_datetimes and max_function_args. The context is internal; SET, ALTER SYSTEM and a line in postgresql.conf are all refused with parameter "max_identifier_length" cannot be changed (the last one stops the server from starting). What it reports is NAMEDATALEN - 1, where NAMEDATALEN is a constant in src/include/pg_config_manual.h that has been 64 since PostgreSQL 7.3 in 2002 (it was 32 before that). The minus one is a C string terminator. name, the type of relname, attname, rolname and every other identifier column in the system catalogs, is a fixed-width 64-byte field with a trailing zero byte, so 63 of the bytes are yours. pg_control_init() has a column that is also called max_identifier_length, and it says 64. Same fact, other side of the fencepost.
The truncation happens in the lexer, in a function called truncate_identifier(), which means it applies to anything that reaches the parser as an identifier: tables, columns, indexes, constraints, schemas, roles, databases, functions, prepared statement names, cursor names, savepoint names, LISTEN channels. Quoting a name preserves its case and does nothing for its length. The cut is at 63 bytes, not characters, made on a character boundary: forty copies of é become thirty-one (62 bytes, because 63 is odd), and thirty CJK characters become twenty-one. The NOTICE carries SQLSTATE 42622 and, being a NOTICE, answers to client_min_messages: a human at a psql prompt will see it, and an application almost never will, because application code does not read notices. Things that arrive as string literals rather than identifiers are checked instead of clipped: an enum label over 63 bytes is an error, and so is a long channel name handed to pg_notify(), while the same name given to NOTIFY is quietly trimmed.
The failure mode is collision. Two names that agree on their first 63 bytes are, as far as PostgreSQL is concerned, one name, and generated names are where this bites, because generated names put the part that varies at the end. Here is a daily partition scheme on a parent table with a 60-character name:
1 =# CREATE TABLE customer_invoice_line_item_allocation_history_archive_detail_p2024_01_01 2 PARTITION OF customer_invoice_line_item_allocation_history_archive_detail 3 FOR VALUES FROM ('2024-01-01') TO ('2024-01-02'); 4 NOTICE: identifier "customer_invoice_line_item_allocation_history_archive_detail_p2024_01_01" will be truncated to "customer_invoice_line_item_allocation_history_archive_detail_p2" 5 CREATE TABLE 6 =# CREATE TABLE customer_invoice_line_item_allocation_history_archive_detail_p2024_01_02 7 PARTITION OF customer_invoice_line_item_allocation_history_archive_detail 8 FOR VALUES FROM ('2024-01-02') TO ('2024-01-03'); 9 NOTICE: identifier "customer_invoice_line_item_allocation_history_archive_detail_p2024_01_02" will be truncated to "customer_invoice_line_item_allocation_history_archive_detail_p2" 10 ERROR: relation "customer_invoice_line_item_allocation_history_archive_detail_p2" already exists
That is the good outcome, because it is an error. The bad outcome is the same script’s retention step, DROP TABLE customer_invoice_line_item_allocation_history_archive_detail_p2023_12_31, which resolves to the same 63 bytes and drops the partition you created this morning, with no error and, if the script runs with client_min_messages at warning, no notice either. I ran exactly that on 18.6; the row count afterwards was zero. The same mechanism executes the wrong prepared statement, delivers a NOTIFY on ..._region_us to a session listening on ..._region_eu, and creates one role where you asked for two.
PostgreSQL’s own generated names are safe. makeObjectName(), which names implicit indexes, sequences and constraints, shortens the table and column parts and never the label, and its callers retry with a counter on collision: put three unnamed indexes on the same columns of that long table and you get ..._identifier_idx, ..._identifie_idx1 and ..._identifie_idx2, all 63 bytes and all distinct. Names that come from outside are your problem, and the tooling is uneven. pg_partman handles it, trimming the parent name to make room for the suffix (it finds the cut by casting to name, which is the right way). Django’s PostgreSQL backend has reported 63 since 2010 and hashes the tail of the index names it generates. Rails threw Index name ... is too long; the limit is 63 characters for over a decade and switched to hashing in 7.1, and its Action Cable adapter, which names LISTEN channels after streams, was measuring characters instead of bytes until a fix landed on the 8.0 branch this year. Bytes, not characters, is the mistake everyone makes once.
You can raise it. The docs say so, and so does the header, with the caveat that “Changing this requires an initdb.” Rebuild with NAMEDATALEN set to 128 and you get 127-byte names, a cluster that pg_upgrade will refuse to move to or from any stock build (it compares the value recorded in pg_control and fails on a mismatch), and C extensions that all have to be rebuilt, because NAMEDATALEN is one of the fields in the module magic block described in max_function_args. Raising the default comes up on the mailing lists every few years (2012, 2017 and 2021, at least) and gets the same answer, which isn’t stubbornness. name is fixed-width so that the columns after it in a catalog row sit at fixed offsets, and every pg_attribute and pg_class row, and every syscache entry built from them, carries the full 64 bytes whether the name uses them or not. Doubling the constant doubles that everywhere, for the benefit of names that are, let’s be honest, too long. (The SQL standard allows 128. Oracle got there in 12.2, SQL Server has been there for ages, and even MySQL allows 64 characters rather than 63 bytes. PostgreSQL is the short one, and it is going to stay the short one.)
So the advice isn’t about the parameter, which you can’t set, but about the number it reports. Treat 63 as a budget with the suffix charged first: if your convention appends _p2024_01_01 or _pkey, the base name gets what’s left, and a base name much over 45 characters is a collision waiting for a second table. When a script generates names, check them before using them; candidate::name::text <> candidate works on any build, and octet_length(candidate) > current_setting('max_identifier_length')::int is the one job this parameter has ever had. Then go look at what you already have:
1 SELECT 'table' AS kind, relname::text AS name 2 FROM pg_class WHERE relkind IN ('r', 'p') AND octet_length(relname) = 63 3 UNION ALL 4 SELECT 'column', attname FROM pg_attribute WHERE octet_length(attname) = 63 5 UNION ALL 6 SELECT 'role', rolname FROM pg_roles WHERE octet_length(rolname) = 63;
Indexes and constraints are left out because PostgreSQL’s own trimmed names land on 63 by design. Nobody else lands on 63 by choice. Every row that query returns was longer when someone typed it, and the question for each one is what the rest of it said.