Hacker News Reader

Comment by throwaway11460 | Hacker News Reader

Not sure about Oracle or MS SQL, I work with Postgres. Back when I worked with Oracle and MS SQL it was about one-few huge DB servers, not many smaller ones.

To scale the database you need replication, and replication makes it a distributed stateful system that might be harder to reason about than a classic server+database setup.

In comparison, scaling a standard stateless backend server that connects to one huge DB server is fairly easy.

If you have any guides to point me to I'd appreciate it.

View on HN

The state comes from the data, not the procedural code implemented in the database.

Issues around replication happen with any distributed database. That doesn’t get worse with stored procedures, which don’t themselves have state.

It's just as hard, indeed. But you don't need to replicate as early with server+DB because your DB is focused on data transactions only. You can have a replica ready for a potential outage and that's all you need to care about for a long time.

When you run everything in your DB, replication issues will hit you much earlier.

Of course this all depends on what kind of app are you building. If it's an internal tool or something like that, you probably don't need to worry.

The stored procedures and views and triggers get replicated too.

I think you’re imagining that moving business logic into stored procedures will overload the database server sooner than having that in application code. I have never seen that. Instead you get fewer simultaneous connections and less data passed back and forth. Querying a compiled view or stored proc will often use fewer resources than parsing, planning, and optimizing SQL sent from application code. And the RDBMS can make better use of caching with business logic in the database.

Scaling and replication may present complications with any architecture, but those aren’t good reasons in my opinion to not put business logic into the RDBMS. Real enterprise-scale databases tend to do that and they actually have scaling and replication issues, not just “may happen someday” issues.

Completely agree; the issue I see with people who try putting logic in the DB ultimately to reject it is they try and bring their procedural programming paradigms (e.g. “for” loops and “if” statements) into the DB and that’s incredibly inefficient, requiring row-by-row looped fetches and ultimately causing their systems to grind to a halt and them to blame the concept of putting logic in the DB. If instead they were to embrace the declarative nature of the database and used efficient patterns such as statement triggers rather than row triggers, common table expressions (CTEs) rather than row-by-row looped processing, set-returning functions and joins rather than row-by-row function calls, etc then they would find that their code is both more expressive and often thousands of times more performant. As you say, embracing this model means less data transferred between cluster and client which can result in reduced load across the entire system without increasing complexity or having to worry about cache invalidation issues. And time spent learning databases is knowledge that will still be useful in a decade or two, it’s well worth investing into.

I completely agree with you. I think of relational databases as a fundamental programming skill. I have made a good career from RDBMS and SQL expertise, and that technology shows no signs of going away.

If your app works with data in the DB and nothing else, sure.

What if you have complex integrations of external services, need to serialize and parse huge JSON or XML, do processing on the data and send it back and forth, accept and trigger webhooks...? That's the workload I'm imagining would be easier to scale on a separate app server.

You use the best tool and write code at the appropriate layer. I don’t think anyone suggests writing the entire application in the database (though you can with Oracle).

UI and interfacing with external services will most likely prove simpler to implement in an application layer. Google and Stripe don’t have SDKs for Postgres.

If the application needs a relational database at all it makes sense to get the most value out of the RDBMS.

Postgres has extensions to make HTTP requests, to write JS (etc) code, and so on. Check out Supabase, the goal is exactly this - use Postgres as your whole application layer, Supabase as the access point.

Reply on news.ycombinator.com