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

51–60 of 98 posts

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

#51

Post by iambateman »

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?

Thanks for this...I'd never heard of DuckDB.

I'm writing in PHP, so it looks like that's a nonstarter for now, but _very_ interested to see if a PHP extension pops up for this in the future.

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

#52

Post by nikisweeting »

rbranson wrote: post #41
hu3 wrote: post #37

Earlier quoted context omitted.

We all wish you were right. But alas life's not that simple. I suggest reading the manual on the section: "Sometimes Queries Return SQLITE_BUSY In WAL Mode" https://sqlite.org/wal.html#sometimes_queries_return_sqlite_...

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.

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

#53

Post by pradeepchhetri »

iambateman wrote: post #51
rodgerd wrote: post #34

Earlier quoted context omitted.

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

Thanks for this...I'd never heard of DuckDB. I'm writing in PHP, so it looks like that's a nonstarter for now, but _very_ interested to see if a PHP extension pops up for this in the future.

You should look at ClickHouse which has good PHP client

[0] https://github.com/smi2/phpClickHouse

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

#54

Post by pradeepchhetri »

iambateman wrote: post #51
rodgerd wrote: post #34

Earlier quoted context omitted.

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

Thanks for this...I'd never heard of DuckDB. I'm writing in PHP, so it looks like that's a nonstarter for now, but _very_ interested to see if a PHP extension pops up for this in the future.

There was a great talk[0] recently from Laravel core team member about ClickHouse, Probably you will enjoy watching it.

[0] https://www.youtube.com/watch?v=_jjvaFWWKqg

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

#55

Post by rpcope1 »

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…

Batching writes is probably a good idea, but by far the absolute best way to do something like this with SQLite is to use WAL and have a single designated writer (and as many readers as you want), probably fed by something like a queue. As long as you do that, I usually find the the performance is often really amazing.

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

#56

Post by rcaught »

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…

https://guides.rubyonrails.org/active_job_basics.html

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

#57

Post by VWWHFSfQ »

I'm still not understanding this push toward using SQLite as a production backend database. It's great for what it is, a tiny embeddable client-side application database. Like an address book on your phone. But even the developers themselves have steadfastly refused to allow it to expand beyond that scope. For instance, they won't add native types for any useful things like dates/times, or uuids. Because that would bloat the code and the size of the embedded object. So you're stuck with "everything is a string". Ref integrity can be enabled, but even those constraint options are very limited.

Not sure why people are still trying to shoe-horn it into a role that it's not meant to be in, and not even really supported to be.

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

#58

Post by fulafel »

> 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 Python extensions culture this would have gotten designed the other way (maybe someone knows how it's done there?).

edit: also, there's this other comment in the linked issue:

> The extralite gem is an alternative SQLite client which releases the GVL during blocking, see note on concurrency here: https://github.com/digital-fabric/extralite?tab=readme-ov-fi.... It is both significantly faster than this gem in general and doesn't have concurrency issues.

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

#59

Post by rcaught »

richjdsmith wrote: post #25

Anyone who is looking at using SQLIte + Rails should check out the work done by Oldmoe (X/Github) on his Litestack project. Here's the intro paragraph: "Litestack is a Ruby gem that provides both Ruby and Ruby on Rails applications an all-in-one solution for web application data infrastructure. It exploits the power and embeddedness of SQLite to deliver a full-fledged SQL database, a fast cache , a robust job queue,…

Rails 8 will by default use the DB for cache, queues and WebSocket broadcasting - https://fly.io/ruby-dispatch/the-plan-for-rails-8/

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

#60

Post by randomdata »

VWWHFSfQ wrote: post #57

I'm still not understanding this push toward using SQLite as a production backend database. It's great for what it is, a tiny embeddable client-side application database. Like an address book on your phone. But even the developers themselves have steadfastly refused to allow it to expand beyond that scope. For instance, they won't add native types for any useful things like dates/times, or uuids. Because that would b…

We eventually realized that the "API server" was actually just a DBMS all along. And once that was realized, it was realized that a DBMS sitting beside another DMBS that deals with the exact same data is rather silly, which is now leading to:

    1. Let the clients connect to Postgres directly.
         - or -
    2. Cut Postgres out of the picture and double down on the homegrown DMBS.
Some are going the #1 route, others #2. Where #2 is opted, SQLite is a convenient engine on which to build upon. It may not be perfect but it is what we have. Keep in mind that this realization on a grand scale (I'm sure some noticed many years ago) is fairly recent, so there is a lot of experimenting going on to figure out what works and what doesn't.

It's the natural cycle of computing. What is old is new again.

(Replace Postgres with MySQL, MSSQL, Oracle, or other DBMS as you see fit.)

Post reply on HN