4 ms·

One thing that worth taking into consideration is that this happened in 2002. When the databases were not in cloud, the ops was done by dba’s and key prefix com

One thing that worth taking into consideration is that this happened in 2002. When the databases were not in cloud, the ops was done by dba’s and key prefix compression thats omnipresent today was likely not that common or potentially not even implemented/available. But i don’t think the point of the post is whats right/wrong way of doing it. The point as mentioned by few here is that programmers makes mistakes. They are costly and will be costly if in tech industry, we continue to boot experienced engineers… the tacit knowledge those engineers have gained wi ll not be passed on and this means more people have to figure things out by themselves

adfgaertyqer 5y ago
You don't really need compression. Rows only need to persist for about an hour. The table can't be more than a few MiB. We can debate the Correct Implementation all day long. The fact of the matter is that adding any index to the original table, even the wrong index, would lead to a massive speedup. We can debate 2x or 5x speedups from compression or from choosing a different schema or a different index, but we get 10,000x from adding any index at all.
- ipaddr 5y ago
  Just to make this fun. Adding an index now increases the insert operation cost/time and adds additional storage. If insert speed/volume is more important than reads keep the indexes away. Replicate and create an index on that copy.
  - sigstoat 5y ago
    > Adding an index now increases the insert operation cost/time that insert is happening after you've checked the table to see if the record is present. so two operations whose times we care about are "select and accept email" and "select, tell the sender to come back in 20 minutes, and then insert". the insert time effectively doesn't matter, unless you've decided to abandon discussion of the original table entirely without mentioning it.
Philip-J-Fry 5y ago
>One thing that worth taking into consideration is that this happened in 2002. When the databases were not in cloud, the ops was done by dba’s Are you implying that isn't the case today? Thousands of (big) companies are still like that and will continue to be like that. I write my own SQL, design tables and stuff, submit it for a review by someone 10x more qualified than myself, and at the end of the day I'll get a message back from a DBA saying "do this, it's better".
- mixmastamyk 5y ago
  The question of it being in the cloud or not is highly orthogonal to schema design.
- WJW 5y ago
  If you have one, more power to you. I'm currently making a good living as a freelance DBA/SRE for startups. With a team of 3-5 devs, some of which frontend and/or mobile devs, proper database knowledge is definitely thin on the ground in some places.
hinkley 5y ago
In 2002, you started seeing major increases in query run time with as little as 5 joins. Once you hit six you had to start thinking about rearchitecting your data or living with slow response times. There was a lot of pressure to relax 3NF as being too academic and not practical. Around then, I had a friend who was using a pattern of varchar primary keys so that queries that just needed the (unique) name and not the metadata could skip the join. We all acted like he was engaging in the Dark Arts.
jhgb 5y ago
> When the databases were not in cloud, the ops was done by dba’s and key prefix compression thats omnipresent today was likely not that common or potentially not even implemented/available. To my understanding, for example Firebird/Interbase had automatic key prefix compression as far back as early 2000s at the very least. I don't believe you could even turn it off.

adfgaertyqer 5y ago

You don't really need compression. Rows only need to persist for about an hour. The table can't be more than a few MiB. We can debate the Correct Implementation all day long. The fact of the matter is that adding any index to the original table, even the wrong index, would lead to a massive speedup. We can debate 2x or 5x speedups from compression or from choosing a different schema or a different index, but we get 10,000x from adding any index at all.

ipaddr 5y ago
Just to make this fun. Adding an index now increases the insert operation cost/time and adds additional storage. If insert speed/volume is more important than reads keep the indexes away. Replicate and create an index on that copy.
- sigstoat 5y ago
  > Adding an index now increases the insert operation cost/time that insert is happening after you've checked the table to see if the record is present. so two operations whose times we care about are "select and accept email" and "select, tell the sender to come back in 20 minutes, and then insert". the insert time effectively doesn't matter, unless you've decided to abandon discussion of the original table entirely without mentioning it.

Philip-J-Fry 5y ago

>One thing that worth taking into consideration is that this happened in 2002. When the databases were not in cloud, the ops was done by dba’s Are you implying that isn't the case today? Thousands of (big) companies are still like that and will continue to be like that. I write my own SQL, design tables and stuff, submit it for a review by someone 10x more qualified than myself, and at the end of the day I'll get a message back from a DBA saying "do this, it's better".

mixmastamyk 5y ago
The question of it being in the cloud or not is highly orthogonal to schema design.
WJW 5y ago
If you have one, more power to you. I'm currently making a good living as a freelance DBA/SRE for startups. With a team of 3-5 devs, some of which frontend and/or mobile devs, proper database knowledge is definitely thin on the ground in some places.

hinkley 5y ago

In 2002, you started seeing major increases in query run time with as little as 5 joins. Once you hit six you had to start thinking about rearchitecting your data or living with slow response times. There was a lot of pressure to relax 3NF as being too academic and not practical. Around then, I had a friend who was using a pattern of varchar primary keys so that queries that just needed the (unique) name and not the metadata could skip the join. We all acted like he was engaging in the Dark Arts.

jhgb 5y ago

> When the databases were not in cloud, the ops was done by dba’s and key prefix compression thats omnipresent today was likely not that common or potentially not even implemented/available. To my understanding, for example Firebird/Interbase had automatic key prefix compression as far back as early 2000s at the very least. I don't believe you could even turn it off.