I thought as of PG 9.2 expanding a varchar column was metadata only operation and therefore low overhead.
I know in SQL Server there are two issues with doing varchar(max) for everything and increasing a columns size is metadata only. First indexes have a limit of 900 byte values and will fail at runtime if you index a column with no max length and insert a value larger than 900 bytes. PG seems to have this issue as well but the limit is 2712 bytes.
Second the query planner makes use of the size to determine how much memory to pre-allocate for the query, with unlimited length field it assume something like 4096 bytes and wastes working memory if your values are not actually that size. Not sure if PG has the second issue, having a max value defined is valuable information to a database engine along with other type information such as UUID's only taking 16 bytes instead of 36 bytes.
> PG seems to have this issue as well but the limit is 2712 bytes.
Note that this is only for bytes indexes. Hash is not so limited.
Tho at this sort of sizes it seems unlikely such simple indexes are of much use. I guess the range matching abilities of btrees could be but that seems unlikely, a 2700 bytes path-like datum is a lot.
I know in SQL Server there are two issues with doing varchar(max) for everything and increasing a columns size is metadata only. First indexes have a limit of 900 byte values and will fail at runtime if you index a column with no max length and insert a value larger than 900 bytes. PG seems to have this issue as well but the limit is 2712 bytes.
Second the query planner makes use of the size to determine how much memory to pre-allocate for the query, with unlimited length field it assume something like 4096 bytes and wastes working memory if your values are not actually that size. Not sure if PG has the second issue, having a max value defined is valuable information to a database engine along with other type information such as UUID's only taking 16 bytes instead of 36 bytes.