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.