Financial products repeat the same database problems: find the latest state, page through history, and survive duplicate events from retries. These are PostgreSQL patterns that work well for that shape of workload.
Find the latest row per account
DISTINCT ON keeps the first row in each group. Put the account first in the ordering, then sort its balance snapshots from newest to oldest:
SELECT DISTINCT ON (bank_account_id)
bank_account_id, balance_amount
FROM account_balance
ORDER BY bank_account_id, account_balance_id DESC;
When IDs embed a timestamp, their order also gives snapshot order. The same key can drive time partitions and cursor pagination. That is a schema choice, not a property of arbitrary IDs.
For a statement page, filter by account and ask for rows with an ID below the last one shown, ordered descending. LIMIT caps the result. This avoids skipping an increasing number of rows with OFFSET as the user pages deeper.
Make repeated events safe
Webhooks and workflow retries can deliver the same operation again. A unique constraint defines what counts as a duplicate. ON CONFLICT DO NOTHING lets you skip it without a separate existence check.
Updates need their own rule. For payment contacts, LEAST keeps the first payment date and GREATEST keeps the last. An older event arriving late cannot move the last payment date backward.
For rules such as “one active certificate per app installation,” a partial unique index works well. Historical certificates can remain in the table while the database rejects a second active one.
Account for replication and time
Some tables feed a sync layer to mobile clients. Replicated, incrementally maintained views often lack primary keys, so REPLICA IDENTITY FULL identifies rows during updates and deletes. It increases replication volume; use it only where needed.
A constraint based on CURRENT_DATE can change its answer tomorrow. Where the rule concerns creation time, compare against the stored creation date instead.
Use pg_stat_statements to find expensive queries and inspect their execution plans. The query, index, and data distribution decide whether a pattern helps. A shorter query alone proves nothing about its cost.