What PostgreSQL is
PostgreSQL is an open source object-relational database whose origins go back to the POSTGRES project at the University of California, Berkeley, in 1986. It is developed by the PostgreSQL Global Development Group and released under its own liberal open source licence, similar to the BSD and MIT licences.
It is known for reliability and data integrity, has been ACID-compliant since 2001 and runs on all major operating systems. Many organisations choose it as their open source relational database, which is why it is one of the reporting destinations our Ledger module connects to.
What it does in your platform
Ledger
Holds a reporting copy of the ledger: entries, balances and reconciliation results in plain SQL tables. Finance, risk and data teams query it with the tools they already use, while the ledger stays the book of record.
How the connection works
One connector in the Ledger module writes to a PostgreSQL database that we deploy inside your platform and run for you. Your teams read it through read-only roles, so nothing they run there can change the ledger.
An entry is booked
A transfer is recorded in the ledger as balanced debits and credits, in real time.
It is copied over
The connector writes the entry to the reporting database over an encrypted connection, inside a transaction, so each row is either fully written or not written at all.
The day joins it
Daily balances and the results of the reconciliation are written next to the entries, so a reported total can be checked against the ledger's day.
Teams read it
Analysts connect with read-only roles. Row security policies can limit a team to the rows it may see, for example one entity's accounts.
Other systems subscribe
Where another system needs the data, PostgreSQL's logical replication can publish chosen tables to it, filtered by rows or columns.
Next to other providers
PostgreSQL is the simplest place to start: a reporting database inside your platform, with no other provider to bring in. When the data team grows, BigQuery or Snowflake can receive the same data through their own connectors.
Your accounting system still receives the daily journals, and the ledger stays the book of record. Adding or replacing a reporting destination does not change your apps.
When PostgreSQL fits best
A strong fit when
- You want SQL access to every entry and balance without adding another provider.
- You want the reporting data to stay inside your own platform.
- Your team already knows PostgreSQL and the tools around it.
Also worth a look
- BigQuery or Snowflake, when many teams run heavy analysis on large volumes.
- Your cloud provider's managed PostgreSQL, if you prefer it to a database we run.
How we get you live
The deployment
We deploy PostgreSQL inside your platform, sized for your volumes, with replication and backups in place from the first day, and we run it from then on.
The tables
We agree the reporting tables with your finance and data teams: entries, balances, reconciliation results and the views they use most.
The access
Database credentials stay in your platform's secrets. Each team gets a read-only role, with row security where a team must only see its own entities.
A full test run
A test environment fills a test database first, and your teams check its tables against the ledger before production data arrives.
