How do I cap a Postgres table by size and delete the oldest rows first?

Preview images for share links sat in a bytea column and were never deleted. Refusing new links at a ceiling is worse: the newest links are the ones in fresh posts.

One DELETE with a running sum, newest first, removes every row past the ceiling:

DELETE FROM shares WHERE id IN (
  SELECT id FROM (
    SELECT id,
      sum(octet_length(image)) OVER (ORDER BY created_at DESC, id DESC) AS acc,
      row_number()             OVER (ORDER BY created_at DESC, id DESC) AS nth
    FROM shares
  ) t
  WHERE acc > $1 AND nth > 1   -- $1 = ceiling in bytes; keep the newest row
) RETURNING octet_length(image) AS freed;

• Trim to 95% of the ceiling, or a full table runs this on every insert.
• Cache the total: re-read the sum every 10 minutes, add each insert's size in between. octet_length on bytea doesn't decompress, so it's cheap.
• Add a per-user cap (newest 200 per user_id), or one heavy user evicts everyone else.

Hit this bounding share-image storage in a game backend (1 GB guests, 4 GB players). Window functions docs