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

11–20 of 98 posts

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

#11

Post by iambateman »

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 second.

Does this seem like a reasonable way to get around the SQLite limitation where it struggles with lots of DB writes? Any better ideas?

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

#13

Post by tptacek »

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…

SQLite doesn't struggle with writes. But it only supports a single write transaction at a time; if you don't trust SQLite's transaction concurrency performance, you might serialize all your writes on a specific thread/process.

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

#15

Post by iambateman »

tptacek wrote: post #13
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…

SQLite doesn't struggle with writes. But it only supports a single write transaction at a time; if you don't trust SQLite's transaction concurrency performance, you might serialize all your writes on a specific thread/process.

Thanks!

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

#16

Post by anothername12 »

dorianmariefr wrote: post #7

much easier just to use pg

Agreed. I always regret starting a new rails project with sqlite. Invariably I end up wanting some bunch of features only pg has.

Even for running tens of thousands of integration tests in a few seconds, pg is fine.

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

#17

Post by dewey »

dorianmariefr wrote: post #7

much easier just to use pg

I kind of have to agree, I recently thought I'll use sqlite in Rails for my new project to keep things simple but then realized it's actually more annoying for my use case. I'd need a persistent volume with the right permissions, and I can't just connect to my PG instance running on the server from my local machine to run some queries.

I'm sure it makes things easier for some use cases but it's not a given.

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

#19

Post by abound »

tptacek wrote: post #14
dorianmariefr wrote: post #7

much easier just to use pg

It is until you realize that using SQLite means you don't have to worry about N+1 queries, which actually does make a pretty big difference in Rails code.

Not sure I understand this point, how does SQLite fix the N+1 query problem? Just by having the data co-located with the app and avoiding the round-trip latency hit?

If so, I'd argue you still have N+1 problems, you just won't notice them until N gets a bit larger.

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

#20

Post by tptacek »

abound wrote: post #19
tptacek wrote: post #14

Earlier quoted context omitted.

It is until you realize that using SQLite means you don't have to worry about N+1 queries, which actually does make a pretty big difference in Rails code.

Not sure I understand this point, how does SQLite fix the N+1 query problem? Just by having the data co-located with the app and avoiding the round-trip latency hit? If so, I'd argue you still have N+1 problems, you just won't notice them until N gets a bit larger.

https://www.sqlite.org/np1queryprob.html
Post reply on HN