pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedChallenges with huge postgresql deployments in 300+ TBs
15+ messages / 11 participants
[nested] [flat]
* Challenges with huge postgresql deployments in 300+ TBs
@ 2026-10-08 07:05 kavya chandren <kavyachandren@gmail.com>
2026-10-08 08:58 ` Re: Challenges with huge postgresql deployments in 300+ TBs Jan Karremans <karremans.ja@gmail.com>
2026-10-08 08:59 ` Re: Challenges with huge postgresql deployments in 300+ TBs Manu <manuelreyesbravo@gmail.com>
2026-10-08 09:56 ` Re: Challenges with huge postgresql deployments in 300+ TBs Juan Rodrigo Alejandro Burgos Mella <rodrigoburgosmella@gmail.com>
2026-10-08 10:09 ` Re: Challenges with huge postgresql deployments in 300+ TBs Alban Hertroys <haramrae@gmail.com>
2026-10-08 11:09 ` Re: Challenges with huge postgresql deployments in 300+ TBs Ron Johnson <ronljohnsonjr@gmail.com>
2026-10-08 14:26 ` Re: Challenges with huge postgresql deployments in 300+ TBs Justin <zzzzz.graf@gmail.com>
2026-10-08 14:50 ` Re: Challenges with huge postgresql deployments in 300+ TBs Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 7 replies; 15+ messages in thread
From: kavya chandren @ 2026-10-08 07:05 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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,
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
Thanks and Regards,
Kavya
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Challenges with huge postgresql deployments in 300+ TBs
2026-10-08 07:05 Challenges with huge postgresql deployments in 300+ TBs kavya chandren <kavyachandren@gmail.com>
@ 2026-10-08 08:58 ` Jan Karremans <karremans.ja@gmail.com>
6 siblings, 0 replies; 15+ messages in thread
From: Jan Karremans @ 2026-10-08 08:58 UTC (permalink / raw)
To: kavya chandren <kavyachandren@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
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.html
>
> Thanks and Regards,
> Kavya
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Challenges with huge postgresql deployments in 300+ TBs
2026-10-08 07:05 Challenges with huge postgresql deployments in 300+ TBs kavya chandren <kavyachandren@gmail.com>
@ 2026-10-08 08:59 ` Manu <manuelreyesbravo@gmail.com>
6 siblings, 0 replies; 15+ messages in thread
From: Manu @ 2026-10-08 08:59 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org; +Cc: kavya chandren <kavyachandren@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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Challenges with huge postgresql deployments in 300+ TBs
2026-10-08 07:05 Challenges with huge postgresql deployments in 300+ TBs kavya chandren <kavyachandren@gmail.com>
@ 2026-10-08 09:56 ` Juan Rodrigo Alejandro Burgos Mella <rodrigoburgosmella@gmail.com>
6 siblings, 0 replies; 15+ messages in thread
From: Juan Rodrigo Alejandro Burgos Mella @ 2026-10-08 09:56 UTC (permalink / raw)
To: kavya chandren <kavyachandren@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
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
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,
>
> 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
>
> Thanks and Regards,
> Kavya
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Challenges with huge postgresql deployments in 300+ TBs
2026-10-08 07:05 Challenges with huge postgresql deployments in 300+ TBs kavya chandren <kavyachandren@gmail.com>
@ 2026-10-08 10:09 ` Alban Hertroys <haramrae@gmail.com>
2026-10-08 11:09 ` Re: Challenges with huge postgresql deployments in 300+ TBs kavya chandren <kavyachandren@gmail.com>
6 siblings, 1 reply; 15+ messages in thread
From: Alban Hertroys @ 2026-10-08 10:09 UTC (permalink / raw)
To: kavya chandren <kavyachandren@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
> 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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Challenges with huge postgresql deployments in 300+ TBs
2026-10-08 07:05 Challenges with huge postgresql deployments in 300+ TBs kavya chandren <kavyachandren@gmail.com>
2026-10-08 10:09 ` Re: Challenges with huge postgresql deployments in 300+ TBs Alban Hertroys <haramrae@gmail.com>
@ 2026-10-08 11:09 ` kavya chandren <kavyachandren@gmail.com>
2026-10-08 14:48 ` Re: Challenges with huge postgresql deployments in 300+ TBs Ray O'Donnell <ray@rodonnell.ie>
0 siblings, 1 reply; 15+ messages in thread
From: kavya chandren @ 2026-10-08 11:09 UTC (permalink / raw)
To: Alban Hertroys <haramrae@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
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.
>
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Challenges with huge postgresql deployments in 300+ TBs
2026-10-08 07:05 Challenges with huge postgresql deployments in 300+ TBs kavya chandren <kavyachandren@gmail.com>
2026-10-08 10:09 ` Re: Challenges with huge postgresql deployments in 300+ TBs Alban Hertroys <haramrae@gmail.com>
2026-10-08 11:09 ` Re: Challenges with huge postgresql deployments in 300+ TBs kavya chandren <kavyachandren@gmail.com>
@ 2026-10-08 14:48 ` Ray O'Donnell <ray@rodonnell.ie>
2026-10-08 17:33 ` Re: Challenges with huge postgresql deployments in 300+ TBs Karsten Hilbert <Karsten.Hilbert@gmx.net>
0 siblings, 1 reply; 15+ messages in thread
From: Ray O'Donnell @ 2026-10-08 14:48 UTC (permalink / raw)
To: kavya chandren <kavyachandren@gmail.com>; Alban Hertroys <haramrae@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
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-...
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Challenges with huge postgresql deployments in 300+ TBs
2026-10-08 07:05 Challenges with huge postgresql deployments in 300+ TBs kavya chandren <kavyachandren@gmail.com>
2026-10-08 10:09 ` Re: Challenges with huge postgresql deployments in 300+ TBs Alban Hertroys <haramrae@gmail.com>
2026-10-08 11:09 ` Re: Challenges with huge postgresql deployments in 300+ TBs kavya chandren <kavyachandren@gmail.com>
2026-10-08 14:48 ` Re: Challenges with huge postgresql deployments in 300+ TBs Ray O'Donnell <ray@rodonnell.ie>
@ 2026-10-08 17:33 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
0 siblings, 0 replies; 15+ messages in thread
From: Karsten Hilbert @ 2026-10-08 17:33 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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-...
>
> I don't see any slides there
https://www.postgresql.eu/events/pgdayparis2026/sessions/session/7226/slides/836/PGDayParis2026-Tere...
Regards,
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Challenges with huge postgresql deployments in 300+ TBs
2026-10-08 07:05 Challenges with huge postgresql deployments in 300+ TBs kavya chandren <kavyachandren@gmail.com>
@ 2026-10-08 11:09 ` Ron Johnson <ronljohnsonjr@gmail.com>
2026-10-08 11:55 ` Re: Challenges with huge postgresql deployments in 300+ TBs Guy Rixon <guyrixon@gmail.com>
6 siblings, 1 reply; 15+ messages in thread
From: Ron Johnson @ 2026-10-08 11:09 UTC (permalink / raw)
To: pgsql-general
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,
>
> 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!
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Challenges with huge postgresql deployments in 300+ TBs
2026-10-08 07:05 Challenges with huge postgresql deployments in 300+ TBs kavya chandren <kavyachandren@gmail.com>
2026-10-08 11:09 ` Re: Challenges with huge postgresql deployments in 300+ TBs Ron Johnson <ronljohnsonjr@gmail.com>
@ 2026-10-08 11:55 ` Guy Rixon <guyrixon@gmail.com>
0 siblings, 0 replies; 15+ messages in thread
From: Guy Rixon @ 2026-10-08 11:55 UTC (permalink / raw)
To: pgsql-general
On Thu, Oct 8, 2026 at 4:16 AM kavya chandren <kavyachandren@gmail.com <mailto: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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Challenges with huge postgresql deployments in 300+ TBs
2026-10-08 07:05 Challenges with huge postgresql deployments in 300+ TBs kavya chandren <kavyachandren@gmail.com>
@ 2026-10-08 14:26 ` Justin <zzzzz.graf@gmail.com>
6 siblings, 0 replies; 15+ messages in thread
From: Justin @ 2026-10-08 14:26 UTC (permalink / raw)
To: kavya chandren <kavyachandren@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
On Thu, Oct 8, 2026 at 4:16 AM kavya chandren <kavyachandren@gmail.com>
wrote:
>
> Few challenges which caught my attention include,
>
> 1. 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
> 1. 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.
>
> 1. 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.
>
> 1. 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.
>
> 1. 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.
>
> 1. 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
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Challenges with huge postgresql deployments in 300+ TBs
2026-10-08 07:05 Challenges with huge postgresql deployments in 300+ TBs kavya chandren <kavyachandren@gmail.com>
@ 2026-10-08 14:50 ` Adrian Klaver <adrian.klaver@aklaver.com>
2026-10-08 15:56 ` Re: Challenges with huge postgresql deployments in 300+ TBs kavya chandren <kavyachandren@gmail.com>
6 siblings, 1 reply; 15+ messages in thread
From: Adrian Klaver @ 2026-10-08 14:50 UTC (permalink / raw)
To: kavya chandren <kavyachandren@gmail.com>; pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Challenges with huge postgresql deployments in 300+ TBs
2026-10-08 07:05 Challenges with huge postgresql deployments in 300+ TBs kavya chandren <kavyachandren@gmail.com>
2026-10-08 14:50 ` Re: Challenges with huge postgresql deployments in 300+ TBs Adrian Klaver <adrian.klaver@aklaver.com>
@ 2026-10-08 15:56 ` kavya chandren <kavyachandren@gmail.com>
2026-10-08 19:41 ` Re: Challenges with huge postgresql deployments in 300+ TBs Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 15+ messages in thread
From: kavya chandren @ 2026-10-08 15:56 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Challenges with huge postgresql deployments in 300+ TBs
2026-10-08 07:05 Challenges with huge postgresql deployments in 300+ TBs kavya chandren <kavyachandren@gmail.com>
2026-10-08 14:50 ` Re: Challenges with huge postgresql deployments in 300+ TBs Adrian Klaver <adrian.klaver@aklaver.com>
2026-10-08 15:56 ` Re: Challenges with huge postgresql deployments in 300+ TBs kavya chandren <kavyachandren@gmail.com>
@ 2026-10-08 19:41 ` Adrian Klaver <adrian.klaver@aklaver.com>
2026-10-09 18:04 ` Re: Challenges with huge postgresql deployments in 300+ TBs Manu <manuelreyesbravo@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Adrian Klaver @ 2026-10-08 19:41 UTC (permalink / raw)
To: kavya chandren <kavyachandren@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
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 <adrian.klaver@aklaver.com
> <mailto: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 <http://
> www.postgresql.org/docs/current/limits.html> <https://
> www.postgresql.org/ <https://www.postgresql.org/;
> > docs/current/limits.html>
> >
> > Thanks and Regards,
> > Kavya
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Challenges with huge postgresql deployments in 300+ TBs
2026-10-08 07:05 Challenges with huge postgresql deployments in 300+ TBs kavya chandren <kavyachandren@gmail.com>
2026-10-08 14:50 ` Re: Challenges with huge postgresql deployments in 300+ TBs Adrian Klaver <adrian.klaver@aklaver.com>
2026-10-08 15:56 ` Re: Challenges with huge postgresql deployments in 300+ TBs kavya chandren <kavyachandren@gmail.com>
2026-10-08 19:41 ` Re: Challenges with huge postgresql deployments in 300+ TBs Adrian Klaver <adrian.klaver@aklaver.com>
@ 2026-10-09 18:04 ` Manu <manuelreyesbravo@gmail.com>
0 siblings, 0 replies; 15+ messages in thread
From: Manu @ 2026-10-09 18:04 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org; +Cc: kavya chandren <kavyachandren@gmail.com>; Adrian Klaver <adrian.klaver@aklaver.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
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2026-10-09 18:04 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-08 07:05 Challenges with huge postgresql deployments in 300+ TBs kavya chandren <kavyachandren@gmail.com>
2026-10-08 08:58 ` Jan Karremans <karremans.ja@gmail.com>
2026-10-08 08:59 ` Manu <manuelreyesbravo@gmail.com>
2026-10-08 09:56 ` Juan Rodrigo Alejandro Burgos Mella <rodrigoburgosmella@gmail.com>
2026-10-08 10:09 ` Alban Hertroys <haramrae@gmail.com>
2026-10-08 11:09 ` kavya chandren <kavyachandren@gmail.com>
2026-10-08 14:48 ` Ray O'Donnell <ray@rodonnell.ie>
2026-10-08 17:33 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
2026-10-08 11:09 ` Ron Johnson <ronljohnsonjr@gmail.com>
2026-10-08 11:55 ` Guy Rixon <guyrixon@gmail.com>
2026-10-08 14:26 ` Justin <zzzzz.graf@gmail.com>
2026-10-08 14:50 ` Adrian Klaver <adrian.klaver@aklaver.com>
2026-10-08 15:56 ` kavya chandren <kavyachandren@gmail.com>
2026-10-08 19:41 ` Adrian Klaver <adrian.klaver@aklaver.com>
2026-10-09 18:04 ` Manu <manuelreyesbravo@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox