I’m working on a project that includes the need to store a fair amount of raw text. I’m using PostgreSQL to store this text, with the raw data being stored in a TEXT data type field. When it came time to decide which fields to index I included this field to see what would happen. It didn’t give me an error when creating the index, so I thought it would be fine. And it was, for awhile.
The first few dozen inserts went fine, the raw text for these weren’t very big. Later on the inserts start failing and PostgreSQL was logging the errors as:
postgres[6289]: [1-1] ERROR: index row size 5364 exceeds btree maximum, 2713
When inserting larger chunks of data the index update failed because it was too large. I removed the index on the raw text column and errors went away. So the lesson I learned here is that the B-tree index in PostgreSQL has a maximum size of 2713 bytes. Keep this in mind when you are going to index large VARCHAR or TEXT fields.
If you really need to index large TEXT fields then you’ll have to look at full text indexing with Tsearch2.