Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xEmib-00000001HCz-3Yv0 for pgsql-general@arkaria.postgresql.org; Thu, 08 Oct 2026 11:55:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xEmia-0000000BnRW-3aPn for pgsql-general@arkaria.postgresql.org; Thu, 08 Oct 2026 11:55:24 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xEmia-0000000BnRN-1h3x for pgsql-general@lists.postgresql.org; Thu, 08 Oct 2026 11:55:24 +0000 Received: from mail-wr1-x42c.google.com ([2a00:1450:4864:20::42c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1xEmiY-00000000z0d-1OhM for pgsql-general@postgresql.org; Thu, 08 Oct 2026 11:55:23 +0000 Received: by mail-wr1-x42c.google.com with SMTP id ffacd0b85a97d-48afe75f055so4675230f8f.2 for ; Thu, 08 Oct 2026 04:55:22 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1791460521; x=1792065321; darn=postgresql.org; h=message-id:in-reply-to:to:references:date:subject:mime-version :content-type:from:from:to:cc:subject:date:message-id:reply-to :content-type; bh=MYhXDURjuCL6GiELUgFwltN08UKxPwMcPtEyJ13uFQs=; b=DPaqdqHRqGLLUAsOZJHmVo2IjWqpEFT2l8AkTdFieJyqs+EIAwifz8SmsEeAWs+Kc2 Y6xfNXbERmt1ym5J2eo+uVxYrY1WBF2/1d9fbhs8LzqcJ/Brm1qgOFC6+QJLBDWRSeXZ 5sHD6tFyGVBfVzOK/v1TKUNt43Px7as5eYWAoVQe84+ZcsIko7kUi2wAUXHCIB4yCPYl 0tYsBBcbW9+pJ96qO5zl72apQMD1dMjSzfkNX5XQBz6citypnt1ZxPDssyVMxsSVi2Xj lZkZIwCGPJR7hL0zfNr4aylEjG+SzL8q4hAZdtO40jLQlzHj2ySLLcEdSdTuwGu9NKUu s7fQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20260707; t=1791460521; x=1792065321; h=message-id:in-reply-to:to:references:date:subject:mime-version :content-type:from:x-gm-gg:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to:content-type; bh=MYhXDURjuCL6GiELUgFwltN08UKxPwMcPtEyJ13uFQs=; b=gpUMWLCzsx7DgtAT5HsG6yjONyVwBM+nc7cmpd6Z5TYVFq7S1c3+31d1NhCsLzJ+jr pVlFByDmu+0q4gaN7DuKaHMtElNtmrM65N+AwYZRRZKnfSXK2N0pFpyciqfkkuI1JIHA yNYmwXzYsnZayl0Ahwlarx6fe2Ifb0KlfhRHpLoaAa6rYPr5Q6/5OJeQjcXMhK3TC92F vL1WDOhxsRl46BCgi7stMBDgcbC77+dfX9Yrd5zNjW0Ov/WNdNKi4wiV24d4PJ9g/ulK z3nuqVyTDqJFf+F/oNISkuzvV2Q/B6fVkHRp0VKd926LuSprFQ+QYSczc6Oe3wY5pJPS T//g== X-Gm-Message-State: AFq9FYIuxwfJrSmM57uJjoLxUoAGSsMqUMTW7gay5fimdjfC0wKPMvY5 C4rpK6/LrJB79wjk6ylFsHpMOfwZaN9E37+UIECcEUP5skuXAcGD3DBGr5GuHES2 X-Gm-Gg: AYBFou3PyJ1MfQYEA4z9JaEMpzFrI4bR4nr7rrP/DbzWlmuue5vNDSd5zNwxSs1JxfD uMyfNcRCCMuQD+tPk/PVwdLy9kcVXHt2B4YqogLL9eMTH/dAhsBpzbKnQijgH6altU3Eiii75Zs Lll9p/45WVsrl1vz5K2KPhbZMS4VSRU+p/JcHBiZA4B8jpjii2iADW7+NwGY8hYcUYzjkh3bQqW R6CMTcrUp2+HPzd2dYH+t1Khp4rnWcHO0pbCX8opxrxBGqcrExZttKFKv1nGQAqAUUCfoBswJgs WWGIr8anD13kq9aF/FQaqamUIGKBrxBgiOCy254FyjPofS9+F8SJ1rqziQcTc9TkpkCp4DNdFJ9 ytmJFFvRbTYakxBMWTRVVgbQ1+kGoSmk/A+Y63y7Ox7L7Ncd8OFMN5KSajHT+3vDRZxwcL2GMDZ snsFj+V3smIGdPMXHTYsPkh9Tcx0697zjgKCsrGLMP4YOGMfxcfk0p5K/S0SNhOBUwnYrTr5DZ7 9JVFbJvIg7bQh0IWtRYNGmwexnQuPWwBmPjXEGr X-Received: by 2002:a05:6000:2888:b0:48b:2a1:dfc1 with SMTP id ffacd0b85a97d-48c7288f8bdmr9200901f8f.22.1791460520775; Thu, 08 Oct 2026 04:55:20 -0700 (PDT) Received: from smtpclient.apple (host-2-103-56-66.as13285.net. [2.103.56.66]) by smtp.gmail.com with ESMTPSA id ffacd0b85a97d-48c71d2d563sm10903046f8f.41.2026.10.08.04.55.20 for (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Thu, 08 Oct 2026 04:55:20 -0700 (PDT) From: Guy Rixon Content-Type: multipart/alternative; boundary="Apple-Mail=_D43978BC-CA25-4482-A2D8-C4B5C5FE8BA8" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.700.81\)) Subject: Re: Challenges with huge postgresql deployments in 300+ TBs Date: Thu, 8 Oct 2026 12:55:09 +0100 References: To: pgsql-general@postgresql.org In-Reply-To: Message-Id: X-Mailer: Apple Mail (2.3826.700.81) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_D43978BC-CA25-4482-A2D8-C4B5C5FE8BA8 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 On Thu, Oct 8, 2026 at 4:16=E2=80=AFAM kavya chandren = > wrote: > Hi, >=20 > 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=E2=80=99t 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=E2=80=99s the = default space where the WAL lives. We allocated an empty, non-pg file of = ~100GB as a reserve against filling a partition.=20 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=E2=80=99t 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=E2=80=99d made an = asynchronous replica, or just had one bigger server with better = performance (and reliable back-up) then I think we=E2=80=99d 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 --Apple-Mail=_D43978BC-CA25-4482-A2D8-C4B5C5FE8BA8 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
On Thu, Oct 8, 2026 at 4:16=E2=80=AFAM = 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=E2=80=99t = 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=E2=80=99s 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=E2=80=99t 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=E2=80=99d made an asynchronous replica, or just had = one bigger server with better performance (and reliable back-up) then I = think we=E2=80=99d 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



= --Apple-Mail=_D43978BC-CA25-4482-A2D8-C4B5C5FE8BA8--