Work on SQLite4 has concluded • Hacker News Board

Re: Work on SQLite4 has concluded

#41

Post by coleifer » Wed, Nov 08, 2017, 12:42 AM UTC

sliverstorm wrote: post #26

Scaevolus wrote: post #3

For context, SQLite4 explored reimplementing SQLite using a key-value store on log-structured merge trees, like RocksDB and Cassandra. I'd be interested to hear why they stopped. Presumably reimplementing SQL on a KV store was seen as not worth it, when applications that are satisfied with an embedded KV store backend (which is much faster and simpler to write!) already have many options.

Everything I hear about SQLite3 always suggests that, essentially, it is considered "done". It does what it is supposed to do with great performance. There is nothing major left to do. If it doesn't meet your needs, pick a different SQL database. Which, while a totally alien concept in the modern software world, is actually a pretty cool thought. (I'm sure under the hood bugs are getting fixed and all)

Check out the changelog...they've added a ton of new stuff. Native json support, completely new full-text search extension, lsm key/value extension, performance improvements. I think they're looking at some changes to locking in the near future as well. Lots of stuff to find if you look.

Re: Work on SQLite4 has concluded

#42

Post by coleifer » Wed, Nov 08, 2017, 12:45 AM UTC

hoodoof wrote: post #15

The biggest thorn I found working with sqlite was the lack of ability to modify columns with ALTER TABLE which was a real pain. Doesn't look like this is fixed in sqlite4 though...

There's an easy workaround: rename the table, create new table, insert into/select from.

Re: Work on SQLite4 has concluded

#43

Post by robotbikes » Wed, Nov 08, 2017, 12:47 AM UTC

Scaevolus wrote: post #3

For context, SQLite4 explored reimplementing SQLite using a key-value store on log-structured merge trees, like RocksDB and Cassandra. I'd be interested to hear why they stopped. Presumably reimplementing SQL on a KV store was seen as not worth it, when applications that are satisfied with an embedded KV store backend (which is much faster and simpler to write!) already have many options.

According to a presentation Richard Hipp did at Ohio State, they just didn't get the same level of performance from a log-structured merge vs. the b-tree in SQLite 3. If I remember correctly, while log structured merges where able to accumulate in memory actual to consolidate writes there was still a number of reads required to support joins etc. So they just determined based upon their testing that the b-tree based code was just faster. I don't recall the exact benchmarks, it is probably on the web somewhere.

Re: Work on SQLite4 has concluded

#44

Post by coleifer » Wed, Nov 08, 2017, 1:02 AM UTC

makmanalp wrote: post #40

Scaevolus wrote: post #3

For context, SQLite4 explored reimplementing SQLite using a key-value store on log-structured merge trees, like RocksDB and Cassandra. I'd be interested to hear why they stopped. Presumably reimplementing SQL on a KV store was seen as not worth it, when applications that are satisfied with an embedded KV store backend (which is much faster and simpler to write!) already have many options.

I've had the chance to hear Richard Hipp talk about SQLite yesterday! He mentioned that the LSM tree storage engine is available as an extension to sqlite3. More specifically, he mentioned that he didn't really get the performance improvements he had hoped for, for insertion-heavy use cases. I think part of this is because of a fundamental limitation of sqlite that it's an embedded database that has to persist data o…

I'm jealous you got to hear Dr. Hipp, that sounds cool. Would love to hear more about the circumstances :)

Regarding the LSM engine, you can find all the relevant implementation details here: https://sqlite.org/src4/doc/trunk/www/lsm.wiki#summary

> The in-memory tree is an append-only red-black tree structure used to stage user data that has not yet flushed into the database file by the system. Under normal circumstances, the in-memory tree is not allowed to grow very large.

Re: Work on SQLite4 has concluded

#45

Post by manigandham » Wed, Nov 08, 2017, 1:03 AM UTC

assface wrote: post #13

Richard Hipp has said that they have signed contracts to support SQLite3 for 35 years. SQLite4 is never going to happen.

I cant even imagine working on the same project for 35 years... let alone how different computing might be after that time.

Re: Work on SQLite4 has concluded

#46

Post by jandrese » Wed, Nov 08, 2017, 1:04 AM UTC

krylon wrote: post #18

assface wrote: post #13

Richard Hipp has said that they have signed contracts to support SQLite3 for 35 years. SQLite4 is never going to happen.

Look at it this way: SQLite3 is (more or less) slowly becoming SQLite4, except for the parts that did not work out. It is not as shiny, but in the long run, you still get all the goodness. Nevermind the name / version number.

It's basically the Perl 5 of the DB world.

Re: Work on SQLite4 has concluded

#47

Post by 8ig8 » Wed, Nov 08, 2017, 1:08 AM UTC

coleifer wrote: post #44

makmanalp wrote: post #40

Earlier quoted context omitted.

I've had the chance to hear Richard Hipp talk about SQLite yesterday! He mentioned that the LSM tree storage engine is available as an extension to sqlite3. More specifically, he mentioned that he didn't really get the performance improvements he had hoped for, for insertion-heavy use cases. I think part of this is because of a fundamental limitation of sqlite that it's an embedded database that has to persist data o…

I'm jealous you got to hear Dr. Hipp, that sounds cool. Would love to hear more about the circumstances :) Regarding the LSM engine, you can find all the relevant implementation details here: https://sqlite.org/src4/doc/trunk/www/lsm.wiki#summary > The in-memory tree is an append-only red-black tree structure used to stage user data that has not yet flushed into the database file by the system. Under normal circumsta…

I stumbled on this video presentation by Dr Hipp recently (may have been from another HN comment). I really enjoyed it. Probably because he seems so enthusiastic and passionate.

SQLite: The Database at the Edge of the Network with Dr. Richard Hipp

https://m.youtube.com/watch?v=Jib2AmRb_rk

Re: Work on SQLite4 has concluded

#48

Post by wongarsu » Wed, Nov 08, 2017, 1:12 AM UTC

contingencies wrote: post #34

gigatexal wrote: post #25

Earlier quoted context omitted.

lack of window functions kill it's usefulness for me.

Official SQLite response http://sqlite.1065341.n5.nabble.com/Window-functions-td77470...

So in essence it's a lot of work for a small functionality gain?

There are some things that are inconvenient to do without window functions, but my primary use for them is simple optimization. Most of the cases where I've used window function in postgres, the query would be trivial to rewrite to use a subquery instead. Window functions always led to a much faster query that scaled much better over large query sets.

Re: Work on SQLite4 has concluded

#49

Post by NelsonMinar » Wed, Nov 08, 2017, 1:14 AM UTC

There's an excellent ~80 minute podcast interview with the sqlite author here: https://changelog.com/podcast/201

Re: Work on SQLite4 has concluded

#50

Post by wongarsu » Wed, Nov 08, 2017, 1:15 AM UTC

richardwhiuk wrote: post #37

giancarlostoro wrote: post #19

Earlier quoted context omitted.

Support contracts according to another comment here. That's usually how open source projects make money as well.

Most established providers resist putting any code into production without a support contact for it.

And sqlite's 100% test coverage makes it really attractive for the kind of customers who have no problem paying for long-term support.