agora inbox for pgsql-committers@postgresql.org
help / color / mirror / Atom feedpgsql: Fix index rebuild progress reporting for REPACK (CONCURRENTLY)
3+ messages / 2 participants
[nested] [flat]
* pgsql: Fix index rebuild progress reporting for REPACK (CONCURRENTLY)
@ 2026-09-16 09:40 Fujii Masao <fujii@postgresql.org>
0 siblings, 0 replies; 3+ messages in thread
From: Fujii Masao @ 2026-09-16 09:40 UTC (permalink / raw)
To: pgsql-committers@lists.postgresql.org
Fix index rebuild progress reporting for REPACK (CONCURRENTLY)
Previously, during REPACK (CONCURRENTLY), index_rebuild_count in
pg_stat_progress_repack and pg_stat_progress_cluster did not advance
as indexes were rebuilt. This made it impossible for users monitoring the
operation to tell how many indexes had been completed.
Fix this by incrementing the count after each index is built on the new
heap, so that both views report the number of completed index builds.
Author: Fujii Masao <masao.fujii@gmail.com>
Reviewed-by: Álvaro Herrera <alvherre@kurilemu.de>
Discussion: https://postgr.es/m/CAHGQGwFUsrBvTurkSU8TEc=DZTunqteUXrasseqNMT7AMBZKRw@mail.gmail.com
Backpatch-through: 19
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/0765b48874a3fcabadfcaa493d068ec6507e7fc7
Modified Files
--------------
src/backend/commands/repack.c | 2 ++
1 file changed, 2 insertions(+)
^ permalink raw reply [nested|flat] 3+ messages in thread
* pgsql: Fix index rebuild progress reporting for REPACK (CONCURRENTLY)
@ 2026-09-16 09:40 Fujii Masao <fujii@postgresql.org>
2026-09-16 12:26 ` Re: pgsql: Fix index rebuild progress reporting for REPACK (CONCURRENTLY) Manuel Reyes Bravo <manuelreyesbravo@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Fujii Masao @ 2026-09-16 09:40 UTC (permalink / raw)
To: pgsql-committers@lists.postgresql.org
Fix index rebuild progress reporting for REPACK (CONCURRENTLY)
Previously, during REPACK (CONCURRENTLY), index_rebuild_count in
pg_stat_progress_repack and pg_stat_progress_cluster did not advance
as indexes were rebuilt. This made it impossible for users monitoring the
operation to tell how many indexes had been completed.
Fix this by incrementing the count after each index is built on the new
heap, so that both views report the number of completed index builds.
Author: Fujii Masao <masao.fujii@gmail.com>
Reviewed-by: Álvaro Herrera <alvherre@kurilemu.de>
Discussion: https://postgr.es/m/CAHGQGwFUsrBvTurkSU8TEc=DZTunqteUXrasseqNMT7AMBZKRw@mail.gmail.com
Backpatch-through: 19
Branch
------
REL_19_STABLE
Details
-------
https://git.postgresql.org/pg/commitdiff/ccbea44294bb3813882052d14a6158302c4b19aa
Modified Files
--------------
src/backend/commands/repack.c | 2 ++
1 file changed, 2 insertions(+)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pgsql: Fix index rebuild progress reporting for REPACK (CONCURRENTLY)
2026-09-16 09:40 pgsql: Fix index rebuild progress reporting for REPACK (CONCURRENTLY) Fujii Masao <fujii@postgresql.org>
@ 2026-09-16 12:26 ` Manuel Reyes Bravo <manuelreyesbravo@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Manuel Reyes Bravo @ 2026-09-16 12:26 UTC (permalink / raw)
To: Fujii Masao <fujii@postgresql.org>; +Cc: pgsql-committers@lists.postgresql.org
Hi,
Neither 4b445479f9e (TOAST index creation writing into
index_rebuild_count) nor 0765b48874a (the concurrent path not counting
its index builds) added a test, and both are easy to break again
without anyone noticing, since nothing checks the values the progress
views report.
repack_toast.spec already has what is needed: it stops REPACK
(CONCURRENTLY) at repack-concurrently-before-lock, which comes right
after build_new_indexes(), on a table that has a TOAST table and one
index. The attached patch adds one step there, in the other session:
SELECT phase, index_rebuild_count FROM pg_stat_progress_repack;
To check that it catches both problems, I ran the spec on master
(0765b48874a) and with each fix reverted, rebuilding only the touched
file each time:
master, both fixes rebuilding index | 1
4b445479f9e reverted rebuilding index | 3
0765b48874a reverted rebuilding index | 0
git show 4b445479f9e -- src/backend/catalog/toasting.c | git apply -R
git show 0765b48874a -- src/backend/commands/repack.c | git apply -R
make -C src/test/modules/injection_points check \
ISOLATION=repack_toast REGRESS= TAP_TESTS=
On master the whole injection_points module passes with it (4 regress
and 14 isolation tests), built with --enable-cassert and
--enable-injection-points. The patch also applies to REL_19_STABLE,
where both fixes were backpatched, but I have only checked that it
applies there, not run it.
The non-concurrent path has no injection point in the middle, so the
test covers only REPACK (CONCURRENTLY). I watched the other commands
live instead, polling pg_stat_progress_repack from a second connection
without pause on a table with 1.5M rows, a 39 MB TOAST table and three
indexes. Each line is a change seen in the view: time in ms, phase,
and index_rebuild_count after the arrow.
19beta2, VACUUM FULL:
3 ms, initializing -> 2
3 ms, seq scanning heap -> 2
241 ms, rebuilding index -> 2
364 ms, rebuilding index -> 1
521 ms, rebuilding index -> 2
1898 ms, performing final cleanup -> 3
master, VACUUM FULL:
3 ms, seq scanning heap -> 0
765 ms, rebuilding index -> 0
962 ms, rebuilding index -> 1
1191 ms, rebuilding index -> 2
2589 ms, performing final cleanup -> 3
19beta2, REPACK (CONCURRENTLY):
5 ms, initializing -> 2
342 ms, rebuilding index -> 2
2042 ms, catch-up -> 2
2044 ms, performing final cleanup -> 2
master, REPACK (CONCURRENTLY):
5 ms, seq scanning heap -> 0
1027 ms, rebuilding index -> 0
1180 ms, rebuilding index -> 1
1416 ms, rebuilding index -> 2
2735 ms, rebuilding index -> 3
2736 ms, catch-up -> 3
On 19beta2 the count jumps to 2 while the new heap is created, goes
backwards to 1 when the first index is rebuilt, and in concurrent mode
stays at 2 to the end. On master CLUSTER and REPACK look exactly like
VACUUM FULL above, so the non-concurrent path is right as well. (The
script is attached too; the master build has --enable-cassert, which is
why it is slower.)
Regards,
Manu
El mié, 16 sept 2026 a las 6:40, Fujii Masao (<fujii@postgresql.org>) escribió:
>
> Fix index rebuild progress reporting for REPACK (CONCURRENTLY)
>
> Previously, during REPACK (CONCURRENTLY), index_rebuild_count in
> pg_stat_progress_repack and pg_stat_progress_cluster did not advance
> as indexes were rebuilt. This made it impossible for users monitoring the
> operation to tell how many indexes had been completed.
>
> Fix this by incrementing the count after each index is built on the new
> heap, so that both views report the number of completed index builds.
>
> Author: Fujii Masao <masao.fujii@gmail.com>
> Reviewed-by: Álvaro Herrera <alvherre@kurilemu.de>
> Discussion: https://postgr.es/m/CAHGQGwFUsrBvTurkSU8TEc=DZTunqteUXrasseqNMT7AMBZKRw@mail.gmail.com
> Backpatch-through: 19
>
> Branch
> ------
> REL_19_STABLE
>
> Details
> -------
> https://git.postgresql.org/pg/commitdiff/ccbea44294bb3813882052d14a6158302c4b19aa
>
> Modified Files
> --------------
> src/backend/commands/repack.c | 2 ++
> 1 file changed, 2 insertions(+)
>
Attachments:
[application/x-shellscript] progress_live.sh (517B, ../../CA+bCEdCqyHwJ7mZkegty2VDhN=TanocdoG_npFewE4=BNk1sWQ@mail.gmail.com/2-progress_live.sh)
download
[text/x-python] progress_live.py (3.4K, ../../CA+bCEdCqyHwJ7mZkegty2VDhN=TanocdoG_npFewE4=BNk1sWQ@mail.gmail.com/3-progress_live.py)
download | inline:
"""Watch pg_stat_progress_repack live while VACUUM FULL / CLUSTER / REPACK run.
usage: progress_live.py <port> <label>
Creates a table with a TOAST table and three indexes, then for each command
polls the progress view from a second connection every few milliseconds and
prints every change of (phase, index_rebuild_count) with its time.
"""
import sys
import threading
import time
import psycopg
PORT, LABEL = sys.argv[1], sys.argv[2]
POLL = float(sys.argv[3]) if len(sys.argv) > 3 else 0.005
DSN = f"host=/tmp port={PORT} user=postgres dbname=postgres"
ROWS = 1_500_000
COMMANDS = [
"VACUUM FULL progress_t",
"CLUSTER progress_t USING progress_t_pkey",
"REPACK progress_t",
"REPACK (CONCURRENTLY) progress_t",
]
def setup():
with psycopg.connect(DSN, autocommit=True) as c:
c.execute("DROP TABLE IF EXISTS progress_t")
c.execute("CREATE TABLE progress_t (id int PRIMARY KEY, a int, b text, t text)")
c.execute(f"""
INSERT INTO progress_t
SELECT g, g % 9973, md5(g::text),
CASE WHEN g % 500 = 0
THEN (SELECT string_agg(md5((g * x)::text), '')
FROM generate_series(1, 400) x)
END
FROM generate_series(1, {ROWS}) g""")
c.execute("CREATE INDEX progress_t_a ON progress_t (a)")
c.execute("CREATE INDEX progress_t_b ON progress_t (b)")
c.execute("ANALYZE progress_t")
n_idx = c.execute("SELECT count(*) FROM pg_index WHERE indrelid = 'progress_t'::regclass").fetchone()[0]
toast = c.execute("""SELECT t.relname, pg_size_pretty(pg_relation_size(t.oid))
FROM pg_class c JOIN pg_class t ON t.oid = c.reltoastrelid
WHERE c.relname = 'progress_t'""").fetchone()
ver = c.execute("SELECT version()").fetchone()[0].split(" on ")[0]
print(f"### {LABEL}: {ver}")
print(f"table progress_t: {ROWS} rows, {n_idx} indexes, TOAST {toast[0]} = {toast[1]}\n")
return n_idx
def watch(command):
done = threading.Event()
error = []
def run():
try:
with psycopg.connect(DSN, autocommit=True) as c:
c.execute(command)
except Exception as e: # reported below, not swallowed
error.append(e)
finally:
done.set()
samples, last = 0, None
timeline = []
with psycopg.connect(DSN, autocommit=True) as mon:
worker = threading.Thread(target=run)
t0 = time.monotonic()
worker.start()
while not done.is_set():
row = mon.execute(
"SELECT phase, index_rebuild_count FROM pg_stat_progress_repack "
"WHERE relid = 'progress_t'::regclass").fetchone()
samples += 1
if row is not None and row != last:
timeline.append((int((time.monotonic() - t0) * 1000), row[0], row[1]))
last = row
time.sleep(POLL)
worker.join()
elapsed = int((time.monotonic() - t0) * 1000)
print(f"-- {command} ({elapsed} ms, {samples} samples)")
if error:
print(f" ERROR: {error[0]}")
for ms, phase, count in timeline:
print(f" {ms:>6} ms {phase:<28} index_rebuild_count = {count}")
print()
n_idx = setup()
for cmd in COMMANDS:
watch(cmd)
print(f"(the table has {n_idx} indexes: a correct count never goes above {n_idx})")
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-09-16 12:26 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-16 09:40 pgsql: Fix index rebuild progress reporting for REPACK (CONCURRENTLY) Fujii Masao <fujii@postgresql.org>
2026-09-16 09:40 pgsql: Fix index rebuild progress reporting for REPACK (CONCURRENTLY) Fujii Masao <fujii@postgresql.org>
2026-09-16 12:26 ` Manuel Reyes Bravo <manuelreyesbravo@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox