[HN Gopher] SQLite 3.45 released with JSONB support
       ___________________________________________________________________
        
       SQLite 3.45 released with JSONB support
        
       Author : genericlemon24
       Score  : 181 points
       Date   : 2024-01-15 19:24 UTC (3 hours ago)
        
 (HTM) web link (www.sqlite.org)
 (TXT) w3m dump (www.sqlite.org)
        
       | jitl wrote:
       | From the original forum post [0] announcing this improvement:
       | 
       | > But if you modify your application to start storing JSONB
       | instead of text JSON, you might see a 3-times performance
       | improvement, at least for the JSON-intensive operations. JSONB is
       | also slightly smaller than text JSON in most cases (about 5% or
       | 10% smaller) so you might also see a modest reduction in your
       | database size if you use a lot of JSON.
       | 
       | I for one am excited about these improvements (specifically the
       | disk use reduction) since we store a lot of JSON here at Notion
       | Labs, and we're increasing our use of SQLite.
       | 
       | [0]: https://sqlite.org/forum/forumpost/fa6f64e3dc1a5d97
        
         | emptysea wrote:
         | Curious how you're using SQLite at Notion, do you have anything
         | public?
        
           | jbverschoor wrote:
           | Probably one per workspace/tenant ?
        
       | elpocko wrote:
       | > The internal JSONB format is also uses slightly less disk space
       | then text JSON.
        
       | nalgeon wrote:
       | If you find the official release notes a bit dry, I've made an
       | interactive version:
       | 
       | https://antonz.org/sqlite-3-45
        
       | stabbles wrote:
       | > Fix a couple of harmless compiler warnings that appeared in
       | debug builds with GCC 16.
       | 
       | Some projects use -Werror, only ever test with older GCC, and
       | builds fail with anything recent. SQLite on the other hand
       | anticipates the new compiler warnings of GCC 3 major versions in
       | the future, that's impressive!
        
         | mgaunard wrote:
         | GCC 16!? Are they from the future?
        
           | stefanos82 wrote:
           | I'm sure they mistyped '6' over '3' from numpad; it can
           | happen to any of us.
        
             | mdaniel wrote:
             | While I find your explanation plausible, who has the muscle
             | memory to press "g", "c", "c", spacebar, lift hand to
             | numpad, "1", "6(no 3 sire!)", hand back to home row?
        
       | ado__dev wrote:
       | Very welcome improvement. I overlooked SQLite for far too long
       | relegating it to just a "toy database, not meant for real world
       | apps". Boy was I wrong.
        
       | simonw wrote:
       | If anyone wants to try this out on macOS here's the fastest way
       | I've found to try a new SQLite version there:
       | https://til.simonwillison.net/sqlite/sqlite-version-macos-py...
       | 
       | Short version:                   cd /tmp         wget
       | 'https://www.sqlite.org/2024/sqlite-amalgamation-3450000.zip'
       | unzip sqlite-amalgamation-3450000.zip         cd sqlite-
       | amalgamation-3450000         gcc -dynamiclib sqlite3.c -o
       | libsqlite3.0.dylib -lm -lpthread         DYLD_LIBRARY_PATH=$PWD
       | python3 -c "import sqlite3; print(sqlite3.sqlite_version)"
       | 
       | That prints "3.45.0" for me.
       | 
       | If you have https://datasette.io/ installed you can then get a
       | web UI for trying it out by running:
       | DYLD_LIBRARY_PATH=$PWD datasette
        
         | csdvrx wrote:
         | and the easiest way for those who can wait for the next update
         | will be to get the binaries from
         | https://cosmo.zip/pub/cosmos/bin/datasette and
         | https://cosmo.zip/pub/cosmos/bin/sqlite3
        
         | sgbeal wrote:
         | > If anyone wants to try this out on macOS here's the fastest
         | way I've found to try a new SQLite version ...
         | 
         | https://sqlite.org/fiddle
         | 
         | is always updated as part of the release process and is updated
         | periodically between releases.
        
       | jkljsfdasdf wrote:
       | Embarrasing question tbh but with all the cloud-native sqlite
       | stuff like cloudflare d1 and fly LiteFS I'm seriously thinking of
       | switching from postgres to sqlite.
       | 
       | Does anyone have a compare/contrast sort of thing between the
       | two?
        
       | Retr0id wrote:
       | Trying to store JSON-like data in a way that's both compact _and_
       | fast to operate on directly is a challenge. IIUC this is is
       | something SQLite has wanted to introduce for a while, but it took
       | them some time to find a viable approach.
        
       | mdaniel wrote:
       | I thought I recognized this but its submission URL was goofy;
       | previously discussed:
       | 
       |  _JSONB has landed_ -
       | https://news.ycombinator.com/item?id=38540421 - Dec 2023 (205
       | comments)
        
       ___________________________________________________________________
       (page generated 2024-01-15 23:00 UTC)