My Long Way To PostgreSQL

November 28, 2022

Actually, the project began on my laptop. I can't tell you what the project, I'd have to kill you then. I can only tell that there's a simple table in the DB, with approx. 4M records. These records are related, many to many, and the number of relations varies from zero to 100K, with 500 in the 90th percentile.

So, it began with SQLite, via aiosqlite. But soon, when since number of records reached 1M, the DB was getting lame. I did not investigate why, the project itself consumed enough CPU time so my gut feeling was to move the DB to a server. But as long as the DB was pretty simple, just more or less big, I did not think I needed any fancy hardware. So I ran it on my NanoPI-3 with 2GB RAM and spinning SATA disk, attached via USB3-SATA adapter.


But, what DB to choose? My first SQL database was Oracle. Yet 7.3. And a little bit of everything since then: Firebird, MySQL, SQLite, MongoDB. Although, MongoDB was the main database at my job over the last decade. So, what to start from? I chose...


I haven't had any single good thought since I started porting my DB module from SQLite to MySQL. The way parameters are passed is awful. I glanced at the source code of asyncmy Mogrify! LOL! This means no ways to very basic optimizations, the server would parse each statement over and over again. Two decades have gone and they did nothing with this. Information Technology is a rapidly changing area, yeah.

Things went worse when I tried to run my code. When I imported the data, the annoying problem was

asyncmy.errors.OperationalError: (2013, 'Lost connection to MySQL server during query ([Errno 104] Connection reset by peer)')

For the main code, for every function that worked with the DB I had to use such a shit:

async def _retry_wrapper(coro, *args, **kwargs):
    while True:
            return await coro(*args, **kwargs)
        except asyncmy.errors.OperationalError:
            tb = traceback.format_exc()
            if 'Lock wait timeout exceeded' in tb or 'Deadlock' in tb:

Another shit I stepped into was transfer of a whole data set to the client. Although I quickly found SSCursor, that fact means MySQL has no more or less big data in mind.

Wait, what "big data"? SQLite database was only 200M before migration. MySQL database took 547M of disk space, and I suppose that's just because of multi-byte encoding, that's okay. However, all this data would easily fit in 2GB RAM. Anyway, not using SSCursor by default is bad, IMAO.

Basically, migration to MySQL went well. The logic of API is slightly different. Not a big deal, either. I replaced INTEGER with TINYINT, INT, and BIGINT as appropriate, in one place I had to use VARCHAR(255) instead of text TEXT, where it was involved in a primary key.

Uniquiness constraint is stricter in MySQL and spotted a couple of bugs: 1) un-stripped values, and 2) null values where they should not be. Also, table names are case sensitive in MySQL. Involuntarily, I used MyISAM. Although default engine is InnoDB for years as I know, for some reason MyISAM was used. I created tables from python code. I have no idea why that happened, I don't care. Didn't dig into it.

After running my shit for a while, MySQL stuck with very low CPU load and 100% disk utilization. Most time was spent on updating link table that held many to many relations. By that time the DB took 5.4GB of disk space. The main table had 3M records and the link table had 30M records. I exported the data to JSON with my script and the result was only 1.1GB. Still could fit in RAM on my NanoPI-3.


MongoDB... I had rich experience with it, and I could even say it was one of my favorite databases, but as many things on the Earth it's nice and ugly at the same time.

Fun started when I ran mongod on NanoPI-3 and it said "Illegal instruction". WTF? My ARM64 is different from their ARM64? Well, I compiled it on my Rockchip-based board. Not on NanoPI-3, its 2GB RAM was insufficient, I compiled it on NanoPI M4 with 4GB RAM. Anyway, I had to add 8GB swap. Approximately 500M was used during compilation and 4GB during linking. Yuck! Either MongoDB is really a big piece of shit or modern compilers are too lame to run on modern tiny boards.

So I wasted 30hrs and the problem has not gone. Still "Illegal instruction". After searching around I found that modern MongoDB needs modern CPU instructions, both on ARM64 and x86-64. The latest version which worked for me was 4.4. Okay, I didn't need anything extraordinary, so 4.4 was okay.

The import went well. Insertion rate was 280 docs per second with 25% CPU load and 30% disk utilization. That looked nicely balanced. In the end the DB took 470M of disk space (compression rocks!), and the journal was 300M. After creating indexes the size of DB increased to 790M.

I was depraved by document-oriented NoSQL so I did not use link table, I stored relations in the document. My shit worked well. Not so long, though. CPU load displayed by top jumped to 375% (RK3328 on NanoPI-3 board has four cores) and every 2 seconds the following was logged:

{"t":{"$date":"2022-11-07T02:50:34.296+00:00"},"s":"I", "c":"STORAGE", "id":22430, "ctx":"conn6610",
 "msg":"WiredTiger message","attr":{"message":"oldest pinned transaction ID rolled back for eviction"}}

Do you know what's that? I do, unfortunately. That's the smelliest shit in MongoDB. That's a sign of the famous cache eviction problem we (and not only us) encountered when migrated from version 2 to version 3 where WiredTiger storage engine became default one. That halted all the system. We had to revert to MMAPv1 and we did not try to deploy WiredTiger in production anymore. Although, we did use WiredTiger, for secondary replicas only, and for archive database. But NEVER, never for the primary one.

For small personal things I did use WiredTiger as well, but that were really small one off things. Very light load. Not even mid, not even mentioning the high ones. I don't know, maybe they have fixed this issue in their Atlas or Enterprise Advanced long ago, but I'd be very cautious even with the latest versions of the community server. Although,... maybe modern CPU instructions were a key and I'm just stuck in time.

From a quick search I found only one ticket from 2020 (2 years ago as of time of writing) where they were only concerned how to handle such a message in their tests and resolved that as nohow.

Back to my shit, I left it running, went to bed, and stopped it only in morning. It barfed a bunch of the following entries to the log:

{"t":{"$date":"2022-10-07T13:32:25.108+00:00"},"s":"I", "c":"COMMAND", "id":51800, "ctx":"conn6610",
 "msg":"Slow query","attr":{"type":"command","ns":"mydb.maindata","command":{"findAndModify":"maindata",
 "errMsg":"Plan executor error during findAndModify :: caused by :: interrupted at shutdown",

The most fancy thing in the above is durationMillis: 33810024. Shit. I had to bury MongoDB. But what's next? Back to SQLite in a client-server way with some wrapper? That looked funny and I was about to do that, but I had a thought: why not PostgreSQL? I never worked with it, and here it is, a chance.


My initial thought was to use JSON as in MongoDB, but thanks to array fields and GIN indexes I did without that. The import made me feeling sad: CPU load 13-16%, disk utilization 80-100%, insertion rate: 83 records per second. Yes, I could try to optimize by turning autocommit off and committing 10-20 records at once, but MongoDB was much faster with default settings. Anyway, it did not make sense for one off task.

So what can I say about PostgreSQL in action? Nothing! It works like a charm! My shit causes 20% CPU load and 30% disk utilization.

That's all.