Challenges with huge postgresql deployments in 300+ TBs
Challenges with huge postgresql deployments in 300+ TBs
От:
kavya chandren <kavyachandren@gmail.com>
Дата:
Hi,
We have an oracle deployment running in 300+ TB. Apart from migration challenges, do we foresee any operational challenges? I have not heard of something huge prior. This would be highly helpful in planning further.
Few challenges which caught my attention include,
- Backup, restore of such a large database
- Recovery of a broken database (A lost replica)
- Fixing a standby that lags a lot
- Triggering a PITR with any backup tools
- Maintenance at times
- 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
Thanks and Regards,
Kavya
Re: Challenges with huge postgresql deployments in 300+ TBs
От:
Jan Karremans <karremans.ja@gmail.com>
Дата:
Hi Kavya,
I also do not expect any major issues and as you have experience running this in Oracle, you are probably well versed enough.
Cheers,
Jan
Verstuurd vanaf mijn iPhone
Op 8 okt 2026 om 10:16 heeft kavya chandren <kavyachandren@gmail.com> het volgende geschreven:
Hi,We have an oracle deployment running in 300+ TB. Apart from migration challenges, do we foresee any operational challenges? I have not heard of something huge prior. This would be highly helpful in planning further.Few challenges which caught my attention include,
- Backup, restore of such a large database
- Recovery of a broken database (A lost replica)
- Fixing a standby that lags a lot
- Triggering a PITR with any backup tools
- Maintenance at times
- 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.htmlThanks and Regards,Kavya
Re: Challenges with huge postgresql deployments in 300+ TBs
От:
Manu <manuelreyesbravo@gmail.com>
Дата:
Hi Kavya, A few thoughts for your planning. Others on this list run larger systems than I have, so take this as what I would personally watch rather than the last word, and corrections are welcome. My read is that at this size the risk is less about capacity and more about operations. Roughly in the order I would expect things to bite: > Backup, restore of such a large database > Recovery of a broken database (A lost replica) > Triggering a PITR with any backup tools To me these are really one decision: a physical, parallel, incremental tool -- pgBackRest (or Barman) -- rather than pg_dump, which is not viable at this size. The same tool covers all three: incremental, block-level backup; PITR; and rebuilding a lost replica by restoring the delta from the backup repository instead of re-streaming it all from the primary. I would derive the full-restore wall-clock from your storage throughput and actually test it; at a few GB/s, 300 TB is still many hours of moving bytes. > Fixing a standby that lags a lot This is the one I would watch most. WAL replay on a standby is essentially single-threaded (the startup process), while the primary generates WAL from many backends at once. At high write volume one core replaying can fall behind, and no network change fixes that. recovery_prefetch (PG15+) and fast standby storage help the read side, but the single-threaded redo is a ceiling I would plan around up front. > Maintenance at times I would treat partitioning as non-optional: you cannot VACUUM or REINDEX a monolithic multi-TB table inside a window. I would also watch transaction-ID and multixact wraparound (age(datfrozenxid) and the multixact age) -- the latter bites FK-heavy schemas and tends to surprise people -- and keep real disk headroom, since REINDEX or pg_repack of a large object needs roughly 2x its size free. A few less obvious things that tend to cause outages at scale: - Disk filling and a hard stop (PANIC): if archiving falls behind, or an orphaned replication slot retains WAL after a replica dies, pg_wal fills. I would set max_slot_wal_keep_size and watch the archiver. - No optimizer hints: a plan flip on a huge table can hurt and you cannot pin it the Oracle way. Raising statistics targets on the big tables and using extended statistics helps. - HA is assembled, not built in: Patroni or repmgr for automatic failover. Failover promotes a standby that may be lagging, so the real RTO/RPO ties back to the replay point above. - Testing a full restore on a schedule; at this size a broken backup tends to be found during the actual incident. > Finding equivalents of oracle items A rough map, as I think of it: RAC -> no shared-everything; streaming replication + read replicas (a different model, not a drop-in). RMAN -> pgBackRest. Data Guard -> physical replication (+ Patroni for failover). AWR/ASH -> pg_stat_statements plus your own monitoring. Flashback -> PITR. ora2pg for the schema/data conversion itself. > I dont see any limits in database/ objects The page you linked is right -- there is no hard limit on database or object size at 300 TB. The ceilings you will actually meet are operational (replay throughput, maintenance windows, connection scaling -- you will want PgBouncer), not catalog limits. One thing I would settle early: is this one instance, or sharded or heavily partitioned? Most of the pain above scales with the size of a single instance, so that choice changes many of the answers. That is my read; people here have run bigger clusters and may disagree on specifics. Happy to go deeper on any of these. Regards, Manu
Re: Challenges with huge postgresql deployments in 300+ TBs
От:
Juan Rodrigo Alejandro Burgos Mella <rodrigoburgosmella@gmail.com>
Дата:
Some things that came up in a migration that i participated:
- Refactoring from PL/SQL to PL/pgSQL
- If there is SP (functions, procedures, triggers) DML must be brought to ANSI SQL
- Validate field data types, anticipating transformation edges
- Atomicity and Rollback, in PostgreSQL transactions are canceled for complete when there is an error.
- PostgreSQL does not support autonomous transactions (PRAGMA AUTONOMOUS_TRANSACTION)
- PostgreSQL does not contemplate Synonyms.
I hope it helps you
- Refactoring from PL/SQL to PL/pgSQL
- If there is SP (functions, procedures, triggers) DML must be brought to ANSI SQL
- Validate field data types, anticipating transformation edges
- Atomicity and Rollback, in PostgreSQL transactions are canceled for complete when there is an error.
- PostgreSQL does not support autonomous transactions (PRAGMA AUTONOMOUS_TRANSACTION)
- PostgreSQL does not contemplate Synonyms.
I hope it helps you
Atte
JRBM
El jue, 8 oct 2026 a las 3:16, kavya chandren (<kavyachandren@gmail.com>) escribió:
Hi,We have an oracle deployment running in 300+ TB. Apart from migration challenges, do we foresee any operational challenges? I have not heard of something huge prior. This would be highly helpful in planning further.Few challenges which caught my attention include,
- Backup, restore of such a large database
- Recovery of a broken database (A lost replica)
- Fixing a standby that lags a lot
- Triggering a PITR with any backup tools
- Maintenance at times
- 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.htmlThanks and Regards,Kavya
Re: Challenges with huge postgresql deployments in 300+ TBs
От:
Alban Hertroys <haramrae@gmail.com>
Дата:
> On 8 Oct 2026, at 09:05, kavya chandren wrote: (…) > 6. Finding equivalents of oracle items (which is common during any oracle to postgres migration) There are a number of key differences between Oracle and Postgres that I’m aware of. This is by no means an exhaustive list, nor may it still be very accurate today; The last version of Oracle I worked with was Oracle 12i, so things may have changed. - Oracle doesn’t (did not?) differentiate between '' and NULL, while Postgres does. Make sure you get the right values in the right places. Safest bet is probably to import your data using column types in PG that allow for '’-values, so that you don’t get interrupted by errors from NOT NULL constraints and the like, and fix those later. That’s probably less of a hassle (even with 300+TB) than trying to correct the data beforehand. - Oracle has CLOBs, where you can dump those values in a (type) text field in PG. That may introduce a character encoding though, on which some values could fail if you don’t take care of those beforehand. - Oracle has BLOBs, where PG has a separate LOB table or bytea fields. Either approach has its cons and pros. - Old Oracle DDL may still contain CONNECT BY PRIOR recursion. PG uses recursive CTE’s. - Space-padded strings (CHAR(n)) in Postgres are supported, but they are usually advised against. Both performance and practical use of VARCHAR(n) are much better, with no practical benefits for CHAR(n). That is, apart from formatting monospace text output without processing (console style output or matrix printers and the like). Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
Re: Challenges with huge postgresql deployments in 300+ TBs
От:
kavya chandren <kavyachandren@gmail.com>
Дата:
Thanks Alban for your inputs around migration. My major concern was around operational challenges (if any) for such huge database
On Thu, Oct 8, 2026 at 3:39 PM Alban Hertroys <haramrae@gmail.com> wrote:
> On 8 Oct 2026, at 09:05, kavya chandren <kavyachandren@gmail.com> wrote:
(…)
> 6. Finding equivalents of oracle items (which is common during any oracle to postgres migration)
There are a number of key differences between Oracle and Postgres that I’m aware of. This is by no means an exhaustive list, nor may it still be very accurate today; The last version of Oracle I worked with was Oracle 12i, so things may have changed.
- Oracle doesn’t (did not?) differentiate between '' and NULL, while Postgres does. Make sure you get the right values in the right places. Safest bet is probably to import your data using column types in PG that allow for '’-values, so that you don’t get interrupted by errors from NOT NULL constraints and the like, and fix those later. That’s probably less of a hassle (even with 300+TB) than trying to correct the data beforehand.
- Oracle has CLOBs, where you can dump those values in a (type) text field in PG. That may introduce a character encoding though, on which some values could fail if you don’t take care of those beforehand.
- Oracle has BLOBs, where PG has a separate LOB table or bytea fields. Either approach has its cons and pros.
- Old Oracle DDL may still contain CONNECT BY PRIOR recursion. PG uses recursive CTE’s.
- Space-padded strings (CHAR(n)) in Postgres are supported, but they are usually advised against. Both performance and practical use of VARCHAR(n) are much better, with no practical benefits for CHAR(n). That is, apart from formatting monospace text output without processing (console style output or matrix printers and the like).
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
Re: Challenges with huge postgresql deployments in 300+ TBs
От:
Ron Johnson <ronljohnsonjr@gmail.com>
Дата:
On Thu, Oct 8, 2026 at 4:16 AM kavya chandren <kavyachandren@gmail.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,
- Backup, restore of such a large database
- Recovery of a broken database (A lost replica)
- Fixing a standby that lags a lot
- Triggering a PITR with any backup tools
- Maintenance at times
- 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!
Re: Challenges with huge postgresql deployments in 300+ TBs
От:
Guy Rixon <guyrixon@gmail.com>
Дата:
On Thu, Oct 8, 2026 at 4:16 AM kavya chandren <kavyachandren@gmail.com> wrote:
Hi,We have an oracle deployment running in 300+ TB. Apart from migration challenges, do we foresee any operational challenges?
My group at the University of Cambridge ran a Postgresql system that reached ~150 TB at end of mission, earlier this year; it had grown for 10 years of an ESA mission. It used a single server with multiple tablespaces (we weren’t allowed a file system that spanned all the disc crates) and was replicated synchronously to one other server. It ran Postgresql 9.3. There were a few issues.
Setting up the replication was very slow. When we had to restore the replica after breakage, it took several weeks, and this was system downtime as the primary could not accept new data until the replica was back.
Upgrading Postgresql would similarly have taken many weeks and (I think) would have needed storage equal to the entire DB. We had neither time nor space, so opted to stay on Postgresql 9.3 until the mission was complete.
The DB was partitioned old-style (our postgres pre-dated logical partitioning) into sets of tables for ~12,000 areas of the astronomical sky. These tables were extended in batches according to the complex observing-patterm. At then end of the mission, when the tablespaces were nearly full, I spent a lot of time moving tables between spaces to allow their growth. A smaller number of bigger tablespaces would have been better. Running out of space in a tablespace is very bad in old postgres; at least disruptive, and possibly deadly if it’s the default space where the WAL lives. We allocated an empty, non-pg file of ~100GB as a reserve against filling a partition.
The I/O performance was not great. because we were funnelling everything across the I/O interface of one server. Because moving to faster hardware was not possible (no money and couldn’t afford the downtime), we never fixed this. A system that federates the data across multiple servers that are not simple mirrors would have been much better.
Backing up was fraught. Our back-up apparatus, which was probably not the best and certainly not up to date with modern practice, had a race condition against the WAL segments disappearing. if we forced the system to keep more WAL, the WAL back-log could overwhelm the default data space and wreck everything. if we had to do this again, I would spend a good deal of the project-planning time sorting out the back-up.
Finally, the synchronous replica was a pain. We built it to get redundancy and later used it to host some read-only reporting apps. We never used the replica for recovery. if we’d made an asynchronous replica, or just had one bigger server with better performance (and reliable back-up) then I think we’d have done better.
This is a lot of negatives, but we did complete our part of the mission with the over-sized, under-resourced postgres. Ultimately, we made it work.
HTH,
Guy Rixon
Re: Challenges with huge postgresql deployments in 300+ TBs
От:
Justin <zzzzz.graf@gmail.com>
Дата:
On Thu, Oct 8, 2026 at 4:16 AM kavya chandren <kavyachandren@gmail.com> wrote:
Few challenges which caught my attention include,
- Backup, restore of such a large database
You going to want to do DISK snapshots with such a large database. You will want to use SAN/NAS that can export its snapshots to create offsite backups. PGBackrest is a great tool and I use it, however recovery time or creating a Read replica with PgBackRest on such a large database is painfully slow vs DISK SNAPSHOTS.
Todo disksnapshot backups you will need to turn on:
Full Page Writes (PG turns this on during a backup if it is off)
CheckSums
WAL Archiving somewhere on a different SAN/NAS and offsite somewhere.
Without WAL archiving you can not create online backup. PgBackRest, PgBasebackup and others tools all require capturing the WAL during the backup.
PG recently added incremental backups in 17
https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-INCREMENTAL-BACKUP
I also suggest creating Logical Backup using pg_dump every so often. This way there is backup that can be restored to any version of PG within reason. Pg_dumps will take some time on a 300 TB database so going to want to use the -f=directory and -j=number of workers arguements
https://www.postgresql.org/docs/current/app-pgdump.html
Todo disksnapshot backups you will need to turn on:
Full Page Writes (PG turns this on during a backup if it is off)
CheckSums
WAL Archiving somewhere on a different SAN/NAS and offsite somewhere.
Without WAL archiving you can not create online backup. PgBackRest, PgBasebackup and others tools all require capturing the WAL during the backup.
PG recently added incremental backups in 17
https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-INCREMENTAL-BACKUP
I also suggest creating Logical Backup using pg_dump every so often. This way there is backup that can be restored to any version of PG within reason. Pg_dumps will take some time on a 300 TB database so going to want to use the -f=directory and -j=number of workers arguements
https://www.postgresql.org/docs/current/app-pgdump.html
- Recovery of a broken database (A lost replica)
Restore a Disksnapsots put PG replica mode with the signal file and use the restore command to fetch the WAL files from the archive, and the connection details to the primary. The replica will bring itself online once the WAL is caught up. This requires keeping all the WAL files from the restored backup.
- Fixing a standby that lags a lot
Have to figure out why the standby is lagging first. Key setting that stops alot of lagging is hot_standby_feedback =on This stops most conflicting WAL replay updates coming from the primary as the replica communicates back to the primary its current tuple locks. This prevents vacuum removing previous tuples versions.
- Triggering a PITR with any backup tool
If you have a good backup and Full Pages writes on you have PITR. The length of time to recover is the time to restore a backup and replay all the WAL files to the desired time code.
- Maintenance at times
You have to define what you mean by maintenance? PG major maintenance event vacuum is automatic; it is pretty well tuned for most database loads. There are conditions where a table needs specific settings to trigger vacuum/analyze more/less often.
- 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.htmlThanks and Regards,Kavya
Re: Challenges with huge postgresql deployments in 300+ TBs
От:
Ray O'Donnell <ray@rodonnell.ie>
Дата:
On 08/10/2026 12:09, kavya chandren wrote: > Thanks Alban for your inputs around migration. My major concern was > around operational challenges (if any) for such huge database There was an interesting talk on very large databases at PGDay UK: https://pgday.uk/events/pgdayuk2026/schedule/session/276-operational-hazards-of-managing-postgresql-dbs-over-100tb/ I don't see any slides there, but it may be worth approaching the presenter or the conference organisers to see if something can be made available. Ray. -- Ray O'Donnell // Galway // Ireland ray@rodonnell.ie
Re: Challenges with huge postgresql deployments in 300+ TBs
От:
Adrian Klaver <adrian.klaver@aklaver.com>
Дата:
On 10/8/26 12:05 AM, kavya chandren wrote: > Hi, > > We have an oracle deployment running in 300+ TB. Apart from migration > challenges, do we foresee any operational challenges? I have not heard > of something huge prior. This would be highly helpful in planning further. Why the change? Is this a pricing issue or are you looking to get something else out of the migration? > > 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 docs/current/limits.html> > > Thanks and Regards, > Kavya -- Adrian Klaver adrian.klaver@aklaver.com
Re: Challenges with huge postgresql deployments in 300+ TBs
От:
kavya chandren <kavyachandren@gmail.com>
Дата:
Hi Adrian,
Intention is to get away from Oracle and get committed to Open source Postgresql.
On Thu, Oct 8, 2026 at 8:20 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 10/8/26 12:05 AM, kavya chandren wrote:
> Hi,
>
> We have an oracle deployment running in 300+ TB. Apart from migration
> challenges, do we foresee any operational challenges? I have not heard
> of something huge prior. This would be highly helpful in planning further.
Why the change?
Is this a pricing issue or are you looking to get something else out of
the migration?
>
> 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 <https://www.postgresql.org/
> docs/current/limits.html>
>
> Thanks and Regards,
> Kavya
--
Adrian Klaver
adrian.klaver@aklaver.com
Re: Challenges with huge postgresql deployments in 300+ TBs
От:
Karsten Hilbert <Karsten.Hilbert@gmx.net>
Дата:
Am Thu, Oct 08, 2026 at 02:48:03PM +0000 schrieb Ray O'Donnell: > There was an interesting talk on very large databases at PGDay UK: > > https://pgday.uk/events/pgdayuk2026/schedule/session/276-operational-hazards-of-managing-postgresql-dbs-over-100tb/ > > I don't see any slides there https://www.postgresql.eu/events/pgdayparis2026/sessions/session/7226/slides/836/PGDayParis2026-TeresaLopes-reduced.pdf Regards, Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: Challenges with huge postgresql deployments in 300+ TBs
От:
Adrian Klaver <adrian.klaver@aklaver.com>
Дата:
On 10/8/26 8:56 AM, kavya chandren wrote: > Hi Adrian, > > Intention is to get away from Oracle and get committed to Open source > Postgresql. Alright I understand. Not sure what your timeline and resources are, but I would start with a test setup using a subset of the existing Oracle setup and work against that to do an initial determination of the challenges. > > On Thu, Oct 8, 2026 at 8:20 PM Adrian Klaver > wrote: > > On 10/8/26 12:05 AM, kavya chandren wrote: > > Hi, > > > > We have an oracle deployment running in 300+ TB. Apart from > migration > > challenges, do we foresee any operational challenges? I have not > heard > > of something huge prior. This would be highly helpful in planning > further. > > Why the change? > > Is this a pricing issue or are you looking to get something else out of > the migration? > > > > > 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 www.postgresql.org/docs/current/limits.html> www.postgresql.org/ > > docs/current/limits.html> > > > > Thanks and Regards, > > Kavya > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > -- Adrian Klaver adrian.klaver@aklaver.com
Re: Challenges with huge postgresql deployments in 300+ TBs
От:
Manu <manuelreyesbravo@gmail.com>
Дата:
Hi Kavya, > 3. Fixing a standby that lags a lot Of the operational walls, this is the one I would size before committing rather than after, so I put together a small harness that turns it into a number: it loads indexed data, pauses replay to separate WAL generation from replay, then times pure replay from local WAL and reports MB/s with a 95% CI and a lag curve. About 15 minutes on a couple hundred MB, and it refuses assert-enabled builds so the timing is real: https://github.com/Manuelreyesbravo/standby-replay-ceiling The numbers are hardware-specific -- one laptop core replays around 190 MB/s here, CPU-bound. The point is to run it on a subset of your intended storage and CPU and get your own ceiling before building the full system, which matches Adrian's start-from-a-test-setup suggestion; whether one replay core keeps up then depends on how much WAL your write workload generates. It measures that wall, it does not move it. Happy to adjust it if your setup would test differently. Regards, Manu