Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Good system design

seangoedecke.com

Post reply on HN
Print view View original

91–100 of 400 posts

Re: Good system design

#91

Post by whodidntante »

Never write an article about good system design.

In all seriousness, this is an extraordinary subtle and complex area, and there are few rules.

For example, "if you need data from multiple tables, JOIN them instead of making separate queries and stitching them together in-memory" may be useful in certain circumstances. For highly scalable consumer systems, the rule of "avoid joins as much as possible" can work a lot better.

There is also no mention of how important it is to understand the business - usage patterns, the customers, the data, the scale of data, the scale of usage, security, uptime and reliability requirements, reporting requirements, etc.

Re: Good system design

#92

Post by motorest »

What a great article. It's always a treat to read this sort of take.

I have some remarks though. Taken from the article:

> Avoid having five different services all write to the same table. Instead, have four of them send API requests (or emit events) to the first service, and keep the writing logic in that one service.

This is not so cut-and-dry. The trade offs are far from obvious or acceptable.

If the five services access the database then you are designing a distributed system where the interface being consumed is the database, which you do not need to design or implement, and already supports authorization and access controls out of the box, and you have out-of-the-box support for transactions and custom queries. On the other hand, if you design one service as a high-level interface over a database then you need to implement and manage your own custom interface with your own custom access controls and constrains, and you need to design and implement yourself how to handle transactions and compensation strategies.

And what exactly do you buy yourself? More failure modes and a higher micro services tax?

Additionally, having five services accessing the same database is a code smell. Odds are that database fused together two or three separate databases. This happens a lot, as most services grow by accretion and adding one more table to a database gets far less resistance than proposing creating an entire new persistence service. And is it possible that those five separate services are actually just one or two services?

Re: Good system design

#93

Post by tossandthrow »

rbees wrote: post #48
tossandthrow wrote: post #38

Earlier quoted context omitted.

Have you ever build a complex app like this? In particular, have you have to do testing, security (eg. row level security), manage migrations, change management (eg. for SOC2 or other security frameworks), cache offloads (Redis, and friends), support for microservices, etc. Comments like this give me a vibe of young developers trying out Supabase for the first time feeling like that approach can scale indefinitely.

> Comments like this give me a vibe of young developers I don’t think so. The context is about avoiding joining in memory, which is fairly awful to do in a application, and should be avoided, along with uninformed use of ORMs, which often just add a layer of unwarranted complexity leading to things like the dreaded N+1 problem that most inexperienced Rails developers had when dealing with ActiveRecord. If anything, w…

I don't know what I am talking about that sounds like hell?

I am merely talking about properties of developing complex web applications that have traditionally not been easy to work with in SQL.

I am in particular not proposing any frameworks.

How can that sound like hell?

Re: Good system design

#94

Post by dakiol »

mdavid626 wrote: post #78
AdrianB1 wrote: post #75

Earlier quoted context omitted.

The SQL database has more than a dozen semi-independent applications that treat different aspects of the manufacturing process, for example from recipes and batches to maintenance, scrap management and raw material inventory. The data is interlocked, the apps are independent as different people in very different roles are using it. No, it never starts in the front end, it started as a system and evolved by adding mor…

This is and “old-school” design. Nowadays I wouldn’t let apps meet in the database. Simple service oriented architecture is much preferred. Each app with its own data. Then such problems can be easily avoided.

It’s not old school, it’s actually solid design. I have worked too with people that think the frontend or even services should guide the design/architecture of the whole thing. Seems tempting and it has the initial impression that it works, but long terms it’s just bad design. Having Data structures (and mainly this means database structures) stable is key to long term maintenance.

Re: Good system design

#95

Post by tialaramex »

bambax wrote: post #14

> When querying the database, query the database. It’s almost always more efficient to get the database to do the work than to do it yourself. For instance, if you need data from multiple tables, JOIN them instead of making separate queries and stitching them together in-memory. Oh yes! Never do a join in the application code! But also: use views! (and stored procedures if you can). A view is an abstraction about the…

Stored procedures seem like a win but the big problem is that while I could write the rest of the software in a very nice modern language like Rust, or more practically in C# since my team all know C# if I write a stored procedure it will be in Transact-SQL because that's the only choice.

T-SQL was not a good programming language last century when it was vaguely current, and so no I do not want to write any significant amount of code in T-SQL. For my sins I maintain a piece of software with huge T-SQL procedures (multi-page elaborations by somebody who really, really like this stuff) and they're a nightmare. The tooling doesn't really believe in version control, the diagnostics when you make a mistake are either non-existent or C++ style useless spew.

We hire a lot of very junior developers. People who still need to be told not to comment out code in release, that variable numbers are for humans to read not machines, that sort of thing. We're not quite hiring physicists to write software (I have done that at a startup) but it's close. However, none of the poor "My first program" code I see in a merge request by a new hire is anywhere close to as unreadable as the T-SQL we already own and maintain.

Re: Good system design

#96

Post by nicoburns »

mdavid626 wrote: post #68
riv991 wrote: post #61

Earlier quoted context omitted.

High Scale is so subjective here, I'd hazard a guess that 99% of businesses are not at the scale where they need to worry about scaling larger than a single Postgres or MySQL instance can handle.

Scalability is not the keyword here. The same principle applies to small applications too. If you apply it correctly, the application never going to be slow due to slow db queries and you won’t have to optimize complex queries at all. Plus if you want to split out part of an app to its own service, it’ll be easily possible.

One of the last companies I worked at had very fast queries and response times doing all the joins in-memory in the database. And that was only on a database on a small machine with 8GB RAM. That leaves a vast amount of room for vertical scaling before we started hitting limits.

Re: Good system design

#97

Post by tossandthrow »

lurking_swe wrote: post #52
tossandthrow wrote: post #38

Earlier quoted context omitted.

Have you ever build a complex app like this? In particular, have you have to do testing, security (eg. row level security), manage migrations, change management (eg. for SOC2 or other security frameworks), cache offloads (Redis, and friends), support for microservices, etc. Comments like this give me a vibe of young developers trying out Supabase for the first time feeling like that approach can scale indefinitely.

Not the person you replied to, but I have! A java project I worked on a couple years ago used a thin persistence layer called JOOQ (java library). It basically helps you safely write sql in java, without ORM abstractions. Worked just fine for our complex enterprise app. Sql migrations? This is a solved problem: https://github.com/flyway/flyway What about micro services? You write some terraform to provision a sql dat…

From what I can se jooq is only really type safe with pojo mappings, to what point it is an orm with an expressive query dsl.

Alternatively you use record style outputs, but that is prone to errors if positions are changed.

Regardless, even with jooq you still accept that there is a sizable application layer to take responsibility of the requirements I listed.

Re: Good system design

#98

Post by valiant55 »

torginus wrote: post #63
bambax wrote: post #14

> When querying the database, query the database. It’s almost always more efficient to get the database to do the work than to do it yourself. For instance, if you need data from multiple tables, JOIN them instead of making separate queries and stitching them together in-memory. Oh yes! Never do a join in the application code! But also: use views! (and stored procedures if you can). A view is an abstraction about the…

Are you sure about this? Let's say you run a webshop and have two tables, one for orders with 5 fields, one for customers, with 20 fields. Let's say you have 10k customers, and 1m orders. A query performing a full join on this and getting all the data would result in 25 million fields transmitted, while 2 separate queries and a client side manual join would be just 5m for orders, and 200k for customers.

My rule of thumb is if it's a 1:1 relationship, use a join. If it's 1:M, separate queries.

Re: Good system design

#99

Post by nicoburns »

torginus wrote: post #63
bambax wrote: post #14

> When querying the database, query the database. It’s almost always more efficient to get the database to do the work than to do it yourself. For instance, if you need data from multiple tables, JOIN them instead of making separate queries and stitching them together in-memory. Oh yes! Never do a join in the application code! But also: use views! (and stored procedures if you can). A view is an abstraction about the…

Are you sure about this? Let's say you run a webshop and have two tables, one for orders with 5 fields, one for customers, with 20 fields. Let's say you have 10k customers, and 1m orders. A query performing a full join on this and getting all the data would result in 25 million fields transmitted, while 2 separate queries and a client side manual join would be just 5m for orders, and 200k for customers.

These days you can use JSON aggregation in the database to avoid returning duplicate data in what would otherwise be large joins.

Re: Good system design

#100

Post by bubblebeard »

motorest wrote: post #92

What a great article. It's always a treat to read this sort of take. I have some remarks though. Taken from the article: > Avoid having five different services all write to the same table. Instead, have four of them send API requests (or emit events) to the first service, and keep the writing logic in that one service. This is not so cut-and-dry. The trade offs are far from obvious or acceptable. If the five services…

I think the author meant, in a general way, it’s better to avoid simultaneous writes from different services, because this is an easy way to introduce race conditions.
Post reply on HN