191–200 of 330 posts
Side question: what’s something as simple as SQLite, but more of an unstructured key-value store? I’ve been using (locally) a Redis container for a very early prototype because it seems to be simple enough to use. I know you can query json strings in salute but that’s not quite the same thing. For one redis offers some geo features.
SQLite may shine in edge cases where you know you can outperform a regular database server and you know why, and you could build everything either way. SQLite could be a way to e.g. decentralize state, using local instances to do local storage and compute before shipping off or coordinating elsewhere.
Otherwise, SQLite can simply be a recipe for lots of lock errors on concurrent operations. I've also never been very impressed with its performance as a general purpose replacement for postgres or MySQL.
I'm using SQLite for a small personal project that's live in production and so far I love it (both for its simplicity in development and for its performance). But I've run into on prod that didn't exist in dev on my MacBook M1, and I'm curious if anyone has any suggestions: My app is basically quiet and serves requests in the dozens (super easy to run on a tiny instance), but for a few hours a day it needs to run sev…
Interesting problem. Did you try grouping up transactions? Ex instead of a few hundred million txns, do a few hundred thousand 1000op chunk txns. SQLite is much much faster within a txn. Edit: a several hundred million txns over a few hours math. How many per second? ~500 According to here (question 19), for old HDDs you could expect 3 orders of magnitude improvement by using bigger txns. Not sure SSD wise but worth…
I'm using SQLite for a small personal project that's live in production and so far I love it (both for its simplicity in development and for its performance). But I've run into on prod that didn't exist in dev on my MacBook M1, and I'm curious if anyone has any suggestions: My app is basically quiet and serves requests in the dozens (super easy to run on a tiny instance), but for a few hours a day it needs to run sev…
Maybe `BEGIN CONCURRENT` [1] could help in your case? :thinking: [1] https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begi...
Many of the replies here attest to the simplicity and fast performance of SQLite particularly for serving pages or data. But how well does SQLite fare in concurrent write/insert situations? Although SQLite is not designed for this type of scenario, this discussion higlights there's a strong demand for a concurrent client/server RDMS that is simple, performant and easy to deploy. PostgreSQL is powerful and feature-ric…
It's pretty well-known that concurrent writes are SQLites weak point, and that if your application requires high numbers of writes, that it's not the proper solution.
The SQLite devs even acknowledge this:
> SQLite will normally work fine as the database backend to a website. But if the website is write-intensive or is so busy that it requires multiple servers, then consider using an enterprise-class client/server database engine instead of SQLite.
> [...] client/server database systems, because they have a long-running server process at hand to coordinate access, can usually handle far more write concurrency than SQLite ever will.
..but like all things, it depends on your needs. Some have already pointed out the pages on SQLite's on site regarding # of writers (the main issue), etc.
Earlier quoted context omitted.
- Setting up a MySQL server on both your dev machine and server, and making sure they're the same version (extra fun if they're on different OS versions) - Setting up an out-of-repo config file on the server with your MySQL credentials - Setting up a backup script for your server data It's only about an hour of work total, but it's an hour of work that I hate doing.
So you trade for some risk for an hour.
One of my previous employers was using SQLite as a large distributed database - they had their own custom sharding strategy, but essentially the idea was to shard A accounts * B tables * C num_of_days with a .db file for every shard. When I first came and saw it, it...did not sound right. But I didn't want to be the guy who comes in and says "you are doing it wrong" month 1. So I went along with it. Of course, eventu…
Sadly, we had to move to Postgres and eat all that scaling complexity. :(
Has anyone ever used multiple SQLite databases per tenant/account? For sake of argument, let's say I have a fixed schema/format that will never change and I never need to aggregate queries across multiple customer accounts. Also, let's say writes to a single database are never going to be more than a hundred concurrent users. Why shouldn't I store each tenant's data in its own SQLite database? It makes it very easy f…
When would this happen for any non-trivial multi-tenant service? The difficultly of performing migrations sounds like it would pretty quickly negate any simplicity gained.