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

311–320 of 330 posts

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

#311

Post by qorrect »

vatotemking wrote: post #282
qorrect wrote: post #208

Earlier quoted context omitted.

Hey I actually do the same in a mobile app. I dump everything into a local database, when they are connected to the internet it syncs, lets it work offline.

Im working on something similar. Mine is a RFID scanner kiosk that uses a local db when offline. How do you manage sync conflicts?

In my workflow, I PULL when they initial get online and MERGE non conflicting changes and ACCEPT the changes with the latest timestamp if there is a conflict.

It's not perfect and people still complain, to do it cleanly I would need to prompt the user which change to take but I haven't figured out a clean way to do that yet.

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

#312

Post by js2 »

oppositelock wrote: post #296
js2 wrote: post #285

Earlier quoted context omitted.

> my sweet spot has become a big honking Postgres instance in RDS Why do you prefer PostgreSQL RDS over Aurora RDS? Aurora seems better in every way but price[1]. (I know it also had some growing pains at launch.) [1]: Amazon RDS for PostgreSQL is ideal when you have a small-to-medium intense workload. It works best when you have limited concurrent connections to your database. If you’re moving from commercial databa…

Price is the main reason. Write performance is a second one.

The article I linked implies that Aurora offers higher write performance:

> If your database workload reaches the Amazon RDS max limit of 80,000 IOPS, and it requires additional IOPS, Aurora PostgreSQL is the preferred database solution. If database workload requires less than 80,000 IOPS, you can choose either Amazon RDS for PostgreSQL or Aurora PostgreSQL based on supported features.

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

#313

Post by michaelcampbell »

I have a few low volume sites backed by SQLite, but perhaps not in the manner you're getting at. Mine are all sites that have a process to insert/update or read the data, then generate static html from it, so I don't need to deal with multiple simultaneous connections to it.

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

#315

Post by tiborsaas »

barryhennessy wrote: post #38
tiborsaas wrote: post #13

Yes, I've used it for a side project of mine. It processed like 5 financial transactions in total, so I'm glad I never invested the time to build anything more robust :) It's also powering another one and I really like the fact that I can just commit the whole DB to the GIT repo.

Very interesting, what's your workflow? And how big is the DB? I've never (deliberately) considered committing a DB to git. Although there was that one time when I was straight out of college... Pro tip: surprising your colleagues in the morning with a 40 minute wait to pull master (because you committed a ???GB db) is a good way to feel like a right eegit.

It's very small, just ~2000 rows, it contains YouTube video ID-s and some metadata.

For backup, I have a small bash script that creates a git commit and pushes it to the github.

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

#316

Post by pieterhg »

WA wrote: post #46
pieterhg wrote: post #6

Yes for all my sites: Nomad List, Remote OK, Hoodmaps, Rebase etc. No real issues at all.

I read in one of your Tweets that you use one database file per (unrelated) table to avoid corruption. Why did you move to this model? Are multiple tables per file really more easy to corrupt?

Yes! It kinda happened because I had no idea how SQLite worked so I thought this was normal.

Then I thought this is great and makes it easier to move the db file if it just has 1 table. So I can download it easily for local dev for ex.

And yes in case there's corruption which never rly happens, only one file thus table would be affected.

PS: one thing that really helped reduce issues was setting PRAGMA MODE to WAL

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

#317

Post by pieterhg »

el_dev_hell wrote: post #8
pieterhg wrote: post #6

Yes for all my sites: Nomad List, Remote OK, Hoodmaps, Rebase etc. No real issues at all.

Awesome to hear! How do you handle this? Do you store the SQLite file somewhere like s3 or just in memory? How does this work for such high traffic sites?

No in the filesystem on a VPS. All my sites just run on a VPS. Nothing fancy!

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

#318

Post by srcreigh »

jjoonathan wrote: post #307
srcreigh wrote: post #251

Earlier quoted context omitted.

This is a common problem. Bulk inserts should be done within a single txn if possible. The limit isn't insert throughput, but transaction throughput. I've commented elsewhere here for docs referencing this problem. It's FAQ#19 on the SQLite website. Was your inserts based on HTTP requests or was it more of a batch process? we're they grouped in txns? Obviously user http requests would be harder to group up, but kudos…

The inserts were batched. That was the second thing I tried after getting rid of the index, and that brought it from 20/s to 100/s. This wasn't a website, it was HPC job tracking. SQLite chugged so hard that the tail wagged the dog, though.

I'm not contradicting you, it is curious what you saw, but this SQLite FAQ does claim that 50k/s inserts on a normal computer HDD is possible. Maybe there was some other resource constraint or configuration somewhere. It is very curious.

> Actually, SQLite will easily do 50,000 or more INSERT statements per second on an average desktop computer. But it will only do a few dozen transactions per second. Transaction speed is limited by the rotational speed of your disk drive. A transaction normally requires two complete rotations of the disk platter, which on a 7200RPM disk drive limits you to about 60 transactions per second.

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

#319

Post by JohnBooty »

marginalia_nu wrote: post #53
qwerty456127 wrote: post #44

Earlier quoted context omitted.

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

With a database you generally are loading it into RAM, thanks to caching at the database and/or filesystem level, and you get all of the fun database features more or less for free.

There's a performance hit relative to skipping the database altogether and simply allocating 4GB of RAM and accessing it directly, of course.

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

#320

Post by dspillett »

qwerty456127 wrote: post #62
syntaxfree wrote: post #57

Earlier quoted context omitted.

Why?

I dunno, just felt like conventional (since long ago) knowledge that MyISAM is the fastest of all SQL DBs in simplistic non-RAM scenarios. I'm not sure this is true so I ask.

Before sqlite is definitely was said to be the fastest, I suspect the two are similar enough that it makes little or no difference these days and sqlite (unless the dynamic typing thing is an issue for you, and even that is going away as recent versions support at least some stricter type enforcement) safer and more “correct” than MyISAM in many ways.
Post reply on HN