[HN Gopher] Exciting SQLite Improvements Since 2020
___________________________________________________________________
Exciting SQLite Improvements Since 2020
Author : thunderbong
Score : 112 points
Date : 2023-04-28 12:52 UTC (10 hours ago)
(HTM) web link (blog.airsequel.com)
(TXT) w3m dump (blog.airsequel.com)
| ilyt wrote:
| I still find it funny that if I write something that's supposed
| to support MySQL/PostgreSQL/SQLite it's almost always MySQL that
| needs tweaking or outright doesn't support something, while
| between PostgreSQL and SQLite it's mostly same SQL
| marvel_boy wrote:
| " Often overshadowed by newer and flashier database management
| systems, many overlook the continued innovation and evolution of
| SQLite."
|
| Absolutely, the last improvements of sqlite are just incredible.
| xhkkffbf wrote:
| I like improvements, but I like speed and light demands for RAM
| or computation even more.
| rektide wrote:
| Of of curiosity, how configurable are sqlite builds?
|
| Can one disable json support, for example? I'm not sure what
| other "categories" of features three might be. Certainly
| there's a lot of builtin functions; how configurable is sqlite
| in picking buultins to omit?
| justinclift wrote:
| > Can one disable json support ...
|
| Yeah, it's pretty configurable, including the JSON support.
|
| Being able to include/exclude things can be done at compile
| time:
|
| https://www.sqlite.org/compile.html - lists the options for
| including/excluding stuff
|
| And there's a function for loading 3rd party extensions at
| run-time too, which itself can be turned off. :)
|
| There's a fair amount of 3rd party extensions too.
|
| eg: https://github.com/nalgeon/sqlean
|
| GIS stuff, encryption support (several varieties), Excel/ODS
| support, and tonnes of other things
| ilyt wrote:
| Excel/ODS support ? I only see csv support in provided
| links?
| bearjaws wrote:
| I briefly looked to see how its footprint has increased, but
| couldn't find anything compelling.
|
| The binary has remained tiny, with most OS's under 2MiB, but
| that won't really indicate memory usage.
|
| Do you have any articles to show its resource usage over time?
| asimjalis wrote:
| Neat use of Haskell for scripting with Stack:
| https://docs.haskellstack.org
| throwawayjs wrote:
| I really love SQLite as a technology. My apps don't require much
| data so it's always been my main choice.
| jgraettinger1 wrote:
| If you're a SQLite fan who does stream processing, we (Estuary)
| recently introduced a capability to write transformations as
| event-driven SQLite [0].
|
| Basically you get a provisioned SQLite DB to which you apply
| whatever migrations you wish, and write SQL lambdas that are run
| with each input document, where your lambdas update your tables
| an/or publish outputs via SELECT.
|
| [0]: https://docs.estuary.dev/concepts/derivations/#sqlite
| OliverJones wrote:
| The clustered primary key (NO ROWID) setup that came in with
| SQLite version 3.8.2 (2013-12-06) makes it very fast indeed when
| used for a large persistent key-value store.
|
| And later improvements have just continue to accrue.
|
| This from a guy who just implemented a persistent object cache
| with it, and was blown away by how well it works. And my
| requirement was all SQLite versions 3.7 and later, so there's
| conditional code. (ROWID or not, UPSERT or not).
|
| Not to mention there are probably ten or more of these databases
| in your mobile phone. We haven't heard, at least I haven't, about
| any monstrous day-1 vulnerabilities in this code.
|
| A really good design choice, SQLite is, if your application can
| live with its local file system requirement.
| dashmeet wrote:
| I wonder how this compares to using redis for key value caching
| purposes?
|
| Definitely reduces the need for another dependency if that's
| your thing and it fits your needs
| bob1029 wrote:
| > Support RETURNING clause on DELETE, INSERT, and UPDATE
| statements.
|
| I really enjoyed this one due to the elegance. We converted a
| bunch of normal methods into expressions because we could write
| them like: public long
| CreateCustomerRecord(string name, string email) =>
| sql.ExecuteScalar<long>(@"INSERT INTO Customers (...)
| VALUES (...) RETURNING Id", new {
| //Param bindings });
| [deleted]
| InfosecIcon wrote:
| [dead]
| kccqzy wrote:
| Older than 2020 but another indispensable feature SQLite added
| was window functions in 2018.
| btilly wrote:
| This is my favorite feature.
| mongol wrote:
| It is interesting to read about it proposed in 2014
| https://www.mail-archive.com/sqlite-users@mailinglists.sqlit...
|
| Hipp:
|
| > You used the word "immense" which I like - it is an apt
| description of the knowledge and effort needed to add windowing
| functions to SQLite (and probably any other database engine for
| that matter).
| swyx wrote:
| why is windowing hard? isnt it kind of a rolling filter?
| tehbeard wrote:
| It allows for recursion in the query (fetching a tree of a
| category structure for instance where it's just a
| id,parent_id,priority tuple)
|
| Handling that efficiently without conditioning the data
| first using something like nested set or materialized paths
| is going to be a challenge when the depth is unknown.
| jFriedensreich wrote:
| "there is a common misconception that SQLite is a stagnant or
| outdated technology" this seems like a strawman argument. there
| seem few software projects with as consistent high levels of
| respect by everyone from seasoned neckbeard to serverless
| hipsters and everyone in between. even in the nosql peak when
| these arguments were maybe heard for mysql or postgres, sqlite
| was mostly spared as it was used as storage engine for more than
| one of the new kids.
| nickpeterson wrote:
| Has SQLite ever attained multiuser functionality or is that still
| the main thing it lacks?
| silvestrov wrote:
| > compiling SQLite to WASM
|
| Is there support for "compiling" WASM to java so we can use
| Sqlite from java without using any JNI library?
| rvcdbn wrote:
| I hope we see more of this sort of use of WASM (creating
| universal libraries). Same argument applies for Go/cgo.
| petercooper wrote:
| There might be a route if using GraalVM which supports WASM:
| https://www.graalvm.org/latest/reference-manual/wasm/
| smallerfish wrote:
| > can use Sqlite from java without using any JNI library
|
| Ah is that a mac thing? On linux, you just set up gradle with a
| dependency for the jdbc driver, and you get sqlite available to
| use without needing to install anything on the OS. It's pretty
| magic.
| wiseowise wrote:
| You're literally doing what parent comment is arguing
| against.
| ilyt wrote:
| He's asking why would you do it the hard way.
| folmar wrote:
| If you do not insist on the on-disk format then H2 is pretty
| much the Java world equivalent.
| kayson wrote:
| A real exciting improvement would be support for NFS, instead of
| hiding behind "some server implementations don't implement
| locking properly" in 2023...
| simonw wrote:
| If you need your SQLite database to be available over a network
| I think you'd do a lot better layering a dedicated network
| protocol on top of it as opposed to trying to get something
| like NFS working, which is evidently a poor platform for files
| that need transactional updates made to them by multiple users
| at once.
| kayson wrote:
| > evidently a poor platform for files that need transactional
| updates made to them by multiple users at once
|
| What makes this the case?
| simonw wrote:
| If it was a solid platform for this I imagine SQLite would
| work already!
| kayson wrote:
| It does work... Sort of. From what I've read, the NFSv4
| server properly implements locking (and I think most of
| those bits are in the Linux kernel now anyway), but
| sqlite won't support it anyway. And I am able to run
| sqlite on NFSv4 with minimal problems. Every once in a
| while I do get a hiccup, but it's not clear why.
| btilly wrote:
| https://access.redhat.com/solutions/120733 explains it. The
| critical bit is the root cause at the end. Which is that
| NFS sees access to any part of the file as access to all of
| the file. So any access locks it for anyone. Therefore
| shared access to the database will cause random hangs due
| to client behavior.
|
| If you turn off locking, then there is no way to avoid data
| corruption.
|
| And this is with NFS working correctly. Which is not a safe
| assumption given that widely used platforms like OS X
| implement it wrong.
|
| In short, there is a reason that we've joked since the last
| millennium that NFS stands for "No File System". And the
| joke is still relevant today.
| kayson wrote:
| Thanks for posting that! I've always wondered what the
| deal is.
|
| How do other file systems do it differently that allows
| for concurrent access to the file?
| btilly wrote:
| https://www.sqlite.org/lockingv3.html explains what it
| wants to happen.
|
| Note in particular that multiple processes can read at a
| time, and only slowly escalate into a write lock which is
| held as short a time as you can before going back to the
| normal state. While NFS assumes that if you read, you may
| write, and may not take care to make sure you have the
| most recent version WHEN you write. (These are all
| important assumptions to make for random programs written
| by random programmers. Few programmers can be assumed to
| take the care that databases do around getting locking
| logic correct.)
| ilyt wrote:
| Eh, I can understand not wanting to deal with NFS fuckery
| wahern wrote:
| Is there something SQLite would need to do to support an NFS-
| mounted filesystem that already supported proper locking
| (fcntl) and sync'ing (fsync)?
| formerly_proven wrote:
| It does work but it also rarely results in hanging locks,
| that's with NFSv3 with NLM. Actual data corruption only
| happened once or twice.
|
| So it's not a super-reliable thing, but when you can't have a
| real database server or you can only make a network share
| accessible to the right groups, say, due e.g. organizational
| dysfunction, then it works, most of the time.
| kayson wrote:
| I've had the same experience. I have a NAS VM with nfs4 on
| Debian, but my services are running on other VMs (since
| they're in a DMZ vlan). A lot of selfhosted stuff runs on
| sqlite since its just easier. So there is only one user per
| db, and 95% of the time I have no issues. Every once in a
| while I get some sqlite i/o error, and most of the time
| it's fine but every once in a while I'll have to restart a
| container.
| simonw wrote:
| Are you running it in WAL mode?
|
| I've had applications (not on NFS, but with multiple
| processes accessing the same SQLite database file at
| once) which throw occasional I/O errors in default
| journal mode but didn't throw errors at all once I
| switched into WAL mode.
|
| https://til.simonwillison.net/sqlite/enabling-wal-mode
| formerly_proven wrote:
| WAL mode requires shared memory (unless you disable
| concurrency through exclusive locking) and therefore
| doesn't work on network shares.
| simonw wrote:
| Great point.
| kayson wrote:
| I think most of the apps use WAL nowadays (at least from
| what I remember when I looked into this a while back) but
| I'll have to check again! Thanks.
| [deleted]
___________________________________________________________________
(page generated 2023-04-28 23:01 UTC)