Revert "Remove pg_repack from docs" (#32393)

* Revert "Remove pg_repack from docs (#18476)" This reverts commit f2508d2. * update pg_repack docs * prettier
supabase · Jan 2, 2025 · 2d1783a · 2d1783a
1 parent 35286ed
commit 2d1783a
Show file tree

Hide file tree

Showing 3 changed files with 109 additions and 0 deletions.
diff --git a/apps/docs/components/Navigation/NavigationMenu/NavigationMenu.constants.ts b/apps/docs/components/Navigation/NavigationMenu/NavigationMenu.constants.ts
@@ -1020,6 +1020,10 @@ export const database: NavMenuConstant = {
           name: 'pg_stat_statements: SQL Planning and Execution Statistics',
           url: '/guides/database/extensions/pg_stat_statements',
         },
+        {
+          name: 'pg_repack: Storage Optimization',
+          url: '/guides/database/extensions/pg_repack',
+        },
         {
           name: 'PostGIS: Geo queries',
           url: '/guides/database/extensions/postgis',

diff --git a/apps/docs/content/guides/database/extensions/pg_repack.mdx b/apps/docs/content/guides/database/extensions/pg_repack.mdx
@@ -0,0 +1,99 @@
+---
+id: 'pg_repack'
+title: 'pg_repack: Physical storage optimization and maintenance'
+description: 'A tool to remove bloat from tables and indexes and optimize physical data order and physical storage'
+---
+
+[pg_repack](https://github.com/reorg/pg_repack) is a Postgres extension to remove bloat from tables and indexes, and optionally restore the physical order of clustered indexes. Unlike CLUSTER and VACUUM FULL, pg_repack runs "online" and does not hold a exclusive locks on the processed tables that could prevent ongoing database operations. pg_repack's efficiency is comparable to using CLUSTER directly.
+
+pg_repack provides the following methods to optimize physical storage:
+
+- Online CLUSTER: ordering table data by cluster index in a non-blocking way
+- Ordering table data by specified columns
+- Online VACUUM FULL: packing rows only in a non-blocking way
+- Rebuild or relocate only the indexes of a table
+
+pg_repack has 2 components, the database extension and a client-side CLI to control it.
+
+## Requirements
+
+- A target table must have a PRIMARY KEY, or a UNIQUE total index on a NOT NULL column.
+- Performing a full-table repack requires free disk space about twice as large as the target table and its indexes.
+
+pg_repack requires the Postgres superuser role by default. That role is not available to users on the Supabase platform. To avoid that requirement, use the `-k` or `--no-superuser-check` flags on every `pg_repack` CLI command.
+
+The first version of pg_repack with full support for non-superuser repacking is 1.5.2. You can check the version installed on your Supabase instance using
+
+```sql
+select default_version
+from pg_available_extensions
+where name = 'pg_repack';
+```
+
+If pg_repack is not present, or the version is < 1.5.2, [upgrade to the latest version](/docs/guides/platform/upgrading) of Supabase to gain access.
+
+## Usage
+
+### Enable the extension
+
+Get started with pg_repack by enabling the extension in the Supabase Dashboard.
+
+<Tabs
+  scrollable
+  size="small"
+  type="underlined"
+  defaultActiveId="dashboard"
+  queryGroup="database-method"
+>
+<TabPanel id="dashboard" label="Dashboard">
+
+1. Go to the [Database](https://supabase.com/dashboard/project/_/database/tables) page in the Dashboard.
+2. Click on **Extensions** in the sidebar.
+3. Search for "pg_repack" and enable the extension.
+
+</TabPanel>
+<TabPanel id="sql" label="SQL">
+
+{/* prettier-ignore */}
+```sql
+-- Example: enable the "pg_repack" extension
+create extension pg_repack with schema extensions;
+```
+
+</TabPanel>
+</Tabs>
+
+### Install the CLI
+
+Select an option from the pg_repack docs to [install the client CLI](https://reorg.github.io/pg_repack/#download).
+
+### Syntax
+
+All pg_repack commands should include the `-k` flag to skip the client-side superuser check.
+
+{/* prettier-ignore */}
+```sh
+pg_repack -k [OPTION]... [DBNAME]
+```
+
+## Example
+
+Perform an online `VACUUM FULL` on the tables `public.foo` and `public.bar` in the database `postgres`:
+
+{/* prettier-ignore */}
+```sh
+pg_repack -k -h db.<PROJECT_REF>.supabase.co -p 5432 -U postgres -d postgres --no-order --table public.foo --table public.bar
+```
+
+See the [official pg_repack documentation](https://reorg.github.io/pg_repack/) for the full list of options.
+
+## Limitations
+
+- pg_repack cannot reorganize temporary tables.
+- pg_repack cannot cluster tables by GiST indexes.
+- You cannot perform DDL commands of the target tables except VACUUM or ANALYZE while pg_repack is working.
+  pg_repack holds an ACCESS SHARE lock on the target table to enforce this restriction.
+
+## Resources
+
+- [Official pg_repack documentation](https://reorg.github.io/pg_repack/)
diff --git a/packages/shared-data/extensions.json b/packages/shared-data/extensions.json
@@ -599,6 +599,12 @@
     "product": "Supabase Vector",
     "product_url": null
   },
+  {
+    "name": "pg_repack",
+    "comment": "Optimize physical storage and remove bloat from tables and indexes",
+    "tags": ["Admin", "Utility"],
+    "link": "/guides/database/extensions/pg_repack"
+  },
   {
     "name": "wrappers",
     "comment": "Foreign data wrappers developed by Supabase",