Skip to main content
Illustration by Annie Ruygt of a balloon doing a lot of tasks

Overview

Fly.io Managed Postgres (MPG) supports Postgres 16 and 17. Clusters include extensions for the Postgres version they run, including commonly used tools and utilities like pgcrypto, pg_stat_monitor, and citext. The PostgreSQL Extensions page allows you to enable or disable PostgreSQL extensions for your managed database cluster. Extensions add features to your database, such as new data types and functions. By default the plpgsql, pg_stat_monitor, and pgaudit extensions are enabled and cannot be disabled.

Using the Extensions Page

Search for Extensions

  • Use the search bar to quickly find extensions by name or description

Select a Database

  • Use the dropdown on the right to choose which database to manage
  • Extensions are enabled/disabled per database, not cluster-wide

Enable or Disable Extensions

  • Click the toggle switch next to any extension to enable or disable it
  • Each extension displays its version number (e.g., v1.3) showing which version is installed when enabled, or which version will be installed when you enable it

Additional notes

  • If your cluster is still initializing or otherwise degraded, you’ll need to wait before managing extensions

Supported Third Party Extensions

Aside from the extensions bundled with the default Postgres distribution, MPG clusters include these third party extensions:
  • pgvector (vector), which you can enable from the Extensions page for your cluster.
  • PostGIS (postgis and its companion extensions), on clusters with PostGIS support.
  • pg_repack, on v2 clusters, for rebuilding bloated tables while your app keeps reading and writing.
  • pg_stat_monitor and pgaudit, which are always enabled.
To get PostGIS support, turn it on when you create the cluster:
  • Via the dashboard, check ā€œEnable PostGISā€ under the ā€œExtensionsā€ section
  • When creating a cluster via flyctl, pass the --enable-postgis-support flag:
On a v2 cluster (marked v2 next to the cluster name), you can also add PostGIS support later: click Enable PostGIS support on the Extensions page. The cluster does a rolling restart, and existing connections may break while the replicas restart. Once that’s done, enable postgis in each database that needs it. We plan on supporting additional third party extensions based on user feedback. If there’s an extension you rely on or commonly use, please let us know!

How extensions are updated

We rebuild v2 cluster images every week with the latest releases of the extensions we ship, including security fixes. A new image reaches your cluster during its maintenance window. PostGIS and pg_repack are pinned and change only when we update them. v1 clusters don’t get these updates.

Supported extensions

The following table lists the extensions you can enable, and whether you can enable them on v1 clusters, v2 clusters (marked v2 next to the cluster name), or both. Check your cluster’s Extensions page for the extensions and versions available to it. ¹ Only on clusters with PostGIS support. See Supported Third Party Extensions.

Rebuild bloated tables with pg_repack

pg_repack rebuilds a table and its indexes to give back the space that dead rows take up, while your app keeps reading and writing to the table. VACUUM FULL does the same job, but it holds an ACCESS EXCLUSIVE lock on the table for the whole run, which blocks every read and write. pg_repack only takes that lock briefly, at the start and at the end of the run. In between, it holds a SHARE UPDATE EXCLUSIVE lock: inserts, updates and deletes carry on as usual, but VACUUM, ANALYZE, CREATE INDEX (including CONCURRENTLY) and ALTER TABLE on that table wait until it finishes, and autovacuum skips the table. pg_repack is available on v2 clusters (marked v2 next to the cluster name). We ship pg_repack 1.5.3 with a security fix from upstream that isn’t in a pg_repack release yet.
pg_repack rolls out to v2 clusters starting November 15, 2026. Until it reaches your cluster, it doesn’t appear on your cluster’s Extensions page and you can’t enable it.

Enable pg_repack

Enable pg_repack in each database whose tables you want to repack, either with the toggle on your cluster’s Extensions page or with fly mpg databases extensions enable:
pg_repack doesn’t accept --schema: it always creates its own repack schema. fly mpg databases extensions list shows public as pg_repack’s schema anyway; that’s expected.

Install the pg_repack client

You run pg_repack from your own machine, and the client must be version 1.5.3 exactly: it refuses to work with any other version of the extension. Install it from the PostgreSQL apt repository (PGDG) on Debian or Ubuntu, and hold it at 1.5.3 so apt upgrade doesn’t move it. Use the package for your cluster’s Postgres major version; this example is for Postgres 17:
The last command should print pg_repack 1.5.3.

On macOS, Windows or other Linux distributions

Run the same steps in a Debian container, and connect to your cluster through fly mpg proxy running on your machine. In one terminal, start the proxy:
In another terminal, start the container. Inside a container, localhost is the container itself, and fly mpg proxy only listens on your machine’s localhost. How you start the container, and how it reaches the proxy, depends on where it runs:
  • macOS or Windows. Start the container as usual and reach the proxy at host.docker.internal, which Docker Desktop forwards to your machine’s localhost: for example postgres://<user>:<password>@host.docker.internal:16380/<database>. With Podman, use host.containers.internal.
  • Linux. Start the container with --network host, so it shares your machine’s network, and connect to localhost:16380 as you would outside the container. host.docker.internal doesn’t reach the proxy on Linux, even with --add-host host.docker.internal:host-gateway, because the proxy doesn’t listen on that address.
In the container, run the commands above, leaving out sudo (you’re root in the container).

Before you repack

  • Primary key. The table needs a primary key, or a unique index on NOT NULL columns.
  • Free disk space. pg_repack builds a full copy of the table and its indexes before it drops the old one. Upstream recommends free space of about twice the size of the tables and indexes you’re repacking. If the table, its indexes and the copy wouldn’t fit under the 1 TB storage limit, don’t repack it. On v2 clusters the volume grows automatically as it fills, so a large repack can grow it. A volume doesn’t shrink afterwards, but v2 clusters are billed for the storage your databases use, not for the volume’s size: usage goes up while pg_repack runs and falls once it drops the old copy, usually below where it started.
  • WAL and replicas. Rewriting a table generates write-ahead log (WAL) in proportion to its size, so your replicas can lag behind the primary while pg_repack runs.
  • Conflicting queries. pg_repack needs a brief exclusive lock on the table at the start of the run and at the final swap. Long-running queries and sessions left idle in a transaction on the table hold it up. Before the swap, pg_repack also waits, with no timeout, for every transaction that started before it did, so a session left idle in a transaction anywhere in that database stalls it. Check for both before you start. See If pg_repack can’t get its lock for what happens when it’s blocked.

Run pg_repack

Only a table’s owner can repack it. The tables you create belong to the schema_admin role, so connect as a user with the Schema Admin role, such as fly-user. Writer and Reader users can’t repack tables. Since you aren’t a superuser, pass -k (--no-superuser-check). Use your cluster’s direct connection string, not the pooled one. In Transaction mode, PgBouncer can move you to a different server connection between transactions, and connecting directly means pg_repack’s sessions are ordinary Postgres sessions that you can find and end if a run gets stuck. See Create and connect for where to find the direct connection string, and Setting up a Proxy Connection to reach it from your machine with fly mpg proxy. The database in the connection string must be the one where you enabled pg_repack. The direct connection string points at fly-db, so change the database name at the end if your tables are in another database. Through fly mpg proxy, the connection string looks like postgres://<user>:<password>@localhost:16380/<database>.
Pass -t once for each table you want to repack. See the pg_repack documentation for its other options. A run that repacks the table prints one line for it and exits with status 0:

If pg_repack can’t get its lock

pg_repack waits up to --wait-timeout seconds (60 by default) for each of its exclusive locks. When the wait runs out, it normally cancels the queries in its way, but your user can’t cancel any other session, not even another session of your own user. What happens next depends on -D (--no-kill-backend):
  • With -D. pg_repack gives up on the table, leaves it as it was and normally cleans up after itself. It still exits with status 0, so check its output for these lines:
    If the lock is still blocked during cleanup, pg_repack exits with an error and leaves an interrupted run behind.
  • Without -D. The run fails with an error, which can leave an interrupted run behind.
Once the conflicting queries and sessions are gone, run pg_repack again. Raise --wait-timeout if you’d rather it waited longer.

If a run is interrupted

If pg_repack stops partway, because the client was killed, its connection dropped, or the cluster failed over, it leaves a trigger on the table and its log and work tables in the repack schema. Your table keeps working, but every write to it also goes to the log table, which keeps growing until you clean up. To clean up:
  1. If pg_repack’s session is still open on the server, end it. Connect as the same user that ran pg_repack, for example with fly mpg connect --username <user>, and run:
    pg_repack names its sessions pg_repack (application_name), which is how this query finds them, so don’t override application_name in the connection string or with PGAPPNAME when you run pg_repack. Your user acts as schema_admin by default, which can’t end your user’s own sessions. SET ROLE NONE switches back to your user for the pg_terminate_backend call. RESET ROLE doesn’t help here: it returns to the default, schema_admin.
  2. Disable pg_repack with --force, using fly mpg databases extensions disable:
    --force drops the extension together with everything that depends on it, which removes the leftover trigger and tables. Your table and its data stay as they are. It also removes the trigger and tables of any other pg_repack run in progress in that database, which breaks that run, so make sure none is running. The toggle on the Extensions page disables extensions without --force, so it fails while the leftovers are there.
  3. Enable pg_repack again:
You can now repack the table again.