Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQLite on Rails: The how and why of optimal performance

fractaledmind.github.io

Post reply on HN
Print view View original

71–80 of 98 posts

Re: SQLite on Rails: The how and why of optimal performance

#71

Post by snovv_crash »

petcat wrote: post #69
tptacek wrote: post #68

Earlier quoted context omitted.

No, you're seeing a surge in interest for SQLite because people like relational databases, but the n-tier architecture is sometimes not the right solution for the problems people have. And again: many of your arguments have been applied to MySQL, but nobody can with a straight face say it's not a "real" backend database. (To a first approximation ~nobody is interested in SQLite because it lacks correctness or rigid t…

I think that most applications are written for their database. Their database defines their application. If you write your application on a flimsy database then your application becomes equally flimsy. All of your business constraints become flimsy because your source-of-truth (the database) is flimsy. SQLite is flimsy by design.

Au contraire, SQLite makes it very easy to write extensive automated testing for your application, since you can spin up in-memory DBs per test with minimal overhead. This makes your application much more robust.

Re: SQLite on Rails: The how and why of optimal performance

#72

Post by rbranson »

nikisweeting wrote: post #52
rbranson wrote: post #41

Earlier quoted context omitted.

Yeah, this almost never happens in practice. It’s not even worth being concerned about.

Happened all the time to me before I did some tuning Depends how much write contention you have and how long open transactions take to finish.

The list of circumstances for WAL-mode busy errors is in the doc linked by one of the posters above. It has nothing to do with transactions.

Re: SQLite on Rails: The how and why of optimal performance

#73

Post by fractaledmind »

fulafel wrote: post #58

> By design, the sqlite3-ruby gem does not release the GVL when calling SQLite. For the most part, this is a reasonable decision [...] Following the issue comment link https://github.com/sparklemotion/sqlite3-ruby/issues/287#iss... , it sounds like they they had a suspicion about a significant cost of reacquiring the lock but didn't validate it. Sounds iffy especially given all this workaround effort. I feel in eg Py…

You can read more discussion here: https://github.com/sparklemotion/sqlite3-ruby/pull/528 and here: https://github.com/digital-fabric/extralite/pull/46 to see how it was validated that simply releases the GVL for every `step` in the SQLite VM majorly hurts single-threaded performance. Finding a middle ground for both single threaded and multi-threaded performance is tricky. In Rails, we know it is multi-threaded because of the connection pool. But the lower level gem is used in many other libraries and tools where it is used in a single threaded environment

Re: SQLite on Rails: The how and why of optimal performance

#76

Post by tosh »

rodgerd wrote: post #34
iambateman wrote: post #11

General SQLite question for the group… I’m making a FOSS analytics system, and ease-of-installation is important. I want to send event data to a separate SQLite database, to keep analytics data separate from the main app’s data. I’m concerned about scaling, since even a modestly busy website could have 1000+ events per second. My thought is to store events in memory on the server and then make one batched write every…

Write events in parquet format and use DuckDB for your analytics?

afaiu appending to parquet files is not cheap, you basically have to re-write the file.

Do you mean periodically flushing a queue of logs into a new parquet file (e.g. named with a timestamp)?

Re: SQLite on Rails: The how and why of optimal performance

#77

Post by fractaledmind »

dwheeler wrote: post #28

Awesome, I'm always glad to see when someone figures out integration problems and helps the rest of us. I hope he manages to get these fixes into the default Rails confug. I run a Rails app; I switched to Postgres years ago and never looked back. Postgres is awesome. Still, it's great to have alternatives available, and I use sqlite for other tasks, so I know it has good capabilities too.

I’m very excited that yes indeed we have the four major pillars in Rails 8, which is releasing soon, but can be used now via the main branch. The default, out-of-the-box experience with Rails 8 will go all in on SQLite, a database will be the only dependency, and you will have a production-ready app with jobs, cache, web sockets, and a primary database ready from `rails new`. I’ll be talking more about this all at Rails World in a couple weeks and that talk will be on YouTube sometime after that. But exciting times are ahead for Rails, for sure.

Re: SQLite on Rails: The how and why of optimal performance

#78

Post by tosh »

notpushkin wrote: post #46
rodgerd wrote: post #34

Earlier quoted context omitted.

Write events in parquet format and use DuckDB for your analytics?

This. If you’re looking for something portable, DuckDB is hard to beat. It’s pretty much the SQLite for analytics. I’ve had good experience with ClickHouse, too, but it feels a bit more like Postgres rather than SQLite in terms of portability.

Take a look at chDB and clickhouse-local. Clickhouse can do a lot even without running it as a server.

https://clickhouse.com/docs/en/chdb

https://clickhouse.com/docs/en/operations/utilities/clickhou...

Re: SQLite on Rails: The how and why of optimal performance

#79

Post by fractaledmind »

klabb3 wrote: post #32
aeze wrote: post #31

If you're using SQLite on Rails are you effectively constrained to one machine/server?

No, you can set up replication with, eg LiteFS where you have one writer and multiple read replicas. That said, then you have operational overhead that defeats a part of the purpose with SQLite. In practice, you can get very far with a single machine and many CPUs (Postgres is ironically a good example of this). In eg Go you can easily parallelize most workloads. In rails, I don’t know if that’s possible. A quick sea…

This is my perspective as well. You certainly can horizontally scale with SQLite, but I strongly recommend that you vertically until you hit an actual limit there. If you know you will absolutely need multiple app nodes on day 1 or day 10, I think you will probably be better served by choosing a client/server database like MySQL or PG instead.

So, you aren’t limited to single machine, but you should stay single machine as long as possible and extract as much value from that operational simplicity before you trade that simplicity for some kind of horizontal scale

Re: SQLite on Rails: The how and why of optimal performance

#80

Post by biorach »

petcat wrote: post #64
tptacek wrote: post #63

Earlier quoted context omitted.

First off, I don't know that Richard Hipp agrees with you about what roles SQLite is "meant" to be in. Second: the reasons are straightforward: * For read-heavy access patterns, SQLite is crazy fast. * It's fast enough that you can often simplify your database access code; for instance, N+1 queries are often just not a problem in practice. * SQLite removes a whole tier from the N-tier architecture, which in turn remo…

> I don't know that Richard Hipp agrees with you about what roles SQLite is "meant" to be in. If Hipp thought that SQLite was suitable for backend applications where the database is the authority then he would allow real types and the associated constraints. But he won't do that because it complicates the code and bloats the embedded object size. SQLite is great for what it is. But it's not a real concurrent backend…

> But it's not a real concurrent backend database. It's a client-side database

People are successfully using it server-side, in specific situations it appears to be a good fit.

> You can accidentally write a string to an int column

Yes, you need more validation logic client-side in exchange for the performance gain. It's a trade-off, not a black/white distinction. A strongly typed language can help here.

Post reply on HN