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

51–60 of 400 posts

Re: Good system design

#51

Post by mattmanser »

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…

Most ORMs will happily let you map stored procedures and views to a class, you can have as many models as you want. So your point doesn't really make sense.

The author's said nothing about ORMs. It feels like you're trying to post a personal beef about ORMs that's entirely against the "pragmatic" software design engineering the author's opining. Using ORMs to massively reduce your boiler-plate CRUD code, then using raw SQL (or raw SQL + ORM doing the column mapping) for everything else is a pragmatic design choice.

You might not like them, but using ORMs for CRUD saves a ton of boilerplate, error-prone, code. Yes, you can footgun yourself. But that's what being a senior developer is all about, using the tools you have pragmatically and not foot gunning yourself.

And it's just looking for the patterns, if you see a massive ORM query, you're probably seeing a code smell. A query that should be in raw SQL.

Re: Good system design

#52

Post by lurking_swe »

tossandthrow wrote: post #38
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…

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 database (e.g. aws aurora) just like you would with dynamo db or similar. What does that have to do with ORMs?

What about redis? Suddenly we need an ORM to query redis, to check if a key exists in the cache before hitting our DB? That’s difficult code to write?

I’m confused reading your comment. It has “you don’t do things my way so you must be dumb and playing with toy projects” vibes.

Re: Good system design

#53

Post by kccqzy »

9rx wrote: post #27
quietbritishjim wrote: post #23

Earlier quoted context omitted.

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…

> which made a few dozen records balloon to many thousands of result rows That doesn't really sound like a place where data is actually conceptually joined. I expect, as it is something commonly attempted, that you were abusing joins to try and work around the n+1 problem. As a corollary to the above, you also shouldn't de-join in application code.

It's a join. A join without any ON or USING clause or any filtering is a Cartesian product which is what's happening here.

Re: Good system design

#54

Post by cpursley »

tossandthrow wrote: post #38
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…

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.

Guessing you are a Rails dev?

Re: Good system design

#55

Post by mrkeen »

maxbond wrote: post #25
setr wrote: post #17

Earlier quoted context omitted.

If you take the statement at face value — essentially storing booleans in the db ever is a bad smell - then he’s correct. Although I’m not even sure it’s broadly a good principle, even in the on_at case; if you actually care about this kind of thing, you should be storing it properly in some kind of audit table. Switching bool to timestamp is more of a weird lazy hack that probably won’t be all that useful in practic…

Audit tables are a big ask both in terms of programming effort to design and support them, and in terms of performance hit due to write amplification (all inserts and updates cause an additional write to an audit table). Whereas making a bool into a timestamp is free. Including timestamps on rows (including created_at and updated_at) are real bacon savers when you've deployed a bug and corrupted some rows and need to…

Audit tables are a dumb concept because they imply bolting on an actual source of truth in addition to the regular not so source of truth tables, and only if the programmer gets around to it (like documentation or logging or whatever else falls along the wayside).

Re: Good system design

#56

Post by mattmanser »

hk1337 wrote: post #46
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…

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 you're treating SQL as the data store it is designed to be. When you embed application logic in SQL, you're hiding core functionality in a place where most developers won't expect to find it. This approach also creates tight coupling between your application and your database provider, making it hard to switch as needs change/the application grows.

Re: Good system design

#57

Post by dondraper36 »

mattmanser wrote: post #51
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…

Most ORMs will happily let you map stored procedures and views to a class, you can have as many models as you want. So your point doesn't really make sense. The author's said nothing about ORMs. It feels like you're trying to post a personal beef about ORMs that's entirely against the "pragmatic" software design engineering the author's opining. Using ORMs to massively reduce your boiler-plate CRUD code, then using r…

In Go, for example, there is a mixed approach of pgx + sqlc, which is basically a combo of the best Postgres driver + type-safe code generator (based on raw SQL).

https://brandur.org/sqlc

Even though I often use pgx only, for a new project, I would use the approach above.

Re: Good system design

#58

Post by StevenWaterman »

What do you call system design, when it's referring to the design of systems in general, and not just computer services?

As in:

- writing a constitution

- designing API for good DX

- improving corporate culture

I intuitively want to call all of those system design, because they're all systems in the literal sense. But it seems like everyone else uses "system design" to mean distributed computer service design.

Any ideas what word or phrase I could use to mean "applying systems thinking to systems that include humans"

Re: Good system design

#59

Post by mrkeen »

tetha wrote: post #22

The distinction of stateful and stateless is one of the main criteria how we're dividing responsibilities between platform-infra and development. I know it's a bit untrue, but you can't do that many things wrong with a stateless application running in a container. And often the answer is "kill it and deploy it again". As long as you don't shred your dataset with a bad migration or some bad database code, most bad thi…

> but you can't do that many things wrong with a stateless application running in a container

> As long as you don't shred your dataset with a bad migration or some bad database code, most bad things at this level can be fixed in a few minutes with a few redeployments.

At some point between these statements you switched from stateless to stateful and I can't follow the rest of the argument.

Re: Good system design

#60

Post by AdrianB1 »

mdavid626 wrote: post #49
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 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.
Post reply on HN