Squeeze the hell out of the system you have • Hacker News Board

Re: Squeeze the hell out of the system you have

#261

Post by Buttons840 » Sat, Aug 12, 2023, 6:55 AM UTC

endisneigh wrote: post #6

The bit on the database performance issues leads me to my hottest, flamiest take for new projects: - Design your application's hot path to never use joins. Storage is cheap, denormalize everything and update it all in a transaction. It's truly amazing how much faster everything is when you eliminate joins. For your ad-hoc queries you can replicate to another database for analytical purposes. On this note, I have mixe…

> On this note, I have mixed feelings about Amazon's DynamoDB, but one things about it is to use it properly you need to plan your use first, and schema second. I think there's something you can take from this even with a RDBMS.

This captures the experience I've had with DynamoDB and document databases in general. They appear more flexible at first, but in truth they are much less flexible. You must get them right up front or your going to be paying thousands of dollars every month in AWS bills just for DynamoDB. The need to get things right up front is the opposite of flexibility.

Re: Squeeze the hell out of the system you have

#262

Post by Zanfa » Sat, Aug 12, 2023, 6:58 AM UTC

yashap wrote: post #249

crazygringo wrote: post #159

Earlier quoted context omitted.

That is a hot take... ;) But joins should never impact performance in a large way if they're on the same server and properly indexed. "It's truly amazing how much faster everything is when you eliminate joins" is just not true if you're using joins correctly. Sadly, many developers simply never bother to learn. On the other hand, having to write a piece of data to 20 different spots instead of 1 is going to be dramat…

Joins are not inherently expensive, but they can lead to expensive queries. For example, say I want to find the 10 most recent users with a phone number as their primary contact method: SELECT … FROM User JOIN ContactMethod on ContactMethod.userId = User.id WHERE ContactMethod.priority = ‘primary’ AND ContactMethod.type = ‘phoneNumber’ ORDER BY User.createdAt DESC LIMIT 10 If there are a very large number of users, a…

> If there are a very large number of users, and a very large number of phone number primary contacts, you cannot make this query fast/efficient

I think specific numbers would help make this point better. With a few hundred thousand to low millions of users this should be plenty fast in Postgres for example. That’s magnitudes more than most startups ever reach anyway.

Re: Squeeze the hell out of the system you have

#263

Post by 59nadir » Sat, Aug 12, 2023, 7:07 AM UTC

roughly wrote: post #76

midasuni wrote: post #73

Earlier quoted context omitted.

And unknown unknowns is a great way to communicate with stakeholders too

Žižek has a followup to that quote: "What he forgot to add was the crucial fourth term: the "unknown knowns," the things we don't know that we know." I've found it's really critical during the project planning phase to get to not just where the boundaries of our knowledge are, but also where are the things we're either tacitly assuming or not even aware that we've assumed. An awful lot of postmortems I've been a part…

I think unknown knowns are more easy to spot when teaching newcomers how the system works. Their questions will sometimes be about things we hadn't even considerered (at least in some time) to be the case, but when you have to spell everything out it is indeed the case. In terms of teaching unknown knowns are critical to identify and instead make known knowns so that everyone can end up with a mostly equal playing field.

As an example, there are a lot of unknown knowns that you accumulate over the years in certain lower level languages that need to be spelled out more clearly to someone who is coming at it as a later endeavor. It's entirely possible to spend all your time in a completely managed language nowadays and the concept of the stack, heap, etc., will be largely alien to you. These ideas and their limitations need to be spelled out clearly in order for someone to build the same knowledge base and intuition.

Unknown knowns are essentially endless in nature and extremely hard to find unless you have someone who simply doesn't know to basically fall into traps and guide you toward finding your hidden knowledge.

Re: Squeeze the hell out of the system you have

#264

Post by bbojan » Sat, Aug 12, 2023, 7:07 AM UTC

endisneigh wrote: post #6

The bit on the database performance issues leads me to my hottest, flamiest take for new projects: - Design your application's hot path to never use joins. Storage is cheap, denormalize everything and update it all in a transaction. It's truly amazing how much faster everything is when you eliminate joins. For your ad-hoc queries you can replicate to another database for analytical purposes. On this note, I have mixe…

> - Design your application's hot path to never use joins. Storage is cheap, denormalize everything and update it all in a transaction.

Hard disagree. You just re-implemented a database engine in your application code. Poorly.

Re: Squeeze the hell out of the system you have

#265

Post by pythooooi » Sat, Aug 12, 2023, 7:08 AM UTC

It has never been as easy as today to see slow / shitty queries life.

Open telemetry, tracing and grafanasupport with k8s you basically get it running in a day.

But with performance it's always the same issue: people apparently do not think about it and the optimizations necessary are often: enable query statistics, finding the query in code and either fix/add an index or slightly rewrite your code or add some kind of cache (query, etc).

The last time I analyzed a slow query apparently no one before me spotted the huge memory footprint of that PostgreSQL query and focused on why it runs slower in one region vs the other.

You know the '10x developer' myth?

Yeah if you still look like a sheep after working in it so long and thinking about architecture and performance is still not second nature for you...

I'm slightly cynical because I love performance and optimizing it but 99% of those issues are no issues just people not knowing enough about their tools.

Re: Squeeze the hell out of the system you have

#266

Post by dajonker » Sat, Aug 12, 2023, 7:10 AM UTC

endisneigh wrote: post #6

The bit on the database performance issues leads me to my hottest, flamiest take for new projects: - Design your application's hot path to never use joins. Storage is cheap, denormalize everything and update it all in a transaction. It's truly amazing how much faster everything is when you eliminate joins. For your ad-hoc queries you can replicate to another database for analytical purposes. On this note, I have mixe…

Very hot take indeed. As with all things, it depends and use the query planner to measure what actually makes a difference.

In our application we have one important join that actually makes things a lot faster than the denormalized alternatieve. The main table has about 8 references to an organization table. To figure out what rows should be selected for a particular organization, you could either query on those 8 columns, making a very big where/or clause. As it turns out, PostgreSQL will usually end up doing a full table scan despite any index you would create.

Instead, there is an auxiliary table with two columns, one for organization and one reference to the main table. Joining on this table simplifies the query and also turns out to be much faster.

Re: Squeeze the hell out of the system you have

#267

Post by kreetx » Sat, Aug 12, 2023, 7:23 AM UTC

re-thc wrote: post #257

theptip wrote: post #211

Earlier quoted context omitted.

Honestly I couldn’t disagree more. I built a startup and paid little attention to perf for years 1-5, and finally in year 6 we started to get bitten by some perf issues in specific tables, and spent a few engineer-months optimizing. In terms of tech debt it would have been way more expensive to make everything perform well from the start, we would have moved much slower and probably failed during a few crunch points.…

> I built a startup and paid little attention to perf for years 1-5, and finally in year 6 we started to get bitten by some perf issues in specific tables, and spent a few engineer-months optimizing. This screams of if I don’t see it it the problem doesn’t exist view of the world. How do you know it’s not a problem? Perhaps customers would have signed up if it as faster? The problem is also treating it in terms of bu…

A denormalized database model is considered bad desig to begin with, and has performance costs on its own. This is why the OP says this is a "hot take". :)

Maybe there are situations where this actually helps, although the resulting datastructure to me looks more like a multi-key cache.

Re: Squeeze the hell out of the system you have

#268

Post by unnouinceput » Sat, Aug 12, 2023, 7:26 AM UTC

yashap wrote: post #249

crazygringo wrote: post #159

Earlier quoted context omitted.

That is a hot take... ;) But joins should never impact performance in a large way if they're on the same server and properly indexed. "It's truly amazing how much faster everything is when you eliminate joins" is just not true if you're using joins correctly. Sadly, many developers simply never bother to learn. On the other hand, having to write a piece of data to 20 different spots instead of 1 is going to be dramat…

Joins are not inherently expensive, but they can lead to expensive queries. For example, say I want to find the 10 most recent users with a phone number as their primary contact method: SELECT … FROM User JOIN ContactMethod on ContactMethod.userId = User.id WHERE ContactMethod.priority = ‘primary’ AND ContactMethod.type = ‘phoneNumber’ ORDER BY User.createdAt DESC LIMIT 10 If there are a very large number of users, a…

If the tables involved in the join are of 100M+ records what I do when the joins use varchar columns to improve the performance is to use an additional integer column of the varchar one that is a CRC of it (or hash if you prefer that) and use the integer one instead in the join.

Re: Squeeze the hell out of the system you have

#269

Post by tlarkworthy » Sat, Aug 12, 2023, 7:31 AM UTC

yashap wrote: post #249

crazygringo wrote: post #159

Earlier quoted context omitted.

That is a hot take... ;) But joins should never impact performance in a large way if they're on the same server and properly indexed. "It's truly amazing how much faster everything is when you eliminate joins" is just not true if you're using joins correctly. Sadly, many developers simply never bother to learn. On the other hand, having to write a piece of data to 20 different spots instead of 1 is going to be dramat…

Joins are not inherently expensive, but they can lead to expensive queries. For example, say I want to find the 10 most recent users with a phone number as their primary contact method: SELECT … FROM User JOIN ContactMethod on ContactMethod.userId = User.id WHERE ContactMethod.priority = ‘primary’ AND ContactMethod.type = ‘phoneNumber’ ORDER BY User.createdAt DESC LIMIT 10 If there are a very large number of users, a…

Yes this is the exact situation where sql falls short. You can't make cross-table indexes to serve OLAP-esq queries, and the most recent X is the common one for pagination in applications. I prefer to denormalize manually at write time in a transaction, rather than use triggers or materialized views.

Re: Squeeze the hell out of the system you have

#270

Post by GrumpyNl » Sat, Aug 12, 2023, 7:33 AM UTC

endisneigh wrote: post #6

The bit on the database performance issues leads me to my hottest, flamiest take for new projects: - Design your application's hot path to never use joins. Storage is cheap, denormalize everything and update it all in a transaction. It's truly amazing how much faster everything is when you eliminate joins. For your ad-hoc queries you can replicate to another database for analytical purposes. On this note, I have mixe…

When your queries become slow because of joins, you havent designed your table structures properly.