Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Work on SQLite4 has concluded

sqlite.org

Post reply on HN
Print view View original

31–40 of 161 posts

Re: Work on SQLite4 has concluded

#31

Post by Someone »

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)

Done? Almost every .0 version I checked in https://www.sqlite.org/chronology.html saw some new features.

Re: Work on SQLite4 has concluded

#32

Post by bch »

oh_sigh wrote: post #30
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.

What kind of companies sign 35 year support contracts?

The kind that give away their product for free, give the company to their wife, develop an in-house distributed version control (fossil (which is excellent)), give that away for free and encourage other version control projects to please steal ideas from fossil. drh (Richard Hipp) is an interesting duck.

Re: Work on SQLite4 has concluded

#33

Post by contingencies »

oh_sigh wrote: post #30
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.

What kind of companies sign 35 year support contracts?

Ones that have effectively 100% test coverage on their codebases, massive existing deployments, famous levels of documentation, and world expert level comprehension of their problem space.

Re: Work on SQLite4 has concluded

#34

Post by contingencies »

gigatexal wrote: post #25
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...

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

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

Re: Work on SQLite4 has concluded

#35

Post by Spooky23 »

oh_sigh wrote: post #30
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.

What kind of companies sign 35 year support contracts?

People who sell or support things with long lives where retrofitting is expensive or impossible.

Example: industrial equipment, military stuff, bridges, aircraft, etc.

Re: Work on SQLite4 has concluded

#36

Post by foxylad »

oh_sigh wrote: post #30
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.

What kind of companies sign 35 year support contracts?

SQLite is used for small and embedded systems, which (if successful) can have very long lifetimes. If you were building something like an ATM for instance, you would be very sensible to sign a 35-year support contract for a crucial part of your system.

Re: Work on SQLite4 has concluded

#37

Post by richardwhiuk »

giancarlostoro wrote: post #19
the_common_man wrote: post #17

Anyone know how sqlite makes money?

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.

Re: Work on SQLite4 has concluded

#38

Post by coleifer »

The source tree for sqlite3 now contains an extension named lsm1 that contains both the standalone lsm kv database as well as a virtual table extension which allows you to use it directly from sqlite3. Some info on python integration can be found here:

http://charlesleifer.com/blog/using-sqlite4-s-lsm-storage-en...

In peewee 3.0a I've also added built-in support for using the lsm1 virtual table if you're interested.

Re: Work on SQLite4 has concluded

#39

Post by contingencies »

kamac wrote: post #20
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...

Same here. Had to switch to dockerized mariadb for local tests, because migrations wouldn't work.

Why not work around the expectation and simply migrate offline? (eg. dump DB, hack dumpfile/stream, load new CSV?) While you may lose instantaneous constraint validation, it would almost certainly be faster and allow you to work with known and well tested tools. Conforms to the Unix design philosophy: "Store data in flat text files." / "Write programs to handle text streams, because that is a universal interface." http://github.com/globalcitizen/taoup

Since you were nominally optimizing for migration, a zoom-out perspective may be to note that upgrading SQLite3 versions vs. upgrading major RDBMS versions is trivial/fast, relatively rarely required, also cohabitation of multiple versions works a lot easier, any kind of CI/CD process is going to be orders of magnitude faster and use much less CPU/memory/disk space, which means smaller build artifacts and thus faster transfer/download.

Re: Work on SQLite4 has concluded

#40

Post by makmanalp »

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 on disk at all times: The design of LSM trees works well with databases with a resident in-memory component because it's an approximation of just dumping every new thing you see at the end of an unordered in-memory array. This is as opposed to a data structure like a b-tree where you have to /find/ exactly where to put the data first, and then put it there. This finding bit means you're doing a lot of random access in memory, which is thrashing all of your caches (CPU / disk etc). LSM trees avoid this thrashing by just dumping stuff at the end of an array. However this means you have to scan that array to do lookups (as opposed to something easier like binary search). Then as your array gets big, you merge and flush it down to a lower "layer" of the lsm tree which is slightly bigger and sorted. And when that one fills, you flush further. And these merge-flushes are nice big sequential writes so that's nice too.

Anyway, with SQLite, the highest layer of your LSM tree would probably (this is conjecture) have to be on disk because of the way that there is no server component, versus in an in-memory system it'd probably be in your L2/L3 cache or at least your main memory. So this could be one reason why that model didn't work out as well for them.

Post reply on HN