101–110 of 178 posts
One drawback I can think of is it would be difficult to create reports, cross customer.
> “But nobody writes production applications with SQLite, right?" We've been doing it for 5 years now. Basic tricks we employ are: Use PRAGMA user_version for purposes of managing automatic migrations, a. la. Entity Framework. This means you can actually do one better than Microsoft's approach, because you don't need a special unicorn table to store migration info. A simple integer compared with your latest integer a…
Out of curiosity, what kind of read and write concurrency is your application dealing with? In my experience, sqlite performance becomes problematic quite quickly, even with settings you mentioned (WAL etc).
I should amend my original post, because I know a lot of developers fall into the trap of thinking that you should always do the open/close connection pattern with these databases. That is a huge trap with SQLite. If you want to add some extra zeroes to your benchmark figures, only use a single connection for accessing SQLite databases. Use application-level locking primitives, rather than relying on the database for purposes of getting consistent output from things like LastInsertRowId and in cases where transactional scopes are otherwise required. This alone can take you from 100 inserts/second to 10k without changing anything else.
The article mentions saas. So instead of a single multi tenant db, you might have a sqlite for each customer? Maybe even copy it down to the client? I have a idea for a media cms, wondering if this design would fit. One drawback I can think of is it would be difficult to create reports, cross customer.
Reports are nice because they can generally be done in an async job that's effectively a big map-reduce run across your DBs. If you need faster real-time reports you're going to want some kind of pipeline for events and stream processing that's outside the scope of this anyways.
Regarding SQLite's performance, some things I've found very useful: Use WAL mode (writers don't block readers): PRAGMA journal_mode = 'WAL' Use memory as temporary storage: PRAGMA temp_store = 2 Faster synchronization that still keeps the data safe: PRAGMA synchronous = 1 Increase cache size (in this case to 64MB), the default is 2MB PRAGMA cache_size = -64000 Lastly, use a modern version of SQLite. Many default inst…
Not a great choice of API IMHO, but it’s a database, I’ve seen much worse.
Things are not quite that simple. You can't say "because my Go app serves a single request under no load in 50us, it will serve 20'000 per core under 100% load" you'd be surprised it will not. Modern machines are like a networked cluster themselves. You need to do a ton of work to tune both kernel and "hardware" parameters to identify bottlenecks with near-non-existing debugging tools. There is one truth here: we use…
That's a fair point. I've updated the post to read, "That translates to thousands of requests per second per core" instead of saying it's linear scaling. Thanks for the feedback!
Do you have some benchmark results by any chance? Although it’s a bit of a can of worms, and I would understand if you didn’t want to get into it at this time.
But what's the endgame of this approach regarding scaling? If I ever need horizontal scaling, am I screwed? Genuinely curious :)
Basically the argument is: do it yourself, and time spent learning how to do it effectively is compensated by the time not spent fighting over complexity caused by operating (and understanding how to use properly) stuff that allegedly "does it for you".
The argument sounds plausible in principle, but I'm not entirely sure if this argument resist the harsh clash with reality (where you end up with both doing it yourself and dealing with the effects of your own complexity but this time with no community to consult)
> “But nobody writes production applications with SQLite, right?" We've been doing it for 5 years now. Basic tricks we employ are: Use PRAGMA user_version for purposes of managing automatic migrations, a. la. Entity Framework. This means you can actually do one better than Microsoft's approach, because you don't need a special unicorn table to store migration info. A simple integer compared with your latest integer a…
>We can afford to lose the last few minutes of work without anyone getting yelled at. Some modern virtualization technologies do help a lot in this regard. Running bare metal you need to be a little more careful. How does virtualization help with data loss? I would expect that a VM can't have guarantees better than the underlying physical hardware provides.