51–60 of 205 posts
Earlier quoted context omitted.
something something XKCD competing standards something something
Believe me, I tried sticking to SQLite or aard2 or stardict, they just were fundamentally inadequate with no good pwa cross platform tooling.
Earlier quoted context omitted.
Believe me, I tried sticking to SQLite or aard2 or stardict, they just were fundamentally inadequate with no good pwa cross platform tooling.
Does this remain true now that SQLite has a WASM build?
Earlier quoted context omitted.
The question is, do the same firms ban Excel? Excel spreadsheets often end up as shadow databases in unlikely places.
Do companies ban text files? Text files are used to store data.
I went from thinking “SQLite is a toy product, not reliable for real data" to "lets use SQLite for almost everything" SQLite is very good if you can fit into the single writer, multiple readers pattern; you'll never lose data if you use the correct settings, which takes a minute of Google search to figure out. Today, most of my apps are simply go binary + SQLite + systemd service file. I've yet to lose data. Performa…
The single writer is less of an issue in practice than it's made out to be. Modern nvme drives are incredible and it's trivial to get 5k writes per second in an optimized WAL setup. Way more than most apps could ever dream. And even then, I've used a batch writer pattern to get 180k writes per second on a commodity vps.
"Batch writer pattern" is a good idea to get rid of expensive commits.
I went from thinking “SQLite is a toy product, not reliable for real data" to "lets use SQLite for almost everything" SQLite is very good if you can fit into the single writer, multiple readers pattern; you'll never lose data if you use the correct settings, which takes a minute of Google search to figure out. Today, most of my apps are simply go binary + SQLite + systemd service file. I've yet to lose data. Performa…
The single writer is less of an issue in practice than it's made out to be. Modern nvme drives are incredible and it's trivial to get 5k writes per second in an optimized WAL setup. Way more than most apps could ever dream. And even then, I've used a batch writer pattern to get 180k writes per second on a commodity vps.
ex: main.db + fts.db. reading and writing to main.db is always available; updating the fts index can be done without blocking the main database — it only needs to read, the reads can be chunked, and delayed. fts.db keeps the index + a cursor table — an id or last change ts
could also use a shard to handle tables for metrics, or simply move old data out of main.db
* some examples:
conn = sqlite3.connect("data.db")
conn.execute("PRAGMA journal_mode=WAL") # concurrent reads (see above)
conn.execute("PRAGMA synchronous=NORMAL") # fsync at checkpoint, not every commit
conn.execute("PRAGMA cache_size=-62500") # ~61 MB page cache (negative = KB)
conn.execute("PRAGMA temp_store=MEMORY") # temp tables and indexes in RAM
conn.execute("PRAGMA busy_timeout=5000") # wait 5s on lock instead of failing
my replies are being ratelimited, so let me add this
the heavy duty server other databases have is doing that load bearing work that folks tend to complain about sqlite can't do
the real dmbs's are doing mostly the same work that sqlite does, you just don't have to think about it once they're set up. behind that chunky server process the database is still dealing with writing your data to a filesystem, handling transaction locks, etc.
by default sqlite gives you a stable database file, that when you see the transaction complete, it means the changes have been committed to storage, and cannot be lost if the machine were to crash exactly after that.
you can decide to wave some, or all of those guaranties in exchange for performance, and this doesn't even have to be an all or nothing situation.
Earlier quoted context omitted.
At least would take it with a grain of salt when the DBA wants you to depend more on the DBA.
Same with devops tbh. "Hey everyone, we need to chose the option that involves us the most and provides us the most job security"
Earlier quoted context omitted.
The single writer is less of an issue in practice than it's made out to be. Modern nvme drives are incredible and it's trivial to get 5k writes per second in an optimized WAL setup. Way more than most apps could ever dream. And even then, I've used a batch writer pattern to get 180k writes per second on a commodity vps.
all* of that + sharding -> https://sqlite.org/lang_attach.html ex: main.db + fts.db. reading and writing to main.db is always available; updating the fts index can be done without blocking the main database — it only needs to read, the reads can be chunked, and delayed. fts.db keeps the index + a cursor table — an id or last change ts could also use a shard to handle tables for metrics, or simply move old data out of…
https://the-php-bench.technex.us/
There's a huge performance difference between memory and file storage within sqlite itself. Not even getting into tuning specifics.
Earlier quoted context omitted.
> I wish exFAT would die in a fire and a journaling filesystem would replace it as the "one filesystem you can use everywhere" Where exactly is everywhere? Win32? All of Linux? BSDs? MacOS? IOS? ...
Something MacOS and Windows support natively would be a good start, it could grow from there.