A vacuum full basically does this for a table, copying the data from location A to location B, cleaning up junk. I think index rebuilding may take a separate command?

Vacuum full does a index rebuild automatically. Since a vacuum full builds an entire new heap table, the old indexs are all pointing to the incorrect locations for all tuples, so it has no choice but to rebuild.

Excellent there you go.

Is there a way to just do the index build part, short of dropping an index and adding it back?

This is described about a quarter the way into TFA.

> REINDEX INDEX CONCURRENTLY index_name;

(A vanilla REINDEX will lock the table, preventing writes, while it runs. The CONCURRENT creates a new index, replicates any updates to the original while it does so, and then does an atomic switcheroo at the end.)

I believe pg_repack can do that.

Dumping and reloading databases used to be mandatory for major postgresql updates, which is one of the reasons postgresql wasn't suitable for production workloads until recently and also why it resisted fixing bugs in vacuum, index, and compaction for many years.

Whoah, that's news to me.

I used PostgreSQL fairly recently (a year or so ago?) and ended up abandoning it after I was forced to do the export/import dance through a few version upgrades.

When did that requirement go away?

Since 9 there's pg_upgrade, personally I never had an issue and it was very fast, so the downtime is in the order of a few minutes, which is ok for my usecase. YMMV.

"pg_upgrade does its best to make sure the old and new clusters are binary-compatible, e.g., by checking for compatible compile-time settings, including 32/64-bit binaries. It is important that any external modules are also binary compatible, though this cannot be checked by pg_upgrade."

This makes me very nervous tho, I've at least two exts (trigrams and gists) maybe they work, maybe not, I just prefer the ease of mind of a old fashioned dump.

Don't take my word for it, but I think those extensions aren't considered "external" as they're part of the PGSQL distribution.

It'd be something like nominatim.so, which is external to PGSQL and (AFAIK) has its own format for certain types of data and indexes.

Ever since there's pg_upgrade available. Since 8.4 or so - https://www.percona.com/blog/2019/04/12/fast-upgrade-of-lega...

Dump and reload is ok if you have a small database or can afford hours of downtime... if not, use pg_upgrade.

It never did.

The difference is that you can use logical replication since 10 to prevent downtime during upgrade.

Which if you were using it a year ago could have been done.