Re:At 22 years old, Postgres might just be the most advanced...
by rolleiflex on Wednesday December 05, 2018 @04:42PM
(#18612896)
The gist I linked is extremely basic, just insert and read, really, and it does use WAL by default. It'll still end up with database lock, and from there it's all shallow waters — reverting from a crashed database lock is not trivial and can cause data loss if handled improperly.
Not all writes are couple milliseconds. I regularly have transactions that take 15< seconds, and it's not because the data size that's being input is large, it's just that the SQL is recursive and complex enough to warrant recursion (storing graph data).
I don't know...it's recently added upsert compatible with Postgres syntax as well as support for window functions.
Additionally, sqlite has a sophisticated json extension.
> it can only ever do one write or read to the database predictably
WAL mode addresses this, allowing an arbitrary number of readers with a writer.
For concurrent applications just use a dedicated write thread and enqueue writes...your example is contrived. Connection management, transaction scope management, etc, can all work in your favor. Writes occur in a few milliseconds, meaning you only need to hold the exclusive lock very briefly in most situations.
More information here: http://charlesleifer.com/blog/going-fast-with-sqlite-and-pyt...