6 ms·
From my perspective on databases, two trends continued in 2025: 1: Moving everything to SQLite 2: Using mostly JSON fields Both started already a few years b
by TekMol 9mo ago
From my perspective on databases, two trends continued in 2025:
1: Moving everything to SQLite
2: Using mostly JSON fields
Both started already a few years back and accelerated in 2025.
SQLite is just so nice and easy to deal with, with its no-daemon, one-file-per-db and one-type-per value approach.
And the JSON arrow functions make it a pleasure to work with flexible JSON data.
- delaminator 9mo agoFrom my perspective, everything's DuckDB. Single file per database, Multiple ingestion formats, full text search, S3 support, Parquet file support, columnar storage. fully typed. WASM version for full SQL in JavaScript.
- sanderjd 9mo agoThis is a funny thread to me because my frustration is at the intersection of your comments: I keep wanting sqlite for writes (and lookups) and duckdb for reads. Are you aware of anything that works like this?
- SchwKatze 9mo agoI think you could build an ETL-ish workflow where you use SQLite for OLTP and DuckDB for OLAP, but I suppose it's very workload dependent, there are several tradeoffs here.
- sanderjd 9mo agoRight. This is what I want, but transparently to the client. It seems fairly straightforward, but I keep looking for an existing implementation of it and haven't found one yet.
- nlittlepoole 9mo agoDuckDB can read/write SQLite files via extension. So you can do that now with DuckDB as is. https://duckdb.org/docs/stable/core_extensions/sqlite https://duckdb.org/docs/stable/core_extensions/sqlite
- sanderjd 9mo agoMy understanding is that this is still too slow for quick inserts, because duckdb (like all columnar stores) is designed for batches.
- theanonymousone 9mo agoThe way I understood it, you can do your inserts with SQLite "proper", and simultaneously use DuckDB for analytics (aka read-only).
- sanderjd 9mo agoAha! That makes so much sense. Thank you for this. Edit: Ah, right, the downside is that this is not going to have good olap query performance when interacting directly with the sqlite tables. So still necessary to copy out to duckdb tables (probably in batches) if this matters. Still seems very useful to me though.
- dietr1ch 9mo agoAnalytics is done in "batches" (daily, weekly) anyways, right? We know you can't get both, row and column orders at the same time, and that continuously maintaining both means duplication and ensuring you get the worst case from both worlds. Local, row-wise writing is the way to go for write performance. Column-oriented reads are the way to do analytics at scale. It seems alright to have a sync process that does the order re-arrangement (maybe with extra precomputed statistics, and sharding to allow many workers if necessary) to let queries of now historical data run fast.
- sanderjd 9mo agoNot all olap-like queries are for daily reporting. I agree that the basic architecture should be row order -> delay -> column order, but the question (in my mind) is balancing the length of that delay with the usefulness of column order queries for a given workload. I seem to keep running into workloads that do inserts very quickly and then batch reads on a slower cadence (either in lockstep with the writes, or concurrently) but not on the extremely slow cadence seen in the typical olap reporting type flow. Essentially, building up state and then querying the results. I'm not so sure about "continuously maintaining both means duplication and ensuring you get the worst case from both worlds". Maybe you're right, I'm just not so sure. I agree that it's duplicating storage requirements, but is that such a big deal? And I think if fast writes and lookups and fast batch reads are both possible at the cost of storage duplication, that would actually be the best case from both worlds? I mean, this isn't that different conceptually from the architecture of log-structured merge trees, which have this same kind of "duplication" but for good purpose. (Indeed, rocksdb has been the closest thing to what I want for this workload that I've found; I just think it would be neat if I could use sqlite+duckdb instead, accepting some tradeoffs.)
- swyx 9mo agovery interesting. whats the vector indexing story like in duckdb these days? also are there sqlite-duckdb sync engines or is that an oxymoron
- cfors 9mo agohttps://duckdb.org/docs/stable/core_extensions/vss https://duckdb.org/docs/stable/core_extensions/vss It's not bad if you need something quick. I haven't had a large need of ANN in duckdb since it's doing more analytical/exploratory needs, but it's definitely there if you need it.
- andrewinardeer 9mo agoPardon my ignorance, yet wasn't the prevailing thought a few years ago that you would never use SQLite in production? Has that school of thought changed?
- lpil 9mo agoSQLite is likely the most widely used production database due to its widespread usage in desktop and mobile software, and SQLite databases being a Library of Congress "sustainable format".
- zerr 9mo agoMost of the usage was/is as a local ACID-compliant replacement for txt/ini/custom local/bundled files though.
- scott_w 9mo agoOnly for large scale multiple user applications. It’s more than reasonable as a data store in local applications or at smaller scales where having the application and data layer on the same machine are acceptable. If you’re at a point where the application needs to talk over a network to your database then that’s a reasonable heuristic that you should use a different DB. I personally wouldn’t trust my data to NFS.
- kopirgan 9mo agoAs a backend database that's not multi user, how many web connections that do writes can it realistically handle? Assuming writes are small say 100+ rows each? Any mitigation strategy for larger use cases? Thanks in advance!
- TekMol 9mo agoWhy have multiple connections in the first place? If your writes are fast, doing them serially does not cause anyone to wait. How often does the typical user write to the DB? Often it is like once per day or so (for example on hacker news). Say the write takes 1/1000s. Then you can serve 1000 * 60 * 60 * 24 = 86 million users And nobody has to wait longer than a second when they hit the "reply" button, as I do now ...
- frje1400 9mo ago> If your writes are fast, doing them serially does not cause anyone to wait. Why impose such a limitation on your system when you don't have to by using some other database actually designed for multi user systems (Postgres, MySQL, etc)?
- TekMol 9mo agoBecause development and maintenance faster and easier to reason about. Increasing the chances you really get to 86 million daily active users.
- frje1400 9mo agoSo in this solution, you run the backend on a single node that reads/writes from an SQLite file, and that is the entire system?
- withinboredom 9mo agoThats basically how the web started. You can serve a ridiculous number of users from a single physical machine. It isn't until you get into the hundreds-of-millions of users ballpark where you need to actually create architecture. The "cloud" lets you rent a small part of a physical machine, so it actually feels like you need more machines than you do. But a modern server? Easily 16-32+ cores, 128+gb of ram, and hundreds of tb of space. All for less than 2k per month (amortized). Yeah, you need an actual (small) team of people to manage that; but that will get you so far that it is utterly ridiculous. Assuming you can accept 99% uptime (that's ~3 days a year being down), and if you were on a single cloud in 2025; that's basically last year.
- odie5533 9mo agoFor as much talk as I see about SQLite, are people actually using it or does it just have good marketers?
- TekMol 9mo agoAmong people who can actually code (in contrast to just stitch together services), I see it used all around. For someone who openly describes his stack and revenue, look up Pieter Levels, how he serves hundreds of thousands of users and makes millions of dollars per year, using SQLite as the storage layer.
- sgbeal 9mo ago> are people actually using it or does it just have good marketers? _You_ are using it right this second. It's storing your browser's bookmarks (at a minimum, and possibly other browser-internal data).
- SJMG 9mo agoIt's the standard for mobile. That said, in server-side enterprise computing, I know no one who uses it. I'm sure there are applications, but in this domain you'd need a good justification for not following standard patterns. I have used DuckDB on an application server because it computes aggregations lightning fast which saved this app from needing caching, background services and all the invalidation and failure modes that come with those two.
- greenavocado 9mo agoIf you use desktops, laptops, or mobile phones, there is a very good chance you have at least ten SQLite databases in your possession right now.
- CyberDildonics 9mo agoIt is fantastic software, have you ever used it?
- odie5533 9mo agoI don't have a use case for it. I've used it a tiny bit for mocking databases in memory, but because it's not fully Postgres, I've switched entirely to TestContainers.
- randomtoast 9mo agoI would say SQLite when possible, PostgreSQL (incl. extensions) when necessary, DuckDB for local/hobbyist data analysis and BigQuery (often TB or PB range) for enterprise business intelligence.
- DrBazza 9mo agoFrom my perspective - do you even need a database? SQLite is kind-of the middle ground between a full fat database, and 'writing your own object storage'. To put it another way, it provides 'regularised' object access API, rather than, say, a variant of types in a vector that you use filter or map over.
- TekMol 9mo agoIf I would write my own data storage I would re-implement SQLite. Why would I want to do that?
- trevor-e 9mo agoNot sure if this is quite what you are getting at, but the SQLite folks even mention this as a great use-case: https://www.sqlite.org/appfileformat.html https://www.sqlite.org/appfileformat.html
- CuriouslyC 9mo agoI think the right pattern here is edge sharding of user data. Cloudflare makes this pretty easy with D1/Hyperdrive.
- quotemstr 9mo agoFWIW (and this is IMHO of course) DuckDB makes working with random JSON much nicer than SQLite, not least because I can extract JSON fields to dense columnar representations and do it in a deterministic, repeatable way. The only thing I want out of DuckDB core at this point is support for overriding the columnar storage representation for certain structs. Right now, DuckDB decomposes structs into fields and stores each field in a column. I'd like to be able to say "no, please, pre-materialize this tuple subset and store this struct in an internal BLOB or something".
- phendrenad2 9mo agoMan, I hope so. Bailing people out of horribly slow NoSQL databases is good business.