re: How to build unique indexes in PostgreSQL on large text VIEW POST


Hi @rhymes ,
Great post. TIL about the limit on size of what can be indexed. A buffer page is generally around 8kb so looks like 2.5 kb is maximum text size to be indexable.

I had a few queries on this:

  1. Are articles queried directly by their text in the code? If not so, is a unique index needed for the article text?
  2. The order of columns in the index matters. So, is there advantage in indexing (hash, title, user) vs (user, title, hash) as any query selecting by user will automatically use this index.
  3. I realized there is a limit to title of 128 char in dev.to. Is that application only or a DB contraint/data type?
  4. Finally, is there any advantage of adding the hash as a separate column and indexing it instead and adding the uniqueness of this column in Rails model like validates article_hash, uniqueness: true

Hi Raunak!

  1. No, it's just used to avoid accidental double posting mostly
  2. It's not queried so it doesn't really matter in this case but yeah, in general it does matter, great point!
  3. We don't have length CHECK constraints in the database, so it's one of those things that it's still only at the application level
  4. It would mean that the hash needs to be updated at the code level at each "article save", which means that we're back to depending on Rails to check uniqueness

Hope this clears a bit :) Great questions!

code of conduct - report abuse