I am using SQLite on paperless-ngx (an app to manage pdf [4]).
It is quite difficult to beat SQLite if you do not have a very huge parallelism factor in writes.
SQLite is an embedded database: no socket to open, you directly access to it via file system.
If you do not plan to use BigData with high number of writers, you will have an hard time beating SQLite on modern hardware, on average use cases.
I have written a super simple search engine [1] using python asyncio and SQLite is not the bottleneck so far.
If you are hitting the SQLite limit, I have an happy news: PostgreSQL upgrade will be enough for a lot of use cases [2]: you can use it to play with a schemaless mongo-like database, a simple queue system [3] or a search engine with stemming. After a while you can decide if you need a specialized component (i.e. Kafka, Elastic Search, etc) for one of your services.
The pattern I like to advocate for now is to do customer sharding with SQLite. Cloudflare makes this easy with D1, you can tie Durable Objects to a user as an afterthought.
The nice thing about this pattern is that you can create foreign data wrappers for your customer SQLite databases and query them as if they were in postgres, cross customer aggregations are slow but individual customer analytics are quite fast, and this gives you near infinite scalability.
You hit those write limits surprisingly early if you use background workers though. I had a project with very little user traffic that choked on SQLite simply because a few Celery workers were updating job statuses concurrently. It wasn't the volume of data, just the contention from the workers that forced the switch to Postgres.
Are you sure it is choked on writes not on reads and writes? SQLite default setup is inefficient in many ways (as well as it's default compilation options), and that often cause issues.
- 1,600 sequential (in a single process) read-after-write transactions, append-only, no batching.
- With a separate writer process (sequential), and concurrently, two reader processes, I'm seeing 400+ append transactions/second (into the append-only table, no batching), and a total of 41,000 reads per second, doing `select *` on the trigger-updated table.
My schema is (deliberately) poor --- most of it is TEXT.
It employs "flexible typing", which does not mean "everything is text". What I am doing is writing fully denormalised text (strings) in most fields, with column type declared as TEXT.
This is deliberate, to emulate "whoops, if I screw up my types, how bad does it get?".
However, when written into the DB with some care, each value is stored per the following storage classes:
Each value stored in an SQLite database (or manipulated by the database engine) has one of the following storage classes:
NULL. The value is a NULL value.
INTEGER. The value is a signed integer, stored in 0, 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
BLOB. The value is a blob of data, stored exactly as it was input.
A storage class is more general than a datatype. The INTEGER storage class, for example, includes 7 different integer datatypes of different lengths. This makes a difference on disk. But as soon as INTEGER values are read off of disk and into memory for processing, they are converted to the most general datatype (8-byte signed integer). And so for the most part, "storage class" is indistinguishable from "datatype" and the two terms can be used interchangeably.
Any column in an SQLite version 3 database, except an INTEGER PRIMARY KEY column, may be used to store a value of any storage class.
All values in SQL statements, whether they are literals embedded in SQL statement text or parameters bound to precompiled SQL statements have an implicit storage class. Under circumstances described below, the database engine may convert values between numeric storage classes (INTEGER and REAL) and TEXT during query execution.
```
(edits: formatting, clarify what I'm doing v/s what SQLite does)
I recently chose Postgres over SQLite for a project, but only after taking SQLite as far as I comfortably could. I found I pretty much immediately ran into issues with how to run migrations in my remote environment, since it wasn’t network accessible. I also wasn’t easily able to connect a db manager for admin tasks.
My data scale is quite small (hundreds on mb), so you’d think SQLite would be perfect but Postgres really was just a lot simpler to spin up in a docker container and the performance difference in a 2G VPS is not noticeable. I’m sure the above issues were solvable but it was easier for me to just use Postgres and move on.
On paper a 386sx is slower than a 386dx, and certainly is in terms of RAM access. But in practice you'd need some expensive hardware to fully take advantage of that speed, like EISA cards and a motherboard that supported them (or, MCA cards on one of the higher end IBM PS/2 models). The typical ISA cards of the era were limited to 8 MHz and 16 bits no matter what processor or motherboard you used.
The 386dx could also use a full 32-bit address space, whereas the 386sx had 24 address lines like the 286. But again, having more than 16 MB would have been expensive at the time.
> Windows 95 on a 386 CPU with enough RAM was alright.
I benchmarked it for PC Pro Magazine when it came out.
We had to borrow a 4MB 386SX from a friend of the editor's, as we had nothing that low-end left in the labs.
In our standard benchmarks, which used MS Word, MS Excel, PowerPoint, Illustrator, Photoshop, WinZip, and a few other real apps, Win95 1.0, not 95A or OSR2, was measurable faster than Windows for Workgroups 3.11 on MS-DOS 6.22, hand-optimised.
When it needed the RAM, 95 could shrink the disk cache to essentially nothing. (Maybe 4 kB or something.) Win3 could not do that.
It was SLOW but under heavy load it was quicker than Win3 on the lowest-end supported hardware.
Under light load, Win3 was quicker, but Win95 scaled down very impressively indeed.
You are ready for misterio: https://github.com/daitangio/misterio
A tiny layer around stareless docker cluster.
I created it for my homelab and it gone wild
Self-hosting is becoming a freedom factor in my humble opinion.
I have an hard time hosting my email server, it was not so diffcult 10 years ago and was trivial 20 years ago.
The reason is the anti-spam rules and the fact that Google, Microsoft and so on are creating a iron trust to each other, and the little server outside are marked spam by default.
Lets encrypt avoided a similar destiny to https connections, but the risk is always out of the window.
I mean, https was becoming "pay-us-to-publish a web server, or our browser will mark you as unsafe and do not display it".
I think it is time also to self-host private free chats and possibly other services lik DDoS services.
I keep using isso https://isso-comments.de/
I installed it on my static blog very easily, and I own all the data.
Also it is GDPR-compliant (because it provide hints on how to remove data like IPs) and it is very light.
For me Disqus and similia are a dead end.
