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

91–98 of 98 posts

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

#91

Post by tracker1 »

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.

I would suggest that sometimes you want N+1 with a collapsed data set (JSON column data) if you have limited request size, separate stores/service and/or otherwise have clear primary key lookups for the secondary data. I've seen these types of queries run faster with separate lookups, especially depending on caching in environments where the lookup would mean a DBMS connecting to another DBMS for the extra data much more slowly.

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

#92

Post by wolf550e »

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…

What are "real types and the associated constraints"? It has strict tables:

https://www.sqlite.org/stricttables.html

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

#93

Post by randomdata »

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.

Of course, once you come to that realization, then you realize that it is all one in the same and that there isn't any magic going on, which then realizes that business constraints can go anywhere in your application and be written by anyone.

I suspect what you are really trying to say is that you trust Hipp more than you trust yourself to get the constraints right. Indeed, if you screw it up you're in for a world of hurt, so you are right to be cautious. But, if you have more trust in a random stranger who has no care for your data than you do yourself to implement it for you, perhaps you shouldn't be writing any code at all? Software development certainly isn't for everyone.

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

#94

Post by JodieBenitez »

tosh wrote: post #83
JodieBenitez wrote: post #81

Some tweaks that I keep for my personal toy webservices: PRAGMA journal_mode = WAL; PRAGMA busy_timeout = 5000; PRAGMA synchronous = NORMAL; PRAGMA cache_size = 1000000000; PRAGMA foreign_keys = true; PRAGMA temp_store = memory; And use BEGIN IMMEDIATE transactions. https://kerkour.com/sqlite-for-servers

What is your opinion on cache_size vs mmap_size?

Sorry, no opinion, I just copied these from the article and it works fine enough for me.

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

#95

Post by ncruces »

rbranson wrote: post #72
nikisweeting wrote: post #52

Earlier quoted context omitted.

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.

That list lists reasons queries might return busy; queries aka. reads.

Reads returning busy is rare under WAL, but WAL mode does very little for writer-writer contention.

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

#96

Post by vidarh »

tracker1 wrote: post #90
vidarh wrote: post #10

Earlier quoted context omitted.

I'm not using Rails, but I now have several sites using my own little thing that is a single docker container where all state + content is in a single sqlite file, and it's very nice to be able to just move that single file around. I love postgres, but doing the equivalent of that with Postgres is a lot more hassle.

While I'm fine with using SQLite for these things, I would counter that a docker-compose file makes using a db with your app roughly as easy as a sqlite file, only in that you'd have have a data directory as a volume mount for the db. PostgreSQL and MySQL/MariaDB in particular are pretty easy to launch with a configured user/pass for the db/app specifically with docker/compose.

Docker compose itself introduces a lot of complexity I don't need with that setup.

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

#97

Post by ncruces »

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…

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

Totally baseless claim. Advances to the query optimizer complicate code and bloat the binary far more than adding DECIMAL, DATETIME or UUID as types would.

The reason types don't change is forward and backward compatibility, and the promise of supporting the current file format and APIs for interacting with it for at least another 25 years.

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

#98

Post by tracker1 »

vidarh wrote: post #96
tracker1 wrote: post #90

Earlier quoted context omitted.

While I'm fine with using SQLite for these things, I would counter that a docker-compose file makes using a db with your app roughly as easy as a sqlite file, only in that you'd have have a data directory as a volume mount for the db. PostgreSQL and MySQL/MariaDB in particular are pretty easy to launch with a configured user/pass for the db/app specifically with docker/compose.

Docker compose itself introduces a lot of complexity I don't need with that setup.

If you're comparing to SQLite, sure... if you're comparing to installing and configuring an RDBMS server on a host OS for development work, I'm going to hard disagree. Most services already have a hosted docker container configured, usually by the developers of said service. Getting that running is often as simple as googling "service-name docker-compose example" and "docker compose up".

And once you do understand docker-compose, it becomes second nature. I'd be willing to state that dealing with a merge conflict with source control is more difficult than docker-compose.

Post reply on HN