1

I went overboard on CITEXT columns on a particularly large table in my application. I would like to back some of these out as it's confusing how to trigger lookups on the desired indexes.

My question is, can I do this without encountering any major difficulties? And if I change this, do I need to rebuild any indexes on those fields?

Will there be any space gains from moving in this direction?

Case-insensitive queries are not necessary on these columns.

I have counts against this table that are predicated on 2 columns, and these counts are taking over an hour. The table has 60 columns.

I am using Postgres 10.6.

I'm primarily interested in whether indexes need to be rebuilt if they contain a column that has changed from CITEXT to VARCHAR.

  • Not enough details. Do you need case insensitive searches or case sensitive or both? What queries do you have now and what problem do you face with them? – ypercubeᵀᴹ Feb 24 at 18:31
1

Will there be any space gains from moving in this direction?

No. citext and text (or varchar) occupy the same space on disk and in RAM.

Case-insensitive queries are not necessary on these columns.

Then there is no point to use citext.

I have counts against this table that are predicated on 2 columns, and these counts are taking over an hour. The table has 60 columns.

count() is not affected by citext vs. text (or varchar) at all. "60 columns" might be something to look into. No problem if you actually need all of them - but do you? And are you using proper data types etc.

I'm primarily interested in whether indexes need to be rebuilt if they contain a column that has changed from CITEXT to VARCHAR.

All indexes involving any of the affected columns have to be rebuilt. (Drop index, change type, recreate index.)

Personally, I stay away from citext after mixed experiences.

  • what is the darkside of citext? I really like it for case-insensitive apps. Expression indexes on lower/upper seem so ugly... – filiprem Feb 25 at 18:50
  • @filiprem: I had troubles with collation and indexes. The chapter Limitations in the manual sums it up nicely. Found this with a quick search: dba.stackexchange.com/a/105250/3684. I am not advising against it, just had mixed experiences: the minor performance gain did not seem worth the added complications. – Erwin Brandstetter Feb 25 at 21:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.