Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xHdnI-00000003DLi-3JJu for pgsql-www@arkaria.postgresql.org; Fri, 16 Oct 2026 09:00:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xHdnH-0000000DXgb-1iRS for pgsql-www@arkaria.postgresql.org; Fri, 16 Oct 2026 09:00:03 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xHdnG-0000000DXgL-1q58; Fri, 16 Oct 2026 09:00:03 +0000 Received: from stravinsky.debian.org ([2001:41b8:202:deb::311:108]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xHdnD-00000002Ri6-0bVP; Fri, 16 Oct 2026 09:00:01 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=debian.org; s=smtpauto.stravinsky; h=X-Debian-User:In-Reply-To:Content-Type:MIME-Version: References:Message-ID:Subject:Cc:To:From:Date:Reply-To: Content-Transfer-Encoding:Content-ID:Content-Description; bh=erKu0t7wd8p4kfwg0KE8mKD5pVNI9AU9d4zi9jOxiyg=; b=UorK4pYQU2IpSKJDKhcZE1BUad rGlVKrIHZliQYz/z2vBrlaCGuY778kxiQWN5rDBEeziiqTR+zsM6nI1GTWPlHKUBM7T0v6ea8UBEk dFeTztHoaIlFmPpaVV1hUoqLGrBp0MaVveW2xCc2nLRmWOYXS73a6VxvNlT2bOWsjk7u9bwFZqalz cwybnqHrgdmLKT89RXhG0zLfaTTBlSxdPch+2mlB0R8VmHjct1sdxPZphL0bJ/Yfkr4v/aFHiPJHg aBv1XjPfoL7ZrLEQHXL56PHOQ/7CAu1FYPhHhJKciEP5zt7rXfMBmBp33tmyvw/hCpLMYhah6Hfwc NAQMsBiQ==; Received: from authenticated-user by stravinsky.debian.org with esmtpsa (TLS1.3:ECDHE_SECP256R1__RSA_PSS_RSAE_SHA256__AES_256_GCM:256) (Exim 4.96) (envelope-from ) id 1xHdn7-005qwt-2A; Fri, 16 Oct 2026 08:59:53 +0000 Date: Fri, 16 Oct 2026 10:59:52 +0200 From: Christoph Berg To: Dave Page Cc: Magnus Hagander , "Jonathan S. Katz" , Daniel Gustafsson , pgsql-www@lists.postgresql.org, Andrey Borodin , Melanie Plageman , PostgreSQL Contributors Subject: Re: Introduce contributor badges Message-ID: References: <329b5d0e-44ba-4c5f-8cc9-c9eb5424955b@postgresql.org> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: X-Debian-User: myon List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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//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