PostgreSQL – Can Column Data Type Be Modified on Live Table?

citextdatabase-designindexperformancepostgresql

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)?

Best Answer

  • 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:

Related Question