I see users excited by REPACK in PostgreSQL 19 (currently in beta), because it looks like a simple command that reduces table bloat. But it's not that simple.
Choosing the "right" FILLFACTOR is fundamentally a heuristic problem. For INSERTs, you can estimate future growth based on the expected lifecycle of newly inserted rows. For REPACK, you're repacking existing rows whose future update patterns are largely unknown.
Excess free space is a waste of space, not only on disk, but also in memory, twice: Linux filesystem cache and PostgreSQL shared buffers. However, for rows that are still likely to be updated, that free space helps avoid massive index maintenance thanks to HOT updates.
So you still need to think about FILLFACTOR, but now you need to think about it twice: what you set for future inserts and what you set for REPACK.
Let's look at a typical row lifecycle: you insert a row, it may be updated a few times, and then it is mostly queried and perhaps deleted in the future. A typical example is a customer order: it is inserted, updated while being processed, and then remains unchanged.
FILLFACTOR 100% and first update
I create a table with 4 indexes (primary key and 3 columns) and define no FILLFACTOR, so it defaults to 100, and insert one thousand rows:
drop table if exists events;
create table events(
id bigserial primary key,
payload text,
status text default 'new',
counter int default 0,
x int,
y int,
z int
);
create index on events(x);
create index on events(y);
create index on events(z);
insert into events(payload)
select repeat('x',100)
from generate_series(1,1000)
;
An update increments the counter, which is not indexed, for each row:
postgres=# explain (analyze, costs off, buffers, wal, summary off)
update events
set counter=counter+1
where status='new'
;
QUERY PLAN
--------------------------------------------------------------------------
Update on events (actual time=9.306..9.307 rows=0.00 loops=1)
Buffers: shared hit=11139 dirtied=27 written=28
WAL: records=6074 bytes=522582
-> Seq Scan on events (actual time=0.389..0.685 rows=1000.00 loops=1)
Filter: (status = 'new'::text)
Buffers: shared hit=19 written=1
Planning:
Buffers: shared hit=2
(8 rows)
We observe a huge write amplification: updating one thousand rows generates more than six thousand WAL records because each update creates a new row version, updates the visibility information of the previous version, and updates four indexes.
This is typically where PostgreSQL experts recommend a FILLFACTOR lower than the default, to leave enough free space for new versions of rows in the same page. The physical location of the row does not change, the indexes do not need to be updated, only one heap page is modified (HOT update), and only one WAL record is generated per updated row.
But that's moving too fast. Don't change FILLFACTOR without understanding the lifecycle of the rows you insert. Let's see what happens with future updates to those rows.
FILLFACTOR 100% and frequent updates
In my case, if I continue updating the counter on the existing rows, after approximately one hundred updates the write amplification disappears, without changing FILLFACTOR (which was left at its default value of 100% when those rows were inserted):
postgres=# \watch count=120 interval=0.01
...
Fri 07 Aug 2026 09:13:33 AM UTC (every 0.01s)
QUERY PLAN
--------------------------------------------------------------------------
Update on events (actual time=2.076..2.077 rows=0.00 loops=1)
Buffers: shared hit=3115
WAL: records=1026 bytes=75854
-> Seq Scan on events (actual time=0.015..0.722 rows=1000.00 loops=1)
Filter: (status = 'new'::text)
Buffers: shared hit=1115
WAL: records=26 bytes=4854
(7 rows)
After a while, all updates become HOT updates because the previous row versions have passed the visibility horizon. Space becomes reusable on each page and new row versions can be placed there. FILLFACTOR only affects inserts by reserving space when rows are created. Updates have their own space management mechanism, storing new versions and reusing space freed by older versions.
FILLFACTOR 100% with inserts and one update
That magic works only for rows that continue to be updated. In real life, however, new inserts also occur, and rows are rarely updated hundreds of times. Typically, new rows are queried and updated for a while, like events being processed, and become mostly read-only once the related business event is completed.
I insert more rows, with the status 'new', and update then to 'old':
postgres=# insert into events(payload)
select repeat('x',100)
from generate_series(1,1000)
\;
explain (analyze, costs off, buffers, wal, summary off)
update events
set status='old' , counter=counter+1
where status='new'
\watch c=120 i=0.01
...
Fri 07 Aug 2026 09:40:08 AM UTC (every 0.01s)
QUERY PLAN
----------------------------------------------------------------------------
Update on events (actual time=19.743..19.743 rows=0.00 loops=1)
Buffers: shared hit=16559 dirtied=25 written=25
WAL: records=6096 bytes=528646
-> Seq Scan on events (actual time=10.412..10.672 rows=1000.00 loops=1)
Filter: (status = 'new'::text)
Rows Removed by Filter: 100000
Buffers: shared hit=3647
WAL: records=19 bytes=2950
(8 rows)
Now the write amplification is visible again because the updates concern newly inserted rows. Space can be reused once older row versions pass the visibility horizon, but new inserts will reuse that space. As a result, pages remain full, HOT updates are no longer possible, and updating a new row generates six WAL records again.
To improve this, we need to reserve some free space for updates by preventing inserts from consuming it. That's the purpose of FILLFACTOR.
FILLFACTOR 50% for one update
If I set FILLFACTOR to 50%, inserts will not consume free space on a page that is only half full. That space remains available for future row versions created by updates:
postgres=# alter table events set (fillfactor=50)
;
ALTER TABLE
postgres=# insert into events(payload)
select repeat('x',100)
from generate_series(1,1000)
\;
explain (analyze, costs off, buffers, wal, summary off)
update events
set status='old' , counter=counter+1
where status='new'
;
QUERY PLAN
--------------------------------------------------------------------------
Update on events (actual time=11.322..11.322 rows=0.00 loops=1)
Buffers: shared hit=6726
WAL: records=1000 bytes=77000
-> Seq Scan on events (actual time=0.006..9.881 rows=1000.00 loops=1)
Filter: (status = 'new'::text)
Rows Removed by Filter: 101000
Buffers: shared hit=4726
Planning:
Buffers: shared hit=2
(9 rows)
The write amplification is solved for this insert-then-update-once pattern. However, choosing the right FILLFACTOR requires knowing how many times newly inserted rows will be updated during the visibility horizon, as well as how their size may change after updates.
FILLFACTOR 33% for two updates
If the lifecycle of the inserted rows includes two updates, the second update brings back the write amplification we observed earlier:
postgres=# insert into events(payload)
select repeat('x',100)
from generate_series(1,1000)
\;
explain (analyze, costs off, buffers, wal, summary off)
update events
set status='cur' , counter=counter+1
where status='new'
\;
explain (analyze, costs off, buffers, wal, summary off)
update events
set status='old' , counter=counter+1
where status='cur'
;
INSERT 0 1000
QUERY PLAN
----------------------------------------------------------------------------
Update on events (actual time=28.247..28.248 rows=0.00 loops=1)
Buffers: shared hit=14428
WAL: records=1052 bytes=84612
-> Seq Scan on events (actual time=10.694..26.768 rows=1000.00 loops=1)
Filter: (status = 'new'::text)
Rows Removed by Filter: 249000
Buffers: shared hit=12428
WAL: records=52 bytes=7612
(8 rows)
QUERY PLAN
----------------------------------------------------------------------------
Update on events (actual time=32.143..32.144 rows=0.00 loops=1)
Buffers: shared hit=22535 read=6 dirtied=41 written=4
WAL: records=3953 bytes=340927
-> Seq Scan on events (actual time=10.227..26.269 rows=1000.00 loops=1)
Filter: (status = 'cur'::text)
Rows Removed by Filter: 249000
Buffers: shared hit=12428
(7 rows)
When I know this update pattern, I can avoid the write amplification with a FILLFACTOR that allows three versions of each row to fit in the same block:
postgres=# alter table events set (fillfactor=33)
;
ALTER TABLE
postgres=# insert into events(payload)
select repeat('x',100)
from generate_series(1,1000)
\;
explain (analyze, costs off, buffers, wal, summary off)
update events
set status='cur' , counter=counter+1
where status='new'
\;
explain (analyze, costs off, buffers, wal, summary off)
update events
set status='old' , counter=counter+1
where status='cur'
;
INSERT 0 1000
QUERY PLAN
----------------------------------------------------------------------------
Update on events (actual time=28.462..28.462 rows=0.00 loops=1)
Buffers: shared hit=14428
WAL: records=1050 bytes=84494
-> Seq Scan on events (actual time=15.491..26.965 rows=1000.00 loops=1)
Filter: (status = 'new'::text)
Rows Removed by Filter: 251000
Buffers: shared hit=12428
WAL: records=50 bytes=7494
Planning:
Buffers: shared hit=2
(10 rows)
QUERY PLAN
----------------------------------------------------------------------------
Update on events (actual time=27.682..27.682 rows=0.00 loops=1)
Buffers: shared hit=14427
WAL: records=1000 bytes=77000
-> Seq Scan on events (actual time=14.726..26.205 rows=1000.00 loops=1)
Filter: (status = 'cur'::text)
Rows Removed by Filter: 251000
Buffers: shared hit=12427
(7 rows)
The math can become complicated because row sizes may change and the number of updates may vary. Ultimately, choosing the right FILLFACTOR is an empirical exercise, balancing write amplification against space amplification.
The impact also depends on the number of indexes that must be maintained when HOT updates are not possible, so indexing strategy matters as well. Partial indexes can be used to support queries on cold data, such as rows with status='old', without affecting rows in earlier lifecycle stages.
REPACK with the current FILLFACTOR
As the Seq Scan buffers show, my table now has 12427 pages, which is also visible from pg_class:
postgres=# vacuum analyze events;
ANALYZE
postgres=# select reltuples, relpages, relallvisible, reltuples/relpages "avg tuples per page", reloptions
from pg_class where oid='events'::regclass
;
reltuples | relpages | relallvisible | avg tuples per page | reloptions
-----------+----------+---------------+---------------------+-----------------
262000 | 12427 | 11277 | 21.083125452643436 | {fillfactor=33}
(1 row)
If I run REPACK, it uses the current FILLFACTOR, so it doesn't actually reduce bloat:
postgres=# repack (verbose, analyze) events
;
INFO: repacking "public.events" in physical order
INFO: "public.events": found 2018 removable, 262000 nonremovable row versions in 12427 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU: user: 0.07 s, system: 0.02 s, elapsed: 0.19 s.
INFO: analyzing "public.events"
INFO: "events": scanned 14556 of 14556 pages, containing 262000 live rows and 0 dead rows; 30000 rows in sample, 262000 estimated total rows
INFO: finished analyzing table "postgres.public.events"
avg read rate: 1775.146 MB/s, avg write rate: 0.366 MB/s
buffer usage: 94 hits, 14542 reads, 3 dirtied
WAL usage: 12 records, 3 full page images, 19814 bytes, 18132 full page image bytes, 0 buffers full
system usage: CPU: user: 0.05 s, system: 0.00 s, elapsed: 0.06
REPACK
postgres=# select reltuples, relpages, relallvisible, reltuples/relpages "avg tuples per page", reloptions
from pg_class where oid='events'::regclass
;
reltuples | relpages | relallvisible | avg tuples per page | reloptions
-----------+----------+---------------+---------------------+-----------------
262000 | 14556 | 0 | 17.999450398461114 | {fillfactor=33}
(1 row)
I now have even fewer rows per block. To reduce bloat, I must change FILLFACTOR before running REPACK. But which value should I choose?
REPACK with the FILLFACTOR 100%
The ideal value depends on the lifecycle of existing rows, not the lifecycle of newly inserted rows. We're repacking pre-existing rows that may have already reached a stage where they will never be updated again. In my example, all rows are now in the 'old' state and will not be updated again, so I can pack them as densely as possible:
postgres=# select status, count(*) from events group by all
;
status | count
--------+--------
old | 262000
(1 row)
postgres=# alter table events set (fillfactor=100)
;
ALTER TABLE
postgres=# repack (verbose, analyze) events
;
DEBUG: building index "pg_toast_21602_index" on table "pg_toast_21602" serially
DEBUG: index "pg_toast_21602_index" can safely use deduplication
INFO: repacking "public.events" in physical order
INFO: "public.events": found 0 removable, 262000 nonremovable row versions in 14556 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU: user: 0.06 s, system: 0.00 s, elapsed: 0.17 s.
INFO: analyzing "public.events"
INFO: "events": scanned 4764 of 4764 pages, containing 262000 live rows and 0 dead rows; 30000 rows in sample, 262000 estimated total rows
INFO: finished analyzing table "postgres.public.events"
avg read rate: 696.528 MB/s, avg write rate: 0.521 MB/s
buffer usage: 831 hits, 4012 reads, 3 dirtied
WAL usage: 12 records, 3 full page images, 20372 bytes, 18660 full page image bytes, 0 buffers full
system usage: CPU: user: 0.04 s, system: 0.00 s, elapsed: 0.04 s
REPACK
postgres=# select reltuples, relpages, relallvisible, reltuples/relpages "avg tuples per page", reloptions
from pg_class where oid='events'::regclass
;
reltuples | relpages | relallvisible | avg tuples per page | reloptions
-----------+----------+---------------+---------------------+-----------------
262000 | 4764 | 0 | 54.99580184718724 | {fillfactor=33}
(1 row)
postgres=# alter table events set (fillfactor=33)
;
ALTER TABLE
The existing rows are now packed much more densely, using 4764 pages instead of 14556. After that, I've restored a low FILLFACTOR for future inserts so that there is room for two HOT updates:
postgres=# insert into events(payload)
select repeat('x',100)
from generate_series(1,1000)
\;
explain (analyze, costs off, buffers, wal, summary off)
update events
set status='cur' , counter=counter+1
where status='new'
\;
explain (analyze, costs off, buffers, wal, summary off)
update events
set status='old' , counter=counter+1
where status='cur'
;
INSERT 0 1000
QUERY PLAN
----------------------------------------------------------------------------
Update on events (actual time=25.079..25.080 rows=0.00 loops=1)
Buffers: shared hit=6820
WAL: records=1000 bytes=77000
-> Seq Scan on events (actual time=23.368..23.608 rows=1000.00 loops=1)
Filter: (status = 'new'::text)
Rows Removed by Filter: 262000
Buffers: shared hit=4820
Planning:
Buffers: shared hit=9 dirtied=2
(9 rows)
QUERY PLAN
----------------------------------------------------------------------------
Update on events (actual time=24.752..24.753 rows=0.00 loops=1)
Buffers: shared hit=6820
WAL: records=1000 bytes=77000
-> Seq Scan on events (actual time=22.989..23.266 rows=1000.00 loops=1)
Filter: (status = 'cur'::text)
Rows Removed by Filter: 262000
Buffers: shared hit=4820
(7 rows)
postgres=#
This is the best outcome: low space amplification for existing rows, allowing more data to fit in memory, and low write amplification for newly inserted rows.
I was able to calculate the right FILLFACTOR because my DML pattern is simple. In most applications, it isn't.
You probably don't want to REPACK existing rows using the same FILLFACTOR that was chosen for future inserts. Set it appropriately before REPACK, and restore it afterward. If it's too low, you may make bloat worse than it already is. If it's too high, you may generate more WAL in the hours or days following the operation.
As we saw in the first experiment, when rows are packed too densely to allow HOT updates, the first updates suffer from write amplification. However, those updates also create reusable space on the page, making future HOT updates possible.
So, when in doubt, a FILLFACTOR close to 100% is probably the safest choice when repacking a table that mostly contains cold rows. The challenge is that choosing the "right" FILLFACTOR remains fundamentally a heuristic problem:
- for INSERTs, you can estimate future growth based on the expected lifecycle of new rows
- for REPACK, you're repacking existing rows whose future update likelihood is largely unknown. Analyzing the data first might be a good choice.
Top comments (1)
This hot-versus-cold distinction is the operationally important part. A table-wide FILLFACTOR is still a blunt instrument when both lifecycles coexist. Where the data model has a real archival boundary, separating active and cold data (often by time/lifecycle partition) lets the active set keep HOT headroom while immutable partitions stay densely packed.
I’d also canary this as a workload change, not just a storage change: compare
n_tup_hot_upd / n_tup_upd, WAL bytes per business update, relation pages, cache behavior, and vacuum cost over a representative window before and after. The immediate post-REPACK size can look excellent while the next update wave silently pays the bill.