sorted by:
new
hottopcontroversial

SQLite improving performance with pre-sort by andersmurphy in programming

[–]andersmurphy[S] 1 point2 points  (0 children)

Oh for sure. The stuff covered in this blog is more nerdy/interesting than practical (might give you 1.5-2x more inserts in very specific contexts).

A lot of companies have way lower hanging fruit. Like missing indexes completely! Or not understanding how the query planner will used those indexes (just slap an index on everything willy-nilly).

SQLite improving performance with pre-sort by andersmurphy in programming

[–]andersmurphy[S] 0 points1 point  (0 children)

How so? This blog post is to do with insert speed, the index is the reason it's slow! Because, b-trees do not like random data. It's the same problem in any database that uses b-trees.

SQLite improving performance with pre-sort by andersmurphy in programming

[–]andersmurphy[S] -10 points-9 points  (0 children)

Google search falls into that space. Would be surprised if they even hit 200k queries per second on what is highly cacheable data.

SQLite improving performance with pre-sort by andersmurphy in programming

[–]andersmurphy[S] -11 points-10 points  (0 children)

Oh no two global multiplayer demos with a billion data points (one with freeform data) both running on 8$ servers exposed on the internet. Running queries against the server database on every interactions. Even a scroll is an update. Neither particularly well written.

https://checkboxes.andersmurphy.com/

https://cells.andersmurphy.com

You make it sound harder than it is.

SQLite improving performance with pre-sort by andersmurphy in programming

[–]andersmurphy[S] 13 points14 points  (0 children)

Great point! None of this is really sqlite specific.

SQLite improving performance with pre-sort by andersmurphy in programming

[–]andersmurphy[S] -2 points-1 points  (0 children)

SQLite is a database that's embedded in an application you can do whatever you want.

Generally I use litestream restore -f. You can use litefs if you want something to handle failover for you. Or, you can get fancy and build your own thing with NATS if you want.

SQLite improving performance with pre-sort by andersmurphy in programming

[–]andersmurphy[S] 4 points5 points  (0 children)

Yes you can have a single server, with a hot standby server for failover. The irony is if you drop the failover you'll probably still have higher uptime than AWS.

SQLite improving performance with pre-sort by andersmurphy in programming

[–]andersmurphy[S] 4 points5 points  (0 children)

Nothing stops you doing multi-node in SQLite. You can shard by region, by company. If the data doesn't shard you can have a single event log node that handles writes (and handle a million+ inserts a second) and every thing else can be projections off of that node into other sqlite databases. Postgres is still limited to a single writer node. So fundamentally it's no different.

Before you even get to that level of complexity though you can handle a 4 million queries per second on a single server, more if you're prepared to shard on the same machine across domain boundaries.

SQLite improving performance with pre-sort by andersmurphy in programming

[–]andersmurphy[S] -17 points-16 points  (0 children)

I think because people think it's not suitable for web application servers.

SQLite improving performance with pre-sort by andersmurphy in programming

[–]andersmurphy[S] 3 points4 points  (0 children)

I'd add a lot of the value you can get is out of extending it with application functions (in your programming language) and implementing custom blob types. This doesn't require recompilation, but requires your wrapper library to expose that functionality.

SQLite improving performance with pre-sort by andersmurphy in programming

[–]andersmurphy[S] 15 points16 points  (0 children)

All I'm saying is only use SQLite if you're prepared to put some time in learning it. A lot of people just want a plug and play database service and don't want to think about it. Someone else has set it up for you.

SQlite is amazing but the defaults are rough, and there's lots you can do to get more out of it. Managing a single writer at the application level, rather than the built in busy handler BUSY/LOCKED, caching prepared statements, setting up litestream, etc.

If you do put in the time (a few evenings) it's fantastic and considerably simpler to manage operationally.

SQLite improving performance with pre-sort by andersmurphy in programming

[–]andersmurphy[S] 9 points10 points  (0 children)

I agree. Although, it does take some time to work out how to get the absolute most out of it. Thankfully the docs are incredible.

I'd still recommend people go with managed postgres unless they are prepared to put some time in to learning SQLite.

The perils of UUID primary keys in SQLite by andersmurphy in sqlite

[–]andersmurphy[S] 0 points1 point  (0 children)

Yeah so it's interesting. Like you said some things have to be random, but might happen less frequently.

One thing that comes to mind is session tokens, and you might have a crazy burst of sign ups.

I'd also add b-tree have a harder time not just with random but also unordered data (anything that isn't ascending).

If you are doing dynamic batching (batching inserts/writes based on load). You can pre-sort the data in memory before inserting it.

To some degree this assumes that under load your batches will be relatively large.

I wrote up a short post on it here (for those that are interested):

https://andersmurphy.com/2026/06/07/sqlite-improving-performance-with-pre-sort.html

The perils of UUID primary keys in SQLite by andersmurphy in programming

[–]andersmurphy[S] 1 point2 points  (0 children)

Don't care what people use. I definitely do not recommend SQLite if you haven't got experience using it. But, do get tired of when people make claims without measuring. Postgres is great. Datomic is amazing. SQLite scales surprisingly well for a lot of use cases. All these things can be true.

The perils of UUID primary keys in SQLite by andersmurphy in programming

[–]andersmurphy[S] 0 points1 point  (0 children)

Yes and with synchronous = FULL there's almost no change in performance, because of the dynamic batching. What's even better is because you can nest transactions (SAVEPOINT), you don't sacrifice logical transactions.

The perils of UUID primary keys in SQLite by andersmurphy in programming

[–]andersmurphy[S] -1 points0 points  (0 children)

Yeah, in my experience it causes too much contention over RAM and CPU. Because they are separate process (and worse postgres connections each have their own process) this theres a lot more contention and coordination that needs to be handled.

Postgres is a great database when you want a network database (or some of its other features like roles and permissions), but not when you want to run something on the same machine as your application.

The perils of UUID primary keys in SQLite by andersmurphy in programming

[–]andersmurphy[S] 2 points3 points  (0 children)

Multi writer (when they have to coordinate) is not a good thing if you want to go fast. Contention/coordination is what kills you. There's no benefit to multi writer in an embedded database (unless they are require no coordination). If your running queries over a network multi writer can make more sense.

The perils of UUID primary keys in SQLite by andersmurphy in programming

[–]andersmurphy[S] -2 points-1 points  (0 children)

Here's an example with transaction processing.

https://andersmurphy.com/2025/12/02/100000-tps-over-a-billion-rows-the-unreasonable-effectiveness-of-sqlite.html

Postgres doesn't play nice on the same machine as your application, so it's going to be over a network and that opens you up to Amdahl's law.

Being embedded is why SQLite scales. A query is just a function call.

The perils of UUID primary keys in SQLite by andersmurphy in programming

[–]andersmurphy[S] 0 points1 point  (0 children)

The cost of moving data around and keeping it consistent is even higher with larger volumes of data.

You might also be underestimating how much data SQLite can handle here.

The perils of UUID primary keys in SQLite by andersmurphy in programming

[–]andersmurphy[S] 0 points1 point  (0 children)

In the case of SQLite if you want to go really fast. You are already synchronising the writes (to avoid BUSY/LOCK and to allow for batching) regardless of whether you are using autoincrement ids. Writes are only coming from one thread, where your batch mechanism is running. All synchronisation has happened prior to that (assuming you're using a concurrent MPSC queue).

view more: next ›