Zero -downtime FULL VACUUM/clustering/defragmentation with zero-downtime and now extra disk space

From: Ahmed Yarub Hani Al Nuaimi <ahmedyarubhani(at)gmail(dot)com>
To: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Zero -downtime FULL VACUUM/clustering/defragmentation with zero-downtime and now extra disk space
Date: 2024-06-25 22:12:44
Message-ID: CAF239vo4y4hDL6ysN0x_3eaXTkKb2884QCZR8yN5Tk_xo+E2FQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi guys,

This is inspired by this TODO list
https://wiki.postgresql.org/wiki/Todo#CLUSTER and by pg_repack and
pg_freeze projects.
My final goal is to create an extension that does direct data-file to
data-file transfer (no intermediate tables, no triggers) with no blocking
at all in order to simulate a zero-downtime FULL VACUUM and work on
continuously maintained clustering (which I'm a big fan of). This of course
should involve fixing inexes, etc... There are multiple steps to have a
final product but the first thing I would do is have two pointers: one
iterates from the beginning till it finds enough space, and the other
iterates from the end and adds this row to the space pointed by the first
pointer.
I would like to first know whether this is useful (in my previous companies
this was a complete game changer), and whether there are any alternative
algorithms that you would suggest.
Of course, there are many essential features that would follow later like
running automatically when system is under light load, adding a threshold
for when to do this "online FULL-VACUUM", and utilizing index CLUSTERing
and/or a set of predefined columns.

Any and all comments are welcome,
Ahmed

Browse pgsql-hackers by date

  From Date Subject
Next Message David E. Wheeler 2024-06-25 22:31:46 Re: RFC: Additional Directory for Extensions
Previous Message Daniel Gustafsson 2024-06-25 21:10:14 Re: Backporting BackgroundPsql