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

61–70 of 98 posts

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

#61

Post by randomdrake »

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…

Worked here. Changed my mind on SQLite. Hipp worked alongside us. SQLite can be crazy performant:

https://use.expensify.com/blog/scaling-sqlite-to-4m-qps-on-a...

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

#63

Post by tptacek »

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…

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 removes a whole set of things that can go wrong (and if you've ever managed your own Postgres or MySQL: things do go wrong).

It's not a perfect fit for every application, or even the majority of applications, but the push you're seeing is a correction against the pretty clearly false idea that SQLite is well suited only for "tiny embedded client-side application databases".

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

#64

Post by petcat »

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

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 database. It's a client-side database. That's all the SQLite developers will ever allow it to be.

We can try to layer-on a bunch of stuff like Lite Stream or whatever, and sharding. But the fact is that the core database itself is not, and will never be, suitable for backend applications.

You can accidentally write a string to an int column. Will SQLite say no? No. SQLite doesn't care. It returns everything is A-OK!

You can query an ISO-8601 string column with date_trunc() and strftime() and it just returns NULL whether there was a value or not, or maybe just because it did't recognize the string in that column (LOL).

SQLite is fine. But it's not a real backend database. It's not a replacement for PG.

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

#65

Post by tptacek »

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's a "real" "concurrent" "backend" database? It's absolutely not a "client-side" database. Plenty of people use it in backends. Some of them are posting about it on this thread.

The correctness arguments apply just as much, if not more so, to MySQL and to document/schemaless databases. Lots of people don't like those databases, but nobody claims they're not "real backend databases".

You seem hung up on the idea that "backend" means "n-tier", with a segregated compute/storage tier for the database with networked connectivity to the app server. That architecture is something SQLite will never support, but that is not the only backend architecture.

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

#66

Post by petcat »

tptacek wrote: post #65
petcat wrote: post #64

Earlier quoted context omitted.

> 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's a "real" "concurrent" "backend" database? It's absolutely not a "client-side" database. Plenty of people use it in backends. Some of them are posting about it on this thread. The correctness arguments apply just as much, if not more so, to MySQL and to document/schemaless databases. Lots of people don't like those databases, but nobody claims they're not "real backend databases". You seem hung up on the idea t…

I think there's a new generation of developers that don't want to use "no sql" databases anymore (MongoDB, etc.) I think that's why we're starting to see a surge in people wanting to run SQLite as their backend database. It's similarly simple to start out with, and also similarly flimsy when dealing with actual data integrity. Very limited types, limited/uncomplicated isolation options, ref integrity disabled by default (Mongo DB also disables things by default that hurts their benchmarks).

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

#67

Post by QuadrupleA »

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…

Why not do UUIDs as a string or blob? And dates as strings, or integer / real timestamps?

It conceptually simplifies things in so many ways that benefit the app developer, not just the sqlite devs and low-spec hardware. Simpler documentation, shorter learning curve, smaller surface area for bugs, smaller binary size, etc.

There's a trend to add bloat and complexity to everything in software these days, but I'm so glad that a few projects like SQLite are pushing against that.

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

#68

Post by tptacek »

petcat wrote: post #66
tptacek wrote: post #65

Earlier quoted context omitted.

What's a "real" "concurrent" "backend" database? It's absolutely not a "client-side" database. Plenty of people use it in backends. Some of them are posting about it on this thread. The correctness arguments apply just as much, if not more so, to MySQL and to document/schemaless databases. Lots of people don't like those databases, but nobody claims they're not "real backend databases". You seem hung up on the idea t…

I think there's a new generation of developers that don't want to use "no sql" databases anymore (MongoDB, etc.) I think that's why we're starting to see a surge in people wanting to run SQLite as their backend database. It's similarly simple to start out with, and also similarly flimsy when dealing with actual data integrity. Very limited types, limited/uncomplicated isolation options, ref integrity disabled by defa…

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 typing features; what's interesting about SQLite is not what was interesting about schemaless databases, but rather the ability to ship backend apps without a separate database tier.)

Again: I think you need to snap out of the idea that n-tier architectures are axiomatically optimal for all backend applications. They often are! But not all the time.

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

#69

Post by petcat »

tptacek wrote: post #68
petcat wrote: post #66

Earlier quoted context omitted.

I think there's a new generation of developers that don't want to use "no sql" databases anymore (MongoDB, etc.) I think that's why we're starting to see a surge in people wanting to run SQLite as their backend database. It's similarly simple to start out with, and also similarly flimsy when dealing with actual data integrity. Very limited types, limited/uncomplicated isolation options, ref integrity disabled by defa…

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.

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

#70

Post by tptacek »

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.

This was the kind of thing people used to say about MySQL before Meta made those arguments look silly, and so they've moved to SQLite as a new target. I like Postgres fine, but it's just a tool, like many others.
Post reply on HN