SQLite Release 3.8.11.1 | Hacker News Reader

SQLite Release 3.8.11.1

sqlite.org

197 pointsbillK53 comments

SQLite should be the default way someone manages information on a binary data file, as opposed to just inventing your format from scratch.

I remember on uni when we started defining our own formats, to later on learn about what goes inside a DBMS, and thinking, "Why on earth would I ever do the first thing again?" (not that there's no space for it, but it's not so big).

Also, when I've found a couple of times that a file that I thought had a proprietary binary format was a SQLite file, it was bliss. So much easier to navigate, inspect and work with that a huge XML file or a plain text one.

I think this is somewhat incomplete information, and I say this as a SQLite lover. It depends on your type of data, but both HDF5 and SQLite are more than adequate formats for data, and you should be using one of them.

I think HDF5 is actually a better format for long term data management, but I think SQLite is a easier to use in everyday life. There's also Parquet and Avro for these as well.

High-Energy physics also has the "code is the documentation" clusterfuck known as ROOT files.

This again misses the point. The point is that you should use the right tool for the job. If your application needs a particular format for whatever reason, use that format. For example, could you imagine trying to encode a video file in a sqlite database? Similarly, would you use a sqlite file for a plaintext document (like c++) or a binary executable? To give a more custom example: I was working on a project which tries to build a database of solutions to games (like Connect 4). Connect 4 has such a large search state that using a database is terrible because it doesnt meet the requirements of really fast lookups (with great caching), small size, and fast parallel writing that the solver needs.

Do you have experience with parallel HDF5? It looks like it has been improving a lot as of late, but I can't really test it myself at present.

Great part from the docs:

"SQLite does not compete with client/server databases. SQLite competes with fopen()."

It is indeed one of their use cases: https://www.sqlite.org/whentouse.html

I agree with that for a lot of things, but I would trade SQLite in a heartbeat for a Newton Soup[1] implementation.

1) https://en.wikipedia.org/wiki/Soup_(Apple)

Could you elaborate why? I imagine not many people are familiar with Soup.

Chapter 11 of the Newton Programmers Guide[1] gives a fairly detailed description with pictures. Relational Databases are nice, but I would rather deal in objects (well frames).

1) https://manuals.info.apple.com/MANUALS/1000/MA1510/en_US/New...

This sounds really intriguing, do you know of any open source implementations for other operating systems/programming languages?

I haven't found just a soup implementation (would be interesting for javascript given the prototype objects). NEWT/0[1] is a full NewtoScript but it looks unused.

1) http://gnue.github.io/NEWT0/

Pretty much everything I've built that is standalone uses exactly this model. It's a wonderful bit of tech!

Relevant change log entry:

> Miscellaneous micro-optimizations result in 22.3% more work for the same number of CPU cycles relative to the previous release. SQLite now runs twice as fast as version 3.8.0 and three times as fast as version 3.3.9. (Measured using cachegrind on the speedtest1.c workload on Ubuntu 14.04 x64 with gcc 4.8.2 and -Os. Your performance may vary.)

SQLite is amazing. It is a tech that no programmer should be unfamiliar with. It takes at most a day or two of playing with it to learn all of its various quirks and tricks, is easy to bake in to any project regardless of language, and performs like mad.

Any particular resources you recommend to learn it?

sqlite.org's documentation for the C api [1] and its explanation of what kind of SQL it supports [2] are both very good.

[1] http://sqlite.org/cintro.html

[2] http://sqlite.org/lang.html

If I may, python sqlite interface is pretty great too.

https://docs.python.org/3.4/library/sqlite3.html#sqlite-and-...

The sqlite3 module that comes with python is terrible when it comes to errors. For example, if OperationalError is raised, it could be any of the following SQLite errors:

SQLITE_ERROR, SQLITE_PERM, SQLITE_ABORT, SQLITE_BUSY, SQLITE_LOCKED, SQLITE_READONLY, SQLITE_INTERRUPT, SQLITE_IOERR, SQLITE_FULL, SQLITE_CANTOPEN, SQLITE_PROTOCOL, SQLITE_EMPTY, SQLITE_SCHEMA

How do you know which? The only thing you can do is convert the exception to a string and compare error messages. That's absurd.

How about IntegrityError? That's either SQLITE_CONSTRAINT or SQLITE_MISMATCH. Which one? Compare strings again.

If you need to use SQLite with python, apsw actually converts errors to discrete exceptions.

(I am the APSW author). The reason why the sqlite3 module (aka pysqlite) behaves the way it does is because of DBAPI (Python standard database API). That tries to make all databases look and behave the same. pysqlite could do more - eg the SQLite error code could be an attribute on the exception, but the code base is very rarely updated.

Wanting a Python wrapper for SQLite that celebrated SQLite's features, functionality and semantics rather than paper them over is why I started APSW a decade ago. It also has considerably better testing and diagnostics, and is thread safe.

http://rogerbinns.github.io/apsw/pysqlite.html

Thank you for your work on APSW, it's a fantastic library. I'm the author of the peewee ORM[1] and I've included an APSW backend[2] for folks who want to use peewee with APSW. Being able to write extension modules in Python is such a cool feature! I also made a demo that exposes Redis as a SQLite table[3].

[1] http://docs.peewee-orm.com/

[2] http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#a...

[3] https://github.com/coleifer/peewee/blob/master/examples/redi...

Hmm I liked the python types embedding and never really worried about dealing with errors that way that much but you're right that should be improved. Thanks for the suggestion.

If you’re not familiar with RDBMS, general introductions to SQL and relational algebra should be useful. Back in uni our textbook was Fundamentals of Database Systems[1]. (It’s okay, but there are probably better books out there.)

[1] http://www.pearsonhighered.com/educator/product/Fundamentals...

http://sql.learncodethehardway.org/

When I was tinkering with http://thredis.org I spent a lot of time looking into SQLite internals, and it is by far the most polished, comment-rich code base that I have ever seen, it reads like a fun book. Especially if you're interested in low-lever relational db stuff - how SQL statements are processed, data is stored, B-Trees, etc. It's better than any book out there (and such books tend to be very expensive).

I wrote a post a while back singing the praises of SQLite. Some of you may be interested:

http://charlesleifer.com/blog/sqlite-small-fast-reliable-cho...

Great, write up convinced me to use Sqlite instead of Mysql for a simple in-house webpage.

I'd like to see the same speed measurements done for mobile. A 64-bit Linux desktop isn't representative of the #1 use case for SQLite as a mobile database replacement for Core Data.

I've never worked with Core Data, but I assume it always uses the SQLite version that ships with iOS (as opposed to a version you ship with your app), so that means that you wouldn't see these improvements on iOS for at least a year or so anyway, or am I wrong about this?

Building and linking your own sqlite.c is completely feasible. (We do it).

Yes, that's something that I do as well, but will Core Data pick it up, or will it use the one that ships with the system?

I imagine it's showing in benchmarks that approximate mobile usage might actually influence that timeline slightly.

sqlite existed before iOS and I doubt that platform is its "#1 use case". Sqlite powers tons of apps on many different platforms.

The post you're replying to doesn't mention iOS, just mobile (though it may have been edited). Anyway SQLite is heavily used in Android and well, and other mobile platforms going back to all but the early versions of Symbian even. It's been the standard for address book, calendar, settings, to-do apps, etc for over a decade. A typical smartphone probably contains around half a dozen SQLite DBs right out of the box, which means the building I'm in probably houses several thousand of them. The total number of DBs in the world must be in the tens of billions, just from mobile devices.

parent says "Core Data", hence iOS. iOS alone is certainly not the #1 case, since Android has bigger numbers as you know. I just thought it smacked a bit of "Apple bubble", that's all.

Actually, I wouldn't be surprised at all, given that it seems to be the canonical on-disk data store for both Android and iOS. Whether you want to measure total uses by developers, the developers themselves, the users using it, the apps using it, the size data stored, I imagine mobile use probably swamps everything else.

For example, I probably have 20-30 sqlite using apps on my phone, if not more. And I wiped it clean when I got a new phone a few months back and have been fairly hesitant to add apps I haven't found proven use for.

>I'd like to see the same speed measurements done for mobile.

While not exactly what you are looking for I can understand the sentiment. SQLCipher provides a project to time the performance of queries when run against standard SQLite vs. SQLCipher on iOS.

https://github.com/sqlcipher/SQLCipherSpeed

Why should it not be similar faster on mobile?

No only different instruction set, but probably drastically different cache hierarchy and branch predictors. The tool cachegrind which they are using is simulating a program's effect on the cache and branch predictor, which will likely both be dramatically smaller in embedded devices vs modern server CPUs, so the results may be very misleading to real world applications for sqite.

Also multicore memory hierarchies are very different on ARM -- e.g. atomics are much more expensive there in my experience.

Different instruction set. The release notification isn't very clear about what optimizations took place. It is quite possible that it will not be the same result on a different architecture.

Even moreso, different cache sizes. Some object that might fit nicely in a 4kB cache might get destroyed with a 1kB cache.

He never said it shouldn't, just that he would be interested in seeing the numbers. I would be too since they would be more relevant to my use case.

SQLite is one of the rare examples of very elaborate implemented software that is very good maintained, too.

One of the best pieces of free software around.

Where else do you find software, that was very good implemented from the beginning and is so carefully enhanced and optimized? (And not only for new "features") -- I remember no official available example.

And SQLite is also one of the most tested pieces of software in the world: https://www.sqlite.org/testing.html

SQLite is the ultimate blackbox. Reliability beyond compare

We use SQLite extensively in Minuum and I couldn't be happier. It's in all our critical real-time code paths that bring up candidates while you're typing. Because we're on SQLite our predictions keep getting faster for free!

Faster predictions mean we can handle more types of errors (more missed characters, more complex typos) in the same time budget. Thanks SQLite, you guys are awesome.

The change in title hides the main point (~2x speed increase) and makes it appear as a simple bugfix release.

You would think with such a performances boost they would have put it first in the change-log, but no it's hidden at the end.

One thing for sure, they never worked for AMD or Nvidia marketing department ;-)

One of the great things about SQLite is its incredibly comprehensive automated test suite. That allows aggressive optimization without fear you're breaking something.

I still remember when Firefox switched to using SQLite for most of its internal data. It felt like it took 3 or 4 releases to work out the concurrency and blocking issues. I remember back in the day it had lots to do with fsync.

Has most of that been worked out between SQLite and newer Linux kernels, or is that a pit waiting for other applications that go the same route?

I wrote up a quick blog post describing how to build the new FTS5 search extension, which was included in the 3.8.11.1 release:

http://charlesleifer.com/blog/building-the-sqlite-fts5-searc...

A little off topic, but is there any benchmarking software to compare embedded databases? For instance SQLite vs H2.

Anybody heard anything about SQLite4?

Reply on news.ycombinator.com