One of the big advantages people enjoy is the elimination of the network latency between the application server and the DB. With SQLite your DB is right there often directly attached over NVME. This improves all access latencies and even enables patterns like N+1 queries which would typically be considered anti-patterns in other DBs.
> One of the big advantages people enjoy is the elimination of the network latency between the application server and the DB. With SQLite your DB is right there often directly attached over NVME.
You can install MySQL/PostgreSQL on the application server, connect over a unix socket and get the same benefits as if you'd used SQLite on the application server (no network latency, fast queries). Plus the other benefits that come from using these database servers (Postgres extensions, remote connections, standard tooling etc). I'm guessing more RAM is required on the application server than if you used SQLite but I haven't benchmarked it.
Unix sockets don't actually give you the same benefit. You're still doing IPC which can incur substantial memory subsystem utilization. SQLite is on the same thread/core as whatever is using it.
Real talk, how do you actually avoid N+1? I realize you can do complicated JOINs, but isn't that almost as bad from a performance perspective? What are you really supposed to do if you need to, e.g. fetch a list of posts along with the number of comments on each post?
Often you can use joins to get the data in a single complex SQL query. Number of comments for a post is relatively straight-forward, but you can also do increasingly complex associated data fetches with modern databases.
In particular, JSON aggregations mean you can have a single query that does things like fetch a blog entry and the earliest 10 comments in a single go. I wrote up some patterns for doing that in SQLite and PostgreSQL here: https://github.com/simonw/til/blob/main/sqlite/related-rows-...
select
blog_entry.id,
title,
slug,
created,
coalesce(json_agg(json_build_object(blog_tag.id, blog_tag.tag)) filter (
where
blog_tag.tag is not null
), json_build_array()) as tags
from
blog_entry
left join blog_entry_tags on blog_entry.id = blog_entry_tags.entry_id
left join blog_tag on blog_entry_tags.tag_id = blog_tag.id
group by
blog_entry.id
order by
blog_entry.id desc
The alternative, more common path is the pattern that Django calls "prefetch_related". Effectively looks like this:
select id, title, created from posts order by created desc limit 20
-- Now extract the id values from that and run:
select
blog_entry.id,
blog_tag.tag
from
blog_entry
join blog_entry_tags on blog_entry.id = blog_entry_tags.entry_id
join blog_tag on blog_entry_tags.tag_id = blog_tag.id
where
blog_entry.id in (?, ?, ?, ...)
-- Now you can re-assemble the list of tags for
-- each entry in your application logic
Once you have a list of e.g. 20 IDs you can run a bunch of cheap additional queries to fetch extra data about all 20 of those items.
it gets more complicated when you need to also display something like "last comment: <author> <3 days ago>" for each post, or if the comment counts need to be filtered by various flags/states/etc.
of course, it's all possible with custom SQL but it gets complicated quick.
That's like saying it gets more complicated when you have to use loops with break statements in programming. It's just what programming is.
The filtering you describe is trivial with COUNT(flag IN (...) AND state=...) etc.
If you want to retrieve data on the last comment, as opposed to an aggregate function of all comments, you can do that with window functions (or with JOIN LATERAL for something idiomatic specifically to Postgres).
Learning how to do JOIN's in SQL is like learning pointers in C -- in that it's a basic building block of the language. Learning window functions is like learning loops. These are just programming basics.
AFAIK the problem of N+1 isn't necessarily one more DB query, but one more network roundtrip. So if for each page of your app you have an API endpoint that provides exactly all of the data required for that page, it doesn't matter how many DB queries your API server makes to fulfill that request (provided that the API server and the DB are on the same machine).
This is essentially what GraphQL does instead of crafting each of these super tailored API endpoints for each of your screens, you use their query language to ask for the data you want, it queries the DB for you and get you the data back in a single network roundtrip from the user perspective.
(Not an expert, so I trust comments to correct what I got wrong)
You still have to write the resolver for graphql. I've seen. N+1 with graphql if you don't actually use data loader+batch pattern OR if you use it incorrectly.
A proper join is the right answer. But, it's not always possible to make those run well. [1] A "client side join" in the right situation can be much better, but then you probably want to do a 1+1 rathet than N+1. Do the first query to get the ids for the second query, and then construct the second query with IN or UNION depending on what works best for you database. UNION likely bloats your query string, but I've seen plenty of situations where UNION is gobs faster than IN.
Alternately, if you can separate query issuance from result parsing, you can make N+1 palletable. Ex, do your query to get the ids, wait for the results, loop and issue the N queries, then loop and wait for results in order. That will be two-ish round trips rather than N+1 round trips. But you have to search to find database apis that allow that kind of separation.
[1] You can almost always express the query you want in SQL, but that doesn't mean it will have a reasonable runtime. Sometimes server side join and client side join have about the same runtime... if it's significant and you have the usual case where clients are easier to scale than database servers, it might be worthwhile to have the join run on the client to reduce server load.
A JOIN is fast, fetching the whole list in one extra query with "WHERE id IN (...)" is also pretty fast and results in less complex queries if you have serval of these. Doing all queries separate is slow because of the network round-trip for each query.
The performance problem in N+1 is (mostly) not in fetching the N rows from disk, but rather from multiplying the network latency by a factor of N. Joins solve this; so do stored procedures.
In general, you want to ask the remote server once for all the data you need, then read all the results. It applies to databases as well as APIs.
Pipelined requests also solve the problem and can be more flexible.
Also, joins can be optimised in different ways. Sometimes the optimal way to do a join isn't to query each row one-by-one, but to do something like (when the rows you want are a large fraction of the rows that exist) making a bloom filter of the rows you want and then sequentially reading all the rows in the table.
Either joins for a fat query, or aggregate the subqueries.
For the latter, it's along the lines of `select * from posts where ...` and `select * from authors where id in {posts.map(author_id)}`. And then once it's in memory you manually work out the associations (or rely on your ORM to do it).
You do indeed use JOINS. The goal is to retrieve exactly the data you require in a single query. Then you get the DB to `EXPLAIN VERBOSE` or similar and ensure that full table scans aren't happening and that you have indexed the columns the query is being filtered on.
Avoiding N+1 doesn't have to mean limiting yourself to 1 query. You can still fetch the posts in one query and the comments of _all_ posts in a separate query, just don't issue a query for _each_ post.
More formally, the number of queries should be constant and not linearly scaling with the number of rows you're processing.
The actual thing that we're getting N+1 of is network round-trips. An additional network round-trip is way, way slower than an extra JOIN clause. That's why N+1 query patterns aren't a problem when you're using a local database: There's no round-trip.
A well-written JOIN against a well-designed database (regardless if we're talking postgres, SQLite, MySQL/MariaDB, or MS SQL) should not be slow. If it's slow, you're using it wrong.
If you think they can't do this task well I encourage you to try feeding ChatGPT some long documents outside of its training cutoff and examining the results. I expect you'll be surprised!
Even though Varnish may not be in fashion any more, there were many companies happily using it for free and still demanding security updates.
I like their transparency about who actually supports them, and what the whole community gets for it. I wish other projects would do that, if for no other reason than to make it obvious that FOSS isn't just something that happens.
I enjoyed this post, but I'm eager to hear what the next step would be for a real "production" userspace driver. Are these typically just daemons that are configured to run at start up? And then some configuration GUI communicates with it over a socket or something?
You could certainly do that, and it would make a ton of sense if there's both no standard software API for communicating with that type of device, and it's important that multiple pieces of software that communicate with the device are able to run at the same time (or you want to avoid repeating work when starting software multiple times). ADB (Android Debug Bridge) takes this approach.
If there is an applicable standard software API (either multiplatform like a filesystem, or a special one exposed by the OS kernel [1] ), the driver probably belongs in the Linux kernel (or in the form of a Windows driver on that platform). My understanding is that GPU APIs are an exception on Linux, and are implemented in userspace by a piece of software called MESA. You could also use the daemon approach in this case if you don't want to bother with getting a driver added to the kernel.
For a more niche device where exclusive access is acceptable and every piece of software would need to add special support for this specific type of device anyway, it's a lot simpler to distribute the driver as a library that software authors can include in their program. If there are several devices that work similarly but communicate differently, you could have one library that either includes multiple drivers, or exposes a common interface that other libraries can implement.
A downside of any approach for USB devices on Linux that isn't a kernel driver is that one or more udev rules will need to be added (as the article described). This also applies when using a device that uses a supported USB protocol, but has different IDs than the ones listed in the kernel driver.
[1] More devices fall into this category than you might expect. For example, Linux has an API for communicating with CAN devices called SocketCAN, so if you're writing a driver for a CAN device that connects via USB and exposes the full CAN bus over USB (maybe something that goes in or connects to a car), you should write a kernel driver that converts data between SocketCAN and whatever USB protocol is being used (assuming one doesn't already exist, a lot of USB CAN devices use protocols that already have drivers in the kernel). SocketCAN only exposes the raw data extracted from the CAN frames, so if you want to expose an easy way to control a particular CAN device, that belongs in a userspace library that uses the SocketCAN API under the hood.
Typically production userspace drivers run as daemons (often systemd services) with udev rules to detect device connections, exposing control via D-Bus, sockets, or a custom API that GUI applications consume.
Plugins have pre-defined APIs. You code your application against the plugin API and plugin developers do the same. Functionality is being consumed directly through this API — this is level 1.
MCP is a meta-protocol. Think of it as an API that lets arbitrary plugins announce their APIs to the application at runtime. MCP thus lives one level above the plugin's API level. MCP is just used to exchange information about the level 1 API so that the LLM can then call the plugin's level 1 API at runtime.
This only works because LLMs can understand and interpret arbitrary APIs. Traditionally, developers needed to understand an API at design-time, but now LLMs can understand an API at runtime. And because this can now happen at runtime, users (instead of developers) can add arbitrary functionality to applications.
> And because this can now happen at runtime, users (instead of developers) can add arbitrary functionality to applications.
I don't understand what you mean by this. Currently without MCP a server has an API that's documented and to interact with it(thus provide "arbitrary functionality") you call those APIs from your own application code(e.x. python script).
With MCP an LLM connected to your application code calls an API that's documented via MCP to provide "arbitrary functionality".
How are these different, and how does MCP allow me to do anything I couldn't before with API access and documentation? In both cases the application code needs to be modified to account for the new functionality, unless you're also using the LLM to handle the logic which will have very unpredictable results.
>In both cases the application code needs to be modified to account for the new functionality, unless you're also using the LLM to handle the logic which will have very unpredictable results.
In the case of MCP, no application code is modified. You first ship the application and then functionality is added. Using plain APIs, it's the other way around. That's the difference.
If my application performs some function dependant on data from an API(e.x. showing tax information, letting a user input tax information, and performing tax calculations and autocomplete), how do I extend that UI easier with MCP than with an HTTP REST API.
Even with MCP I need to update my application code to add UI elements(inputs, outputs) for a user to interact with this new functionality, no?
No, MCP does not include any concept of UI (yet). Tool results are usually text only, although there is also the abstraction of an Image (which can be displayed as clients as decide to, e.g. inline).
So no application code needs to be changed because no application code exists.
Isn't that like saying you don't need to modify application code with an REST API if your "application" is just a list of instructions on how to use wget/bash to accomplish the task?
As it currently stands, MCP is absolutely a security nightmare. Combine this with a general lack of appreciation for security culture amongst developers, and the emerging vibe coding paradigm where non-security-minded people automatically generate and fail to properly audit production-facing code, and it's a disaster waiting to happen.
Feels like we've slid back into the 90s in this regard. Great time to be a security researcher!
> Feels like we've slid back into the 90s in this regard.
Thank $deity. 90s and early 2000s were the times software was designed to do useful work and empower users, as opposed to lock them into services and collect telemetry, both of which protected by the best of advancement in security :).
I'm only half-joking here. Security is always working against usefulness; MCP is designed to be useful first (like honest to $deity useful, not "exploit your customers" useful), so it looks like security nightmare. Some of that utility will need to go away, because complete lack of security is also bad for the users - but there's a tradeoff to be made, hopefully one that doesn't just go by modern security zeitgeist, because that is already deep into protecting profits by securing services against users.
> a general lack of appreciation for security culture amongst developers, and the emerging vibe coding paradigm where non-security-minded people automatically generate and fail to properly audit production-facing code
There is also a general lack of consideration of who is being protected from whom, and why in the security culture. MCP, vibe coding, and LLMs in general are briefly giving end-users back some agency, bringing back the whole idea of "bicycle for the mind" that was completely and intentionally destroyed when computing went mainstream. Let's not kill it so eagerly this time.
- How does a consumer of a remote MCP server trust that it is not saving/modifying their data, or that it is doing something other than what it said it would?
- How does a consumer of a local MCP server trust that it won't wreck their machine or delete data?
- How do servers authorize and authenticate end users? How do we create servers which give different permissions to different users?
These are examples of things which must be done right, and sacrificing user security in order to achieve market dominance is ethically bankrupt. Pedestrians don't know exactly which regulations serve them when a bridge is built, so we don't expect pedestrians to be able to stop corruption and laziness in civil engineering. The same should be true for mass infrastructure; we have a duty as engineers to make the right call.
> MCP, vibe coding, and LLMs in general are briefly giving end-users back some agency, bringing back the whole idea of "bicycle for the mind"
I love what software might look like in 15 years. I don't plan to kill that. I want to protect it, and also protect everyone involved.
It’s pretty astounding to me that this aspect of MCP is not mentioned more. You’re putting a LOT of trust in both the model and the system prompt when you start attaching MCPs that provide unfettered access to your file system, or connect up to your REST API’s POST endpoints.
(That being said, I have to admit I’ve been writing my own powerful but extremely dangerous tools as an experiment (e.g. run arbitrary Python code on my machine, unsandboxed) and I have to admit the results have been incredibly compelling.)
I really enjoyed both your blog posts. You've clearly thought about this a lot and explained things well. I'd love to subscribe to be updated on your next post (even if it's not for months/years). Any chance you could add an RSS feed to your blog?
The origin of this meaning is very similar, but hati means either heart or liver depending on the context. With heart probably being a more common way that it is used.
