Use UNLOGGED table for `idempotency_plug_requests` (or make it configurable)

[RodolfoSilva]

I was looking at the migration and I think there's a small optimization worth considering here: making idempotency_plug_requests an UNLOGGED table.

Every request going through IdempotencyPlug hits this table at least twice (one INSERT and at least one SELECT, plus the cleanup of expired rows in the background). On busy apps that adds up, and most of the cost there is WAL related. Every write goes to pg_wal first, gets fsynced on commit, keeps the walwriter and checkpointer busy, and gets shipped to replicas if you have streaming or logical replication set up. Full page writes after checkpoints also push more pressure on shared_buffers.

UNLOGGED tables skip all of that. Writes only touch the heap and shared buffers, so INSERTs get noticeably cheaper and the table stops adding to WAL volume.

The catch is the usual one. If Postgres crashes or shuts down uncleanly, UNLOGGED tables get truncated on recovery, and they don't replicate to physical standbys, so they show up empty after a failover. For idempotency keys I think that's fine in most cases. The rows already have a short lifetime via expires_at, after a crash the app is in a degraded state anyway and clients will retry, and the contract here is "dedupe within a time window", not "durable log of every request".

That said, some apps (payments, anything where double processing is really bad) would rather keep the durability guarantee. So probably the safer move is making it configurable on the generator, or just documenting the ALTER TABLE ... SET UNLOGGED trick for whoever wants it.

Would a PR for this be welcome?

[danschultzer]

Yeah I was looking into UNLOGGED, but didn't add it because;

1. POST/PATCH requests are usually not frequent for most systems and most systems has little db load
2. POST/PATCH requests are usually write heavy so if you have perf issues you would likely already have tracked this down
3. The ecto migration is generic so it can't have postgres specific statements
4. And as you said, there would no longer be idempotent handling for requests that doesn't complete when the db unexpectedly shuts down

I think for systems with high load where this is necessary there would already be many other perf considerations and an awareness. It would be good to document it, and the reasons to use it (or not use it). Not sure where it should sit in the docs, maybe IdempotencyPlug.EctoStore docs? I would appreciate a PR, thanks!

[danschultzer]

Added a note on performance in #15

[RodolfoSilva]

Sorry, I was planning to add it later next week... Thanks for this :D

[danschultzer]

All good I'll get a release out soon!