[lordofmoria]

A small warning for folks.

I once was responsible for migrating a legacy business app to Azure, and the app had a local MSSQL server co-running with the app (the same pattern that Litestream is using).

As have been mentioned below, the app had been developed assuming the local access (and thus <1ms latency), so it had a ton of N+1 everywhere.

This made it almost impossible to migrate/transition to another configuration.

So, if this style of app hosting doesn't take off and you're at all worried about this being a dead end storage once you reach a certain scale, I'd recommend not doing this, otherwise your options will be very limited.

Then again - I bet you could get very very far on a single box, so maybe it'd be a non factor! :)

[masterj]

> I bet you could get very very far on a single box,

With single instances topping out at 20+ TBs of RAM and hundreds of cores, I think this is likely very under-explored as an option

Even more if you combine this with cell-based architecture, splitting on users / tenants instead of splitting the service itself.

[immibis]

Single instance is underappreciated in general. There's a used server reseller near me, and sometimes I check their online catalogue out of curiosity. For only $1000ish I could have some few generations old box with dual socket 32-core chips and 1TB of RAM. I don't have any purpose for which I'd need that, but it's surprisingly cheap if I did. And things can scale up from there. AWS will charge you the same per month that it costs to get one of your own forever - not counting electricity or hard drives.

[inerte]

I used to work on a product where the app server and database were in the same rack - so similar low latency. But the product was successful, so our N+1 would generate thousands of queries and 1ms would become >500ms or more easily. Every other month we would look at New Relic and find some slow spot.

It was a Rails app, therefore easy to get into the N+1 but also somewhat easy to fix.

[1-more]

For our rails app we actually added tests asserting no N+1s in our controller tests. Think a test setup with 1 post vs 10 posts (via factorybot) and you could do an assertion that the DB query count was not different between the two. A useful technique for any Railsheads reading this!

[rco8786]

Bad query practices are always going to bite you eventually. I would not call that a shortcoming of this approach

[laurencerowe]

It's not a bad query practice in SQLite! https://www.sqlite.org/np1queryprob.html

[metadat]

What is N+1?

[cbm-vic-20]

There's a common access pattern with object-relational mapping frameworks where an initial query will be used to get a list of ids, then an individual queries are emitted for each item to get the details of the items. For example, if you have a database table full of stories, and you want to see only the stories written by a certain author, it is common for a framework to have a function like

    stories = get_stories(query)

which results in a SQL query like

    SELECT id FROM stories WHERE author = ?

with the '?' being bound to some concrete value like "Jim".

Then, the framework will be used to do something like this

    for id in stories {
        story = get_story_by_id(id)
        // do something with story
    }

which results in N SQL queries with

    SELECT title, author, date, content FROM stories WHERE id = ?

and there's your N+1

[simonw]

The thing where your app displays 20 stories in the homepage, but for each story it runs an extra query to fetch the author, and another to fetch the tags.

It's usually a big problem for database performance because each query carries additional overhead for the network round trip to the database server.

SQLite queries are effectively a C function call accessing data on local disk so this is much less of an issue - there's an article about that in the SQLite docs here: https://www.sqlite.org/np1queryprob.html

[upmostly]

The N+1 problem basically means instead of making one efficient query, you end up making N separate queries inside a loop. For example, fetching a list of tables, then for each table fetching its columns individually — that’s N+1 queries. It works, but it’s slow.

We ran into this while building, funnily enough, a database management app called DB Pro (https://dbpro.app) At first we were doing exactly that: query for all schemas, then for each schema query its tables, and then for each table query its columns. On a database with hundreds of tables it took ~3.8s.

We fixed it by flipping the approach: query all the schemas, then all the tables, then all the columns in one go, and join them in memory. That dropped the load time to ~180ms.

N+1 is one of those things you only really “get” when you hit it in practice.

[Scubabear68]

Object Relational Mapping (ORM) tools, which focus on mapping between code based objects and SQL tables, often suffer from what is called the N+1 problem.

A naive ORM setup will often end up doing a 1 query to get a list of object it needs, and then perform N queries, one per object, usually fetching each object individually by ID or key.

So for example, if you wanted to see “all TVs by Samsung” on a consumer site, it would do 1 query to figure out the set of items that match, and then if say 200 items matched, it would do 200 queries to get those individual items.

ORMs are better at avoiding it these days, depending on the ORM or language, but it still can happen.

[genpfault]

https://stackoverflow.com/questions/97197

[Quarrelsome]

I mean, that's not much of a trade off given that it seems that what you're saying is that using such a service might just show you how shit your code actually is.

Its not its fault. :)