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

141–149 of 149 posts

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

#141

Post by orf »

agnivade wrote: post #105
pilif wrote: post #96

Earlier quoted context omitted.

`select * from utxos where address =?` should not take 300ms no matter the size or type of database (assuming there is an index on address).

Not really. If your table does not fit into memory, there is a good chance that a disk seek is required. Now all bets are off.

Perhaps if you're using a really slow spinning disk and have no indexes, or the results are distributed evenly across the entire dataset (i.e lots of random, non sequential access).

It's just 300ms is really slow, even with a largeish dataset that doesn't fit into memory. Perhaps you hit a corner case in some way that destroyed performance in sqlite, but I'd be surprised if those results were representative.

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

#142

Post by int_19h »

creshal wrote: post #120
int_19h wrote: post #95

While we're speaking of SQLite; one thing that has little exposure that could probably use more is that it now ships with Windows as a system DLL: https://engineering.microsoft.com/2015/10/29/sqlite-in-windo... Between that, and packages readily available on most Linux and BSD distros out there (and, in most cases, installed by default), it's well on its way to become a de facto standard system API for relational sto…

It's amusing how Microsoft's different departments keep trying to kill off each other. They spent so much time trying to shove SQL Server Express down everyone's throat, and now everyone gets SQLite included instead.

SQLite is not a competitor to SQL Express, because the latter is still an out-of-proc server. It is a competitor to SQL CE (which shipped its last release to date in 2011, so...).

The nice thing about SQLite is how little it assumes about the world outside. That made it easy to run in WinRT application sandbox with minimal changes; and for quite a while, it was the only local DB readily available to WinRT code written in C# or C++ (JS got IndexedDB).

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

#144

Post by tracker1 »

talideon wrote: post #130
tracker1 wrote: post #29

Earlier quoted context omitted.

I find that Firebird has been pretty nice as well, with the option to move from embedded to server.

And much overlooked. It doesn't get half the love it deserves. While it could do with some work around making the sysadmin experience a bit better and have a way of aliasing short 'public' database names to the file-system level database, it's quite a good RDBMS.

About a decade ago, I wrote a system of intermittently connected systems that would run independently and sync up to a central db, all using firebird (uuid custom type) and a few utilities that ran via C# (mono under suse). It worked surprisingly well for the purpose it was designed.

Firebird was definitely nice in terms of being able to utilize the same db from the local systems (embedded) to the centralized server (service mode). As you say, it could use some love, but some of that could be done via symlink and/or consistent structure (/var/firebird/db/*). I haven't even looked at it in a while, wonder how hard it would be to use with electron/node, may have to look.

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

#145

Post by shermanyo »

thawkins wrote: post #89
SwellJoe wrote: post #21

I never stop being impressed at how often people will jump to odd, unsupportable, conclusions like, "using MySQL will make this thing faster". I've seen it so many times over the years regarding users and email configurations. I can't count the number of times I've dropped into someone's badly behaving mail configuration and found they had MySQL hosting the users, and explained it was for "performance" reasons. Someh…

Its mostly about concurrency, sqllite serialises all writes, for an embedded database in a single user application that is ok, but for a web application that may have a lot of concurrent writes going on its a performance disaster area. Note that for good perfomamce on a write heavy web application even mysql can be a problem unless you use innodb. As myisam has the same write table locking behaviour.

I'm a little ignorant of the situation on the sqlite side, so this may be a dumb question...

Say you have an MVC web app, could you have asynchronous calls to a thread performing the actual DB writes? From your app's perspective, concurrent writes would be placed into a queue that performs them serially.

Are there any functional issues to this approach? Thanks in advance for your thoughts or info.

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

#146

Post by dragonne »

kyriakos wrote: post #107
SwellJoe wrote: post #21

I never stop being impressed at how often people will jump to odd, unsupportable, conclusions like, "using MySQL will make this thing faster". I've seen it so many times over the years regarding users and email configurations. I can't count the number of times I've dropped into someone's badly behaving mail configuration and found they had MySQL hosting the users, and explained it was for "performance" reasons. Someh…

Kodi media player uses mysql and runs on most platforms. As an end user mysql is completely invisible

No, Kodi uses SQLite internally (I've fixed garbage in its databases with the sqlite CLI tool before). It does also support external MySQL, though.

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

#147

Post by lomnakkus »

Programmatic wrote: post #132
lomnakkus wrote: post #124

Earlier quoted context omitted.

Those optimizations also apply to PostgreSQL.

It would still allow you to use a simpler solution and not paint yourself in a corner if you optimized on sqlite; no preemptive optimization necessary wrt choice of dbms if you can perform the work on the simpler solution.

Optimizing in SQLite is just as much of a sunk cost as optimizing in PostgreSQL if you switch away to another database.

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

#148

Post by zyx321 »

shermanyo wrote: post #145
thawkins wrote: post #89

Earlier quoted context omitted.

Its mostly about concurrency, sqllite serialises all writes, for an embedded database in a single user application that is ok, but for a web application that may have a lot of concurrent writes going on its a performance disaster area. Note that for good perfomamce on a write heavy web application even mysql can be a problem unless you use innodb. As myisam has the same write table locking behaviour.

I'm a little ignorant of the situation on the sqlite side, so this may be a dumb question... Say you have an MVC web app, could you have asynchronous calls to a thread performing the actual DB writes? From your app's perspective, concurrent writes would be placed into a queue that performs them serially. Are there any functional issues to this approach? Thanks in advance for your thoughts or info.

That might work, as long as you don't ever need to read the data you've just written (logging?).

Otherwise you'll end up serving stale data that might be several minutes out of date.

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

#149

Post by shermanyo »

zyx321 wrote: post #148
shermanyo wrote: post #145

Earlier quoted context omitted.

I'm a little ignorant of the situation on the sqlite side, so this may be a dumb question... Say you have an MVC web app, could you have asynchronous calls to a thread performing the actual DB writes? From your app's perspective, concurrent writes would be placed into a queue that performs them serially. Are there any functional issues to this approach? Thanks in advance for your thoughts or info.

That might work, as long as you don't ever need to read the data you've just written (logging?). Otherwise you'll end up serving stale data that might be several minutes out of date.

Ah, longer caching is the factor with web I always forget after focusing on apps / games. Thanks for pointing it out.
Post reply on HN