If you're running on a single machine then you'll get way more performance with something like sqlite (instead of postgres/MySQL) which also makes managing the database quite trivial.

▲

immibis 6 days ago | parent | next [-]

SQLite has serious concurrency concerns which have to be evaluated. You should consider running postgres or mysql/mariadb even if it's on the same server.

SQLite uses one reader/writer lock over the whole database. When any thread is writing the database, no other thread is reading it. If one thread is waiting to write, new reads can't begin. Additionally, every read transaction starts by checking if the database has changed since last time, and then re-loading a bunch of caches.

This is suitable for SQLite's intended use case. It's most likely not suitable for a server with 256 hardware threads and a 50Gbps network card. You need proper transaction and concurrency control for heavy workloads.

Additionally, SQLite lacks a bunch of integrity checks, like data types and various kinds of constraints. And things like materialised views, etc.

SQLite is lite. Use it for lite things, not hevy things.

▲

andersmurphy 5 days ago | parent | next [-]

Not sure what you are talking about? In WAL mode (which is what you should be using) writes don't block reads and reads don't block writes. If you are connections pooling (which you should) the cache will stay hot.

Sqlite (properly configured) will outperform "proper databases" often by an order of magnitude in the context of a single box. You want a single writer for high performance as it lets you batch.

> 256 hardware threads...

Have you tried? I have. Others have too. [1]

> Additionally, SQLite lacks a bunch of integrity checks, like data types and various kinds of constraints. And things like materialised views, etc.

Sqlite has blobs so you can use your own custom encoding which is what you want in a high performance context.

Here's sqlite on a 5$ shared VPS that can handle 10000+ checks per second over a billion checkboxes [2]. You're gonna be fine.

- [1] https://use.expensify.com/blog/scaling-sqlite-to-4m-qps-on-a...

- [2] https://checkboxes.andersmurphy.com

▲

hruk 6 days ago | parent | prev | next [-]

Agree on many things here, but SQLite does support WAL mode which supports 1 writer/N writer readers with snapshot isolation on reads. Writes are serialized but still quite fast.

SQLite (actually SQL-ite, like a mineral) maybe be light, but so are many workloads these days. Even 1000 queries per second is quite doable with SQLite and modest hardware, and I've worked at billion dollar businesses handling fewer queries than that.

▲

wild_egg 5 days ago | parent | prev | next [-]

You know, it's ok to say that you're out of your element and don't have direct experience with the thing you're commenting on.

SQLite is easily the best scaling DB tech I've used. I've moved all my postgres workloads over to it and the gains have been incredible.

It's not a panacea and not the best in all cases but it's a very sane default that I recommend everyone start with and only complicate their stack with an external DB when they they start hitting real limits (often never happens)

▲

immibis 5 days ago | parent [-]

> You know, it's ok to say that you're out of your element and don't have direct experience with the thing you're commenting on.

I moved several projects from sqlite to postgres because sqlite didn't scale enough for any of them.

▲

andersmurphy 5 days ago | parent [-]

May I suggest you could have been holding it wrong?

The out of the box defaults for sqlite are terrible for web apps.

	▲	immibis 4 days ago \| parent [-]
		Are you aware of the irony behind saying "You're holding it wrong"? Do you know where that phrase came from?

▲

Rohansi 6 days ago | parent | prev [-]

Is any SQL database suitable for 50GBps of network traffic hitting it?

Most if not all of your concerns with SQLite are simply a matter of not using the default configuration. Enable WAL mode, enable strict mode, etc. and it's a lot better.

▲

rixed 6 days ago | parent | prev [-]

If you have a single request at a time and need little integrity checks.