5 ms·

Does Postgresql have index size limits? I know in SQL server one thing that can bite you using varchar(max) is that if later you want to index it, it can fail i

Does Postgresql have index size limits? I know in SQL server one thing that can bite you using varchar(max) is that if later you want to index it, it can fail if anything is over 900 bytes as that is the index size limit. Its a run-time error too so the index may get created no problem then later someone tries to insert a >900 bytes value and it throws.

volkadav 8y ago
roughly 2700 bytes, from a bit of googling. (off the top of my head, the most likely use case for 2+kb text fields would be things you would be using pg's full text index on anyway, not the usual btree type indexes as you'd want to speed up a join or whatever.)
- SigmundA 8y ago
  I agree, but sometimes its not something you think about and when every says just use varchar(max)/text then later on you want to speed it up with a btree, oops. IMO its better to set the length if it might be indexed to let everyone know ahead of time not to go over. I am not that familiar with PG full text index types, but in SQL server the full text index doesn't really work well with exact/begins with searches like the b-tree so your kinda stuck in that situation. With SQL server I like to stick with varchar(255) as it gives head room for unicode indexing and at one point there where some internal optimizations for less <=255 due to the legacy max being 255, not sure anymore. Beyond 255 its much less likely to need a btree and becomes more of a full text issue.
dfjhdkjfhsdkf 8y ago
I did not know about this limitation in SQL Server, thanks!
tracker1 8y ago
IIRC you can still set the index, it's just that you can't do matching if the match/content is over the length and it can still do begins with matching. That said, I still don't get why they didn't just change the behavior for TEXT/NTEXT vs adding (MAX) options. edit: I am mistaken, when setting an index, the index max is now the insert limit (from response below).
- SigmundA 8y ago
  If your referring to SQL Server you get on insert "Operation failed. The index entry of length 901 bytes for the index 'xyz' exceeds the maximum length of 900 bytes."
  - tracker1 8y ago
    Thanks. It's been a long while since I've dealt with the situation.
fomojola 8y ago
Last time I looked it is limited to the page size (so about 4kb on the boxes I played with). If you try and put more than that into an indexed field it throws an error.
da_chicken 8y ago
Even if it doesn't have index size limits, I can't imagine that it doesn't have row size limits and some kind of off-row storage or multi-page storage with resulting overhead. Looking at the doc[0], I see this: > PostgreSQL uses a fixed page size (commonly 8 kB), and does not allow tuples to span multiple pages. Therefore, it is not possible to store very large field values directly. To overcome this limitation, large field values are compressed and/or broken up into multiple physical rows. This happens transparently to the user, with only small impact on most of the backend code. So, yeah, large values are stored differently and there's an impact on performance. Sorry, a blanket statement like "you should use varchar with no length limit" seems extremely ill advised to me. Like, sure, you don't want to use varchar(30) for last names. But varchar(255) is probably going to do you just fine. [0]: https://www.postgresql.org/docs/current/storage-toast.html https://www.postgresql.org/docs/current/storage-toast.html

volkadav 8y ago

roughly 2700 bytes, from a bit of googling. (off the top of my head, the most likely use case for 2+kb text fields would be things you would be using pg's full text index on anyway, not the usual btree type indexes as you'd want to speed up a join or whatever.)

SigmundA 8y ago
I agree, but sometimes its not something you think about and when every says just use varchar(max)/text then later on you want to speed it up with a btree, oops. IMO its better to set the length if it might be indexed to let everyone know ahead of time not to go over. I am not that familiar with PG full text index types, but in SQL server the full text index doesn't really work well with exact/begins with searches like the b-tree so your kinda stuck in that situation. With SQL server I like to stick with varchar(255) as it gives head room for unicode indexing and at one point there where some internal optimizations for less <=255 due to the legacy max being 255, not sure anymore. Beyond 255 its much less likely to need a btree and becomes more of a full text issue.

dfjhdkjfhsdkf 8y ago

I did not know about this limitation in SQL Server, thanks!

tracker1 8y ago

IIRC you can still set the index, it's just that you can't do matching if the match/content is over the length and it can still do begins with matching. That said, I still don't get why they didn't just change the behavior for TEXT/NTEXT vs adding (MAX) options. edit: I am mistaken, when setting an index, the index max is now the insert limit (from response below).

SigmundA 8y ago
If your referring to SQL Server you get on insert "Operation failed. The index entry of length 901 bytes for the index 'xyz' exceeds the maximum length of 900 bytes."
- tracker1 8y ago
  Thanks. It's been a long while since I've dealt with the situation.

fomojola 8y ago

Last time I looked it is limited to the page size (so about 4kb on the boxes I played with). If you try and put more than that into an indexed field it throws an error.

da_chicken 8y ago

Even if it doesn't have index size limits, I can't imagine that it doesn't have row size limits and some kind of off-row storage or multi-page storage with resulting overhead. Looking at the doc[0], I see this: > PostgreSQL uses a fixed page size (commonly 8 kB), and does not allow tuples to span multiple pages. Therefore, it is not possible to store very large field values directly. To overcome this limitation, large field values are compressed and/or broken up into multiple physical rows. This happens transparently to the user, with only small impact on most of the backend code. So, yeah, large values are stored differently and there's an impact on performance. Sorry, a blanket statement like "you should use varchar with no length limit" seems extremely ill advised to me. Like, sure, you don't want to use varchar(30) for last names. But varchar(255) is probably going to do you just fine. [0]: https://www.postgresql.org/docs/current/storage-toast.html https://www.postgresql.org/docs/current/storage-toast.html