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

61–70 of 400 posts

Re: Good system design

#61

Post by riv991 »

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…

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.

Re: Good system design

#62

Post by dondraper36 »

riv991 wrote: post #61
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…

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.

Re: Good system design

#63

Post by torginus »

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.

Re: Good system design

#64

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.

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.

Re: Good system design

#65

Post by mrkeen »

usernamed7 wrote: post #35

One thing i would add, is that a well designed system is often one that is optimized for change. It is rare that a service remains static and unchanging; browsers and libraries are regularly updated, after all. Thus if/when a developer takes on a feature ticket to add or change XYZ, it should be easy to reason about and have predictable side-effects of how that change will impact the system, and ideally be easy to ch…

And you get this part somewhat for free if you're actually testing as you're going.

When my service wants to store and retrieve as part of its behaviour, of course I'm going to back it with a hashmap first.

Once I know it fulfills its business logic I'll start fiddling with hard-to-change stuff like DB schemas and migrations.

And having finished and tested the logic, I'll have a much better idea of the actual access patterns so I can design good tables & indexes.

Re: Good system design

#66

Post by gethly »

Actually event-sourcing solves most of the pains - events, schema, push/pull, caching, distribution... whatever. The downside is that it is definitely not suitable for small projects and the overhead is substantial(especially during the development stage when you want to ship the product as soon as possible). On the other hand, once you get it going, it's an unstoppable beast.

Re: Good system design

#67

Post by Tade0 »

riv991 wrote: post #61
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…

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.

In the case of one project I've been in, the issue was the ORM creating queries, which Postgres deemed too large to do in-memory, so it fell back to performing them on-disk.

Interestingly it didn't even use JOIN everywhere it could because, according to the documentation, not all databases had the necessary features.

A hard lesson in the caveats of outsourcing work to ORMs.

Re: Good system design

#68

Post by mdavid626 »

riv991 wrote: post #61
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…

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.

Re: Good system design

#69

Post by dondraper36 »

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 I particularly like about the comments in this thread is how it proves that everything is a trade-off :)

Re: Good system design

#70

Post by quibono »

KronisLV wrote: post #26

> Schema design should be flexible, because once you have thousands or millions of records, it can be an enormous pain to change the schema. However, if you make it too flexible (e.g. by sticking everything in a “value” JSON column, or using “keys” and “values” tables to track arbitrary data) you load a ton of complexity into the application code (and likely buy some very awkward performance constraints). Drawing the…

> storing audit data in the same DB and it inevitably having functionality written against it, your audit data becoming a part of the business logic

What's the "proper" way to do this? Separate DB? Separate data store?

Post reply on HN