7

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.

MDCCL
  • 8,530
  • 3
  • 32
  • 63
RubyRedGrapefruit
  • 345
  • 9
  • 18

1 Answers1

5

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.

Indexes involving the column are converted automatically. (This can mean rebuilding indexes.) The manual on ALTER TABLE:

SET DATA TYPE

This form changes the type of a column of a table. Indexes and simple table constraints involving the column will be automatically converted to use the new column type by reparsing the originally supplied expression.

It can fail when involving expressions that don't work for the new type (for expression or partial indexes). You have to drop such indexes and recreate them in adapted form after the change.

Personally, I stay away from citext after mixed experiences. Consider the limitations discussed in the manual.

Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633