Postgres Benchmarking: A Toy Bank Project

I saw this post and got really interested in trying to push Postgres further with more restrictions. 4M tps is a huge number, but, one could argue that this test was only for SELECTs, which is correct. My plan was, how well would Postgres do with a bit more restrictions?

The plan

I wanted to create a scenario that would be somewhat real, but not so much that I would take too long to finish the writing; so, I came up with the idea of a simple bank with three entities: account, deposit and bank_transaction. account would be an user, able to make deposits of money, and bank_transaction would be a way for users to transfer money from one account to another.

Just like the post that inspired this, we'll use pgbench for benchmarking.

Here's the code:

CREATE TABLE IF NOT EXISTS account (
   id            INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
   customer_name TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS bank_transaction (
   id           INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
   account_from INTEGER NOT NULL,
   account_to   INTEGER NOT NULL,
   amount       INTEGER NOT NULL,
   ts           TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

   FOREIGN KEY (account_from) REFERENCES account(id),
   FOREIGN KEY (account_to)   REFERENCES account(id)
);

CREATE INDEX ON bank_transaction USING btree(account_from);
CREATE INDEX ON bank_transaction USING btree(account_to);

CREATE TABLE IF NOT EXISTS deposit (
   id      INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
   account INTEGER NOT NULL,
   amount  INTEGER NOT NULL,
   ts      TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

   FOREIGN KEY (account) REFERENCES account(id)
);

CREATE INDEX ON deposit USING btree(account);

The idea should be straightforward. We got account, that can transfer money to another account through a bank_transaction. We want to have some constraints on that operation though; the ones regarding account existence are already there as FOREIGN KEYs, but, we have to make sure an account has enough money to transfer. With the relation deposit, we're able to put money in the accounts, and, create a query that aggregates the deposit values with the bank_transaction executed, making sure no one tries to sneak in a transaction without having money.

We also add indexes for the basic performance tuning; more could be done, but we only want a simple test for now.

So, deposit and account are simple inserts:

  • Accounts

    INSERT INTO account(customer_name)
  SELECT md5(random()::text) FROM generate_series(1,10000);

  • Deposits

This one has a trick: we can assume that, in the real use case, we'll already know the exact account to which we'll make the deposit, so we can do it this way;

\set value random(1,10000)
\set account random(1,10000)
INSERT INTO deposit(account, amount) VALUES (:account, :value);
  • Bank transactions

This is the more interesting part. First, we'll get all the deposits for a given account:

SELECT account, SUM(amount) FROM deposit
  WHERE id = 1
  GROUP BY account;

Next, for that same account, we need to figure out all the transfers it has ever done:

SELECT account_from, SUM(amount) FROM bank_transaction
 WHERE account_from = 1 
 GROUP BY account_from;

And then, all the transactions this same account has received:

SELECT account_to, SUM(amount) FROM bank_transaction
 WHERE account_to = 1 
 GROUP BY account_to;

We can then put it all together in a single trigger, that will run before every insertion on bank_transaction, ensuring that a value is always valid, no matter what happens there:

CREATE OR REPLACE FUNCTION is_balance_enough()
RETURNS TRIGGER AS $is_balance_enough$


DECLARE BALANCE INTEGER;

BEGIN

IF NEW.account_to = NEW.account_from THEN
RETURN NULL;
END IF;

   balance := (SELECT COALESCE(SUM(d.amount),0)
                    + COALESCE(SUM(acct.amount),0)
                    - COALESCE(SUM(accf.amount),0)
   FROM account a
   LEFT OUTER JOIN deposit d ON a.id = d.account
   LEFT OUTER JOIN bank_transaction acct ON a.id = acct.account_to
   LEFT OUTER JOIN bank_transaction accf ON a.id = acct.account_from
   WHERE a.id = NEW.account_from
   GROUP BY a.id);

  IF NEW.amount <= @balance THEN
       RETURN NEW;
   END IF;
   RETURN NULL;
END;
$is_balance_enough$ LANGUAGE PLPGSQL;

CREATE OR REPLACE TRIGGER is_balance_enough BEFORE INSERT ON bank_transaction
FOR EACH ROW EXECUTE FUNCTION is_balance_enough();

This wraps our database; next, we can run some pgbench; a bank will surely have concurrent users making transactions and deposits, let's try 100 concurrent clients for deposits, and then transactions, for 60 seconds each.

The pgbench options are:

  • -c 100: 100 concurrent clients;

  • -T 60: Run this test for 60 seconds;

  • -j 4: Run this with 4 jobs;

  • -M prepared: Use prepared statements;

  • –no-vacuum: Don't vacuum before test; used because we have a custom test scenario

  • Deposits

    $pgbench -d postgres  -U postgres -h localhost -p 5432 -c 100 -T 60  -j 4 -f deposit_bench.sql  --no-vacuum -M prepared
pgbench (17.5, server 18.1 (Debian 18.1-1.pgdg12+2))
transaction type: deposit_bench.sql
scaling factor: 1
query mode: prepared
number of clients: 100
number of threads: 4
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 617018
number of failed transactions: 0 (0.000%)
latency average = 9.674 ms
initial connection time = 402.001 ms
tps = 10337.219585 (without initial connection time)

10k tps! Of course, nowhere near 4M tps, but that was also on a huge machine. Let's think in other terms: how much is 10k tps? Here, Nubank claimed to have processed 1 billion Pix (a pix transaction is basically a transfer between two bank accounts, so, the transaction scenario here looks more like it) transactions per month in 2022; we can estimate that to around 385 tps. So, that's a good number, for deposits!

  • Transactions

    The next test is for performing transactions, in other words, sending money from one account to another. This is the most interesting one, as this always executes the trigger we defined; because of that, we are sure that any transaction is valid (no transaction can be performed that will cause an account to have negative balance):

    $pgbench -d postgres  -U postgres -h localhost -p 5432 -c 100 -T 60  -j 4 -f transaction_bench.sql  --no-vacuum -M prepared
pgbench (17.5, server 18.1 (Debian 18.1-1.pgdg12+2))
transaction type: transaction_bench.sql
scaling factor: 1
query mode: prepared
number of clients: 100
number of threads: 4
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 25993
number of failed transactions: 0 (0.000%)
latency average = 231.174 ms
initial connection time = 166.193 ms
tps = 432.574215 (without initial connection time)

432 tps! This one was a bit harder, but still, we would be able to process around the same amount of transactions as Nubank! And that's on my PC! Of course, there are much more things involved here; but it's cool to see that you could do quite a lot with a Postgres on a good enough machine.

And yes, this is a toy project, but the mechanism used here shows the essential parts of a simple payment system. Using only Postgres with minimal performance enhancements, we would already be able to achieve a TPS large enough to hold quite the number of users, so, it's unlikely that the database would be an issue here.

My PC specs are:

  • CPU: Intel(R) Core(TM) i5-8350U (4) @ 3.60 GHz
  • GPU: Intel UHD Graphics 620 @ 1.10 GHz [Integrated]
  • Memory: 26.19 GiB / 62.72 GiB (42%)
  • Swap: Disabled
  • Disk (/): 201.81 GiB / 1.83 TiB (11%) - ext4