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

We’re pretty happy with SQLite and not urgently interested in a fancier DBMS

beets.io

Post reply on HN
Print view View original

61–70 of 149 posts

Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS

#61

Post by ams6110 »

chjj wrote: post #41

Premature optimization is evil, but preemptive optimization is necessary unless you want to paint yourself into a corner. I realized this after implementing a bitcoin full node. In my bitcoin implementation, as an experiment, I tried storing the blockchain in sqlite, postgres, and leveldb. I gathered up a bunch of data from the first ~200k blocks of the blockchain and benchmarked all three databases. I queried for so…

With relational databases, you absolutely can see subsecond to 30+ second ranges for the same query on the same data, if you don't have proper indexing or stats on your tables.

Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS

#62

Post by oppositelock »

I don't get the point of this article. SQLite is fine, especially in an embedded database, but once you have concurrent access, it starts to suffer because it has very coarse grained locks, so a "real" database is better for a distributed single-DB design. It's more about using the right tool for the job, and the author seems to be talking himself out of some kind of guilt for SQLite being the right tool for him.

Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS

#63

Post by vonklaus »

chjj wrote: post #55
panic wrote: post #50

Earlier quoted context omitted.

What operations were you doing in your benchmark? What was SQLite actually doing (CPU?, disk I/O?) while taking 30 seconds to finish? This would be a lot more useful and less FUDdy with more detail.

I'll clarify here: these weren't very scientific benchmarks, as I alluded to. So, don't take my word for it. I didn't measure ops per second, I didn't take into account system load or cpu usage, etc. This is all anecdotal evidence. I'm not saying this an announcement that "you shouldn't use sqlite". I measured the time of one query. It was a benchmark I ran just to personally give me a general idea of what I was up a…

I would be interested in a repo or post about your setup for the actual project; downloading, storing and using blockchain data. Sounds super interesting so if it(article, repo, site) isn't private & exists, would be keen. Cheers.

Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS

#64

Post by andrewflnr »

oppositelock wrote: post #62

I don't get the point of this article. SQLite is fine, especially in an embedded database, but once you have concurrent access, it starts to suffer because it has very coarse grained locks, so a "real" database is better for a distributed single-DB design. It's more about using the right tool for the job, and the author seems to be talking himself out of some kind of guilt for SQLite being the right tool for him.

I think you're misreading the post. They're just trying to explain why sqlite is the right tool for their particular job, so they don't have to keep explaining it to other people who keep trying to shoehorn in the wrong tool. Lack of concurrency in their app is specifically mentioned as a reason sqlite is appropriate. There's no guilt there.

Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS

#66

Post by c-smile »

mewm wrote: post #53
c-smile wrote: post #48

Earlier quoted context omitted.

Consider multi-user wordpress site for example. You can have one SQLite DB per user - there is simply no information to share between users. If so you can split all your users between multiple backend machines and do per user request routing - like one machine serving users from A to F. SQLlite has cheap DB loading sequence so you can open/close it for each page request. That would be perfect horizontal scaling as al…

well, you kinda just move the problem to another layer. You'll face same horizontal scaling issues once you need to scale on each user.

Or just remove one [DB] layer completely. Such databases can be stored directly on machines executing http requests. And if you need to rebuild DB structure you don't need to stop the world - do them one by one.

Of course, all this depends on amount of data you need to store for each user and informational structure of the app.

Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS

#67

Post by tedmiston »

Svenstaro wrote: post #30
StavrosK wrote: post #15

Earlier quoted context omitted.

What are the limitations? I love SQLite, and have vaguely heard that it has issues with concurrency, but what , exactly? I use it on production for www.tithess.gr and it seems to be working beautifully, no concurrency problems whatsoever there. What problems should I be expecting in a multi-access scenario? I've never had that question answered adequately.

It only supports a subset of ALTER TABLE and the work around for the limitations is very annoying: https://www.sqlite.org/lang_altertable.html

I've run into this issue before with Django's automatically generated migrations breaking in SQLite. As a workaround, I rm my local test db or make the change myself.

Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS

#68

Post by c-smile »

andrewguenther wrote: post #19
StavrosK wrote: post #15

Earlier quoted context omitted.

What are the limitations? I love SQLite, and have vaguely heard that it has issues with concurrency, but what , exactly? I use it on production for www.tithess.gr and it seems to be working beautifully, no concurrency problems whatsoever there. What problems should I be expecting in a multi-access scenario? I've never had that question answered adequately.

You cannot have multiple writers to a SQLite database. Only a single file descriptor may be open with 'write' access. As long as you can get the performance you need out of a single writer, then you're good!

Yes, but in some cases it could be faster to work in sole- writer mode. It is kind of cooperative multitasking demonstrating by Node.js.

Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS

#69

Post by coleifer »

tshtf wrote: post #35
smegel wrote: post #32

Single user, embedded-database situations is what DBMS like SQLite is designed for. And this guy thinks he deserves a pat on the back for using a tool in the way it was designed to be used?

This was a useful and informative post. Where on earth do you think the author is asking for a 'pat on the back' in this?

Where was the useful information? I don't like negative comments as much as the next person, and have written many posts about Sqlite...

I'm glad the author of beet uses Sqlite, but to use a client/server database system would be ludicrous!

It's not that I am trying to hate on the guy, he had good intentions and wanted other people to listen up.

At the end of the day, though, dudes just another dev who made a reasonable decision. Bfd.

Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS

#70

Post by marktangotango »

andrewguenther wrote: post #19
StavrosK wrote: post #15

Earlier quoted context omitted.

What are the limitations? I love SQLite, and have vaguely heard that it has issues with concurrency, but what , exactly? I use it on production for www.tithess.gr and it seems to be working beautifully, no concurrency problems whatsoever there. What problems should I be expecting in a multi-access scenario? I've never had that question answered adequately.

You cannot have multiple writers to a SQLite database. Only a single file descriptor may be open with 'write' access. As long as you can get the performance you need out of a single writer, then you're good!

WAL allows multiple readers and writers:

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

Post reply on HN