pg.ddx.io  pgsql-www@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Christoph Berg <myon@debian.org>
To: Dave Page <dpage@pgadmin.org>
Cc: Magnus Hagander <magnus@hagander.net>
Cc: Jonathan S. Katz <jkatz@postgresql.org>
Cc: Daniel Gustafsson <daniel@yesql.se>
Cc: pgsql-www@lists.postgresql.org, Andrey Borodin <x4mmm@yandex-team.ru>
Cc: Melanie Plageman <melanieplageman@gmail.com>
Cc: PostgreSQL Contributors <contributors@lists.postgresql.org>
Subject: Re: Introduce contributor badges
Date: Fri, 16 Oct 2026 10:59:52 +0200
Message-ID: <atHniPzZPas6faP6@msg.df7cb.de> (raw)
In-Reply-To: <CA+OCxoz62dPkZwihHVz0zt1LoAYAG-bJsEmyfPDoMkV9QN5CqA@mail.gmail.com>
References: <aszgh1hJGT6bG7QU@msg.df7cb.de>
	<329b5d0e-44ba-4c5f-8cc9-c9eb5424955b@postgresql.org>
	<as_JRi3I0UCkFvBJ@msg.df7cb.de>
	<AC76685F-1AE4-44F9-8108-03952D02493A@yesql.se>
	<c8a36992-f9f7-4ef8-bd54-15ac711ce621@postgresql.org>
	<CABUevEy6LfTWE9QaJuZKyUcBkggozTp_87OLmzDSBxyo2cV1iQ@mail.gmail.com>
	<atHYqT5b0eFnHEZl@msg.df7cb.de>
	<CA+OCxoz62dPkZwihHVz0zt1LoAYAG-bJsEmyfPDoMkV9QN5CqA@mail.gmail.com>

Re: Dave Page
> I haven't had time to review it properly yet, but I just had Claude Opus
> review it, and it seems to think there are a few issues that need fixing. I
> told it to pay particular attention to performance:

Thanks!

> ## Varnish cache invalidation

Fwiw some of these gaps were intentional because we deemed them either
minor, or out-of-scope-ish because they need fixes in the "items"
management infra in pgweb and aren't specific to badges. We should
have documented that, sorry. Anyway, this is probably the time to fix
all of them, thanks for pointing them out.

> 4. **The image URL never changes when the image does.**
> `/community/badge/<slug>/image/` has no file extension and no version in
> it. The docs say Varnish applies a longer policy to images "because they
> are considered never to change". Varnish itself is purged, but if the
> Varnish configuration (VCL) also sends long browser cache headers for
> images, browsers will keep showing the old image.

I think purging Varnish is enough here. Badge image changes aren't
critical, if a user sees the image somewhat longer, that doesn't cause
trouble.

> ## Database and app server
> 
> 5. **The `has_image` annotation reads every image out of TOAST**,
> PostgreSQL's out-of-line storage for large values
> (`BadgeManager.with_has_image`). The comment says the value is "never
> transferred", which is true for the network, but `imagedata > ''` compares
> the full bytea, so PostgreSQL has to fetch and reassemble the whole value.
> A test of 200 rows of about 96 KB each on the local database showed the
> comparison reading about 3,000 buffers in 31 ms, whilst `octet_length(img)
> > 0` read 2 buffers in 0.04 ms, because it only reads the TOAST header.
> Switching to `Length('imagedata') > 0` is a one-line fix. It runs on every
> cache miss of the people page (up to 200 badges) and on every account
> profile page load, which is never cached.

Ack.

> 6. **Oversized images are decoded in full (a "decompression bomb").**
> `ImageBinaryField.to_python` decodes the entire image with Pillow before
> rescaling. A PNG under 1 MB can still be about 170 megapixels before Pillow
> refuses it, which is several hundred MB of RAM plus a bicubic resize on the
> app server. `ModelForm` full-clean also re-parses the stored image on every
> badge edit, even when the image hasn't changed. Only managers of an
> approved organisation can upload, so the risk is moderate, but checking
> `img.size` before decoding would be cheap.

What max input size would we accept? 2000x2000 (4 megapixels) should
be pretty much enough for everyone?

Christoph






view thread (45+ messages)  latest in thread

Message-ID: <atHniPzZPas6faP6@msg.df7cb.de>
Permalink:  ../atHniPzZPas6faP6@msg.df7cb.de/
Also on:    postgresql.org/message-id/atHniPzZPas6faP6@msg.df7cb.de

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-www@postgresql.org
  Cc: myon@debian.org, dpage@pgadmin.org, magnus@hagander.net, jkatz@postgresql.org, daniel@yesql.se, x4mmm@yandex-team.ru, melanieplageman@gmail.com, contributors@lists.postgresql.org
  Subject: Re: Introduce contributor badges
  In-Reply-To: <atHniPzZPas6faP6@msg.df7cb.de>
  References: <aszgh1hJGT6bG7QU@msg.df7cb.de> <329b5d0e-44ba-4c5f-8cc9-c9eb5424955b@postgresql.org> <as_JRi3I0UCkFvBJ@msg.df7cb.de> <AC76685F-1AE4-44F9-8108-03952D02493A@yesql.se> <c8a36992-f9f7-4ef8-bd54-15ac711ce621@postgresql.org> <CABUevEy6LfTWE9QaJuZKyUcBkggozTp_87OLmzDSBxyo2cV1iQ@mail.gmail.com> <atHYqT5b0eFnHEZl@msg.df7cb.de> <CA+OCxoz62dPkZwihHVz0zt1LoAYAG-bJsEmyfPDoMkV9QN5CqA@mail.gmail.com> <atHniPzZPas6faP6@msg.df7cb.de>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox