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

141–150 of 400 posts

Re: Good system design

#141

Post by Too »

bob1029 wrote: post #24
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…

This is a big part of what makes ORMs a problem. Writing raw SQL views/queries per MVC view in SSR arrangements is one of the most elegant and performant ways to build complex web products. Let the RDBMS do the heavy lifting with the data. There are optimizations in play you can't even recall (because there's so many) if you're using something old and enterprisey like MSSQL or Oracle. The web server should be able to…

With an ORM your application code is your views.

You can write reusable plain functions as abstractions, returning QuerySets that allow further filters being chained onto the query, before the actual SQL is materialized and sent to the database.

The result of this doesn’t have to match the original object models you defined, it’s still possible to be flexible with group bys resulting in dictionaries.

Re: Good system design

#142

Post by PartiallyTyped »

Muromec wrote: post #128
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…

>And what exactly do you buy yourself? More failure modes and a higher micro services tax? Nice boxes in the architectural diagram. Each box is handed to a different team and then, when engineers from those teams don't talk to each other, the system doesn't suddenly fail in an unexpected way.

At amzn a decision from atop was made that nobody would ever write in shared dynamo db tables. A team would own and provide APIs. That massively improved reliability and velocity.

Re: Good system design

#143

Post by Ozzie_osman »

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…

There are definitely examples of when you want to do joins in the application.

For example, you may want to (or have the option to) vertically partition your database, or use different data stores. The app layer is usually stateless and can scale perpetually, but the database might be a bottleneck.

Joining in the database over the application is a great default. But I wouldn't say "never join in the application code".

Re: Good system design

#144

Post by LinXitoW »

Yokohiii wrote: post #106
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.

I don't understand why all these problems should be easier handled with an ORM then with raw sql?

Why is it so hard to believe that well tested, typed code is better than manual string concatenation?

Before you tell me about how you just use a Query Builder/DSL and a object mapper for convenience: That's a freaking ORM!

Re: Good system design

#145

Post by doitLP »

tialaramex wrote: post #95
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 significa…

I worked at a place with just such a system. Half the application code was baked into sprocs, no version control and hidden knock on effects everywhere.

There was _one guy_ who maintained it and understood how it worked. He was very smart but central to the company’s operations. So having messy stuff makes it brittle/hard to change in more ways than one and

Re: Good system design

#146

Post by aembleton »

digitalPhonix wrote: post #71
torginus wrote: post #63

Earlier quoted context omitted.

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”?

Client report generation.

Re: Good system design

#147

Post by [deleted] »

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…

[deleted]

Re: Good system design

#148

Post by foobarian »

PartiallyTyped wrote: post #142
Muromec wrote: post #128

Earlier quoted context omitted.

>And what exactly do you buy yourself? More failure modes and a higher micro services tax? Nice boxes in the architectural diagram. Each box is handed to a different team and then, when engineers from those teams don't talk to each other, the system doesn't suddenly fail in an unexpected way.

At amzn a decision from atop was made that nobody would ever write in shared dynamo db tables. A team would own and provide APIs. That massively improved reliability and velocity.

I don't need a decision from atop amazon to remind me how painful it would be to migrate a widely shared dynamo instance or god forbid change dax settings

Re: Good system design

#149

Post by paffdragon »

PartiallyTyped wrote: post #142
Muromec wrote: post #128

Earlier quoted context omitted.

>And what exactly do you buy yourself? More failure modes and a higher micro services tax? Nice boxes in the architectural diagram. Each box is handed to a different team and then, when engineers from those teams don't talk to each other, the system doesn't suddenly fail in an unexpected way.

At amzn a decision from atop was made that nobody would ever write in shared dynamo db tables. A team would own and provide APIs. That massively improved reliability and velocity.

The team boundary is very important. You can get away with shared DB for a long time if the same team handles all services that access it and have absolute tight control over them. If there are different teams in picture, however, the tight coupling is a source of problems and a bottleneck, beyond prototyping / idea validation, etc.

Re: Good system design

#150

Post by wild_egg »

richardlblair wrote: post #89
bob1029 wrote: post #24

Earlier quoted context omitted.

This is a big part of what makes ORMs a problem. Writing raw SQL views/queries per MVC view in SSR arrangements is one of the most elegant and performant ways to build complex web products. Let the RDBMS do the heavy lifting with the data. There are optimizations in play you can't even recall (because there's so many) if you're using something old and enterprisey like MSSQL or Oracle. The web server should be able to…

If your ORM is going to the DB per row you're using it wrong. N+1 queries are a performance killer. They are easy to spot in any modern APM. Rails makes this easy to avoid. Using `find_each` batches the queries (by 1,000 records at a time by default). Reading through the comment section on this has been interesting. Either lots of people using half baked ORMs, people who have little experience with an ORM, or both.

I mean Rails also makes it easy to accidentally nest further queries inside your `find_each` block and end up with the same problem.

Your team can have rules and patterns in place to mitigate it but I'd never say "Rails makes this easy to avoid".

Post reply on HN