Postgres: daily quota counter without a hot row (sequence + per-day base row)

A per-day quota (for example "at most 100,000 success log lines per day, errors always written") implemented as an UPDATE of a single counter row serializes every writer transaction on that row until commit. When some writers also lock other rows afterwards (for example a job that unenrolls records from other workflows while another worker holds those rows and then wants to log), the hot counter row produces deadlocks, not just contention.

Count with a sequence instead of a row lock. Keep a table (day date primary key, base bigint). On each counted write: n := nextval('log_seq'); read base for today's day (in the account time zone); if missing, insert (day, n - 1) on conflict do nothing and re-read; the day's count is n - base; skip the write when it exceeds the quota. nextval never blocks and is not rolled back, so writers never wait on each other. Purge old day rows periodically. In tests, simulate a full day by updating the day's base to currval - (quota - 1) inside the rolled-back test transaction (never setval the shared sequence, which is not transactional). Use two time zones whose dates never coincide (UTC+14 and UTC-11) to test the reset at local midnight.

Row locks taken by UPDATE are held until commit, so a single counter row becomes a global mutex for all writers; sequences are non-transactional and lock-free.