Supabase to Self-Hosted Postgres: A Production Migration

On 2 July 2026 I moved Opaeron, the operations platform I build at Trilliant Digital, off Supabase and onto a self-hosted PostgreSQL 16 instance running the pgvector/pgvector:pg16 image. By then the codebase was 226,000 lines, 711 API route handlers across 89 routers, and 77 Postgres tables, every one of them prefixed tdx_. Almost all of that route code called the database through a Supabase client. The migration had one hard constraint: I was not going to rewrite hundreds of route handlers to get a different database host.

#Why we left the managed layer

The honest version is not that Supabase failed. It is that the overlap between what Supabase offers and what Opaeron actually used had shrunk to one thing: Postgres. Auth had already moved to Google OAuth restricted to the company domain plus our own JWT sessions with sliding expiry and revocation. File storage was on DigitalOcean Spaces through boto3. Background work was Celery and Redis with a beat schedule (deadline reminders every 15 minutes, nightly recurrence regeneration, a nightly Postgres to Google Sheets backup, a daily ads sync, weekly telemetry pruning). Realtime was unused. We were paying a per-project managed premium for the one component we were most capable of running.

Cost is worth being precise about, because the naive version of this argument is wrong. Supabase Pro is $25 per month including $10 of compute credits, and the compute add-on ladder runs from $10 per month for Micro to $410 for 2XL (32 GB, 8 dedicated cores), $960 for 4XL and $3,730 for 16XL, billed per project. Egress beyond the included 250 GB is $0.09 per GB and disk beyond 8 GB is $0.125 per GB. Now compare that egress rate to AWS list price for internet data transfer out of us-east-1: $0.090 per GB for the first 10 TB a month. They are the same number. Neon charges $0.10 per GB past its included 500 GB. Bandwidth pricing across the managed layer is effectively uniform, so leaving a provider does not make your egress cheaper. The savings are entirely in compute, and only once your instance is big enough that a dedicated box beats the add-on tier.

The second reason was version control over the data layer itself. Self-hosting means I pick the Postgres minor, the pgvector build, the extensions, the autovacuum settings and the backup cadence, and none of those change under me on someone else's release schedule. Betting on Postgres rather than on a vendor also looks like the safer long bet: it is the most-used database in the 2025 Stack Overflow survey at 55.6% of all respondents and 58.2% of professional developers, and in the DB-Engines ranking for August 2026 it sits at rank 4 with a score of 684.58, the only system in the top five to gain year over year while Oracle, MySQL, SQL Server and MongoDB all fell.

#The shim that saved the rewrite

Every one of those 711 route handlers spoke Supabase client syntax: pick a table, chain filters, call execute, read a data attribute. Rewriting that into raw SQL across 32 backend feature modules would have been a multi-month project with a bug in every tenth handler. So I wrote a query builder shim on top of psycopg3 that reproduces the subset of that API we actually used, and pointed the import at it. Route code did not change.

Two rules kept the shim safe. Identifiers go through psycopg.sql.Identifier so table and column names are quoted rather than concatenated, and values are always bound parameters, never interpolated into the statement text. A string-formatting builder would have been half the code and an injection surface across every route in the platform.

from psycopg import sql
from psycopg.rows import dict_row
from psycopg_pool import ConnectionPool

POOL = ConnectionPool(
    conninfo=settings.DATABASE_URL,
    min_size=4,
    max_size=20,
    max_lifetime=30 * 60,
    kwargs={"row_factory": dict_row},
)


class Query:
    """Supabase-shaped chainable builder over psycopg3.

    Identifiers are quoted, values are always bound parameters.
    Call sites keep reading:
        db.table("tdx_tasks").select("id,title,due_date")
          .eq("account_id", account_id).order("due_date").limit(50).execute().data
    """

    def __init__(self, table: str):
        self._table = sql.Identifier(table)
        self._cols = sql.SQL("*")
        self._where = []
        self._args = []
        self._order = None
        self._limit = None

    def select(self, cols: str = "*"):
        if cols != "*":
            self._cols = sql.SQL(", ").join(
                sql.Identifier(c.strip()) for c in cols.split(",")
            )
        return self

    def eq(self, col: str, value):
        self._where.append(sql.SQL("{} = %s").format(sql.Identifier(col)))
        self._args.append(value)
        return self

    def in_(self, col: str, values):
        self._where.append(sql.SQL("{} = ANY(%s)").format(sql.Identifier(col)))
        self._args.append(list(values))
        return self

    def order(self, col: str, desc: bool = False):
        self._order = sql.SQL("ORDER BY {} {}").format(
            sql.Identifier(col), sql.SQL("DESC" if desc else "ASC")
        )
        return self

    def limit(self, n: int):
        self._limit = int(n)
        return self

    def execute(self):
        parts = [sql.SQL("SELECT {} FROM {}").format(self._cols, self._table)]
        if self._where:
            parts.append(sql.SQL("WHERE ") + sql.SQL(" AND ").join(self._where))
        if self._order is not None:
            parts.append(self._order)
        if self._limit is not None:
            parts.append(sql.SQL("LIMIT {}").format(sql.Literal(self._limit)))
        with POOL.connection() as conn, conn.cursor() as cur:
            cur.execute(sql.SQL(" ").join(parts), self._args)
            return Result(data=cur.fetchall())


def table(name: str) -> Query:
    return Query(name)

The shim covered roughly nine in ten call sites unchanged. The rest were the interesting ones: joins that the client had been faking with two round trips, aggregate dashboards feeding Recharts, and the vector similarity queries. Those got hand-written SQL, which is where they belonged anyway. I deliberately did not add an ORM. With 77 tables and a team that reads SQL fluently, an ORM would have been a second dialect to learn and a second query planner to argue with.

#Authorization without Row Level Security

This is the part where self-hosting genuinely costs you something. On Supabase, Row Level Security policies read JWT claims and the database itself refuses rows you should not see. Self-hosted, the application connects as a single role and there is no RLS at all, so every authorization decision lives in application code. In Opaeron that is one module, pod_permissions.py, and the rule is that no route resolves account-scoped data without going through it.

Before treating RLS as the thing we lost, it is worth reading Supabase's own performance guide, which is refreshingly blunt about the cost. Wrapping an auth function call in a scalar subquery so the planner caches it as an initPlan took one benchmark query from 178,000 ms to 12 ms, roughly a 14,800x difference. Adding an index on the column a policy filters by took another query from 171 ms to under 0.1 ms. Adding an explicit role clause so anonymous requests skip the policy entirely took 170 ms to under 0.1 ms. RLS is correct by construction and slow by default, and getting it fast requires knowing three non-obvious planner tricks. Moving the checks into application code trades a performance footgun for an auditability problem: the database no longer guarantees anything, so the guarantee has to be a code path you can point at.

DISCIPLINE_BY_PRACTICE_POD = {"maverick": "smm", "ajna": "paidads", "alpha": "seo"}


def visible_account_ids(user) -> list[str] | AllAccounts:
    """The single choke point. Every account-scoped query starts here."""
    if user.is_super_admin and user.view_as is None:
        return ALL_ACCOUNTS

    # View-As runs through exactly the same resolution path, never a bypass.
    effective = user.view_as or user

    if effective.pod_type == "client":
        rows = db.table("tdx_accounts").select("id").eq("pod_id", effective.pod_id).execute().data
        return [r["id"] for r in rows]

    matrix = settings_cache.get("pod_permission_matrix")  # tdx_app_settings, live editable
    discipline = DISCIPLINE_BY_PRACTICE_POD.get(effective.pod_slug)
    if discipline is None or discipline not in matrix.get(effective.pod_slug, []):
        return []  # fail closed: unknown pod or revoked discipline sees nothing

    rows = (
        db.table("tdx_account_disciplines")
        .select("account_id")
        .eq("discipline", discipline)
        .execute()
        .data
    )
    return [r["account_id"] for r in rows]


def require_account(user, account_id: str) -> str:
    allowed = visible_account_ids(user)
    if allowed is not ALL_ACCOUNTS and account_id not in allowed:
        audit.write(actor=user.id, action="account.denied", target=account_id)
        raise HTTPException(status_code=404, detail="Not found")  # 404, not 403
    return account_id

Two details in there matter more than the structure. The denial returns 404 rather than 403, so an unauthorized caller cannot enumerate which account ids exist. And View-As, the mode that lets a super admin see the product as another pod sees it, resolves through the same function instead of short-circuiting it, because a debugging feature that skips the permission path eventually becomes the permission path.

The client-facing portal at clients.opaeron.com is a deliberately separate application with a stricter posture, because it is the only surface that faces outside the company. It does not use JWTs at all: sessions are opaque SHA-256 tokens stored server side, delivered in HttpOnly cookies with no Domain attribute so nothing leaks across subdomains. Every endpoint fails closed, and the account id is read from the server-side session row, never from a URL parameter or request body. That last rule removes an entire class of tenant-crossing bug by making the client incapable of naming the account it wants.

#Connection pooling and psycopg3

A useful thing to notice from the Supabase docs: direct Postgres connections scale with compute size but plateau at 500, while pooler clients go from 200 on Micro to 12,000 on 16XL. Micro allows 60 direct and 200 pooled, Large allows 160 and 800, 4XL allows 480 and 3,000. In other words a transaction-mode pooler was already mandatory before the migration, not something self-hosting forced on us. Moving in-house simply relocated the pooler into our own processes.

The layout is one psycopg_pool ConnectionPool per container: the FastAPI app on Uvicorn, the Celery worker and Celery beat each hold their own, sized so that the sum of max_size across all running containers stays comfortably under the server's max_connections. The beat schedule is the part that catches people out. Deadline reminders fire every 15 minutes and fan out across accounts, nightly recurrence regeneration and the Postgres to Google Sheets backup both run in the same window, and each is a burst of concurrent connections that has nothing to do with web traffic. I set max_lifetime to 30 minutes so connections recycle rather than accumulate server-side state, and left min_size low so idle containers are not holding backends they never use.

#pgvector and the cutover window

Opaeron embeds briefs, meeting notes and account context with openai/text-embedding-3-small through OpenRouter, and searches them with pgvector, which is why the database image is pgvector/pgvector:pg16 rather than plain postgres:16. Vector data made the cutover window harder to estimate than the relational data, because copying rows is fast and rebuilding an HNSW index is not.

Supabase's own benchmark is the number I planned against: with parallel index builds in pgvector 0.6.0, an HNSW index over 1 million 1536-dimension vectors dropped from 1 hour 27 minutes 30 seconds to 9 minutes 28 seconds on a 16-core, 64 GB instance at m=16 and ef_construction=200. That is the difference between a maintenance window you can hold and one you cannot. The other version-sensitive number is recall. AWS measured pgvector 0.8.0's iterative index scan taking a basic top-10 vector query on Aurora PostgreSQL from 123.3 ms to 13.1 ms, a 9.4x improvement, and raising recall on selective filtered searches from as low as 1% to 100%. Filtered vector search is exactly what a multi-tenant platform does, since every similarity query is scoped to an account, so on older builds those queries can silently return incomplete results rather than fail. Pinning the pgvector version was not a detail, it was a correctness decision.

#Additive migrations only

There is no Alembic and no ORM-generated schema. Migrations are numbered SQL files applied by a small runner that records versions in a table, and the rule is that a migration may only add. New columns are nullable or carry defaults. Nothing is dropped or renamed in the same release that stops using it: the code stops writing a column in one deploy, and the column is removed weeks later once no rollback target still needs it. That discipline is what makes a rollback a container image swap instead of an incident.

-- Rule 1: additive only. New columns are nullable or carry a default.
-- Rule 2: no drops or renames in the release that stops using the column.
BEGIN;

ALTER TABLE tdx_tasks
  ADD COLUMN IF NOT EXISTS recurrence_rule       text,
  ADD COLUMN IF NOT EXISTS recurrence_parent_id  uuid REFERENCES tdx_tasks(id);

INSERT INTO tdx_schema_migrations (version, applied_at)
VALUES ('0042', now())
ON CONFLICT (version) DO NOTHING;

COMMIT;

-- Rule 3: CREATE INDEX CONCURRENTLY cannot run inside a transaction block,
-- so index work lives in its own file the runner executes with autocommit.
-- migrations/0042b_task_recurrence_index.sql
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_tdx_tasks_account_due
  ON tdx_tasks (account_id, due_date)
  WHERE deleted_at IS NULL;

The deploy shape around it is deliberately dull. A multi-stage Dockerfile builds the React 19 and Vite 7 bundle, then FastAPI serves the static output alongside the API. Docker Compose runs app, celery-worker, celery-beat, redis and postgres behind host-level nginx, and GitHub Actions ships on push to main. Backups are a nightly pg_dump to DigitalOcean Spaces plus the Sheets mirror, which is less about disaster recovery and more about the account team being able to read yesterday's numbers when the platform is down. Transactional email goes through the Resend HTTPS API because the droplet blocks outbound SMTP, which is the kind of thing you find out on migration day.

#What I would do differently

• Write the data-access shim on day one, before the first hundred route handlers exist. Ours was retrofitted under time pressure; as a first-week decision it would have cost two days and made the database provider a configuration detail from the start.
• Add a test that fails the build when an account-scoped query does not pass through require_account. With RLS gone, the guarantee is a convention, and conventions need a linter.
• Keep RLS as a second layer on the client-portal tables even with application checks in place. Defence in depth is cheap on the handful of tables an external user can reach, and the performance objections above apply mostly to large internal tables, not to a narrow portal surface.
• Benchmark the HNSW rebuild on a copy of production before scheduling the window, rather than trusting a published benchmark on different hardware.
• Inventory the ambient platform services, not the database. Outbound SMTP, cron, TLS renewal and object storage were all handled for us before, and each one is a small separate decision after.

#Takeaways

• Leave a managed provider for control or for compute cost, not for egress. Supabase, Neon and AWS list price bandwidth within a cent of each other.
• Keep the call-site API stable and swap what is underneath. A few hundred lines of shim beat rewriting 711 route handlers.
• Bind parameters and quote identifiers in any hand-rolled builder. That is the entire security budget of the abstraction.
• Losing RLS means authorization becomes a single audited function, plus a test that proves nothing bypasses it.
• Size pools for the background workers, not the web traffic. Beat schedules cause the connection spikes.
• Pin the pgvector version deliberately; on filtered vector search it changes recall, not just latency.
• Additive migrations only, so a rollback is an image swap.