Postgres row_number() doubling table size roughly every 24 hours

Question

I have an Assets table with ~165,000 rows in it. However, the Assets make up "Collections" and each Collection may have ~10,000 items, which I want to save a "rank" for so users can see where a given asset ranks within the collection.

The rank can change (based on an internal score), so it needs to be updated periodically (a few times an hour).

That's currently being done on a per-collection basis with this:

UPDATE assets a
SET rank = a2.seqnum
FROM
  (SELECT a2.*,
          row_number() OVER (
                             ORDER BY elo_rating DESC) AS seqnum
   FROM assets a2
   WHERE a2.collection_id = #{collection_id} ) a2
WHERE a2.id = a.id;

However, that's causing the size of the table to double (i.e. 1GB to 2GB) roughly every 24 hours.

A VACUUM FULL clears this up, but that doesn't feel like a real solution.

Can the query be adjusted to not create so much (what I assume is) temporary storage?

Running PostgreSQL 13.

How many consecutive doublings have been observed? – jjanes Nov 14 '21 at 13:44 — jjanes, Nov 14 '21 at 13:44
@jjanes Maybe two, at most, before I `VACUUM`. – Shpigford Nov 14 '21 at 16:33 — Shpigford, Nov 14 '21 at 16:33

Erwin Brandstetter · Accepted Answer · 2021-11-14T04:17:45.750

Every update writes a new row version in Postgres. So (aside from TOASTed columns) updating every row in the table roughly doubles its size. That's what you observe. Dead tuples can later be cleaned up to shrink the physical size of the table - that's what VACUUM FULL does, expensively. See:

Are TOAST rows written for UPDATEs not changing the TOASTable column?

Alternatively, you might just not run VACUUM FULL and keep the table at ~ twice it's minimal physical size. If you run plain VACUUM (without FULL!) enough - and if you don't have long running transactions blocking that - Postgres will have marked dead tuples in the free-space map by the time the next UPDATE kicks in and can reuse the disk space, thus staying at ~ twice its minimal size. That's probably cheaper than shrinking and re-growing the table all the time, as the most expensive part is typically to physically grow the table. Be sure to have aggressive autovacuum settings for the table. See:

Probably better yet, break out the ranking into a minimal separate 1:1 table (a.k.a. "vertical partitioning") , so that only minimal rows have to be written "a few times an hour". Probably including elo_rating you mention in the query, which seems to change at least as frequently (?). (LEFT) JOIN to the main table in queries. While that adds considerable overhead, it may still be (substantially) cheaper. Depends on the complete picture, most importantly the average row size in table assets and the typical load apart from your costly updates.

See:

Postgres row_number() doubling table size roughly every 24 hours

1 Answers1