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

Ask HN: Have you used SQLite as a primary database?

news.ycombinator.com

Post reply on HN
Print view View original

51–60 of 330 posts

Re: Ask HN: Have you used SQLite as a primary database?

#51

Post by barryhennessy »

otoolep wrote: post #45

rqlite author here, happy to answer any questions. One thing I've noticed is a trend towards folks doing bitcoin mining (and related applications) wanting to use rqlite. I think they like that it is very easy to run, and gives them complete control over their data. https://docs.google.com/presentation/d/1Q8lQgCaODlecHa2hS-Oe...

I only very briefly looked into rqlite. It's very interesting, but if I understand it correctly it's also not geared toward a write heavy workflow. (all writes are routed to the same node)

I.e. it's leaning more toward the moderate, but reliable writes, and heavy read use cases?

Please let me know ~if I'm missing anything~ what use cases I'm missing.

Re: Ask HN: Have you used SQLite as a primary database?

#52

Post by anonyfox »

It is exceptionally great if you don't need parallel writes or have many terabytes of data - ie: for most services out there.

When embedding natively, like in a Rust app, the performance is better than any other RDBMs because no network/serialization overhead and being able to use pointers in-process if needed.

The DevOps story also is a dream: typically it is just a single file (optionally + some more for journaling) and setup is automated away (most language libs bundle it already), plus it is widely known since smartphone SDKs and all webbrowsers include/expose it.

A subtile advantage: the supported SQL subset is so small, that "if it works in sqlite, it will also work with $RDBMS" in most cases, but not the other way around. I always use it when getting started when in need of relational data, and only had to swap it out for postgres once, but not due to technical/scaling reasons (IT policy change & stuff).

Having said that, it is mind-boggling what kind of load you can handle with a small VPS that runs a Rust microservice that embeds it's own SQLite natively... that would be an expensive cluster of your typical rails/django servers and still have worse performance.

Re: Ask HN: Have you used SQLite as a primary database?

#53

Post by marginalia_nu »

qwerty456127 wrote: post #44
the__prestige wrote: post #22

The sqlite docs page has a nice article [1] on when to use an embedded database such as sqlite and when to go with a client/server model (postgres, mysql or others) When not to use sqlite: - Is the data separated from the application by a network? - Many concurrent writers? - Data size > 280 TB For device-local storage with low writer concurrency and less than a terabyte of content, SQLite is almost always better. [1…

> For device-local storage with low writer concurrency and less than a terabyte of content, SQLite is almost always better. Isn't MySQL MyISAM faster and this way constitute a better choice for a scientific number crunching application? I mean near 4GB DB, very simple schema, heavy reading load, little/no inserts and no updates.

With 4 Gb you might as well just load the data into RAM.

Re: Ask HN: Have you used SQLite as a primary database?

#55

Post by ajani »

barryhennessy wrote: post #27
ajani wrote: post #17

Yes. For http://ht3.org which is a search engine I wrote for tech related articles. It works really well. It uses the fts5 extension, that allows full text searching. There are over a million indexed pages and it’s no trouble.

> The irritant-free web This is a very respectable goal. I wish you great success! Good full text search without pulling in another dependency would be quite a win. I'll add fts5 to my reading list. :) Out of interest, what kind of compute and storage resources do you have underneath that?

Thank you for the appreciation :)

It’s a linode. Shared cpu Plan.

1 CPU Core 50 GB Storage 2 GB RAM

It’s just $10 per month.

With linode even shared cpus are powerful and I’m yet to hit any overload. I’m sure it will at some point, I might upgrade then.

Re: Ask HN: Have you used SQLite as a primary database?

#56

Post by otoolep »

barryhennessy wrote: post #51
otoolep wrote: post #45

rqlite author here, happy to answer any questions. One thing I've noticed is a trend towards folks doing bitcoin mining (and related applications) wanting to use rqlite. I think they like that it is very easy to run, and gives them complete control over their data. https://docs.google.com/presentation/d/1Q8lQgCaODlecHa2hS-Oe...

I only very briefly looked into rqlite. It's very interesting, but if I understand it correctly it's also not geared toward a write heavy workflow. (all writes are routed to the same node) I.e. it's leaning more toward the moderate, but reliable writes, and heavy read use cases? Please let me know ~if I'm missing anything~ what use cases I'm missing.

That's correct. rqlite replicates SQLite for fault-tolerance and high-availability, not for performance. It fact performance takes a hit, for the reasons you state. But it's no worse (nor better) than something like, say, etcd or Consul.

https://github.com/rqlite/rqlite/blob/master/DOC/FAQ.md#what...

Re: Ask HN: Have you used SQLite as a primary database?

#57

Post by syntaxfree »

qwerty456127 wrote: post #44
the__prestige wrote: post #22

The sqlite docs page has a nice article [1] on when to use an embedded database such as sqlite and when to go with a client/server model (postgres, mysql or others) When not to use sqlite: - Is the data separated from the application by a network? - Many concurrent writers? - Data size > 280 TB For device-local storage with low writer concurrency and less than a terabyte of content, SQLite is almost always better. [1…

> For device-local storage with low writer concurrency and less than a terabyte of content, SQLite is almost always better. Isn't MySQL MyISAM faster and this way constitute a better choice for a scientific number crunching application? I mean near 4GB DB, very simple schema, heavy reading load, little/no inserts and no updates.

Why?

Re: Ask HN: Have you used SQLite as a primary database?

#58

Post by christophilus »

tbran wrote: post #29

Yes, works great for my sites (they are mostly read-heavy). I used to default to Postgresql, now I default to sqlite. This [0] is a good article with some benchmarks, misconceptions about speed, and limitations. [0]: https://blog.wesleyac.com/posts/consider-sqlite

> SQLite has essentially no support for live migrations, so you need to instead make a new table, copy the data from the old table into the new one, and switch over.

That seems like a pretty big flaw as your data grows. Zero downtime migrations are really nice. Anyone here got a war story / experience with this one?

Re: Ask HN: Have you used SQLite as a primary database?

#59

Post by jmstfv »

barryhennessy wrote: post #34
jmstfv wrote: post #7

I'm using SQLite in Notion Backups (most of the workload happens in background jobs; the web app itself doesn't get that many visits) Except for some rare exceptions, it's been doing pretty great. I don't have any plans to migrate from SQLite any time soon.

Any chance I could convince you to share some of those 'rare exceptions'? I love a good exception ;) Also they're where the real insights are.

Lately, Rails has been complaining about connection timeouts, stating that all connections in the pool were in use (this usually happens when Sidekiq, a background jobs framework, processes multiple long-running jobs).

By default, the connection pool in Rails contains 5 connections, and they time out within 5 seconds.

Re: Ask HN: Have you used SQLite as a primary database?

#60

Post by dangerface »

Yes! Sqlite works great in production for single threaded access it can handle nearly the same sort of performance as a mysql install, inserts are fast full text search is fast it all just works great.

If you have multiple threads accessing the same database it will kill the speed of sqlite completely, it will work for development but as soon as you put it into production and put any sort of threaded load on the database it will quickly become the bottle neck and bring the whole thing down. If you run into this threaded issue you can just switch to mysql at that point and it will fix the issue.

Post reply on HN