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

121–130 of 149 posts

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

#121

Post by hyc_symas »

corysama wrote: post #46
jtakkala wrote: post #13

Earlier quoted context omitted.

I gained a newfound respect for SQLite recently after reading Dan Luu's post on file consistency ( http://danluu.com/file-consistency/ ). I had always thought of SQLite as a bit of a toy database, but having read that post I was surprised by how rigorously it appears to have been developed.

Reading that post makes me want to investigate LMDB as an alternative "competition for fopen()". I'd be very interested if anyone had opinions/experience on that idea.

(Disclaimer: LMDB author here) When your data is simple, too simple for SQL, LMDB is the rational choice. (And as others have already pointed out, you can use SQLightning, SQLite built on LMDB, if you really want the relational data model.)

Nothing else even approaches LMDB for small footprint, efficiency, and reliability.

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

#122

Post by ajuc »

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…

> Also, I don't even want to try to imagine shipping an installable desktop application for non-technical users that relies on MySQL!

Amarok music player on KDE linux desktop environment (at least on version 3.5) kept the music collection metadata in database. You could choose sqlite, mysql or postgress during installation if I remember correctly. It worked ok, because of the package system.

I guess if you used separately installed databases there would be some conflicts, though.

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

#123

Post by chmike »

SQLite is Ok, but write access must be synchronized. I used it for my Flask (Python) application and was forced to switch to PostgreSQL because of synchronization problems. I would prefer sticking with SQLite which was simpler to manage.

The author doesn't say a word about synchronization when writing to SQLite.

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

#124

Post by lomnakkus »

mappu wrote: post #57
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…

I've found sqlite's performance on bulk insertions to be massively (100x) improved by wrapping the bulk insertion in a transaction. fsync()ing a couple hundred thousand individual INSERTs isn't fast.

Those optimizations also apply to PostgreSQL.

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

#125

Post by rwmj »

creshal wrote: post #116
spudlyo wrote: post #52

Earlier quoted context omitted.

Also, I don't even want to try to imagine shipping an installable desktop application for non-technical users that relies on MySQL! It's really not that hard to imagine. There is an embedded library version of MySQL called libmysqld which is made for this very purpose. Of course as you point out it's quite overkill for something that would work perfectly well with Berkeley DBs.

I've never seen it used like that, but Microsoft's equivalent SQL Server Express is a nightmare in deployment and support, because you need to administer a full-blown SQL Server on every customer PC (backups, migration debugging, …). I've no idea why people don't use SQLite instead.

SQLite is popular enough for Windows applications that SQLite had to rename its temporary files to "etilqs_..." (SQLite backwards) to avoid getting unnecessary bug reports from naive users (https://answers.microsoft.com/en-us/windows/forum/windows_7-...)

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

#126

Post by alex_hitchins »

creshal wrote: post #116
spudlyo wrote: post #52

Earlier quoted context omitted.

Also, I don't even want to try to imagine shipping an installable desktop application for non-technical users that relies on MySQL! It's really not that hard to imagine. There is an embedded library version of MySQL called libmysqld which is made for this very purpose. Of course as you point out it's quite overkill for something that would work perfectly well with Berkeley DBs.

I've never seen it used like that, but Microsoft's equivalent SQL Server Express is a nightmare in deployment and support, because you need to administer a full-blown SQL Server on every customer PC (backups, migration debugging, …). I've no idea why people don't use SQLite instead.

Certianly from my experience the main reason for using it is that once it becomes to large for a desktop machine it's trivial to migrate to a 'proper' MsSql server.

I also don't find the admin side much of a faff. Provide your users a backup/restore option within your app and ensure you install your own instance.

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

#127

Post by douche »

alex_hitchins wrote: post #126
creshal wrote: post #116

Earlier quoted context omitted.

I've never seen it used like that, but Microsoft's equivalent SQL Server Express is a nightmare in deployment and support, because you need to administer a full-blown SQL Server on every customer PC (backups, migration debugging, …). I've no idea why people don't use SQLite instead.

Certianly from my experience the main reason for using it is that once it becomes to large for a desktop machine it's trivial to migrate to a 'proper' MsSql server. I also don't find the admin side much of a faff. Provide your users a backup/restore option within your app and ensure you install your own instance.

SQL Express has some rather shitty default configuration when you install it, that you usually have to change to actually use it for anything useful.

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

#128

Post by mgkimsal »

kefka_p wrote: post #99
mgkimsal wrote: post #86

Earlier quoted context omitted.

"right tool for the job" ends up being almost meaningless unless the players involved have enough experience with multiple tools to make a useful judgement. Too often "right tool" is "whatever I've already used before". :/

That is true. I failed to adjust my perspective for the fact that not everyone has nearly thirty years of programming experience given I still consider myself an amateur. Still we should recall the utility gained in using the right tool is finite and variable. In some cases can be like night and day while in other cases a moderate or negligble improvement might be all you see. Under some circumstances familiarity wit…

The "constraints" part is def something to remember. I've done a lot of short term consulting or training over the years, and I'll come in to a new team and see what they're doing. The "right tool" line is often used to justify something, and it's often either because it's the only thing person X knew, but often the 'right tool' might take an extra several weeks to get the team up to speed on. It's definitely the 'right' tool, but they're denied that decision.

My reaction to "right tool" was less about this particular article (and I agree with you) as it was to the phrase in general. I've seen it used to justify just about any tech decision ever made.

The other reality is... if you're a C# shop, with a team of 15 C#/.NET devs, the 'right tools', if there's any deadline at all, will probably involve Windows, C# and .NET tools, even if they are demonstrably inferior to, say, a Linux-based stack. Deadlines, existing code and budgets do play a role in decision making, further confounding the usefulness of the "right tool" rule.

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

#129

Post by blahi »

int_19h wrote: post #94
greenleafjacob wrote: post #91

Earlier quoted context omitted.

I've found SQLite absolutely amazing for getting the power of SQL for R data frames.

Could you expand on some of your use cases? An R data frame, by definition, has to fit into memory, so it would seem that any sort of map/filter/group/fold operation would be fastest if performed in-memory, as well. And I assumed that e.g. joining data frames (where you would run out of memory really quickly if your datasets are large to begin with) would be uncommon... am I wrong?

Depends on what you mean by uncommon.

I for example often need to score/model data which doesn't fit in RAM (on my PC) so I use libraries like bigGLM which can use out-of-memory data to build the models. One of the options is SQLite, but you can use an ODBC connection.

Additionally, I can explore slices of the data, which resides only on disk. I don't even need to import it. I can use dplyr (very famous package for aggregations and slicing) which will map the R syntax to SQL which is executed by SQLite.

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

#130

Post by talideon »

tracker1 wrote: post #29
cyberferret wrote: post #6

I'm a long time user and lover of SQLite, since way back when. Use it in a lot of our projects (web and Win32) that require local databases for logging etc. Sure for larger or concurrent user access to a db, we use other databases, but nothing beats the 'zero footprint' install of SQLite. I even enjoyed the fact that earlier versions of SQLite forced me to think optimise my queries due to the lack of nested SELECTs.…

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.
Post reply on HN