[richardc323]

For reasons I wont go into here, I built a system with a similar approach 10 years ago. The system was horizontally scaleable. There was no database tier, instead each server had a replica of the database locally which were used for reads. The servers discovered each other other and nominated one server as the master, which writes were sent to. Replication was done by having the master sending the DML queries to a writer process on each server. When a new server joined the cluster it was sent a copy of the entire database and a stream of queries for it to catch up before it joined the cluster. There were other tricks to make sure reads from replicas waited until the replicas were sufficiently up to date.

It worked fine as the system was read heavy and write light. SQLite serialises writes so does not perform well with multiple writers, particularly if the write transactions are long running. Reads were blazingly fast as there was no round-trips across the network to a separate database tier. The plan for dealing with performance problems if/when they arrived was to shard the servers into groups of customers.

I moved on and the next developer ripped it out and replaced it with Postgres because it was such an oddball system. I came back six months later to fix the mess as the new developer messed up transactions with the new database code.

Technically using SQLite with replication tacked on works fine. Superficially it is all the same because it is SQL. However the performance characteristics are very different from a conventional Multi Version Concurrency Control databases such as Postgres.

This is where the problem lies with this kind of database - developers seeing SQL and assuming they can develop exactly the same way they would with other SQL databases. That said I love approaches that get away from the database architectures of last century.

[kumarvvr]

As a Postgres enthusiast, I am really curious to know how the new developer messed up the transactions.

Could you please elaborate?