Ledger Implementation in PostgreSQL

First, the tl;dr: I am working on a financial ledger implementation implemented entirely in PostgreSQL called pgledger.

Before I get to the why, here’s how it looks so far:

-- Set up your accounts:
select id from pgledger_create_account('account_1'); -- save this as account_1_id
select id from pgledger_create_account('account_2'); -- save this as account_2_id

-- Create transfers:
select * from pgledger_create_transfer($account_1_id, $account_2_id, 12.34);
select * from pgledger_create_transfer($account_1_id, $account_2_id, 56.78);

-- See updated balances:
select name, balance, version from pgledger_get_account($account_2_id);

   name    | balance | version
-----------+---------+---------
 account_2 |   69.12 |       2

-- See ledger entries:
select * from pgledger_entries where account_id = $account_2_id;

  id   | account_id | transfer_id | amount | account_previous_balance | account_current_balance | account_version |          created_at
-------+------------+-------------+--------+--------------------------+-------------------------+-----------------+-------------------------------
 96198 |         42 |       48103 |  12.34 |                     0.00 |                   12.34 |               1 | 2025-03-19 21:31:03.596426+00
 96200 |         42 |       48104 |  56.78 |                    12.34 |                   69.12 |               2 | 2025-03-19 21:31:21.615916+00

Each transfer subtracts from one account’s balance and adds to another account’s balance. It also writes two entries, one for each account, which record the previous and current balances, as well as the account versions. Each transfer to or from an account increments the version, giving a linear view of the changing balance over time.

This makes it easy to view the history to understand why an account balance is at its current value, or even query for a historical value at a given time.

It’s all just functions and tables, so you have the full power of PostgreSQL. Start a transaction and execute as many transfers as you want grouped together. Or query the tables using whatever SQL you desire.

Why Ledgers

I’ve worked in payments for a long time at many different companies, and one recurring theme is building in-house financial ledger software.

Ledgers are a fundamental building block of any software that deals with money. It’s incredibly important to know what money is where, how it got there, and what it’s for.

They serve both current app needs as well as reporting and reconciliation. Everything from “do I have enough money in this account to do X?” to “why doesn’t an account have the balance I expect?” and “where is my money getting held up in my processes?”

A pattern I’ve noticed is that most companies tend to build their own internal ledger. There are many ways to implement a ledger, but at their core, the concepts are the same and the feature sets are pretty consistent. It doesn’t feel like something everyone has to reimplement every time.

Furthermore, building a ledger properly from scratch is tricky, and there are lots of potential edge cases and race conditions. For example, concurrent transfers causing an account to go negative, or concurrent balance updates clobbering each other. Several times, I’ve seen folks start with a simple table to record payments/transfers/etc, and then realize over time that they actually did need a proper double entry ledger.

Why PostgreSQL

Today, if you don’t want to build your own ledger, you do have a few options. For example, there are hosted services like Modern Treasury and ledger-specific databases like TigerBeetle. Both of these are impressive and probably a good fit for many.

But by using a ledger outside of the main application database, you lose transactionality and atomicity. Namely, you have to worry about orchestrating two systems that can fail independently. What happens if you write your main data, but the ledger update fails? Or the ledger operation succeeds but your app hits an error and fails to write the surrounding data. Integrating with these often requires two phase commits and other strategies to ensure they stay in sync. And when they fall out of sync, it can be very hard to debug.

What I generally want is to be able to include ledger updates in the same database transaction as the other work, and then have it either all commit or all rollback atomically. In the past, I’ve done this with a bunch of application code. But that ties it to a specific language, framework, etc. It’s not very portable to a new project, and possibly why we don’t see a lot of open source libraries around ledgers.

So this time I’m trying something different. I am working on a ledger implementation entirely in PostgreSQL. That means as long as you use PostgreSQL (and in theory it could be ported to other databases), it is entirely transactional/atomic and language/framework/application agnostic. All you need to do from app code is execute the right SQL functions within the same database transactions that you write everything else.

Building it in PostgreSQL also means you don’t have to integrate with any new APIs or run any new services. It’s in line with the idea of “Just Use Postgres for Everything”, which is especially attractive to startups and small companies.

This is partly an experiment to see what a ledger implementation in pure PostgreSQL would look like, and partly something I hope to use on a future project.

Testing in Go

If you look at the code today, you’ll notice that while the entire implementation is in SQL, the tests are in Go. I chose Go mainly because it’s the language I’m working with the most these days, and it supports good concurrency. I have a couple of concurrent tests already that look for deadlocks and race conditions, and I hope to write more.

Feedback Please

Please check out pgledger and I’d love to hear what you think, especially if you work with ledgers today. I have many more features I’d like to implement, so feel free to keep an eye on the project. And if this is something that you are interested in using, please let me know.