pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Guy Rixon <guyrixon@gmail.com>
To: pgsql-general@postgresql.org
Subject: Re: Challenges with huge postgresql deployments in 300+ TBs
Date: Thu, 8 Oct 2026 12:55:09 +0100
Message-ID: <EDE3D4D4-BBEA-4122-B620-253E245B2976@gmail.com> (raw)
In-Reply-To: <CANzqJaARsR4feHM11=kf3HoDEFGcEMuK0b2sj+NqALLcTVwkCg@mail.gmail.com>
References: <CAPXWBGai9XJdoikZXt_7-XmUHF5iSTkKpM3cQnk2xVQVcou8dQ@mail.gmail.com>
	<CANzqJaARsR4feHM11=kf3HoDEFGcEMuK0b2sj+NqALLcTVwkCg@mail.gmail.com>

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

view thread (15+ messages)  latest in thread

Message-ID: <EDE3D4D4-BBEA-4122-B620-253E245B2976@gmail.com>
Permalink:  ../EDE3D4D4-BBEA-4122-B620-253E245B2976@gmail.com/
Also on:    postgresql.org/message-id/EDE3D4D4-BBEA-4122-B620-253E245B2976@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: guyrixon@gmail.com
  Subject: Re: Challenges with huge postgresql deployments in 300+ TBs
  In-Reply-To: <EDE3D4D4-BBEA-4122-B620-253E245B2976@gmail.com>
  References: <CAPXWBGai9XJdoikZXt_7-XmUHF5iSTkKpM3cQnk2xVQVcou8dQ@mail.gmail.com> <CANzqJaARsR4feHM11=kf3HoDEFGcEMuK0b2sj+NqALLcTVwkCg@mail.gmail.com> <EDE3D4D4-BBEA-4122-B620-253E245B2976@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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