Thread: CLUSTER details
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/sql-cluster.html Description: The cloister command should describe how or deals with free pages (I.e. are released) and what it’s lock model is (i.e. that it is (not yet?) an online operation. It should probably also mention if it has intermittent additional disk space requirements (or not).
On Thursday, June 6, 2024, PG Doc comments form <noreply@postgresql.org> wrote:
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/16/sql-cluster.html
Description:
The cloister command should describe how or deals with free pages (I.e. are
released) and what it’s lock model is (i.e. that it is (not yet?) an online
operation. It should probably also mention if it has intermittent additional
disk space requirements (or not).
All of that is already covered on the page. If you think it needs rewording I suggest you specify exactly what you think would be an improvement. For my part saying “rewrites the table contents into a new file” would probably be better than “physically reordered” which I sense you are misinterpreting as a kind of piecemeal operation when it isn’t.
David J.
Hello,
David G. Johnston wrote on 7. June 2024 15:24 (GMT +02:00):
> All of that is already covered on the page.
You are right, I missed the note about the two modes and their storage
consumption, I guess that part is described enough
> If you think it needs
> rewording I suggest you specify exactly what you think would be an
> improvement. For my part saying “rewrites the table contents into a new
> file” would probably be better than “physically reordered” which I
> sense
Exactly, something like that would help plus explicite mentioning
if any free pages (besides fillfactor) are created in the new files.
> you are misinterpreting as a kind of piecemeal operation when it isn’t.
Calm down, I am not (miss)interpreting anything I am asking for clarification
since it was unclear to me. Since I didnt want to assume how it works I could
not suggest a wordiing, without first relying on your expertise.
Is the following correct (I.e. it does not preserv free pages) and can
be added?
The temporary files for index and rewritten table occupy space on the same
filesystem as the original tablespace.
After successful completion,
all free pages of the clustered relation are deallocated.
Sidenote how is the feeling about pointing to
non-included extension? This footnote would be
an helpful tip:
If you need to carry out operations similar to CLUSTER or
VACCUUM FULL, but without blocking workload ("ONLINE"),
you might want to look into the pg_repack extension.
(And add sql-VACCUM to See Also)
Gruss
Bernd
--
http://bernd.eckenfels.net
On Fri, Jun 7, 2024, 10:57 Bernd Eckenfels <ecki@zusammenkunft.net> wrote:
Is the following correct (I.e. it does not preserv free pages) and can
be added?
You are hung up on the idea of free pages when this operation makes them irrelevant. Complete new files are written, populated to the extent of fill factor, then the old files are removed. At no point do empty pages get left over in the new file and they get treated the same as non-empty pages in the old file - they are utterly destroyed.
Sidenote how is the feeling about pointing to
non-included extension?
Not going to happen.
David J.
I am mostly „hung up“ on extending the doc to make it even more usedul (by sharing my use cases). As a user I want to know if CLUSTERing also frees space, and since it has the effect of doing so (and not pre-allocates, which is a bit odd),why not mention it. Btw where is that friendly community the Internet brags about? Gruss Bernd David G. Johnston wrote on 7. June 2024 20:44 (GMT +02:00): > On Fri, Jun 7, 2024, 10:57 Bernd Eckenfels <ecki@zusammenkunft.net> wrote: > >> >> Is the following correct (I.e. it does not preserv free pages) and can >> be added? >> > > You are hung up on the idea of free pages when this operation makes them > irrelevant. Complete new files are written, populated to the extent of > fill factor, then the old files are removed. At no point do empty pages > get left over in the new file and they get treated the same as non-empty > pages in the old file - they are utterly destroyed. > > >> Sidenote how is the feeling about pointing to >> non-included extension? > > > Not going to happen. > > David J. > Gruß Bernd — https://bernd.eckenfels.net
On Friday, June 7, 2024, Bernd Eckenfels <ecki@zusammenkunft.net> wrote:
As a user I want to know if CLUSTERing also frees space, and since it
has the effect of doing so (and not pre-allocates, which is a bit odd),why
not mention it.
Yeah, it doesn’t make it clear that rewriting the table means that only live tuples are written into the new file and thus there is a size difference before and after equal to the dead tuples not written.
David J.