- 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
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).
- 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.
citext operators are a bit slower everywhere than plain
varchar operators. (They have to do more work.) Possible one-time effects from rewriting involved indexes can be achieved with
REINDEX as well.