[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.

[switz]

I run my entire business on a single OVH box that costs roughly $45/month. It has plenty of headroom for growth. The hardest part is getting comfortable with k8s (still worth it for a single node!) but I’ve never had more uptime and resiliency than I do now. I was spending upwards of $800/mo on AWS a few years ago with way less stability and speed. I could set up two nodes for availability, but it wouldn’t really gain me much. Downtime in my industry is expected, and my downtime is rarely related to my web services (externalities). In a worst case scenario, I could have the whole platform back up in under 6 hours on a new box. Maybe even faster.

[likium]

What's the benefit of using K3 on a single node?

[switz]

I'd list these as the real-world advantages

  * Very flexible, but rigid deployments (can build anywhere, deploy from anywhere, and roll out deployments safely with zero downtime)
  * Images don't randomly disappear (ran into this all the time with dokku and caprover)
  * If something goes wrong, it heals itself as best it can
  * Structured observability (i.e. logs, metrics, etc. are easy to capture, unify, and ship to places)
  * Very easy to setup replicas to reduce load on services or have safe failovers 
  * Custom resource usage (I can give some pods use more/less CPU/memory limits depending on scale and priority)
  * Easy to self-host FOSS services (queues, dbs, observability, apps, etc.)
  * Total flexibility when customizing ingress/routing. I can keep private services private and only expose public services
  * Certbot can issue ssl certs instantly (always ran into issues with other self-hosting platforms)
  * Tailscale Operator makes accessing services a breeze (can opt-in services one by one)
  * Everything is yaml, so easy to manipulate
  * Adding new services is a cake-walk - as easy as creating a new yaml file, building an image and pushing it. I'm no longer disincentivized to spin up a new codebase for something small but worthwhile, because it's easy to ship it.

All-in-all I spent many years trying "lightweight" deployment solutions (dokku, elastic beanstalk, caprover, coolify, etc.) that all came with the promise of "simple" but ended up being infinitely more of a headache to manage when things went wrong. Even something like heroku falls short because it's harder to just spin up "anything" like a stateful service or random FOSS application. Dokku was probably the best, but it always felt somewhat brittle. Caprover was okay. And coolify never got off the ground for me. Don't even get me started on elastic beanstalk.

I would say the biggest downside is that managing databases is less rigid than using something like RDS, but the flip side is that my DB is far more performant and far cheaper (I own the CPU cycles! no noisy neighbors.), and I still run daily backups to external object storage.

Once you get k8s running, it kind of just works. And when I want to do something funky or experimental (like splitting AI bots to separate pods), I can go ahead and do that with ease.

I run two separate k8s "clusters" (both single node) and I kind of love it. k9s (obs. tool) is amazing. I built my own logging platform because I hated all the other ones, might release that into its own product one day (email in my profile if you're interested).

[bavell]

Also running a few single node clusters - perfect balance for small orgs that don't need HA. Been running small clusters since ~2016 and loving it.

[tehlike]

Deployments are easy. You define a bunch of yamls for what things are running, who mounts what, and what secrets they have access to etc.

If you need to deploy it elsewhere, you just install k3s/k8s or whatever and apply the yamls (except for stateful things like db).

IT also handles name resolution with service names, restarts etc.

IT's amazing.

[ahoog42]

any notes or pointers on how to get comfortable with k8? For a simple nodejs app I was looking down the pm2 route but I wonder of learning k8 is just more future proof.

[lillecarl]

Use K3s in cluster mode, start doing. Cluster mode uses etcd instead of kine, kine is not good.

Configure the init flags to disable all controllers and other doodads, deploy them yourself with Helm. Helm sucks to work with but someone has already gone through the pain for you.

AI is GREAT at K8s since K8s has GREAT docs which has been trained on.

A good mental model is good: It's an API with a bunch of control loops

[tehlike]

I'd say rent a hetzner vps and use hetzner-k3s https://github.com/vitobotta/hetzner-k3s

Then you are off to races. you can add more nodes etc later to give it a try.

[switz]

Definitely a big barrier to entry, my way was watching a friend spin up a cluster from scratch using yaml files and then copying his work. Nowadays you have claude next to you to guide you along, and you can even manage the entire cluster via claude code (risky, but not _that_ risky if you're careful). Get a VPS or dedicated box and spin up microk8s and give it a whirl! The effort you put in will pay off in the long run, in my humble opinion.

Use k9s (not a misspelling) and headlamp to observe your cluster if you need a gui.

[sciencesama]

Is this vanilla k8 or any flavor?

[switz]

I use microk8s

[Fnoord]

I guess you got cheap power. Me too, but not 24/7 and not a whole lot (solar). So old enterprise hardware is a no-go for me. I do like ECC, but DDR5 is a step in the right direction.

[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!

[simonw]

That's a good trick. In Django world I like pytest-django's django_assert_max_num_queries fixture: https://pytest-django.readthedocs.io/en/latest/helpers.html#...

  def test_homepage_queries(django_assert_max_num_queries, client):
      with django_assert_max_num_queries(10):
          assert client.get("/").status_code == 200

Or django_assert_num_queries to assert an exact number.

[jasonwatkinspdx]

Way back in the prehistoric era of Rails I just wrote a like 5 line monkey punch to ActiveRecord that would kill mongrel if queries per request went above a limit.

Probably some of the most valuable code I've ever written on a per LOC basis lol.

But anyhow, merging that into a new project was always a fun day. But on the other side of the cleanup the app stops falling down due to memory leaks.

[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

[ComputerGuru]

This plagues (plagued?) pretty much everything to do with WordPress, from core to every theme and plugin developed.

[metadat]

Oh yeah, the ORM thing (common side-effect with DB query abstractions) - I must not have been fully awake. Cheers and thank you for humoring me, @cbm-vic-20!

[tehlike]

With orms, it can be easy, but also often fixed with eager fetching too.

[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.

[OJFord]

I dislike ORMs as much as the next ORM disliker, but people who are more comfortable in whatever the GP programming language is than SQL will write N+1 queries with or without an ORM.

[iamflimflam1]

Very true. But ORMs did make it particularly easy to trigger N+1 selects.

It used to be a very common pitfall - and often not at all obvious. You’d grab a collection of objects from the ORM, process them in a loop, and everything looked fine because the objects were already rehydrated in memory.

Then later, someone would access a property on a child object inside that loop. What looked like a simple property access would silently trigger a database query. The kicker was that this could be far removed from any obvious database access, so the person causing the issue often had no idea they were generating dozens (or hundreds) of extra queries.

[Spivak]

This problem is associated with ORMs but the moment there's a get_user(id) function which does a select and you need to display a list of users someone will run it in a loop to generate the list and it will look like it's working until the user list gets long.

I really wish there was a way to compose SQL so you can actually write the dumb/obvious thing and it will run a single query. I talked with a dev once who seemed to have the beginnings of a system that could do this. It leveraged async and put composable queryish objects into a queue and kept track of what what callers needed what results, merged and executed the single query, and then returned the results. Obviously far from generalizable for arbitrary queries but it did seem to work.

[riffraff]

I think many ORMs can solve (some of) this these days.

e.g. for ActiveRecord there's ar_lazy_preloader[0] or goldiloader[1] which fix many N+1s by keeping track of a context: you load a set of User in one go, and when you do user.posts it will do a single query for all, and when you then access post.likes it will load all likes for those and so on. Or, if you get the records some other way, you add them to a shared context and then it works.

Doesn't solve everything, but helps quite a bit.

[0] https://github.com/DmitryTsepelev/ar_lazy_preload

[1] https://github.com/salsify/goldiloader

[skeeter2020]

I defense of the application developer, it is very difficult to adopt set theory thinking which helps with SQL when you've never had any real education in this area, and it's tough to switch between it and the loop-oriented processing you're likely using in your application code for almost everyone. ORMs bridge this divide which is why they fall in the trap consistently. Often it's an acceptable trade-off for the value you get from the abstraction, but then you pay the price when you need to address the leak!

[whizzter]

Yep, people who think OOP is all you need will just "abstract away the database".

[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. :)