SERIAL for multi-column unique constraints

Question

I'm trying to build an append-only data model in Postgres, using the following schema:

CREATE TABLE IF NOT EXISTS account (
    id UUID DEFAULT uuid_generate_v4(),
    user_id INT NOT NULL,
    currency_id INT NOT NULL,
    balance INT NOT NULL,
    version INT NOT NULL DEFAULT 1,
    PRIMARY KEY(user_id, currency_id, version)
);

The idea is to have n users, owning m accounts. Each account is connected to a certain type of currency and can have a balance. Changes to the balance are persisted by adding a new row to the table (as opposed to updating the current row) and increasing the version field by 1.

The latest balance of a user would always be found by

SELECT balance FROM account WHERE user_id=? ORDER BY version DESC

with a full history of balance changes.

I should be able to solve this using multiple queries in a transaction (if exists, read version, then insert new row with version+1). But I was wondering if there was a more clever way of designing the table structure itself (something like a SERIAL that would only increment on inserts in the same unique constraints).

I assume it probably won't be as straight forward as this, but I thought I'd ask.

Answer 1

If you insist on gap-less version numbers, you cannot use a plain serial. You have to defend against race conditions with concurrent writes manually. You mentioned:

if exists, read version, then insert new row with version+1

But if there can be concurrent writes, it's not that simple. There are race conditions. You either need SERIALIZABLE transaction isolation (expensive), or lock manually. The manual:

To guarantee true serializability PostgreSQL uses predicate locking, which means that it keeps locks which allow it to determine when a write would have had an impact on the result of a previous read from a concurrent transaction, had it run first.

Predicate locking is (currently) exclusive to SERIALIZABLE transactions. Not available for manual locks. But you can work around this.
Example: if you have a corresponding users table, I'd suggest something like:

BEGIN;

SELECT FROM users WHERE user_id = 123 FOR UPDATE; -- lock the user

INSERT INTO account (user_id, currency_id, version)
VALUES (123
      , 66
      , COALESCE((SELECT max(version) + 1 FROM account WHERE (user_id, currrency_id) = (123, 66)), 1)
       );

COMMIT;

The exclusive lock on the user effectively prevents others from messing with it. All concurrent transactions have to comply, of course. No writing to account without taking that lock first. (At least, when it might affect the current maximum version in any way.)

Committing the transaction releases all acquired locks. So keep the transaction brief.

Why bother? Typically cheaper than using SERIALIZABLE transactions. And you can still work on distinct users concurrently.