Hacker News Reader

Comment by ponytech | Hacker News Reader

By "50% faster" what they actually mean is this new version requires 50% less CPU cycles than the former for doing the same task. In real world I think you will not get your program to run "50% faster": disk io is often the bottleneck in the database area.

View on HN

disk io is often the bottleneck in the database area.

SQLite usually gets used in situations where the data easily fits into memory (multiple times). It isn't really postgres ;)

Right. But by no fault of Postgres either because disk-based io offers durability and in-memory classically does not. Apples and oranges.

In fact all the recent discussions of "performance" for RDBMSs has to be understood in terms of "what's being sacrificed for one or more aspects of ACID?". RDBMS technology is incredibly mature and optimisations that keep the ACID aspects intact are also incredibly difficult now. Marginal returns.

This is no where near true, might be true for spinning disks, but we haven't even really started to optimize RDBMSs for SSDs.

Also sqlite is IO and ACID compliant, it's just that the data generally fits in RAM where as many Postgres installs have data that doesn't fit in RAM.

You're of course right, what I meant to say: In the typical use cases of sqlite the whole db content usually is cached in RAM anyway, so 50% faster really is 50% faster. I found that out quite recently when researching whether caching pythons sqlite in a web app prototype would make it faster: It won't, It's usually completely cached anyway.

There is still plenty of room for improvement. See eg http://arxiv.org/abs/1210.0481

Percentages are multiplicative:

50% fewer cycles for the same work would be 100% faster.

50% more work for the same cycles would be 33% fewer CPU cycles for the same work.

50% fewer cycles might also mean, not faster at all, but your CPU stalls/idles more.

Which can lead to better battery usage or the ability to use slower CPUs.

Which I believe is in fact the case for many of these improvements as per discussion on the thread.

True, but as SQLite is heavily used in mobile applications it still has the advantage of consumes less CPU and thus less battery.

Strictly speaking they only claim 10% improvement from the previous release, the 50% improvement is from a release 16 months ago.

SQLIte mostly runs on slow CPU devices with relative fast SSDs. Reads are parallel, but writes are single threaded, so there is no advantage from multi core ARM CPUs.

The in-memory mode is also the default for many APIs. You can basically just use it like a collection for your language of choice but use SQL for putting and fetching things from the collection.

Sqlite mostly runs everywhere, especially on normal desktop PCs, including all those old ones with slow HDDs and relatively powerful CPUs.

But I don't get the ARM CPU remark, did parent change his comment?

Not for the same database obviously, but SQLite databases are cheap. If your data can be split into different databases, you can pull off parallel writes.

Reply on news.ycombinator.com