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

71–80 of 400 posts

Re: Good system design

#71

Post by digitalPhonix »

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.

What sort of application is regularly doing a query for “all data”?

Re: Good system design

#72

Post by mdavid626 »

dondraper36 wrote: post #62
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.

Vertical scaling is criminally underrated, unfortunately. Maybe, it's because horizontal scaling looks so much better on Linkedin.

Sooner or later even small apps reach hardware limits.

My proposed design doesn’t bring many hard disadvantages.

But it allows you to avoid vertical hardware scaling.

Saves money and development time.

Re: Good system design

#73

Post by magicalhippo »

quietbritishjim wrote: post #23
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…

I think it's ok to have this rule as a first approximation, but like all design rules you should understand it well enough to know when to break it. I worked on an application which joined across lots of tables, which made a few dozen records balloon to many thousands of result rows, with huge redundancy in the results. Think of something like a single conceptual result having details A, B, C from one table, X, Y fro…

I think it's more like avoid doing a "limiting" join in the application, ie where the join is used to limit the output to a subset or similar.

As a somewhat contrived example since I just got out of bed, if your software has a function that needs all the invoice items from invoices from this year which invoice address country is a given value, use a join rather than loading all invoices, invoice addresses and invoice items and performing the filtering on the client side.

Though as you point out, if you just need to load a given record along with details, prefer fetching detail rows independently instead of making a Cartesian behemoth.

Re: Good system design

#74

Post by klabb3 »

magnio wrote: post #11

I think it's a very good article. Even if you disagree with some of the individual points in it, the advice given are very concrete, pragmatic, and IMO tunable to the specifics of each project. On state, in my current project, it is not statefulness that causes trouble, but when you need to synchronize two stateful systems. Every time there's bidirectional information flow, it's gonna be a headache. The solution is o…

Yes it’s total madness to synchronize and replicate complex state that logically belongs together. This is why microservices are such hot garbage. Well, how people tend to use them anyway.

Monotonic state is also better than mutable state. If you must distribute state, think ownership. Who owns it? Eg Theres nothing necessarily wrong with having state owned by eg a mobile client that can be adjusted by the user. Then you can sync it to the backend if you want, but they are only a reader/listener, and should never try to control it directly.

Re: Good system design

#75

Post by AdrianB1 »

mdavid626 wrote: post #64
AdrianB1 wrote: post #60

Earlier quoted context omitted.

My manufacturing data is hundreds of GB to a few TB in size per instance and I am talking about hot data, that is actively queried. It is not possible to restructure and it is a terrible idea to do joins in the front end. Not every app is tiny.

In some cases, it’s true. But your thinking is rather limited. Even such data can be organized in a way, that joins are not necessarily in the db. This kind of design always “starts” on the frontend - by choosing how and what data will be visible eg. on a table view. Many people think, showing all data, all the time is the only way.

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 more data and more apps. Think SAP as another such example.

Re: Good system design

#76

Post by mdavid626 »

AdrianB1 wrote: post #60
mdavid626 wrote: post #49

Earlier quoted context omitted.

I disagree. In modern highly scalable architectures I’d prefer doing joins in the layer front of the database (backend). The “backend” scales much easier than the database. Loading data by simple indexes, eg. user_id, and joining it on the backend, keeps the db fast. Spinning up another backend instance is easy - unlike db instance. If you think, your joins must happen in db, because data too big to be loaded to memo…

My manufacturing data is hundreds of GB to a few TB in size per instance and I am talking about hot data, that is actively queried. It is not possible to restructure and it is a terrible idea to do joins in the front end. Not every app is tiny.

Good, simple solution could be data duplication, eg. store some props from the joined tables directly in the main table.

I know, for many, this is one of the deadly sins, but I think it can work out very well.

Re: Good system design

#77

Post by dondraper36 »

mattmanser wrote: post #56
hk1337 wrote: post #46

Earlier quoted context omitted.

You should be careful with how much you lean into “doing it in the database” as well with how you implement it. Lest, you get the situation where your application inserts as one value and it gets saved completely different.

I'm not sure if this is what you mean, but I think a big thing missing from the article is how you should isolate you business logic. A great software design will separate all business logic into its own layer. That might be a distinct project, module, or namespace, depending on what your language supports. Keep business logic out of SQL and out of web server code (controllers, web helpers, middleware, etc.). Then yo…

That also depends on what you would consider "business logic in the database".

What would you say about CHECK constraints, though? I don't think it's something few developers expect to see, and having these checks is very convenient.

I know that there are even opponents of foreign keys (which makes sense sometimes), but in general, I don't understand why I would ever throw away the nice features of Postgres that can enforce correctness.

Re: Good system design

#78

Post by mdavid626 »

AdrianB1 wrote: post #75
mdavid626 wrote: post #64

Earlier quoted context omitted.

In some cases, it’s true. But your thinking is rather limited. Even such data can be organized in a way, that joins are not necessarily in the db. This kind of design always “starts” on the frontend - by choosing how and what data will be visible eg. on a table view. Many people think, showing all data, all the time is the only way.

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.

Re: Good system design

#79

Post by mattlondon »

There was an article here recently about how to write good design docs: the TL;DR for that was basically your design doc should make your design seem obvious. I think that is the same conclusion here - good design is simple, straightforward design with no real surprises.

Wholly agree.

Re: Good system design

#80

Post by nvarsj »

> Paradoxically, good design is self-effacing: bad design is often more impressive than good.

Rings very true. Engineers are rated based on the "complexity" of the work they do. This system seems to encourage over-engineered solutions to all problems.

I don't think there is enough appreciation for KISS - which I first learned about as an undergrad 20 years ago.

Post reply on HN