Outline

Introduction

Recently on hackernews, I came across this article^[1], and got landed on this github gist^[2] that made me wonder if postgres was actually a viable alternative to say, H2 database which is an in-memory database that I use to cache my data.

I had to depend on an in-memory database to make my tic-tac-toe^[3] game as the state updates had to be made in a quick manner compared to what it took to write to the database in the disk. Nobody wants a slow game, right?

The main idea is that you could greatly speed up your database write performance by altering the table to be unlogged. There are disadvantages to it as well, you could spend a bit of time reading about it here ^[4], but for a quick overview - you lose the data if your postgres process crashes.

Not so much of a problem for a temporary caching service right? So I looked up online for a way to actually compare the performance compared to an in-memory storage [H2 for me, in this case] and I couldn't find anything that was informative.

I took upon myself to insert and calculate the performance between them so you don't have to!

Testing Setup

I already have a h2 database table available for me to use, so I created a table in postgres with the same schema.

    H2 DATABASE 
    ===========
    CREATE TABLE IF NOT EXISTS tic_tac_toe_game
    (
        id                    BIGINT PRIMARY KEY auto_increment,
        player_one_session_id VARCHAR(255),
        player_one_stomp_id   VARCHAR(255),
        player_two_session_id VARCHAR(255),
        player_two_stomp_id   VARCHAR(255),
        game_state            VARCHAR(10),
        created               VARCHAR(50),
        room_code             VARCHAR(10),
        who_plays             VARCHAR(10),
        won                   BOOLEAN,
        play_again            BOOLEAN,
        who_asked             VARCHAR(10)
    ); 

    POSTGRES DATABASE
    =================
    
    CREATE UNLOGGED TABLE IF NOT EXISTS tic_tac_toe_game
    (
        id                    BIGSERIAL PRIMARY KEY,
        player_one_session_id VARCHAR(255),
        player_one_stomp_id   VARCHAR(255),
        player_two_session_id VARCHAR(255),
        player_two_stomp_id   VARCHAR(255),
        game_state            VARCHAR(10),
        created               VARCHAR(50),
        room_code             VARCHAR(10),
        who_plays             VARCHAR(10),
        won                   BOOLEAN,
        play_again            BOOLEAN,
        who_asked             VARCHAR(10)
    ); 

The java code for inserting goes like this:

long totalTime = 0;
for(int i = 0; i < 1_000_000; i++) {
    TicTacToeGame tic = new TicTacToeGame(
        ...
    )
    
    long curr = System.currentTimeMillis();
    ticTacToeGameRepository.save(tic);
    long time = System.currentTimeMillis() - curr;
    
    totalTime += time;
}
System.out.println("Total time to insert 1_000_000 rows: " + totalTime);

I re-ran this code twice for both the databases and took the average time to insert 1_000_000 rows.

Results

               
+----------+-----------------------------+---------------------+-------------------------+
|  trail   | postgres (logged - default) | postgres (unlogged) | h2 database (in-memory) |
+----------+-----------------------------+---------------------+-------------------------+
| 1        | 433766 ms                   | 258341 ms           | 108814 ms               |
| 2        | 474333 ms                   | 259641 ms           | 107035 ms               |
| -------- | --------------------------- | ------------------  | ----------------------- |
| Average  | 454049.5 ms                 | 258991 ms           | 107924.5 ms             |
+----------+-----------------------------+---------------------+-------------------------+

• It took 7.56 minutes to insert 1_000_000 rows in a default postgres table.
• It took 4.31 minutes to insert 1_000_000 rows in an unlogged postgres table.
• It took 1.79 minutes to insert 1_000_000 rows in an in-memory h2 database.
• The unlogged postgres table was 43% faster compared to the logged postgres table.
• Using an in-memory database was 76% faster compared to the logged postgres table and 58% faster compared to the unlogged postgres table.

Conclusion

Use unlogged tables in postgres if you are looking for a quick way to cache your data. It is faster compared to a default postgres table but be mindful of its disadvantages.

If performance is a concern, then nothing can beat an in-memory database.

notes:

1. https://www.amazingcto.com/postgres-for-everything/
2. https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f06dbb#nosql
3. https://alles-tools.com/tic-tac-toe
4. https://www.crunchydata.com/blog/postgresl-unlogged-tables