9 ms·
Discussion on why SQLite is gaining popularity [audio]
- RamblingCTO 2y agoHonestly, I think it's just hype and people jumping on the band waggon that don't have a lot of experience yet? It has it's places, and for that it's awesome, but an app db? Come on
- freeqaz 2y agoI would love to read this in transcription. I find my attention span for videos is less than for text. Does anybody have any other info on this topic?
- fatboy 2y agoThere's one on that page: https://syntax.fm/show/779/why-sqlite-is-taking-over-with-brian-holt-and-marco-bambini/transcript https://syntax.fm/show/779/why-sqlite-is-taking-over-with-br...
- deskr 2y agoI wonder what they used to generate that transcription?
- iv42 2y agoI was kinda wondering too, and did a (very shallow) dive into the JavaScript on that page. I'm almost positive they are using Deepgram(dot com)'s speech-to-text service. I ran whisper.cpp on that audio file on my laptop, and it does a reasonably well job too.
- cb33 2y agoThey discuss their transcription process at 23:15 in episode #621 https://syntax.fm/621?t=0:23:15 https://syntax.fm/621?t=0:23:15
- justinclift 2y agoThat transcription seems to be really bad. It's full of strange errors. :(
- the_duke 2y agoSQLite has been the most used DB for quite a while, by orders of magnitude. It's in every browser, on every phone, lots of desktop apps, .... Now there are a bunch of new companies that also want it to take over in the more traditional domains. So far I don't see a lot of uptake. It's still vastly inferior to the more entrenched databases. Both in terms of performance - especially under concurrency, and in available featureset.
- biorach 2y agoI think the point is that there are surprisingly many applications where the advantages outweigh the issues with concurrency and featureset.
- hruk 2y agoIndeed. I've found that as long as you store your database on a modern NVMe drive, you can easily push 5K+ write transactions per second with SQLite. I've worked at very large companies who received an order of magnitude fewer writes than that, and only during peak business hours.
- colechristensen 2y agoOver the years i have seen extraordinary many database instances doing exactly nothing down to a very small margin of error.
- bux93 2y agoI wonder if the Intel Management Engine, which famously runs MINIX on just about every Intel motherboard since 2008, uses a database.
- simondotau 2y agoBut that would be merely one instance per machine. The number of truly distinct usages of SQLite is probably an order of magnitude greater than the number of machines in use.
- 2y ago
- DoItToMe81 2y agoSQLite is nice and simple, but I don't think I'd ever use it for a larger project I knew was going to have a sizable database.
- lupusreal 2y agoSQLite can reasonably handle databases as large as you can fit onto your machine. The biggest limitation is needing to serialize your writes.
- carbonatom 2y agoFor me the biggest limitation is replicating the SQLite across machines. If my app is running on multiple nodes, then we need to write/use some tooling to replicate the database file across nodes. With that comes the problem of figuring out how we want to handle error scenarios like failed replication, partial replication and such other things. And these are all hairy problems. At that point it might be just simpler to use a centralized Postgres or a proper distributed database.
- zarzavat 2y agoTrue but it lacks the tools you’d want to have to deal with larger databases, for example everything is one file and no table-level locking. You can split into different databases and use ATTACH but at that point you might as well install Postgres. SQLite really shines when you know that a database can only get so large, let’s say you have a paid product that is only ever going to have a moderate number of users.
- deskr 2y agoSQLite in its current form is not going to "take over". But it's the right tool for so many instances where Postgres/MySQL/... were a massive overkill but were the only thing available in common use.
- kvgr 2y agoExactly, i have website that is visited by couple people a day and I just want to store some data on every action in one table + some settings in DB. Perfect for SQLite and anything else if overkill. Also if I ever want to rewrite it in the future its not a big thing, its just a database.
- creshal 2y agoI have a website that's visited by a couple thousand people a day, but since it's not a bloated mess that writes to the DB for every page hit, it's still faster than any competition site in that particular niche, and those deploy to expensive DB clusters to handle their spurious writes. :)
- da02 2y agoAre you able to disclose the URL of the website? How often are you writing to the database? Every few seconds or minutes?
- jddj 2y agoNot OP but just checked the logs for one that I'm running. As a first approximation I grepped for PATCH in the web server logs and it seems to service about 30 of those per second on a $40/month vps. I haven't load tested it so not sure how high it could go. That's not including any POSTs etc which may also generate DB writes. Bear in mind this isn't particularly performance tuned either as the webserver uses go (slow in general) and the portable go sqlite3 driver (also slow)
- sofixa 2y ago
- throwaway38375 2y agoI love SQLite, so please don't think that I'm a SQLite hater, but: I've never seen SQLite used in a setup which multiple machines connect to the same database over a network. For example: A web application with a web server, a worker/job server, and a database server. In these instances MySQL or PostgreSQL seem to be much better choices. So will SQLite ever be able to "take over" in these scenarios?
- colechristensen 2y agoYou can have a backend talking to sqlite and everything else interacting with backend apis
- shiroiushi 2y agoWhat's the point of this? If you have multiple applications on multiple systems accessing the same DB, it seems to make more sense to just use PostgreSQL, since it's specifically designed for concurrent operation like this, instead of trying to handle this in your own custom backend code.
- rcxdude 2y agoIt's not a particularly unusual situation: it's very common for a database to effectively be entirely owned by an application which manages its own constraints on top of that database. In that circumstance sqlite is pretty interchangable with other databases.
- geraldwhen 2y agoIt’s not unusual but is never performant. Adding an api layer and network hops on what should be a database shard or view is why enterprise software sucks so much ass. Why does the api take 3s to respond? Well it needs to call 6 other apis all of which manager their own data. The problem compounds over time. APIs are not the way to solve cross organization data concerns.
- OskarS 2y agoThe killer feature for me with SQLite is as an application file format: https://sqlite.org/appfileformat.html https://sqlite.org/appfileformat.html It has SO MANY advantages over every other solution. It can do the "just a bundle of files" thing, but it can also do all this rich data as well. And unlike most "just a bundle of files" formats, it's incrementally updatable, it's so good. We're using this in production right now and couldn't be happier with it, wouldn't dream of using anything else at this point. One notable application that uses SQLite for this is Audacity: all the stuff you record is streamed into a SQLite database (the project file). This is a huge reason why Audacity is so good for recording long sessions, and why it's so resilient against crashes and failures.
- therein 2y agoI don't use Audacity often, maybe once or twice a month at most but stability and snappiness aren't really things I associate with Audacity. None of the crashes I ever encountered with Audacity has been due to SQLite of course. If anything this article made me want to use SQLite in more of my projects where I need to store local data.
- bux93 2y agoThe resiliency against crashes offered by it's SQLite file format is much needed. Because it crashes a lot.
- sureglymop 2y agoI agree. And the best feature for me is "ATTACH DATABASE". Then one can just join together tables from multiple files etc.
- romwell 2y agoOnce upon a time, I revamped a binary file format for a propriety industrial-scientific software. The software was writing vast arrays of numbers into a file, and reading them based on offsets. As the software (and its data) evolved, versioning became a nightmare. I ended up writing an ORM serializer which read from/wrote into an SQLite DB with a variation on an EAV schema. One of my favorite projects, result-wise. Reading and writing was clean and efficient; the format was portable, language-agnostic, compact. Versions of the new format were backwards- (and, within reason, forward-) compatible with each other (I forced specifying sane defaults for attributes if they're missing). I'm still thinking of reimplementing a project like that as FOSS one day. Serializing numeric data to something like JSON is a waste, and the structured binary formats I've looked at aren't nearly as thought out as SQLite.
- __natty__ 2y agoI like the idea of SQLite, even more with tools like Litestream [0] when the database is replicated - in this case, just continuously backup to S3 (and from S3 during startup). I found often for my and my friend's hobby projects, single server instances can be optimized easily enough to handle small and medium traffic. In such cases, I don't even bother with a fully managed database because SQLite covers all the needs and when it's located on the same machine as the server is extremely fast [1]. [0] https://litestream.io/ https://litestream.io/ [1] https://www.sqlite.org/speed.html https://www.sqlite.org/speed.html
- ksynwa 2y agoThere are some configurations that can allegedly allow SQLite to be more suited to higher and more concurrent load. I have never had to use them but people have written about it like at: https://kerkour.com/sqlite-for-servers https://kerkour.com/sqlite-for-servers
- ammo1662 2y agoWhen I start a project, my priority will be SQLite>PostgreSQL>Others. If the application only runs on one server, then definitely SQLite. That means you do not need fail over, replication or other fancy features. Why not simple SQLite? A complex system is more likely to have problems. Network, TLS, runtime, your application may have so many reasons to crash. But for SQLite, just check IO. If it does not have problem, then you check your application.
- chipdart 2y ago> If the application only runs on one server, then definitely SQLite. That means you do not need fail over, replication or other fancy features. Why not simple SQLite? I agree. So many usecases require a bounded context, and even ephemeral data stores. Memory caches are all the rage but the cost of doing network calls to fetch data from a memory cache can eclipse the cost of simply fetching the data from a local disk.
- ammo1662 2y agoI experienced that an application randomly throw exceptions when trying to access the SQL Server in the same PC. And finally we found that it is caused by a broken firewall software. A traditional DBMS is a quite complex application, sometimes using that may introduce more bugs than your application itself.
- TheRoque 2y agoWell, since the SQLite database is one simple file, isn't SQLite a hassle when you have some concurrency/parallelism ? And what if you have separate services that need to access the same DB ?
- adamtaylor_13 2y agoNope! That’s what the write-ahead log (WAL) is for: https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html Of course, if you experience scaling issues there (you probably won’t if it’s anything less than enterprise-level usage), you can always just add a second db file!
- pprotas 2y agoThis episode is more of an ad for their cloud offering, rather than talking about SQLite and the reasons for the hype.
- wg0 2y agoAnd at that point, if you are already talking to network over TCP then why not talk to Postgres or MySQL. The beauty of SQLite is single process, same process simplicity and there are lot many cases where SQLite is more than enough. Basecamp is using SQLite in their on premise offering.
- grahar64 2y agoI have just refactored a phone app to use SQLite from a previous in memory model and it is so much faster than I need. I could use a few more features, but already so amazing.
- madeofpalk 2y agoSQLite is taking over? SQLite has already taken over. <10 years ago.
- 0x_rs 2y agoSQLite has been the "get X thing done, quickly and effectively" for a very long while now, while far too many would keep chasing the shiny new fads with not as much solid footing and not as boring. There's still that lingering webscale effect on its reputation unfortunately. Not to say it's appropriate in every case, and many of its functions are incredibly unwieldy as opposed to postgres' to mention one, but that's something you can live with if it any of its great upsides apply to you. What seems to be taking over now are services that bolt stuff on top of it, some of which end up being valuable FOSS additions, others may as well just be proprietary hosted instances.
- mihaic 2y agoAll the recent attempts to monetize SQLite seem wrong to me, since its very core is about how to handle data without any third parties, embeded in your app and mostly on local storage. Any cloud offering feels to me like offering a custom STL as a SaaS: if you're going over the network, why not use a database designed for this? The only usecase I can see would be something like mini-DBs in an S3, which again would just be a library to abstract away.
- killingtime74 2y agoI mean it's not by the Sqlite authors. That like opposing someone "monetizing" any open source library by using it?
- mg 2y agoI wonder how much of a factor the "One DB, one file" concept is in SQLite's success. I always loved that an SQLite DB is just a single file. People say "Who cares how many files the DB is?". But in practice, I always found it to be very convenient. It makes the concept of a project easy to grasp when the data store is simply a file. I recently realized that a Django project can also be done in a single file. That made me like Django even more. I have the feeling that this type of logic and simplicty is indeed a factor in the long term success of a software project.
- TeMPOraL 2y agoI think it's the same reason Windows feels easier than Linux to manage software in, or why Docker has the extremely fast and wide adoption it enjoys, or why so many of us hate working with modern web stack. Windows software is often enough "portable executables" that can run off the folder they were unzipped into, and historically, even installed apps could often enough be copy-pasted from their installation folder into another machine and Just Work (it was helpful, in particular, that all the DLL files lived next to the executable instead of being installed system-wide). Docker, obviously, because it eliminates all the bullshit of installing software - again, especially painful on Linux systems - and gives you a single file that drives it all. The platform itself may be system-wide, but the thing you care about: individual software - is contained (literally) so it doesn't spill out. Easy to reason about. Modern web stack is a mess of tooling, some of which system-wide (so people will reach for Docker to contain it all!), with huge version churn and lots of moving pieces. Source code of your app doesn't feel like a major component of it; it's useless and unparseable without chains of finicky tooling. That's in contrast to "vanilla JS", old-school experience, where source files were the only thing that mattered. No transpilation, no build chains. You could fire a site up straight from your hard drive, or FTP/SCP it to a file host, and It Would Just Work. Same with SQLite. To this day I don't like RDBMSes, because they're a system-level platform designed for admins, while all I care about is the RDB part. My database. SQLite maps conceptually to how I think about it - one database, one file (+/- WAL). One well-defined place my data lives, that I can move around and between machines using regular file operation tools. Etc. Files over apps, always. EDIT: This also makes me very eager to try doing something with RedBean[0] - a webserver in a single Actually Portable Executable[1] that's also a ZIP file into which you put all the data you want it to serve. A turn-key website in a single file! -- [0] - https://redbean.dev/ https://redbean.dev/ [1] - https://justine.lol/ape.html https://justine.lol/ape.html
- deleted 2y ago[deleted]
- adamtaylor_13 2y agoIf you want a good head start with SQLite, Aaron Francis and Stephen Margheim are 2 big voices in the space that have helped me learn a ton!
- haolez 2y agoHow do people use it on bigger deployments? Do they manage to make a single "instance" work for enormous volumes? Do they make one database per customer shenanigans?
- masfoobar 2y agoI have become fond of Sqlite in the last few years. I use it for client applications that need to store local data -- normally temporary data to send to the server, etc. I am now using it for a Message Queue middle-man (broker) program I wrote, which uses sqlite to store the messages. They tell us the queue they belong to, if there is a delay before handing out to a Worker, the current status, etc. It has worked very well. The other reason why I was not concerned using sqlite was because the program would do one thing at a time.. so there would not be multiple requests trying to insert/update data at the same time. To me it was a win-win. Designed to be fast and light. Thumbs up to sqlite team!
- tambourine_man 2y agoSQLite took over many years ago. It's one of the greatest pieces of software out there. Is there something new?
- gregors 2y agoHaving worked with sqlite in an enterprise production environment for vehicles many years ago, it's gotten much better over the years. It's also gotten a performance boost as everyone now uses SSD's. See Faq for details where they still mention rotational speeds and inserts etc. With WAL mode Sqlite is much much better, in the past we had all kinds of problems with trying to read/write at the same time. At that point in time every table was a separate database as a workaround. https://stackoverflow.com/questions/1005206/does-sqlite-lock-the-database-file-on-reads https://stackoverflow.com/questions/1005206/does-sqlite-lock... https://www.sqlite.org/faq.html#q19 https://www.sqlite.org/faq.html#q19
- lakomen 2y agoI prefer a dedicated database server over some 1 file solution. You people have got to be kidding, or aren't professionals. Well HN has never been one to make a whole lot of sense. What's next, plain text file for saving data? Ridiculous
- OutOfHere 2y agoWell, if you're not the one paying for it, then sure, why stop at a dedicated server? Ask for a redundant multi-region multi-node cluster for your database. Who cares how much or how little value it delivers. Ask to hire a whole db admin team plus a devops team. It's not coming out of your paycheck.
- IX-103 2y agoSome people are using a database for a single user, on a single machine. They don't want or need 1000+QPS, live failover, or massive concurrency. They just want something simple that works. The Chrome browser stores many pieces of the user profile (bookmarks, browsing history, etc) in SQLITE databases. I'm not sure if you consider that "professional".
- iansinnott 2y agoNot strictly related to the podcast, but if you'd like to have a local-first browser application running sqlite check out wa-sqlite. It can use both IndexedDB and OPFS as a backend filesystem. [1]: https://github.com/rhashimoto/wa-sqlite https://github.com/rhashimoto/wa-sqlite
- bastien2 2y ago[flagged]
- Sytten 2y agoI am a big user of SQLite, we use it everywhere in our software. But I think I would like something in between Postgres and SQLite. A few issues I have with it: - Migrations are a real PITA, you often need to copy whole tables over since the DDL available is so limited. Even basic things that are not breaking change like dropping a foreign key is not possible - Single file doesn't scale that well, you will see select performance degradation, vacuuming will be extremely long/take a lot of disk space. We started splitting our databases in multiple files and using ATTACH but you lose a lot of nice things like FK support. - No parallelism except on sorting, this one is annoying when you have compute heavy select (like searching on compressed data). It will max out a core but it can't spawn workers for the select. It can for the ordering part so it's not really a "technical" limitation. Still grateful it exists though.
- fiddlerwoaroof 2y agoFirebird database can run both as an embedded database and as a server. I’d like to see something like Postgres adopt this model.
- deleted 2y ago[deleted]
- jdthedisciple 2y agoAnother big advantage: Super easy to backup