标签：#postgres

Event sourcing gets sold on immutability. You don't update, you don't delete, you only append, so the history is permanent. It mostly isn't. The events are immutable because your code agrees not to touch them, not because anything actually stops it. Underneath they're still rows in Postgres, and rows have a DBA with write access. A migration that "cleans up" old data. A 2 a.m. query run against the wrong connection. A backup restored with slightly different bytes in it. Change one of those rows and a replay won't blink. The aggregate rebuilds, the projections rebuild, everything looks fine. Usually the first person to notice is a customer whose balance is off, and by then the trail is cold. Chain each event into the next The trick is small. Give every row two extra columns: a hash of its contents, and the hash of the row before it. #1 AccountOpened prev=00000… hash=70be4f… │ ▼ #2 AmountDeposited prev=70be4f… hash=796018… │ ▼ #3 AmountWithdrawn prev=796018… hash=6a0260… The hash is SHA-256(previousHash || json(payload)) . Nothing exotic. The point is that each hash depends on the one before it. Edit a payload and its hash stops matching. Rewrite that hash to cover for the edit, and now the next row's pointer is wrong. You can't fix one without breaking the next. About forty lines of it Appending an event hashes it together with the previous one: public HashChainedEntry Append ( object payload ) { var previousHash = _entries . Count == 0 ? GenesisHash : _entries [^ 1 ]. Hash ; var hash = ComputeHash ( previousHash , payload ); var entry = new HashChainedEntry ( _entries . Count + 1 , payload , previousHash , hash ); _entries . Add ( entry ); return entry ; } internal static byte [] ComputeHash ( byte [] previousHash , object payload ) { var payloadJson = JsonSerializer . SerializeToUtf8Bytes ( payload , payload . GetType ()); var combined = new byte [ previousHash . Length + payloadJson . Length ]; Buffer . BlockCopy ( previousHash , 0 , combined , 0 , previousHash .

Si vous utilisez PostgreSQL, vous avez probablement déjà dû choisir entre une clé primaire BIGSERIAL et un UUID. Depuis des années, la version 4 (aléatoire) est le choix par défaut quand on veut un identifiant unique et distribué. Mais en 2026, une alternative plus récente s’impose : UUID v7, qui intègre un timestamp et promet de meilleures performances pour les index. Dans cet article, je vous explique concrètement ce qui change, avec des benchmarks PostgreSQL et des exemples de code, pour que vous puissiez décider en connaissance de cause. UUID v4 : le standard aléatoire et son problème d’index Un UUID v4 est constitué de 122 bits aléatoires. Cette absence totale de tri est sa force pour l’unicité, mais elle devient un handicap dans un index B‑tree, qui est la structure utilisée par PostgreSQL pour les clés primaires. Lorsque vous insérez un nouvel UUID v4, il a autant de chances de se retrouver au début de l’index qu’à la fin. Résultat : l’index se fragmente, les pages se remplissent mal, et les performances d’écriture se dégradent à mesure que la table grossit. J’ai reproduit un test simple sur PostgreSQL 16 avec 10 millions de lignes, en utilisant une table dont la seule différence est la colonne id : -- Table UUID v4 CREATE TABLE events_v4 ( id UUID DEFAULT gen_random_uuid () PRIMARY KEY , payload JSONB , created_at TIMESTAMPTZ DEFAULT now () ); -- Table UUID v7 (généré côté application, voir plus bas) CREATE TABLE events_v7 ( id UUID PRIMARY KEY , payload JSONB , created_at TIMESTAMPTZ DEFAULT now () ); Après insertion, voici les mesures : Type de clé Taille de l’index Fragmentation Latence moyenne d’insertion BIGINT ~214 Mo 0 % ~0,8 ms/ligne UUID v4 ~428 Mo (2×) 99 % ~4,8 ms/ligne UUID v7 ~428 Mo (2×) ~2 % ~1,1 ms/ligne Ce qui frappe, c’est la fragmentation quasi nulle de l’UUID v7. L’index reste compact et les insertions sont presque aussi rapides qu’avec un BIGSERIAL. L’UUID v4, lui, est plus de quatre fois plus lent à l’insertion sur ce volume. UUID v7 :

If you manage PostgreSQL databases across multiple environments, you've probably done this: SSH to the DB host (or connect via psql ) Run CREATE USER jsmith CONNECTION LIMIT 20 PASSWORD '...' Slack the password to the developer Forget to log it anywhere Repeat for every environment, every onboarding, every access request It's tedious, error-prone, and leaves zero audit trail. Here's a better way. What I Built pg-user-api is a lightweight Flask REST API that wraps PostgreSQL user provisioning in clean HTTP endpoints. You register your databases once in a SQLite inventory, then any tooling — CI pipelines, internal portals, Ansible playbooks, or a plain curl — can create and manage users across environments without ever touching psql . GitHub: pcraavi/PostgreSQL-user-creation-API The Problem It Solves In teams that span dev, QA, UAT, and prod, you end up with different patterns of users: App service accounts — named after the host/port combo ( web01_8080 ) Kubernetes workload accounts — named after env prefix + farm ( dv_gearservice ) Individual dev/QA accounts — low connection limits, scoped to non-prod Read-only analyst accounts — prod only, no DDL DBA accounts — CREATEDB CREATEROLE LOGIN , rarely provisioned Each type has different CONNECTION LIMIT values, privilege levels, and naming conventions. Encoding these patterns in an API means the rules are consistent, repeatable, and auditable. Architecture The project is intentionally small — five Python files and a requirements list: pg_user_api/ ├── app.py # Flask app — all endpoints ├── auth.py # HTTP Basic Auth (constant-time compare) ├── database.py # SQLite registry + audit log ├── notifications.py # Notification stubs (Webex / Slack / Email) ├── seed_db.py # One-time setup: creates DB + sample records └── requirements.txt Two credential pairs, clearly separated: PG_API_USER / PG_API_PASS — who can call this API (your team/tooling) PG_ADMIN_USER / PG_ADMIN_PASS — the PostgreSQL DBA role that executes DDL The DBA cr