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

81–90 of 400 posts

Re: Good system design

#81

Post by KronisLV »

quibono wrote: post #70
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?

Typically you want your audit/log data to be immutable and kept in an append only data store.

Whether that's a typical relational DB or something more specialized (like a log shipping solution) that's up to you, but usually it would be separate from the main DB.

If you need some functionality that depends on events that have taken place, you probably want to store information about those events in the main data store (but only what's needed for that functionality, not a list of all mutations done to a table like audit data might include).

In general, it's nice to have such a clear boundary of where the business domain ends and where the aux. stuff to help you keep it running goes - your logs and audit data, analytics and metrics, tracing spans and so on.

Edit: as a critique of my own arguments here, I will admit that doing the above can introduce some complexity and that in simpler systems it might be overkill. But I've seen what happens when everything is just in one huge DB instance, where about 90% of the overall schema size is literally due to records in those audit tables and everyone is surprised why opening the "History" tab for a record takes a while (and anything else that references said history, e.g. visibility of additional records), and it's not great either.

Re: Good system design

#82

Post by setnone »

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…

Some systems are designed to last vs. designed to adapt

Re: Good system design

#83

Post by thisbeensaid »

Since the author praises proper use of databases and talks about event bus, background jobs and caching, I highly recommend to check out https://dbos.dev if you have Python or TypeScript backends. DBOS nicely solves common challenges in simple and complex systems and can eliminate the need for running separate services such as Kafka, Redis or Celery. The best: DBOS can be used as a dependency and doesn't require deploying a separate service.

Very recently discussed here a week ago: https://news.ycombinator.com/item?id=44840693

Re: Good system design

#84

Post by bubblebeard »

Very good article, right on point!

I do wonder about why the author left out testing, documentation and qa tool design though. To my mind, writing a proper phpcs or whatever to ensure everyone on the team writes code in a consistent way is crucial. Without documentation we end up forgetting why we did certain things. And without tests refactors are a nightmare.

Re: Good system design

#85

Post by richardlblair »

Tade0 wrote: post #67
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.

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.

I've worked both with ORMs and without. As a general rule, if the ORM is telling you there is something wrong with your query / tables it is probably right.

The only time I've seen this is my career was a project that was an absolute pile of waste. The "CTO" was self taught, all the tables were far too wide with a ton of null values. The company did very well financially, but the tech was so damn terrible. It was such a liability.

Re: Good system design

#86

Post by dondraper36 »

bubblebeard wrote: post #84

Very good article, right on point! I do wonder about why the author left out testing, documentation and qa tool design though. To my mind, writing a proper phpcs or whatever to ensure everyone on the team writes code in a consistent way is crucial. Without documentation we end up forgetting why we did certain things. And without tests refactors are a nightmare.

Especially given that generating documentation and tests (of course, with manual revision) is so much faster with, say, Claude Code.

Re: Good system design

#87

Post by dondraper36 »

mdavid626 wrote: post #72
dondraper36 wrote: post #62

Earlier quoted context omitted.

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.

Not really disagreeing with you here, but that "later" never comes for most companies.

Re: Good system design

#88

Post by jameshart »

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.

If you need all the orders and all the customers sure.

But usually you need some of the orders and you need the customer info associated with them. Often the set of orders you’re interested in might even be filtered by attributes of the customers they belong to.

The decision of whether to normalize our results of a database query into separate sets of orders and customers, or to return a single joined dataset of orders with customer data attached, is completely orthogonal to the decision of whether to join data in the database.

Re: Good system design

#89

Post by richardlblair »

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…

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.

Re: Good system design

#90

Post by oftenwrong »

ZYbCRq22HbJ2y7 wrote: post #13

> You’re supposed to store timestamps instead, and treat the presence of a timestamp as true. I do this sometimes but not always - in my view there’s some value in keeping a database schema immediately-readable. Seems overly negative of broad advice on a good pattern? is_on => true on_at => 1023030 Sure, that makes sense. is_a_bear => true a_bear_at => 12312231231 Not so much, as most bears do not become bears at som…

A boolean is smaller, which is a relevant consideration for some workloads. For example, you may be pre-aggregating a large amount of data to serve a set of analytical queries which do not care about the associated timestamp. The smaller data type is more efficient both in storage and in query execution.

Additionally, there are situations where it is logical to store a boolean. For example, if the boolean denotes an outcome:

    process_executed_at timestamp not null
    process_succeeded boolean not null
Post reply on HN