4 ms·

When I did my Oracle DBA training 15 years ago, I learnt about database reorgs. It means basically exporting your database (or tables) and importing it again.

When I did my Oracle DBA training 15 years ago, I learnt about database reorgs. It means basically exporting your database (or tables) and importing it again. What happens is that deleted data which doesn't necessarily free up space (Oracle reuses the freed up space sometimes) doesn't get exported. https://www.iri.com/blog/vldb-operations/database-reorgs-why-they-matter/ https://www.iri.com/blog/vldb-operations/database-reorgs-why... https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1336002638218 https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUEST...

brianwawok 6y ago
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?
- Tostino 6y ago
  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.
  - brianwawok 6y ago
    Excellent there you go. Is there a way to just do the index build part, short of dropping an index and adding it back?
    - jvolkman 6y ago
      I believe pg_repack can do that.
    - Jedd 6y ago
      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.)
jeffbee 6y ago
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.
- AbacusAvenger 6y ago
  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?
  - dialamac 6y ago
    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.
  - Uberphallus 6y ago
    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.
    - avereveard 6y ago
      "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.
      - Uberphallus 6y ago
        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.

brianwawok 6y ago

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?

Tostino 6y ago
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.
- brianwawok 6y ago
  Excellent there you go. Is there a way to just do the index build part, short of dropping an index and adding it back?
  - jvolkman 6y ago
    I believe pg_repack can do that.
  - Jedd 6y ago
    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.)

jeffbee 6y ago

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.

AbacusAvenger 6y ago
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?
- dialamac 6y ago
  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.
- Uberphallus 6y ago
  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.
  - avereveard 6y ago
    "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.
    - Uberphallus 6y ago
      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.