PSQL, Caching, PAKE, SQLite, AVIF, SNAP, GIMP, EU/AI

1. PostgreSQL 18 (PSQL) - Nice features, especially the AIO is great. Providing something like 3x performance improvement on sequential scans is huge. Calls which trigger huge sequential scans are source of major slow downs with traditional solutions. Because those often also read so much data, that it's causing caching issues and causes completely flush of simple LRU caches. - Virtual columns are nice, yet I've used those as "Computed Columns" with SQL Server and or views and materialized views (with index). - uuidv7 is quite expected addition (Python also added it) - OAuth 2.0 is great, if you need it. SCRAM for passwords is (old) and nice too. - I've gotta wait for a month or so, and see what happens. After that I'll upgrade the production versions as well.

2. SCRAM is nice, yet PAKE is even better. Both provide mutual password, passphrase or shared secret (like API key) based authentication.

3. SQLite3 indexing (@ emschwartz.me) - A very nice post about SQLite3 indexing. When doing complex index optimizations, I usually end up performance testing the options, if not absolutely sure that the stuff will work. I've so many times encountered things what seem to be fine, are staid to be fine, but when you do practical testing, it doesn't still work out as expected. Or requires some fine tuning, just like that filtered index example in this post. You would THINK it works, but nope... Yet nothing new, this applies to basically everything, when tuning something. Just as example set_x=1, fine setting IS DONE, but does it practically change anything, that always needs to be verified separately. kw: SQLite3, databases

4. SQL OR is expensive (@ ethanseal.com) - Yep, been there done that. In one case one query triggered massive table scans lasting HOURS, no, not kidding. After few modifications it took well, less than a second. So, it CAN make massive difference in some situations. Developer said that he tested it and it worked fine, sure it did work fine with the test database having tens of rons, instead of having tens of billions of rows. Some cases the approach using UNION used index seek, and the version with OR or IN (...) used TABLE SCAN, which well, dose make a difference. Especially with massive archive databases, which are stored on slow medium, because nobody ever ... kw: SQL query optimization

5. AVIF, WebP - Lots of tinkering with different quality and encoding effort settings, color spaces, etc. But now it's all good. I've got script to convert legacy JPEGs to WebP / AVIF automatically and efficiently.

6. Fixed ram disk issues with snap. Now snap installed programs can access when specified so, to access my data-temp ram disk. It was annoying that you couldn't access specific files on ram disk from snap programs on Ubuntu / Linux. It seems that /tmp access is blocked on purpose with snap, but now it works.

7. GIMP - Bleep, why everything is... Now SNAP is working, but GIMP which is using AppImage is now crashing, which it didn't do earlier. - Kaboom: /tmp/.mount_GIMP-3ChpMMA/usr/lib/x86_64-linux-gnu/gimp/3.0/plug-ins/script-fu/script-fu: fatal error: GIMP crashed - (script-fu:34154): LibGimp-WARNING **: 13:15:26.695: script-fu: gimp_flush(): error: Broken pipe - fish: Job 1, 'GIMP-3.0.4-x86_64.AppImage --ve…' terminated by signal SIGSEGV (Address boundary error) - After some testing, and getting crazy about it, I debugged moar. It seems that the problem us a .json file in the tmpfs dir, it seems to crash GIMP. There's clearly something very wrong. Just for fun I created this file: gimp-denial-of-service-attack.json - (@ s.sami-lehtinen.net) Feel free to use that to troll colleagues. Place it is some directory they're likely going to use with GIMP, just for lulz. - And because GIMP is now mentioned, why doesn't GIMP allow setting AVIF as default export format.

8. EU investing heavily in AI technology for whole European Union. Let's just hope something good comes out of this. Project: Resource for AI Science in Europe (RAISE).

2026-03-22