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".
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.
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)
• IBM Spectrum LSF (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.
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.
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.)
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.
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.
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.
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.
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).
Students still care and should have learned these principles in uni. They tend to overengineer and focus too much on the technical challenges
In bigger companies, most IT people tend to be people who got the job because they started coding back in the day when you could get by using just common sense and gut feeling. Today we know those often lead to dark patterns and bad database design, but those people have now grown into architecture and management positions and have no clue what they are talking about.
This is very prevalent in tech teams of non tech companies (e.g. manufacturing, banking, healthcare, CPGs,...)
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.
ie: Using a RDBMS as if was a NoSql and complaint that RDBMS are bad...
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.