0

I have a sparse matrix table where every attribute is citext. There are some indexes built against a few of the fields.

I want to change these columns back to varchar.

  • Can I do this against a live production table, or do I need to bring down the database?
  • Do the indexes need to be rebuilt afterwards?
  • Will this result in any space savings?
  • Will this give us any kind of performance boost (if those columns were heavily indexed)?
1
  • 2
    Can you run a test against a toy system? You also should consider changing to the TEXT data type instead of VARCHAR (n). This may also be of interest! Jan 7 '20 at 6:52
1
  • Can I do this against a live production table, or do I need to bring down the database?

Yes. You can do it against a live table. Will even be very fast since citext and varchar are binary coercible, so no table rewrite is required (since Postgres 9.1).

But this still acquires an ACCESS EXCLUSIVE lock on the table, which makes any concurrent access on the table wait, and potentially fail. And if there are any indexes involving the column, those must still be rebuilt (automatically), which may take substantially longer.

And any dependent objects like views that use the column are road blockers and need to be removed before the change (and added back later).

Related:

  • Do the indexes need to be rebuilt afterwards?

No. Indexes involving the column in any way have to be rebuilt, but that happens automatically. See above. The manual:

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

  • Will this result in any space savings?

No. Binary coercible means same space on disk and in RAM. There may be a one-time effect for rebuilt indexes that are now in pristine condition - especially if those were bloated.

  • Any kind of performance boost?

Not likely. Depends on the complete picture. citext may or may not have been useful. I don't recommend citext after mixed experiences. I rather go with expression indexes. Or consider a non-deterministic collation in Postgres 12 or later.

Generally, citext operators are a bit slower everywhere than plain text / varchar operators. (They have to do more work.) Possible one-time effects from rewriting involved indexes can be achieved with REINDEX as well.

Related:

2
  • Thank you Erwin. Will this give us any kind of performance boost if those columns were heavily indexed?
    – AKWF
    Jan 7 '20 at 20:37
  • 1
    @AKWF: I added the performance item to your question & addressed it in the answer. Jan 7 '20 at 22:37

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.