Re: Challenges with huge postgresql deployments in 300+ TBs

On Thu, Oct 8, 2026 at 4:16 AM kavya chandren <kavyachandren(at)gmail(dot)com>
wrote:

> Hi,
>
> We have an oracle deployment running in 300+ TB. Apart from migration
> challenges, do we foresee any operational challenges?
>

Of course. There are *always* operational challenges in ultra-large
databases... 😉

> I have not heard of something huge prior. This would be highly helpful in
> planning further.
>
> Few challenges which caught my attention include,
>
> 1. Backup, restore of such a large database
> 2. Recovery of a broken database (A lost replica)
> 3. Fixing a standby that lags a lot
> 4. Triggering a PITR with any backup tools
> 5. Maintenance at times
> 6. Finding equivalents of oracle items (which is common during any
> oracle to postgres migration)
>
> I dont see any limits in database/ objects as specified in
> https://www.postgresql.org/docs/current/limits.html
>

Postgresql has tablespaces, which obviously is important for you.

Convert all those NUMERIC(38,0) types to BIGINT!! ora2pg can do that for
you.

ora2pg can also export sections of a database, and also views. This
allowed me to migrate 95% (all the old, "read only" records) of an 8TB
before cutover day.

The PG database (which is heavy on BLOBs converted to PG's bytea type)
was *half
the size* of the 8TB Oracle database. Your Mileage Will Vary, of course,
but don't be surprised if the resulting DB is only 150TB.

PgBackRest:
- is multi-threaded.
- has delta restores, which means restoring just the changed files on top
of an existing (problematic) instance.
- can be the source of replication. Doing a delta restore makes it even
faster.
- is quite fast. With just 8 threads, we back up a 3.75TB db to a NAS in 2
hours (656MB/sec). More threads might make it faster, but really is
bottlenecked by the whole hardware stack.
- Is very efficient at handling small tables and indices (which is
important, since each PG object is (at least) its own file, and additional
files are added after 1GiB.
- can only write to one directory (i.e. each thread cannot write to a
separate mount point), which is a bottleneck.
- has zst compression, and also AES-256-CBC compression.

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!