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

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.