Re: Rearchitecting for storage - Mailing list pgsql-general

From Luca Ferrari
Subject Re: Rearchitecting for storage
Date
Msg-id CAKoxK+7XA+VHGApDn-1N=VSJKFpd=SR=ysTBRSUTB3muP+cKZw@mail.gmail.com
Whole thread Raw
In response to Re: Rearchitecting for storage  (Matthew Pounsett <matt@conundrum.com>)
Responses Re: Rearchitecting for storage
List pgsql-general
On Thu, Jul 18, 2019 at 10:09 PM Matthew Pounsett <matt@conundrum.com> wrote:
> That would likely keep the extra storage requirements small, but still non-zero.  Presumably the upgrade would be
unnecessaryif it could be done without rewriting files.  Is there any rule of thumb for making sure one has enough
spaceavailable for the upgrade?   I suppose that would come down to what exactly needs to get rewritten, in what order,
etc.,but the pg_upgrade docs don't seem to have that detail.  For example, since we've got an ~18TB table (including
itsindices), if that needs to be rewritten then we're still looking at requiring significant extra storage.  Recent
experiencesuggests postgres won't necessarily do things in the most storage-efficient way.. we just had a reindex on
thatdatabase fail (in --single-user) because 17TB was insufficient free storage for the db to grow into. 

This could be trivial, but any chance you can partition the table
and/or archive unused records (at least temporarly)? A 18 TB table
quite frankly sounds a good candidate to contain records no one is
interested in the near future.
In any case, if you can partition the table chances are you can at
least do a per-table backup that could simplify maintanance of the
database.

In desperate order, I would check also the log files (I mean, textual
logs, not wals) because occasionally I found them requiring a few GBs
on my disk, and that can be easily archived to gain some more extra
space.
Then I would go for some commodity NAS to attach as extra storage, at
least for the upgrade process.

If any of the following fails, I would probably drop all the indexes
to gain extra space, perform the upgrade, and then reindex (removing
the old cluster, in the case it has not been upgraded with the link
option).

Luca



pgsql-general by date:

Previous
From: Where is Where
Date:
Subject: maximum distance vs fixed distance for tsquery_phrase
Next
From: Tiemen Ruiten
Date:
Subject: very high replay_lag on 3-node cluster