4 ms·

Is it just me, or is the basic knowledge of database normalization and indexing kind of a lost art these days? How many teams out there just "add cache and mor

Is it just me, or is the basic knowledge of database normalization and indexing kind of a lost art these days? How many teams out there just "add cache and more hardware" when no one ran an EXPLAIN on that one core query in a giant table? Perhaps we should do that before talking about anything "at scale".

sb8244 3y ago
I wrote a system that used the database as essentially a rules engine for "what work to execute next?" There were constraints about max throughput per key, waiting periods, etc. That system broke me down and built me back up over time. Really deep explaining, understanding the internals of how the data is actually being processed, then finding ways to improve it. I had 1-2 other people who were really interested in postgres that would help work on debugging. We would continuously get 100x query improvements every time we dove in. Including after we thought we maxed it out. I hope everyone gets the chance to work on something like that. It was really fun.
- PTOB 3y ago
  I would slaver at mouth over that opportunity.
- renegade-otter 3y ago
  If anyone starts a new side project, do yourself a favor - write your data access layer in straight SQL. With minimal wiring and safe variable substitution that most libraries provide, you will learn a lot.
- derefr 3y ago
  > a rules engine for "what work to execute next?" I'm afraid to inform you, that you have built a cluster workload manager. (Where a "cluster workload manager" is a type of job scheduler designed for — usually HPC — clusters, that prioritizes among ongoing abstract batch "jobs" that can each broken down into more granular atomic scheduleable work-units; and which accounts the often-unpredictable resource-consumption outcomes of those work-units, to the account of the job and its owning user+group+org, to then weight further executions of that job or other jobs owned by that user+group+org, so that ultimately, jobs can proceed at a QoS/rate that's 1. "fair", and 2. in proportion to the amount that a given customer is willing to pay for prioritization. "Business rules" in such a system, are expressed in terms of conditional coefficients or formulae to apply to the "costs" used to re-weight jobs.) Examples of cluster workload management software: • Slurm (https://en.wikipedia.org/wiki/Slurm_Workload_Manager https://en.wikipedia.org/wiki/Slurm_Workload_Manager) • IBM Spectrum LSF (https://www.ibm.com/products/hpc-workload-management https://www.ibm.com/products/hpc-workload-management) • technically, Kubernetes' kube-scheduler — though it's the most trivial possible implementation of one, without any of the ongoing-fairness-of-QoS logic. The key thing to know about these, is that it's nearly impossible to build one that lives "quiesced" inside a database (as you've probably found.) Imagine taking an OS process scheduler and trying to keep the state for that canonically inside a database. Then imagine multiplying the write load by 1000x, while keeping the realtime read requirements the same. It's just a bad idea. You can certainly persist the state of these systems into a DB — but what you really want is one or more active scheduler nodes, that keep a big in-memory ledger matrix, that can be mutated moment-to-moment, without needing to persist every single change to disk in some WAL/journal/etc. Like a regular OS scheduler. Or like, say, a game physics engine — very similar in how it's all about tiny little "integration" steps, many times per second.
  - sb8244 3y ago
    I was generic in how I described it because I didn't want to talk about the system (it doesn't matter). The system was for a very specific use case and the set of rules were all specific to that domain. The domain was not related to workload management or jobs, but you could generalize what it did in those terms. edit: removed some snark. I did want to rewrite this into an in-memory data structure (Elixir-based). It generally worked but the postgres solution was actually really really good for the domain it was built for.
    - derefr 3y ago
      My point was that — for just your use-case as originally stated — you probably could have taken a FOSS cluster workload manager like SLURM and "narrowed" it to your domain + use-case; rather than starting from a DBMS and adding all the stuff necessary to make it understand how to be a scheduler and then fighting with it to scale its use-case-impedance-mismatched state-storage backend to your production workload. (Where I assume you were "fighting with it" because, as you said, you were "torn down and built back up" in the process of making this system scale.) If, however, you had other database-related needs you didn't mention — e.g. being able to arbitrarily join the state data of this system as dimension data in OLAP queries on other, non-scheduler-y tables in that DB — then your constraints probably did indeed make using a DBMS the best choice here, despite the scaling challenges. (Also, funny enough, I am myself in the middle of writing a cluster-workload-manager as a custom in-memory Elixir daemon! But that's with the unique constraint of needing lightweight, realtime prioritization of workloads, each backing synchronous HTTP RPC requests. Having millions of teeny-tiny jobs that are usually over in milliseconds [but which can sometimes run for multiple minutes], isn't something any HPC-targeting workload manager is really architected for.)
      - sb8244 3y ago
        I don't think that would've been a good idea. I guess it's possible, but I'd rather trade known obstacles versus unknown obstacles (that will definitely exist). I said "torn down and built back up" in an endearing way. Sure it was difficult work at times, but it was super fun and we would spend 2 days at a time fixing it until something popped up 6 months later. But the amount of stuff I learned from that was (I will wager) exponentially more useful than figuring out how to adapt/run a fairly major FOSS.
lowercased 3y ago
I've worked in a couple of projects where the existing teams were converting some API calls to new tech and changing UI to accomodate the 'new way' of getting data because "the current is way too slow". And it was. Going to a common dashboard was taking... 45-50 seconds, and growing. Core was they were pulling all data - 20k records - in to a js datatable. "This is bad - we have to move to server side paging!" They'd convinced their clients (I've seen this variations of this process 5x in the past 6 years) to 'upgrade'. In at least 2 cases, I rewrote a couple of the queries to just pull what was needed - id/name/email - instead of full ORM records with nested associations. One screen went from 45 seconds to 1 second. And this took ... 10-15 minutes of digging and rework. Yes, if your real goal is you want to learn Hotwire or some other new tech... go for it, but it feels wrong to not even investigate why the code is slow in the first place. Other project - similar - took a screen from 50 seconds to 2 seconds by reducing the query. Then got the whole thing under a second with a couple of indexes. Now, yes, this isn't all specific to database optimization, but they feel related. This idea of "oh, compute is cheap, devs are expensive, just throw more hardware at it!" has to have some boundaries/limits, but they aren't often realized until you're really ... in deep.
- krembo 3y ago
  As a dba that gives professional services to solve postgres performance issues, I've seen the same too fu*ing many times that it made my eyes bleed.
zeroCalories 3y ago
Database optimization isn't something you'll need running a flask app on your personal machine, you'll only have to learn it while trying to handle tons of traffic, which I think explains why many developers are late to the party. That said, I've never worked on a team that didn't carefully consider their schema from both a performance and extendability perspective. Even if the average age of your team is 23, any half decent TL will know the basics. I imagine this is only a problem at startups founded by students.
- lowercased 3y ago
  > I imagine this is only a problem at startups founded by students. I think you imagine wrong. > I've never worked on a team that didn't carefully consider their schema from both a performance and extendability perspective. Count yourself very lucky.
  - zeroCalories 3y ago
    Maybe you're the unlucky one? How does someone that doesn't know basic system design get put in charge of designing a system?
    - renegade-otter 3y ago
      They murdered the Leetcode interview, so they must be good at everything else /s
    - rapsey 3y ago
      Because people with experience expect a certain salary level.
    - lowercased 3y ago
      Shortest answer is they're put in charge by someone who doesn't know that the appointee doesn't know. Either they don't know they're supposed to know that, or (ime) more often they don't know how to judge. As someone else replied, passing leetcode-style tests is taken as a mark of technical skill well beyond what it's intended to indicate. Glad to hear you've been part of well functioning competent teams of professionals. I know they exist - I've been there a couple times myself over the years, but it's not a given. I would probably say it's not even the norm if you include a wider range of places where tech/developer folks may be employed. A 'team lead' role is filled by someone who is dating the department manager's family member? Seen that happen. "Joe's been here longer, so he gets to make the final decision on things". OK, but Joe graduated high school last year, and he's cutting in to systems built by people with 20+ years of experience (both in software and in this domain), and Joe is replacing it with some cut/paste stuff he found on stackoverflow last week. I was going to produce a larger litany of dumb scenarios I've had to wrestle with, in painful detail, but unsure that would do much to convince you of anything. (venting does help me occasionally, but won't ATM).
mixmastamyk 3y ago
Running explain is easy. Understanding the explanation is the hard part, and for some reason resources are scarce.
mamcx 3y ago
Wait until you see DBs with non-sensical schemas, wrong data types usage (like store dates in text), non-existent Pk/FK/Checks constraints, no use of VIEWS... ie: Using a RDBMS as if was a NoSql and complaint that RDBMS are bad...
- sgarland 3y ago
  I've seen ints being deliberately stored as text. Not because the id field changed at some point and they just kept it around as legacy (I mean, bad idea, but fair enough), but they thought storing them as text would somehow be better. As to NoSQL, yeah - devs love shoving everything into a JSON column, and then complain that it's slow. MongoDB is of course there for their web scale needs.
sgarland 3y ago
I think most devs understand indexing at this point, if only because ORMs generally include it as a keyword. What they don't understand is why an index might fail to be used, which to be fair, is a massive subject on its own. MySQL is one thing, since it pretty much only has B+tree (plus FTS). It's also case-insensitive by default, so foot guns like "smith" != "Smith" go away. But of course, MySQL isn't cool anymore, and dear lord does Postgres have some gotchas. Wrong collation? No index. Wrong case? No index. You didn't explicitly cast something and the result technically works, but isn't exactly correct? No index. Used a partial index with `WHERE foo` and then queried with `WHERE foo IS NOT FALSE`? No index. Table stats are wrong because you haven't adjusted autovacuum/autoanalyze daemons? Believe or not, no index. As for normalization, yeah, that's right out. Unless you had a unicorn design your schema, 0% chance dev teams know or care about it until it bites them, and then it's a massive undertaking to fix it. Source: DBRE who is both very happy that my skillset is absurdly in demand, and also constantly screaming into the void about everything.

sb8244 3y ago

I wrote a system that used the database as essentially a rules engine for "what work to execute next?" There were constraints about max throughput per key, waiting periods, etc. That system broke me down and built me back up over time. Really deep explaining, understanding the internals of how the data is actually being processed, then finding ways to improve it. I had 1-2 other people who were really interested in postgres that would help work on debugging. We would continuously get 100x query improvements every time we dove in. Including after we thought we maxed it out. I hope everyone gets the chance to work on something like that. It was really fun.

PTOB 3y ago
I would slaver at mouth over that opportunity.
renegade-otter 3y ago
If anyone starts a new side project, do yourself a favor - write your data access layer in straight SQL. With minimal wiring and safe variable substitution that most libraries provide, you will learn a lot.
derefr 3y ago
> a rules engine for "what work to execute next?" I'm afraid to inform you, that you have built a cluster workload manager. (Where a "cluster workload manager" is a type of job scheduler designed for — usually HPC — clusters, that prioritizes among ongoing abstract batch "jobs" that can each broken down into more granular atomic scheduleable work-units; and which accounts the often-unpredictable resource-consumption outcomes of those work-units, to the account of the job and its owning user+group+org, to then weight further executions of that job or other jobs owned by that user+group+org, so that ultimately, jobs can proceed at a QoS/rate that's 1. "fair", and 2. in proportion to the amount that a given customer is willing to pay for prioritization. "Business rules" in such a system, are expressed in terms of conditional coefficients or formulae to apply to the "costs" used to re-weight jobs.) Examples of cluster workload management software: • Slurm (https://en.wikipedia.org/wiki/Slurm_Workload_Manager https://en.wikipedia.org/wiki/Slurm_Workload_Manager) • IBM Spectrum LSF (https://www.ibm.com/products/hpc-workload-management https://www.ibm.com/products/hpc-workload-management) • technically, Kubernetes' kube-scheduler — though it's the most trivial possible implementation of one, without any of the ongoing-fairness-of-QoS logic. The key thing to know about these, is that it's nearly impossible to build one that lives "quiesced" inside a database (as you've probably found.) Imagine taking an OS process scheduler and trying to keep the state for that canonically inside a database. Then imagine multiplying the write load by 1000x, while keeping the realtime read requirements the same. It's just a bad idea. You can certainly persist the state of these systems into a DB — but what you really want is one or more active scheduler nodes, that keep a big in-memory ledger matrix, that can be mutated moment-to-moment, without needing to persist every single change to disk in some WAL/journal/etc. Like a regular OS scheduler. Or like, say, a game physics engine — very similar in how it's all about tiny little "integration" steps, many times per second.
- sb8244 3y ago
  I was generic in how I described it because I didn't want to talk about the system (it doesn't matter). The system was for a very specific use case and the set of rules were all specific to that domain. The domain was not related to workload management or jobs, but you could generalize what it did in those terms. edit: removed some snark. I did want to rewrite this into an in-memory data structure (Elixir-based). It generally worked but the postgres solution was actually really really good for the domain it was built for.
  - derefr 3y ago
    My point was that — for just your use-case as originally stated — you probably could have taken a FOSS cluster workload manager like SLURM and "narrowed" it to your domain + use-case; rather than starting from a DBMS and adding all the stuff necessary to make it understand how to be a scheduler and then fighting with it to scale its use-case-impedance-mismatched state-storage backend to your production workload. (Where I assume you were "fighting with it" because, as you said, you were "torn down and built back up" in the process of making this system scale.) If, however, you had other database-related needs you didn't mention — e.g. being able to arbitrarily join the state data of this system as dimension data in OLAP queries on other, non-scheduler-y tables in that DB — then your constraints probably did indeed make using a DBMS the best choice here, despite the scaling challenges. (Also, funny enough, I am myself in the middle of writing a cluster-workload-manager as a custom in-memory Elixir daemon! But that's with the unique constraint of needing lightweight, realtime prioritization of workloads, each backing synchronous HTTP RPC requests. Having millions of teeny-tiny jobs that are usually over in milliseconds [but which can sometimes run for multiple minutes], isn't something any HPC-targeting workload manager is really architected for.)
    - sb8244 3y ago
      I don't think that would've been a good idea. I guess it's possible, but I'd rather trade known obstacles versus unknown obstacles (that will definitely exist). I said "torn down and built back up" in an endearing way. Sure it was difficult work at times, but it was super fun and we would spend 2 days at a time fixing it until something popped up 6 months later. But the amount of stuff I learned from that was (I will wager) exponentially more useful than figuring out how to adapt/run a fairly major FOSS.

lowercased 3y ago

I've worked in a couple of projects where the existing teams were converting some API calls to new tech and changing UI to accomodate the 'new way' of getting data because "the current is way too slow". And it was. Going to a common dashboard was taking... 45-50 seconds, and growing. Core was they were pulling all data - 20k records - in to a js datatable. "This is bad - we have to move to server side paging!" They'd convinced their clients (I've seen this variations of this process 5x in the past 6 years) to 'upgrade'. In at least 2 cases, I rewrote a couple of the queries to just pull what was needed - id/name/email - instead of full ORM records with nested associations. One screen went from 45 seconds to 1 second. And this took ... 10-15 minutes of digging and rework. Yes, if your real goal is you want to learn Hotwire or some other new tech... go for it, but it feels wrong to not even investigate why the code is slow in the first place. Other project - similar - took a screen from 50 seconds to 2 seconds by reducing the query. Then got the whole thing under a second with a couple of indexes. Now, yes, this isn't all specific to database optimization, but they feel related. This idea of "oh, compute is cheap, devs are expensive, just throw more hardware at it!" has to have some boundaries/limits, but they aren't often realized until you're really ... in deep.

krembo 3y ago
As a dba that gives professional services to solve postgres performance issues, I've seen the same too fu*ing many times that it made my eyes bleed.

zeroCalories 3y ago

Database optimization isn't something you'll need running a flask app on your personal machine, you'll only have to learn it while trying to handle tons of traffic, which I think explains why many developers are late to the party. That said, I've never worked on a team that didn't carefully consider their schema from both a performance and extendability perspective. Even if the average age of your team is 23, any half decent TL will know the basics. I imagine this is only a problem at startups founded by students.

lowercased 3y ago
> I imagine this is only a problem at startups founded by students. I think you imagine wrong. > I've never worked on a team that didn't carefully consider their schema from both a performance and extendability perspective. Count yourself very lucky.
- zeroCalories 3y ago
  Maybe you're the unlucky one? How does someone that doesn't know basic system design get put in charge of designing a system?
  - renegade-otter 3y ago
    They murdered the Leetcode interview, so they must be good at everything else /s
  - rapsey 3y ago
    Because people with experience expect a certain salary level.
  - lowercased 3y ago
    Shortest answer is they're put in charge by someone who doesn't know that the appointee doesn't know. Either they don't know they're supposed to know that, or (ime) more often they don't know how to judge. As someone else replied, passing leetcode-style tests is taken as a mark of technical skill well beyond what it's intended to indicate. Glad to hear you've been part of well functioning competent teams of professionals. I know they exist - I've been there a couple times myself over the years, but it's not a given. I would probably say it's not even the norm if you include a wider range of places where tech/developer folks may be employed. A 'team lead' role is filled by someone who is dating the department manager's family member? Seen that happen. "Joe's been here longer, so he gets to make the final decision on things". OK, but Joe graduated high school last year, and he's cutting in to systems built by people with 20+ years of experience (both in software and in this domain), and Joe is replacing it with some cut/paste stuff he found on stackoverflow last week. I was going to produce a larger litany of dumb scenarios I've had to wrestle with, in painful detail, but unsure that would do much to convince you of anything. (venting does help me occasionally, but won't ATM).

mixmastamyk 3y ago

Running explain is easy. Understanding the explanation is the hard part, and for some reason resources are scarce.

mamcx 3y ago

Wait until you see DBs with non-sensical schemas, wrong data types usage (like store dates in text), non-existent Pk/FK/Checks constraints, no use of VIEWS... ie: Using a RDBMS as if was a NoSql and complaint that RDBMS are bad...

sgarland 3y ago
I've seen ints being deliberately stored as text. Not because the id field changed at some point and they just kept it around as legacy (I mean, bad idea, but fair enough), but they thought storing them as text would somehow be better. As to NoSQL, yeah - devs love shoving everything into a JSON column, and then complain that it's slow. MongoDB is of course there for their web scale needs.

sgarland 3y ago

I think most devs understand indexing at this point, if only because ORMs generally include it as a keyword. What they don't understand is why an index might fail to be used, which to be fair, is a massive subject on its own. MySQL is one thing, since it pretty much only has B+tree (plus FTS). It's also case-insensitive by default, so foot guns like "smith" != "Smith" go away. But of course, MySQL isn't cool anymore, and dear lord does Postgres have some gotchas. Wrong collation? No index. Wrong case? No index. You didn't explicitly cast something and the result technically works, but isn't exactly correct? No index. Used a partial index with `WHERE foo` and then queried with `WHERE foo IS NOT FALSE`? No index. Table stats are wrong because you haven't adjusted autovacuum/autoanalyze daemons? Believe or not, no index. As for normalization, yeah, that's right out. Unless you had a unicorn design your schema, 0% chance dev teams know or care about it until it bites them, and then it's a massive undertaking to fix it. Source: DBRE who is both very happy that my skillset is absurdly in demand, and also constantly screaming into the void about everything.