Hacker News Reader

Comment by pphysch | Hacker News Reader

> Developing against a relational database requires devs to watch out for "N+1" query patterns, where a query leads to a loop that leads to more queries. N+1 queries against Postgres and MySQL can be lethal to performance. Not so much for SQLite.

This is misleading AFAICT. The article(s) is actually comparing remote RDBMS to local RDBMS, not Postgres to SQLite.

Postgres can also be served over a UNIX socket, removing the individual query overhead due to TCP roundtrip.

SQLite is a great technology, but keep in mind that you can also deploy Postgres right next to your app as well. If your app is something like a company backend that could evolve a lot and benefit from Postgres's advanced features, this may be the right choice.

View on HN

You could run Postgres over UNIX sockets although you will still get higher latency than SQLite's in-process model. Also, running a Postgres on every app instance on the edge probably isn't practical. Postgres has some great advanced features if you need them but it's also much more heavy weight.

With LiteFS, we're aiming to easily run on low resource cloud hardware such as nodes with 256MB or less of RAM. I haven't tried it on a Raspberry Pi yet but I suspect it would run fine there as well.

SQLite almost certainly is the better edge RDBMS than Postgres, if only because it has less features taking up space.

However, "local SQLite vs. remote Postgres/MySQL" remains a false dichotomy when talking about network latency.

It's an application design choice. It's perfectly reasonable to consider those two options when designing a system. The constraints of the system determine which is better for the application.

I'd pick a centralized network-reachable database with a strong relational schema for write-heavy applications, and a lighter in-process system for something that is mostly reads and where latency matters. It's not a false dichotomy — but more like a continuum that certainly includes both extremes on it.

There's still plenty of overhead in serializing data over a unix domain socket to a different process, waiting for that process to be scheduled, waiting for it to serialize a response, waiting for the client process to be scheduled. SQLite avoids all of that.

Isn't this all talking to S3 anyway, not to mention the network trips intrinsic to the system before it gets to S3? I mean, I'm sure there's some performance win here, but I'm surprised it's so significant.

It's not like the address-space separation is without benefits... heck, if it weren't, you could simply have embedded the whole application inside Postgres and achieved the same effect.

You may be confusing Litestream and LiteFS.

Litestream writes everything to S3 (or similar storage).

LiteFS lets different nodes copy replicated data directly to each other over a network, without involving S3.

In either case, the actual SQLite writes and reads all happen directly against local disk, without any network traffic. Replication happens after that.

I think I was definitely confusing it with Litestream as the blog post made reference to it (and I did find that confusing).

That said, unless I've misunderstood the LifeFS use case, you're still going over the network to reach a node, and that node is still going through a FUSE filesystem. That would seem to create overhead comparable (potentially more significant) to talking to a Postgres database hosted on a remote node.

It just doesn't seem that obvious that there's a big performance win here. I'd be curious to see the profiling data behind this.

The performance boost that matters most here is when your application reads from the database. Your application code is reading directly from disk there, through a very thin FUSE layer that does nothing at all with reads (it only monitors writes).

So your read queries should mostly be measured in microseconds.

> So your read queries should mostly be measured in microseconds.

You should check out the read latency for read-only requests over unix domain sockets with PostgreSQL. You tend to measure it in microseconds, and depending on circumstances it can be single-digit microseconds.

Regardless of whether your FUSE logic does nothing at all, It sure seems like there's intrinsic overhead to the FUSE model that is very similar to the intrinsic overhead of talking to another userspace database process... because you're talking to another userspace process (through the VFS layer).

When the application reads, those requests go from userspace to the FUSE driver & /dev/fuse, with the thread being put into a wait state; then the FUSE daemon needs to pull the request from /dev/fuse to service it; then your FUSE code does whatever minimal work it needs to do to process the read and passes it back through /dev/fuse and the FUSE driver, and from there back to your application. That gets you pretty much the same "block and context switch" overhead of an IPC call to Postgres (arguably more). FUSE uses splicing to minimize data copying (of course, unix domain sockets also minimize data copying), though looking at the LiteFS Go daemon, I'm not entirely sure there isn't a copy going on anyway. Memory copying issues aside, from a latency perspective, you're jumping through very similar hoops to talking to another user-space process... because that's how FUSE works.

There's a potential performance win if the data you're reading is already in the VFS cache, since that would bypass having to through the FUSE filesystem (and the /dev/fuse-to-userspace jump) entirely. The catch is, at that point you're bypassing SQLite transaction engine semantics entirely, giving you a dirty read that's really just a cached result from a previous read. That's not really a new trick, and you can get even better performance with client-side caching that can avoid a trip to kernel space.

I'm sure there's a win here somewhere, but I'm struggling to understand where.

That's a really detailed, interesting reply. I am now really interested to understand more about the comparative overhead here.

Wow, thank you so much for explaining the overhead around FUSE usage, that’s really eye opening and makes having some performance benchmarks down the line even more interesting.

> There's a potential performance win if the data you're reading is already in the VFS cache, since that would bypass having to through the FUSE filesystem.

That's a typical case for a lot of databases. Most of the "hot" data is in a small subset of the pages and many of those can live in the in-process page cache.

> The catch is, at that point you're bypassing SQLite transaction engine semantics entirely, giving you a dirty read that's really just a cached result from a previous read.

It's not bypassing the transaction engine semantics. For WAL mode, SQLite can determine when pages are updated by checking the SHM file and then reading updated pages from the WAL file. Pages in the cache don't need to flushed on every access or even between transactions to be valid.

> I'm sure there's a win here somewhere, but I'm struggling to understand where.

The main goal of LiteFS is to make it easy to globally replicate applications. Many apps run in a single region of the US (e.g. us-east-1) and that's fast for Americans but it's a 100ms round trip to Europe and a 250ms round trip to Asia. Sure, you can spin up a multi-region Postgres but it's not that easy and you'll likely deploy as separate database and application servers because Postgres is not very lightweight.

LiteFS aims to have a minimal footprint so it makes it possible to deploy many small instances since SQLite is built to run on low resource hardware.

As far as comparisons with Postgres over UNIX sockets, I agree that the performance of a single instance is probably comparable with a FUSE layer.

> That's a typical case for a lot of databases. Most of the "hot" data is in a small subset of the pages and many of those can live in the in-process page cache.

Yes, though most database engines end up managing their own cache and using direct IO, rather than the VFS cache.

> It's not bypassing the transaction engine semantics. For WAL mode, SQLite can determine when pages are updated by checking the SHM file and then reading updated pages from the WAL file. Pages in the cache don't need to flushed on every access or even between transactions to be valid.

That sounds a lot like at least the SHM & WAL checks wouldn't be cached by the VFS, but as I've been looking at the design more carefully, I'm starting to think I understand the idea here. Basically, the SHM & WAL get updated separately, so you might read a stale version, but since you aren't elected to be a writer, that just means you're looking at stale data, not creating an integrity problem.

> The main goal of LiteFS is to make it easy to globally replicate applications. Many apps run in a single region of the US (e.g. us-east-1) and that's fast for Americans but it's a 100ms round trip to Europe and a 250ms round trip to Asia. Sure, you can spin up a multi-region Postgres but it's not that easy and you'll likely deploy as separate database and application servers because Postgres is not very lightweight.

So, I get that multi-region Postgres can be tricky to set up, if you're doing multi-leader, but this seems about as complicated as a "single leader, many followers" set up, and given that what you're trying to do is shave off the hundreds of milliseconds from partially circumnavigating the earth at the speed of light, I'm not sure the perceived performance differences are significant (or really even measurable) compared to fluctuations in network latency of requests to the region-local node.

> LiteFS aims to have a minimal footprint so it makes it possible to deploy many small instances since SQLite is built to run on low resource hardware.

This part I'm getting and the objective a lot of sense to me (and certainly running local postgres instances on every node wouldn't be an obvious approach to me). I hadn't thought of FUSE + SQLite as a way to get there, so this is an interesting and surprising approach. I'm looking forward to how this plays out.

> As far as comparisons with Postgres over UNIX sockets, I agree that the performance of a single instance is probably comparable with a FUSE layer.

Interesting. I was thinking I was missing something. Thanks for all the insight.

Yes, FUSE adds overhead. However,

1. kernel page cache fully removes the read overhead for in-memory pages

2. There's a FUSE_PASSTHROUGH mode that removes that overhead for all reads & writes. I haven't studied exactly what writes LiteFS needs to observe (just journal vs all data writes), but at least for reads it seems to pass them straight through. We could well submit a FUSE_PASSTHROUGH_READ patch to the kernel, and use that to remove all read overhead. The patch should be trivial, since the full FUSE_PASSTHROUGH mode is there already.

Disclaimer: I wrote the FUSE framework LiteFS uses, https://bazil.org/fuse

https://source.android.com/docs/core/storage/fuse-passthroug... https://lwn.net/Articles/674286/

I had totally forgotten about FUSE_PASSTHROUGH. That's a great insight. Thanks.

It's an "eventual consistency" transaction scheme for SQLite: reads happen locally and immediately with the nearest edge copy available, it's only writes that need to be forwarded to a (potentially far away) primary DB over the network somewhere, and transactions will then take some amount of network time to fan back out to edges, so eventually the edges will read the latest transactions, but during that fan out will read the previous transaction state.

Local for the application code on the node you're talking to (just like the read would be local for a Postgres process running on the node), but there's still a network trip to get to the node, no?

Even if the reads are happening locally, if they're going through FUSE (even a "thin" pass-through that does nothing), that means they're getting routed from kernel space to a FUSE daemon, which means you're still doing IPC to another process through a kernel...

TL;DR "Postgres doesn't fit well our edge services business model" which sure fine but the article is indeed biased/misleading by completely ignoring and not mentioning the option of running postgres and app in a single server.

The gains in latency with sqlite won't matter as soon as throughput starts to dominate.

Anyone want to chime in with the largest app they've deployed where prod Postgres was reachable over a Unix domain socket?

I would wager that the median IT shop/dept is running off a single node RDBMS, the majority of which also have the spare resources to support running the primary application (which has 1-100 DAU).

No practical way to test this, of course.

is it cheating when pgbouncer is being accessed via socket? lol

Yes, that is cheating.

I don't have any context around this: why is that cheating?

Because the point of the Unix domain socket (in this argument) is eliminating network round trips.

Oh gotcha, I assumed they meant all 3 we're on one host. I'm not too familiar with the pg ecosystem.

Used to work developing standalone kiosks and things (think along the lines of stuff you'd find at a museum as the basic type of thing I'd work on), so absolutely not the use case you're thinking of.

In a number of cases, we were working with data that would really benefit from actual GIS tooling so PostGIS was kind of the natural thing to reach for. Many of these kiosks had slow, intermittent, or just straight up no internet connection available.

So we just deployed PostGIS directly on the kiosk hardware. Usually little embedded industrial machines with passive cooling working with something like a generation or two out-of-date Celeron, 8GB RAM, and a small SSD.

We'd load up shapefiles covering a bunch of features across over a quarter million square miles while simultaneously running Chromium in kiosk mode.

I know for a fact some of those had around 1k DAU. I mean, never more than one simultaneously but we did have them! I'm sure it would have handled more just fine if it weren't for the damn laws of physics limiting the number of people that can be physically accessing the hardware at the same time.

That said, we had the benefit of knowing that our user counts would never really increase and due to real-world limitations we'd never be working with a remote database because there was literally no internet available. In general I'd still say colocating your database is not an excuse to allow N+1 query patterns to slip in. They'll be fine for now and just come back to bite you in the ass when your app _does_ outgrow colocating the app/database.

Are there significant limits to the size of an app that could be deployed alongside Postgres versus the size of an app that could be deployed alongside SQLite?

No, the big difference is write speed. SQLite is limited in the amount of writes it can do at the same time(typically 1). This is generally called "concurrent writes". Last I checked SQLite can't really get past 1 write at the same time, but it can emulate concurrent writes in WAL mode such that it isn't normally a problem, for most applications. Postgres has no such limit(though there can be limits to updating a particular row/column of data concurrently, obviously).

Otherwise both are generally limited to the physical resources of the machine(memory, disk, etc). Generally speaking you can scale boxes much much farther than your data size for most applications.

> it can emulate concurrent writes in WAL mode

Seems a bit unfair to call WAL mode emulation of "true" concurrent writes as I'm pretty sure a write-ahead-log (WAL) is exactly how other databases implement multiple concurrent writes. It's just always-on rather than being opt-in.

Well, but that's not really what it's doing. WAL1 mode in SQLite is different than in your typical RDBMS. WAL mode in SQLite is just getting the write path outside of the read path, but there is still only 1 write allowed at any given time. The "fake" concurrency is done by letting write transactions queue up and wait for the lock.

See [here](https://sqlite.org/wal.html) under "2.2. Concurrency" where it says:

> "However, since there is only one WAL file, there can only be one writer at a time."

SQLite is awesome, I'm a huge fan, but if you need to do lots and lots of writes, then SQLite is not your friend. Luckily that's a very rare application. There is a reason you never see SQLite being the end point for logs and other write-heavy applications.

It's a great way to deploy if you have the RAM and disk.

Bump up the shared buffers, have a multithreaded or pooled driver for your app server so connections are not being opened and closed all the time.

It really 'flies' under such a scenario.

Largest app was a big analytics and drug tracking application for a small pharmaceutical company. Not sure of the size but it was very reporting heavy.

Not me personally, but my understanding some Cisco products have Postgres "embedded." If they're not using unix domain sockets, they're at least using loopback.

...come to think of it, they may not be the only network device maker that does this.

I'm surprised no one is chiming in from the LAMP era. Maybe I was just in a bubble, but I feel like before modern cloud providers, folks deploying on VPSs tried to keep operational complexity lower by running their database on the same box until they couldn't anymore.

In my experience, I just cached the living hell out of the apps to avoid the I/O from the database. Providers in those days had huge issues with noisy neighbors, so I/O performance was quite poor.

[0]: https://en.wikipedia.org/wiki/LAMP_(software_bundle)

We use managed postgres on GCP, and it is served over unix socket.

Kind of by definition, GCP's managed postgres is served up over the network. I'm guessing you mean "unix socket" in the sense of the socket API, not unix domain sockets.

Back in my consulting days one of my clients had an application with nearly all of the business logic being done in-database. The app was essentially a frontend for stored procedures galore and it sat on an absolute monster of a system for its time (~256GB of RAM, 24 or 32 Cores I think). It handled something like 100k DAU with no issue. That was for just the transactional system. They replicated data to other (similar style) systems for less mission-critical things like reporting, billing, etc.

I want to be clear though, I would never recommend anyone do this. For its time it was impressive but I suspect by now it's been re-engineered away from that model, if for no other reason than its a lot of eggs in one basket.

Had an instance with almost 2TB on disk in 2015, it's probably a lot more by now, if it's still running as-is. Was for analytics/data-warehousing. Though the big table was partitioned so queries were always nice and snappy. The machine had 128gb ram back then, but probably never needed that much. The applications working with the data ran on that same server, so processing latency was usually <10ms even for larger queries.

A better comparison might be Postgres to BedrockDB.

BeckrockDB is a MySQL compatible interface wrapped around SQLite and as such, is intended for remote RDBMS use.

Expensify has some interesting scaling performance data for their use of it.

https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...

Isn't "n + 1" typically a degenerate case of ORM rather than a relational thing? The whole point of rdbms is you can do a join instead of iterated lookups.

Yeah most N+1 issues can be mitigated by optimizing the queries.

You definitely don't need an ORM to get to N+1 hell, a for-loop in vanilla PHP will do.

A for-loop in vanilla anything will do. Nothing special about PHP. Ruby, Python, Java, etc...

Of course. PHP is the one you will mainly encounter in the wilderness, though.

Most common place I saw N+1 is ActiveRecord (Ruby on Rails).

Because by default, no relationships are loaded and the way AR "loads" stuff makes is extremely easy to create N+M queries on 5 lines of ruby. Then, those people will tell you that you run out of database before you run out of ruby...

Yeah, but you have to go out of your way to do that. If you're in touch with the SQL you're executing at all, the join is the obvious easy way to do it, it's not some arcane optimization.

That’s a really big “if”. In my experience, most application engineers aren’t very good at SQL because they don’t have to be; most apps just don't ever reach a scale where it matters, and something like Rails or Django with N+1 queries all over the place performs just fine.

Hence my 2 decade losing battle against ORM at every place I've worked :)

I'm, like, 1 for 10 maybe.

You do see it often in things like canned ecommerce software. They have this ecosystem that encourages you to add widgets to category listings, product listings, etc, that all create queries keying off the page context.

Where widgets might be "similar items", "people that bought x, bought y", "recent purchases of this widget", "best sellers in this category" and so on.

You see it in direct code too, from new or naive coders (the good old "it was fast enough on my test data…") or when proof-of-concept code "accidentally" escapes into production.

Even within the database you'll sometimes find examples: stored procedures looping over a cursor and running queries or other statements in each iteration. This doesn't have the same latency implications as N+1 requests over a network, but still does add significant time per step compared to a set based equivalent.

If you think about it, the database will sometimes do this itself when your database is not optimally structured & indexed for your queries (in SQL Server, look at "number of executions" in query plans).

> You see it in direct code too, from new or naive coders

Many moons ago in one of my first coding projects I not only used a separate query for each record, but a separate database connection too. In addition to this, I didn't use a loop to generate these queries, I copy and pasted the connection setup and query code for each record, leading to a 22k code file just for this single web page.

There were probably on the order of 1000 records (they represented premier league football players), and amazingly this code was fast enough (took a couple of seconds to load - which wasn't so unusual back in 2007) and worked well enough that it actually had ~100 real users (not bad a 14 year old's side project).

It is a result of representing application state as complex entity structures. I am not sure I would call it degenerate. n+1 would be quite natural if relational databases were theoretically pure. It only falls down in the real world because we accept deviation from the theory to allow implementation.

Joins are one possible workaround to the n+1 problem. However, they push the problem into the application to unpack, which detracts from having a nice high level abstraction in the database for fetching data. The many queries of n+1 would be ideal if you had an ideal database. You are querying on behalf of many different entities so many different queries is conceptually sound. But since we don't have ideal databases...

That's not to say that joins don't have their place. In the relational model, joins are essential. But the n+1 problem happens when you are not working in the relational model.

I get the impression that there's still quite a bit of overhead involved in talking to PostgreSQL that you don't get with SQLite. A SQLite query is pretty much a C function call that works directly with data structures and local disk.

Yep. The poison is still in the cup, it's just a smaller dose. Keep drinking if you think that fact is enough to save you...

I see bad judgement calls coming from small numbers, often due to failing to do the cost x frequency math properly in your head. If you're looking at an operation that takes 3ms or 3μs that is called a million times per minute, or twenty thousand times per request, you don't have enough significant figures there and people make mistakes. 3ms x 12345 = ~40s, not 37035ms. Better if you use a higher resolution clock and find out it's actually 3.259 ms, leading to a total of ~40.23s

Point is, when we are doing billions of operations per second, lots of small problems that hide in clock jitter can become really big problems, and a 5-10% error repeated for half a dozen concerns can lead to serious miscalculations in capacity planning and revenue.

Select N+1 is a fundamental anti-pattern of relational database usage. Reducing the latency per round trip doesn't change this fact.

I disagree. I think it makes a big difference.

https://sqlite.org/np1queryprob.html#the_need_for_over_200_s... talks about this in the context of Fossil, which uses hundreds of queries per page and loads extremely fast.

It turns out the N+1 thing really is only an anti-pattern if you're dealing with significant overhead per query. If you don't need to worry about that you can write code that's much easier to write and maintain just by putting a few SQL queries in a loop!

Related: the N+1 problem is notorious in GraphQL world as one of the reasons building a high performance GraphQL API is really difficult.

With SQLite you don't have to worry about that! I built https://datasette.io/plugins/datasette-graphql on SQLite and was delighted at how well it can handle deeply nested queries.

Perhaps the overhead of chatty queries is diminished in this special use case.

But it doesn't change the fact that standalone database server processes are designed to support specific queries at lower frequencies. This is one of the main points of The SQL language is to load precisely the data that is needed in a single statement.

Relying on this is a design pattern would only scale in specific use cases and would hit hard walls in changing scenarios

Reply on news.ycombinator.com