Adding a NOT NULL column locked our table for two minutes. How do people avoid that?
Split it: add the column nullable, backfill in batches, add a validated check constraint, then set NOT NULL. Each step takes a brief lock instead of one long one, and the backfill can be paused if replication lag grows.
Is there a fast approximate count for a huge table?
For monitoring, read reltuples from pg_class, which the planner keeps roughly current. It is off by a few percent between vacuums, which is fine for a dashboard and wrong for invoicing.
The index exists but EXPLAIN shows a sequential scan.
Usually one of three things: the query returns a large fraction of the table so the scan really is cheaper, the types do not match so the index is not applicable, or statistics are stale. ANALYZE the table first, then compare with SET enable_seqscan = off to see what the planner thinks the index would cost.
Any downside worth knowing before I commit?
It commits you to a format that is tedious to migrate away from later. The first weeks also look worse than doing nothing, which is when most people abandon it.