Squeeze the hell out of the system you have(blog.danslimmon.com) |
Squeeze the hell out of the system you have(blog.danslimmon.com) |
glad to see there was a better ending to the story though.
Given the options to optimize SQL, move read operations to replicas, shard data or go towards micro services, optimizing SQL is the easy choice.
However, sometimes everyone needs to chill and sharpen the tool they have at hand. It might prove much more capable than first anticipated. Or you may be holding the tool wrong to a degree.
Weird thing about computers, even after a fresh install of your favorite OS, the whole thing is sitting on a mountain of complexity, and that's before you start installing programs, browse the web, etc
Only the die-hard use things like MINIX[0] to do their computing. Correction: MINIX is in the Intel Management Engine so you have /two/ computers.
Great to see this cultural side-effect called out.
It’s easy to fall in love with complexity, especially since you see a lot of complexity in existing systems. But those systems became complex as they evolved to meet user needs, or for other reasons, over time. Complex systems are impressive, but you need to make sure that your team has people who recognize the heavy costs of complexity, and who can throw their engineering efforts directly against the most important problems your team faces.
A lot of teams that think this way end up with really high oncall burdens and then never have the time to even iterate on their infrastructure.
Now... Who knows why our AWS bill is so high?
With real hardware in a DC, you'd have to justify large capital expenditures to do something that stupid.
Yet some interesting patterns will emerge if teams accept some basic constraints:
1. A low-cpu-power client-process is identical to a resource taxed server-process
2. A systems client-server pattern will inevitably become functionally equivalent to inter-server traffic. Thus, the assumption all high performance systems degenerate into a hosted peer-to-peer model will counterintuitively generalize. Accordingly, if you accept this fact early, than one may avoid re-writing a code-base 3 times, and trying to reconcile a bodged API.
3. Forwarding meaningful information does not mean collecting verbose telemetry, then trying to use data-science to fix your business model later. Assume you will eventually either have high-latency queuing, or start pooling users into siloed contexts. In either case, the faulty idea of a single database shared-state will need seriously reconsidered at around 40k users, and later abandoned after around 13m users.
4. sharding only buys time at the cost of reliability. You may disagree, but one will need to restart a partitioned-cluster under heavy-load to understand why.
5. All complex systems fail in improbable ways. Eventually consistent is usually better than sometimes broken. Thus, solutions like Erlang/Elixir have been around for awhile... perhaps the OTP offers a unique set of tradeoffs.
6. Everyone thinks these constraints don't apply at first. Thus, will repeat the same tantalizing... yet terrible design choices... others have repeated for 40+ years.
Good luck, =) J
There's definitely a subset of the industry which seems to really love complexity, and I suspect a large part of that is caused by the incentives involved and the need for "growth" and justification for one's continued employment.
Reads can scale to infinity.
YAGNI? But you could say that about a lot of things and this also gives you other options like releasing to a subset of customers (rolling release system like LTS etc.)
I work somewhere that has done this but for other driving reasons but it is a scalability dream. It is the web equivalent of desktop software running in Citrix! I haven’t seen anyone tune SQL in 4 years there whereas it has been a regular pastime everywhere else!
Yes you can’t do this for social networks but you can do it for most “customer with isolated clients” type shops which is most companies.
And I get that upgrades can be scary, but often they are relatively low cost.
Leaving everyone on the old system unhappy… means they will eventually push to re-platform, or rebuild, instead of just doing suggested maintenance along the way to keep they system they have in good shape.
My advice… do the maintenance. Do all the maintenance! Don’t just drive it into the ground and get mad when it breaks; change the oil and tires and spring for a car wash and some new wiper blades every now and then and you’ll be happier in the long run.
TLDR: when facing problems like these, it’s too easy to look at grandiose solutions and, because they look like cool engineering problems, we end up justifying that it’s worth and reasonable to take on such year-long projects. But most often, the boring incremental solutions are easier and cheaper to achieve, while delivering benefit along the way.
This article shows examples of both, and I’m happy to see that the “boring” solution won.
The advice is obvious when you're thinking at that level of abstraction. Which suggests that, in practice, people who are architecting such systems rarely think at that level of abstraction. Which is why it is nice to have posts like this, that periodically remind us to get our heads out of the daily minutiae and consider the bigger picture (of complexity tradeoffs, realistic projections, staffing and availability, etc.)
- Voltaire
That is true of every ORM in existence. The easiest thing to do is naively follow the object graph in code, because that's what the ORM gives you. If the ORM was to somehow add friction here to encourage some other approach it would be panned as "too hard!!1" and fade away into obscurity.
I don't think this is generally true. This sort of work has a lifecycle. By the time the performance problems emerge, often years later, the people involved that understood what is going on, and the tooling involved in building the system, are no longer readily at hand. This gets multiplied by the fact that often an N+1 problem is compounded by decoupling strategies (network APIs, etc.) where the front and backend must both be evolved: The backend needs a new, better API and the frontend has to adopt it. Often these are two completely distinct technology stacks, and also often spread among multiple parties which cannot easily coordinate change.
Anything is possible, of course. But "easy" goes right out the window.
- 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 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.
In fact, I'd go as far to say as joins are unnecessary for nonanalytical purposes these days. Storage is so mind booglingly cheap and the major DBs have ACID properties. Just denormalize, forreal.
- Use something more akin to UUIDs to prevent hot partitions. They're not a silver bullet and have their own downsides, but you'll already be used to the consistently "OK" performance that can be horizontally scaled rather than the great performance of say integers that will fall apart eventually.
/hottakes
my sun level take would be also to just index all columns. but that'll have to wait for another day.
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.
Instead we paid probably a few $k/mo more than we really needed to on machines, and in return saved man-months of effort at a time when we couldn’t hire enough engineers and the opportunity cost for feature work was huge. (Keep in mind that making everything perform well would have required us to do 10-20x as much work, because we could not know ahead of time where the hot spots would be. Some were surprising.)
Joins may be evil at scale, but most startups don’t have scale problems, at least not at first.
Denormalizing can be a good optimization but you pay a velocity cost in keeping all the copies in sync across changes. Someone will write the bug that misses a denormalized non-canonical field and serves up stale data to a user. It’s usually cheaper (in total cost, ie CapEx+OpEx) to write the join and optimize later with a read-aside cache or whatever, rather than contorting your schema.
Isn’t that the cost of one engineer already?
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 business value and/or cost.
A lot of things are “free” and yet it’s ignored.
For most people, in simple cases like turning on http3, brotli, switching to newer instances and many others are all quick wins that I see ignored 90% of the time.
A good design, implementing some good practices etc are performance specific and don’t always cost more.
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 dramatically slower performance-wise, not to mention make your queries tremendously more complex and bug-prone, when you remember to update a value in 18 spots but forget about 2 of them.
You mention cheap storage as an advantage for denormalizing, but storage is the least problem here. It's a vastly larger surface area for bugs, and terrible write performance (that can easily chew up read performance).
Memory is the new disk, and disk is the new tape.
You want everything to remain resident in memory, and spool backups and transaction logs to disk.
If you’re joining from disk, you’ve probably done something wrong.
E.g.: compression is often a net win because while it uses more CPU, it allows more data to fit into memory. And if it doesn’t fit, it reduces the disk I/O required.
This is why I look upon JSON-based document databases in horror. They’re bloating the data out many times over by denormalizing and then expand that into a verbose and repetitive text format.
This is why we have insanity like placeholders for text on web apps now — they’re struggling to retrieve a mere kilobyte of data!
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, and a very large number of phone number primary contacts, you cannot make this query fast/efficient (on most RDBMSes). You CAN make this query fast/efficient by denormalizing, ensuring the user creation date and primary contact method are on the same table, and then creating a compound index. But if they’re in separate tables, and you have to join, you can’t make it efficient, because you can’t create cross-table compound indeces.
This pattern of join, filter by something in table A, sort by something in table B, and query out one page of data, is something that comes up a lot. It’s why ppl thing joins are generally expensive, but it’s more like they’re expensive in specific cases.
I also don’t think it’s worth the trouble “never using joins” for an existing project. Denormalize as necessary. But for a green one I honestly think since our access patterns can be understood as you continue you can completely get rid of joins.
Again, assuming your new project can’t fit on a single machine. If it can you’re best just following the “traditional” advice, or better yet keep everything in memory.
Facebook really only has people, posts, and ads.
Netflix really only has accounts and shows.
Amazon (the product) really only has sellers, buyers, and products, with maybe a couple more behind the scene for logistics.
The reason for this is because tall applications are easy. Much, much easier than wide applications, which are often called "enterprise". Enterprise software is bad because it's hard. This is where the most unexplored territory is. This is where untold riches lie. The existing players in this space are abysmally bad at it (Oracle, etc.). You will be too, if you enter it with a tall mindset.
Advice like "never user joins" and "design around a single table" makes a lot of sense for tall applications. It's awful, terrible, very bad, no-good advice for wide applications. You see this occasionally when these very tall companies attempt to do literally anything other than their core competency: they fail miserably, because they're staffed with people who hold sacrosanct this kind of advice that does not translate to the vast space of "wide" applications. Just realize that: your advice is for companies doing easy things who are already successful and have run out of low-hanging fruit. Even tall applications that aren't yet victims of their own success do not need to think about butchering their data model in service of performance. Only those who are already vastly successful and are trying to squeeze out the last juices of performance. But those are the people who least need advice. This kind of tall-centered advice, justified with "FAANG is doing it so you should too" and "but what about when you have a billion users?" is poisoning the minds of people who set off to do something more interesting than serve ads to billions of people.
- Sellers (Amazon, third party, retail store) - Inventory (forecasting, recommendation) - Customers (comments, ratings, returns, preferences) - Warehouses (5+ distinct types, filled with custom machines) - Transit Options (long haul, air, vans, cars, bikes, walking, boats) - Delivery Partners (DSP, Flex, Fedex, UPS - forecasting capacity here) - Routing (between warehouses, within warehouses, to specific homes) - skipping AWS - skipping billing - skipping advertising on amazon.com (bidding, attribution, etc)
There's optimizations and metrics collected and packages transition between all these layers. There's hundreds of "neat projects" running to special case different things; all them useful but adding complexity.
For example ordering prescriptions off Amazon pharmacy needs effectively its own website and permissions and integrations. Probably distinct sorting machines with supporting databases for them. Do you need to log repairs on those machines? Probably another table schema.
You want to normalize international addresses? And fine tune based on delivery status and logs and customer complaints and map data? Believe it not like 20 more tables. Oh this country has no clear addresses? Need to send experienced drivers to areas they already know. Need to track that in more tables.
> Amazon (the product) really only has sellers, buyers, and products, with maybe a couple more behind the scene for logistics.
Is a comically bad hot take that is so entirely divorced from reality. A full decade ago the item catalog (eg ASINs or items to purchase) alone had closer to 1,000 different subsystems/components/RPCs etc for a single query. I think you'd have to go back to circa 2000 before it could be optimistically described as a couple of databases for the item catalog.
DylanDmitri sibling comment is a hell of a lot closer to the truth, and I'd hazard is still orders of magnitude underestimating what it takes to go from viewing an item detail page to completing checkout, let alone picking or delivery. Theres a reason the service map diagram, again circa 2010, was called "the deathstar."
> "FAANG is doing it so you should too" and "but what about when you have a billion users?" is poisoning the minds of people
This part I completely agree with. And many individual components in those giant systems are dead simple. I dare say the best ones are simplistic even.
The world runs on success stories, not on technology. I wish “wide” thinking was default, for both un-delusion and better development in this area. But everyone is amazed with facebook (not the site, just money), so they have to imitate it, like those tribes who build jets out of wood.
I also agree with your take that tall applications are generally easier to build engineering-wise.
Where I disagree is that I think in general wide applications are failures in product design, even if profitable for a period of time. I've worked on a ton of wide applications, and each of them eventually became loathed by users and really hard to design features for. I think my advice would be to strive to build a tall application for as long as you can muster, because it means you understand your customers' problems better than anyone else.
What is the market for "wide" applications though? It seems like any particular business can only really support one or two of them, for some that will be SAP and for others it might be Salesforce (if they don't need much ERP), or (as you mentioned) some giant semi homebrewed Oracle thing.
Usually there is a legacy system which is failing but still runs the business, and a "next gen" system which is not ready yet (and might never be, because it only supports a small number of use cases from the old software and even with an army of BAs it's difficult to spec out all the things the old software is actually doing with any accuracy).
Or am I not quite getting the idea?
Some of these store things for user-facing product entities and associations between them -- you missed the vast majority of product functionality in your "people, posts, and ads" claim. Others are for internal purposes. Some workloads use joins, others do not.
Nothing about Facebook's database design is "tall", nor is it "easy". There are a lot of huge misconceptions out there about what Facebook's database architecture actually looks like!
Advice like "never user joins" and "design around a single table" is usually just bad advice for most applications. It has nothing to do with Facebook, and ditto for Amazon based on the sibling replies from Amazon folks.
If they didn’t then I’d change my advice to be simply multi tenant per customer and replicate into a column store for cross customer analytics.
What advice would you give for a “wide” application?
You can mash those big joins into a materialized view or ETL them into a column store or whatever you need to fix performance later on, but once someone has copied the `subtotal_cents` column onto the Order, Invoice, Payment, NotificationEmail, and UserProfileRecentOrders models, and they're referenced and/or updated in 296 different places...it's a long road back to sanity.
I'd amend that to "don't let your scan coverage get too big". Understanding how much data must be loaded in memory and compared is essential to writing performant database applications. And yes, those characteristics change over time as the data grows, so there may be a one-size-fits-all solution. But "table too large" can pretty much always be solved by adding better indexes or by partitioning the table, and making sure common queries (query's?) hit only one partition.
As a simple example: a lot of queries can be optimized to include "WHERE fiscal_year = $current". But you need to design your database and application up front to make use of such filtered indexes.
Instead, it'll throw you hot key throttling if you start querying one partition too much
Grab (uber of asia) did this religiously and it created a ton of friction within the company due to the way the teams were laid out. It always required one team to add some sort of API that another team could take advantage of. Since the first team was so busy always implementing their own features, it created roadblocks with other teams and everyone started pointing fingers at each other to the point that nothing ever got done on time.
Law of unintended consequences
The application doesn't need to know how the data is physically stored in the database. They specify the logical view they need of the data. The DBAs create the materialized view/stored procedure that's needed to implement that logical view.
Since the application is never directly accessing the underlying physical data, it can be changed to make the retrieval more efficient without affecting any of the database's users. You're also getting the experts to create the required data access for you in the fastest, most efficient way possible.
We've been doing this for years now and it works great. It's alleviated so many headaches we used to have.
> The application doesn't need to know how the data is physically stored in the database.
In all the applications that I've designed, the application and the database design are in sync. That's not say that you wouldn't use materialized views to deal with certain broad queries but I just don't see how this level of abstraction would make a big difference.
2011, "Materialized Views" by Rada Chirkova and Jun Yang, https://dsf.berkeley.edu/cs286/papers/mv-fntdb2012.pdf
> We cover three fundamental problems: (1) maintaining materialized views efficiently when the base tables change, (2) using materialized views effectively to improve performance and availability, and (3) selecting which views to materialize. We also point out their connections to a few other areas in database research, illustrate the benefit of cross-pollination of ideas with these areas, and identify several directions for research on materialized views.
In a show hosting/ticket booking app for example, I never want in any case user facing search/by-id endpoints to serve a show from 2 months ago. So I create a view `select * from shows where time > now`. I can now use this as a 'table' and apply more filters and joins to this if I wish.
This seems close to the territory of "why do I need a database? I just keep a bunch of text files with really long names that described exactly what I did to compute the file. They're all in various directories, so if you need to find one just do some greps and finds on the whole system"
I recognize there's a big gap, but boy howdy does what you're suggesting sound messy.
I had a Chief Architect who decreed this.
So engineers wound up doing joins in application code, with far worse performance, filtering, memory caching, etc.
if you ask Amazon, they might suggest that you design around a single table (https://aws.amazon.com/blogs/compute/creating-a-single-table...).
in my opinion it's easier to use join tables. which are what are sometimes temporarily created when you do a join anyways. in this case, you permanently create table1, table2, and table1_join_table2, and keep all three in sync transactionally. when you need a join you just select on table1_join_table2. you might think this is a waste of space, but I'd argue storage is too cheap for you to be thinking about that.
that being said, you really have to design around your access patterns, don't design your application around your schema. most people do the latter because it seems more natural. what this might mean in practice is that you do mockups of all of the expected pages and what data is necessary on each one. then you design a schema that results in you never having to do joins on the majority, if not all, of them.
As an added data point I don't really like programming books but bought this since the data out there on Single Table Design was sparse or not well organized, it was worth every penny for me.
I won't say that it's trivial to update all of your business logic to do this, but I think it's definitely worth it for a new project at least.
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.
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.
Hard disagree. You just re-implemented a database engine in your application code. Poorly.
Also, doing these things ; dejoining, UUIDs and indexing all columns (really unsure about this one; why?), might be better later on, but at the start it will be a lot heavier.
Modern hardware and databases can take an incredible amount of traffic if you use them in the right and natural way without artificial tricks.
I spent two or so months optimizing the crap out of a majestic monolith and went from under 2K RPS when the PM thought, and the team repeatedly reported, that everything had been squeezed as much as it could, then changing the hardware, which got us to less than 3200 RPS, then to 4K RPS after just a few days of tinkering, to 10K RPS with a bit more effort, to 40K RPS a week or so later. "Oh that's, enough, we don't need to go further." I then changed "quite a bit of stuff" which then jumped us to 2M+ RPS, and then a month later, a consistent 40M+ RPS with low latency on a single box and there is still some juice left in the box should we want to go a little harder.
Right now we're not even touching 5% of the capacity of what we can pull from, it was that much of a change, simply by changing how we think about the problems. Moving from the old server to the new server let us jump from around 1800 RPS to a hair over 3000 RPS. Adding more hardware didn't fix our underlying problems. Adding more complexity was just punting the problem down the road. But changing how to think about the problem? _That_ changed the problem. And changed our answer to the problem.
Just to note: you don't have to split out all the possible microservices at this junction. You can ask, "what split would have the most impact?"
In my case, we split out some timeseries data from Mongo into Cassandra. Cass's table structure was a much better fit — that dataset had a well defined schema, so Cass could pack the data much more efficiently; for that subset, we didn't need the flexibility of JSON docs. And it was the bulk of our data, and so Mongo was quite happy after that. Only a single split was required. (And technically, we were a monolith before and after: the same service just ended up writing to two databases.)
Ironically, later, an airchair architect wanted to merge all the data into a JSON document store, which resulted in numerous "we've been down that road, and we know where it goes" type discussions.
Log queries, filter our the ones that are very frequent or take loads of time to execute, cache the frequent ones, optimize the fat ones, do this systematically and your system will be healthier.
Things that help massively from my experience: - APM - slow query log - DB read/write replicas - partitioning and sharding
You go to war with the army you have, not the army you might want or wish to have at a later time.
You may want to ignore that this this comes from Donald Rumsfeld (he has some great ones though: “unknown unknowns …”, etc.)
I think about this a lot when working on teams. Everyone is not perfectly agreeable or has the same understanding or collective goals. Some may be suboptimal or prone to doing things you don’t prefer. But having a team is better than no team, so find the best way to accomplish goals with the one you have.
It applies to systems well too.
Profile real world queries being run in production that use the most resources. Take a look at them. Get a sense of the shape of the tables that they're running against. Sometimes the ORM will be using a join where you actually want a subquery. Sometimes the opposite. Sometimes you'll want to aggregate some results beforehand, or adjust the WHERE conditions in a complex join. I've seen situations where a semi-frequent ORM-generated query was murdering the DB, taking 20+ seconds to run, and with a few minor tweaks it would run in less than a second.
I like ORMs but this is just frustratingly complicated on so many levels. I also understand that SQLAlchemy is an enormous library and not everything will be easy. But I think this case exemplifies the trade-offs involved with using an ORM.
(Yes I am aware that using insert() itself in Core does what I want, I'm talking about .add()-ing an ORM object to an AsyncSession).
A friend used to say Zookeeper was where the crazy lived in any application that used it - sqlalchemy is where the slow lives in any application that uses it.
...or just mental load. I'm tired of working on micro-service systems that still have downtime, but no one knows how it all works. Most are actually just distributed monoliths so changes often touch multiple services and have to be rolled out in order. Data has to be duplicated, tasks have to be synchronized, state has to be shared, etc...
It reminds me of Richard L. Sites's book _Understanding_Software_Dynamics_ where he basically teaches how to measure and fix latency issues, and how at large scales, reducing latency can have tremendous savings.
Measuring and reasoning about those issues are hard, but the solutions are often simple. For example, on page 9 he mentions that "[a] simple change paid for 10 years of my salary."
I hope to someday make such an impactful optimization!
I did that at Google more than once. They use a tremendous amount of machine resources and have excellent performance tools [1], so it's fertile ground.
There are a lot of other smart people around though so if you find a big opportunity there's probably a reason no one else has jumped on it. Maybe technical, maybe organizational. As an example of the latter, Google doesn't usually reward this kind of thing except when there's a resource crunch. Like, maybe I got a peer bonus (~$100) for one of them. I certainly didn't a 10% commission or a promotion or the ability to keep getting a paycheck without showing up for the next 10 years or whatever. As a general rule, they'd prefer engineers work on growing revenue than on reducing cost. Whether this is the right policy or not is kind of above my pay grade...
They couldn't upgrade their config with a few clicks in the admin console anymore (I'm guessing what's involved here) so now they had to use actual grey matter to fix their capacity problem. Maybe if they had spent more time optimizing specific parts of their code, they wouldn't even need such a large config instance.
Administering and tuning RDBMS is dark magic. Doing basic query optimization should be viewed the same as "maybe don't write an O(n^3) algorithm."
I admit in the face of finding Prod/market fit, you do the expedient thing, but damned if I'm not often at the receiving end of these sorts of decisions.
Caching is mostly a lie. Redis will not “make” your application faster. It will just let you pretend the problem doesn’t exist for a while, and then when the caching eventually falls over, you will be even more stuck, because you’re now past your scaling limits, with no more defenses left and software that has been (continually) built without the understanding of its performance requirements.
1. Efficiency, which I define as minimising losses i.e not writing things inefficiently, avoiding artificial complexity, bloat and keeping code simple. Performance hits here can also be a 1000 cuts problem when depending on many 3rd party pieces while having people chanting "premature optimisation" at you.
2. Optimization, which I define as employing specialist algorithms (which sometimes come in the form of entire tech stacks these days) with the cost of added complexity (and potentially performance trade) to get performance beyond the basic or naive yet efficiently implemented methods. The cost benefit ratio to these is not always worth it, especially in the beginning.
Hopefully the point I'm trying to make should be obvious, that attempting #2 before #1 is a bad idea, and in less explicit words I suspect this is kind of what the author is getting at... Yet it's not all that uncommon to see someone trying to fit a turbocharger to a cheese skateboard with 64 triangular wheels.
But the post makes it seem that there was no real query-level monitoring for the Postgres instance in place, other than perhaps the basic CPU/memory ones provided by the cloud provider. Using an ORM without this kind of monitoring is sure way to shoot yourself in the foot with n+1 queries, queries not using indexes/missing indexes etc
The other thing that is amazing that everyone immediately reached for redesigning the system without analyzing the cause of the issues. A single postgres instance can do a lot!
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.
Caching adds a lot of complexity. It denormalizes the data, and now you "need to know" when to update the cache. Because "the single source of truth" is no longer maintained, it's easy to accidentally add regressions.
If it's a matter of adding a read replica, that's a much better solution, long-term, because you don't have the effort of "does this query also need to update the cache?"
(I'd think by now there would be a way to expose events in a DB when certain tables are updated; and then (semi) automatically invalidate the cache.)
Reminds me of the mantra that I’ve read here to easily go for reversible things and very careful when going for irreversible things.
> “We use the terms one-way door and two-way door to describe the risk of a decision at Amazon. Two-way door decisions have a low cost of failure and are easy to undo, while one-way door decisions have a high cost of failure and are hard to undo. We make two-way door decisions quickly, knowing that speed of execution is key, but we make one-way door decisions slowly and far more deliberately.”
Look, I get it, the devx sucks. And it feels proprietary, icky, COBOL-like experience. It means you have to dwell in the database. What are you, a db admin?!
But I'm telling you, the payoff is worth it. (and also, if you ship it you own it so yes you're a db admin). My company ran for many years on 3 machines, despite it's extremely heavy page weight because the original author wrote it stored procs from the beginning. (He also liberally threw away data, which was great, but that's another post.) Part of my job was to migrate away from .NET and to Java and JavaScript - and another engineer wrote an ingenious tool that would generate Java bindings to SQL Server stored procs that made it really nice to work with them. And the performance really was outrageous - 100x better than any system I've worked with before or since. Those 3 boxes handled 300k very data intensive monthly actives, and that was like 10 years ago.
Don't worry - even if you lean into SPs there is still plenty of engineering to do! It's just that your data layer will simplify, and your troubleshooting actually gets easier, not harder. I liked the custom bindings - a bit like ActiveRecord, and no ORM. But really, truly: if you want to squeeze, move some queries into SPs and prepare to be amazed.
- Detect overload/congestion build-up at the database
- Apply queueing at the gateway service and schedule requests based on their priority
- Shed excess requests after a timeout
[0]: https://docs.fluxninja.com/blog/protecting-postgresql-with-a...
I don’t know if the author has worked with micro services. MS solve a communication issue. If implemented semi-properly teams stop blocking each other and the overall result is _faster_ and _safer_ feature delivery to production because the scope a team (or tribe, etc) will be working on a smaller, isolated codebase. The challenge _usually_ is that now developers have to take the environment into consideration introducing new patterns (retries, structured logs, time outs, circuit breakers, possibly SLIs for other teams, distributed tracing, metrics, etc). Given a large enough org, someone will either adopt or write a micro-framework to handle all or most of them.
To re-iterate if introducing MS stalled feature delivery, then it is a premature decision. YMMV, of course as there are other reasons to isolate part of the code base (e.g. compliance).
Sharding is a really simple and comprehensible way to distribute some load and I favor it for situations that are generally like this.
However, if you want to take a baby step, you can shard a database within the same machine by sharding the storage subsystem.
That is, instead of splitting up your database between X machines, you split the database between X SSD arrays within the existing machine.
Now each table (or whatever) that you've made a shard has a unique storage throughput and bus path and you aren't competing for iops on one array/disk/whatever.
Some workloads can gain a lot from that and it might involve simply plugging in a handful of additional SSDs.
If you have a dataset for which cache invalidation is easy (e.g., data that is written and never updated), yeah, absolutely go for this.
In our case, and most cases I've seen, it wasn't so simple, and "split this off to a DB better suited to it" was less complex (maybe still a lot of work, but conceptually simple) than figuring out cache invalidation.
Cost: a fraction of the developper cost.
I see so many things done on the cloud that 10X their complexity because of it. Modern hardware in increadibly powerfull.
They likely already had 24.xlarge or something lol
Anybody has documentation about this with examples?
check out MariaDB "ColumnStore". it recently got merged into the upstream binary, and i started reading about it. ngl i was salivating a bit.
Is not.
"Scale-up" MUST be the "obvious" solution. What is missed by many, and this article touch (despite saying that micro-services is a "solid" choice) is that "Scale-up" is "scale-out" without breaking the consistency of the DB.
Is a lot you can do to squeeze, and is rare you need to ignore join, data validations and other anti-patterns that are normally trow casual when problems of performance happens.
There are sometimes diminishing returns to simple scaling; e.g., in my current job, each new disk we add adds 1/n disks' worth of capacity. Each scaling step happens quicker and quicker (assuming growth of the underlying system). Eventually, you hit the wall in the OP, in that you need design level changes, not just quick fixes.
The situation I mention in my comment was one of those: we'd about reached the limits of what was possible with the setup we had. We were hitting things such as bringing in new nodes was difficult: the time for the replica to replicate was getting too long, and Mongo, at the time, had some bug that caused like a ~30% chance that the replica would SIGSEGV and need to restart the replication from scratch. Operationally, it was a headache, and the split moved a lot of data out that made these cuts not so bad. (Cassandra did bring its own challenges, but the sum of the new state was that it was better than where we were.)
Consistency is something you must pay attention to. In our case, the old foreign key between the two systems was the user ID, and we had specific checks to ensure consistency of it.
Depending on your read load and application structure you can get a lot more scale with caching.
Decent article.
The specific data that went into Cassandra in our case was basically immutable. (And somehow, IIRC, we still had issues around tombstones. I am not a fan of them.) Cassandra's tooling left much to be desired around inspecting the exact state of tombstones within the cluster.
Tools like https://explainmysql.com that make it clearer what you actually need to optimise are an easier system for Devs with enough database knowledge to set stuff up, but not enough to understand how it's used.
I assume someone's already working on an AI system that takes schema and logs and returns the SQL needed to magically improve things. Not sure I'd trust that, but I'd bet many companies would rather use that then get a full DBA.
This should unclog the most low hanging fruit. Then there is of course more advanced scenarios, especially with joins.
That’s not to say that the UX for explaining (hah) this doesn’t have a lot of room for improvement.
Use the Index Luke (also recommended by cocoflunchy) was one of my go to resources once.
Also, Tobias Petry does a really good job by covering many advanced topics on a Twitter and his books: https://twitter.com/tobias_petry
You don't fix bad coding with a new architecture. That just puts the problem off by some time.
> Q: Could I follow up, Mr. Secretary, on what you just said, please? In regard to Iraq weapons of mass destruction and terrorists, is there any evidence to indicate that Iraq has attempted to or is willing to supply terrorists with weapons of mass destruction? Because there are reports that there is no evidence of a direct link between Baghdad and some of these terrorist organizations.
> Rumsfeld: Reports that say that something hasn't happened are always interesting to me, because as we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns -- the ones we don't know we don't know. And if one looks throughout the history of our country and other free countries, it is the latter category that tend to be the difficult ones.
> And so people who have the omniscience that they can say with high certainty that something has not happened or is not being tried, have capabilities that are ...
https://archive.ph/20180320091111/http://archive.defense.gov...
But whatever position he had, Iraq turning into a clusterfuck wasn't a sign of bad leadership by his part. It was a sign of bad ethics, but not leadership. His options were all of getting out of his position, disobeying the people above him, or leading the US into a clusterfuck.
If by “explaining how” you mean “deflecting (often preemptively) responsibility for”, yes.
If someone is 83.7% likely to provide good leadership, how would you evaluate the choice to hire that person as a leader in the hindsight that the person failed to provide good leadership -- was it a bad choice, or was it a good choice that was unlucky?
(Likelihood was selected arbitrarily.)
Once they’re in, the predilections that led to power often rear their dark long tails. But they’re all (even the ones I disagree with) talented.
He’s also one of the best candidates for that type of conspiracy theory. His career history is flabbergasting.
Check out https://en.wikipedia.org/wiki/Donald_Rumsfeld#Corporate_conn...
In addition to all the Bohemian Club, RAND corp, defense and government posts, in the 70s the guy was a CEO in the pharmaceuticals and electronics industries, was a director in aerospace, media and tech.
Definitely the type of resume that lets the imagination run wild with, “… wait, was he a lizard person …?”
Moltke's thesis was that military strategy had to be understood as a system of options, since it was possible to plan only the beginning of a military operation. As a result, he considered the main task of military leaders to consist in the extensive preparation of all possible outcomes.[3] His thesis can be summed up by two statements, one famous and one less so, translated into English as "No plan of operations extends with certainty beyond the first encounter with the enemy's main strength" (or "no plan survives contact with the enemy") and "Strategy is a system of expedients".[18][8] Right before the Austro-Prussian War, Moltke was promoted to General of the Infantry.[8]
“The athlete knows the day will never come when he wakes up pain-free. He has to play hurt.”
This applies to ourselves more than our systems though.
I try to remind myself of this fact when I'm frustrated with other people. A bit of humility and gratitude go a long way.
It's eye opening how many people are outright lazy with thought, don't care about the joy of doing something well (apart from whatever extrinsic rewards are attached to the work). Many team members can actually produce negative value.
It seems that people who are really capable of (or care about) conscientious, original thought in problem solving and driving projects forward are few. Count yourself lucky if you get to manage one of these people, they can produce incredible value when well directed.
Depends on what you are working on. Btw, good communication can also make someone a 'star' and elevate the whole team.
> I try to remind myself of this fact when I'm frustrated with other people. A bit of humility and gratitude go a long way.
That's good advice for most situations.
Meanwhile our side of the org has a much more collaborative relationship with our product team. We have our issues for sure, but our relationships are sound. The feedback loop is tight and product pushes back on things as much as the dev team does. Product works with the dev team to figure out what we can do and stays with us to the end. There's much less tossing things over the fence and everybody seems happier.
The unknown unknowns quote brings the concept that however confident you are in a plan you absolutely need margin. The other quote thought...what do you do differently when understanding that your team is not perfect ?
On one side, outside of VC backed startups I don't see companies trying to reinvent linux whith a team of 4 new graduates. On the other side companies with really big goals will hire a bunch until they feel comfortable with their talent before "going to war". You'll see recruiting posts seeking specialists in a field before a company bets the farm on that specific field (imagine Facebook renaming itself to Meta before owning Oculus...nobody does that[0])
Edit: sorry, I forgot some guy actually just did that 2 weeks ago with a major social platform. And I kinda wanted to forget about it I think.
Rumsfeld was complicated, but there's no doubt he was very effective at leading the Department. I think most people fail to realize how sophisticated the Office of the Secretary of Defense is. Their resources reel the mind, most of all the human capital, many with PhDs, many very savvy political operators with stunning operational experiences. As a small example, as I recall, Google's hallowed SRE system was developed by an engineer who had come up through the ranks of Navy nuclear power. That's but one small component reporting into OSD.
Not a Rumsfeld apologist, by any means. Errol Morris did a good job showing the man for who he is, and it's not pretty (1). But reading HN comments opining about the leadership qualities of a Navy fighter pilot who was both the youngest and oldest SECDEF makes me realize how the Internet lets people indulge in a Dunning-Kruger situation the likes of which humanity has never seen.
Most business logic would be better expressed in the language of relational algebra (plus some extensions) than via OOP.
Side-note: the GP comment was actually doing pretty well (like +7) now it's (+2) so a cadre of anti-stored proc folks did a drive by down-voting. A bit sad, IMO.
I have yet to see a good application of microservices. I’m not saying there are none but the companies that can truéy benefit from that are few and fare apart.
From my experience smaller companies usually benefit a lot from simple monolythical architecture. Large companies tend to split problem into multiple products. But each product is still a kinda monolyth.
I have no experience with huge SaaS companies like Netflix. I can easily see why there the situation is quite different.
My horror story from recent days is that I had a 100-ish LOC patch. I had to push an update to 8 repos. That means 8 merge requests, 8 code reviews, deploy changes in correct order such that it does not break anything. The whole thing took 3 days. Coding was done in two hours.
- You have so many deploy stacks that it's literally boggling. I had to deploy code that used everything from make to GitHub CI to Jenkins to serverless just to push a single change.
- You have infinite implementations of your business logic. This is for two major reasons. First it's just hard to keep everything sync'd up; even if you've got "libbiz" you're gonna have services on various versions. But second, a core tenet of microservices is to just fork a new service for this and let other services migrate, but now you've evolved from an ecosystem of library differences to an ecosystem of service differences, which is way more complicated and expensive to maintain. It would be infinitely better if all parts of your app used the same versions of your business logic, but it will never ever happen.
- Your stacks are probably really heterogeneous ("right tool for the job"), but what that means is your devs now probably all have to know some mix of Java, Ruby, JavaScript, Python, Go, and maybe something more esoteric like Clojure or Elixir.
- Maintaining a microservice infra is way more complicated. Good luck monitoring multiple app and deploy stacks. Good luck keeping all of them up to date with security fixes. Good luck with Kubernetes and helm (or whatever). Good luck with multiple persistence systems (Postgres, Maria, Mongo, Redis, Cockroach, BigQuery, etc)
- You have to have an event bus. Boo.
- Your logging is hyper complicated now
- Because of the ecosystem around microservices, you're probably doing a lot of weird enterprisy things in your code (DDD, CQRS) that mostly only add layers of indirection or pull in more complicated dependencies, or inspire you to (against all good advice) build your own framework.
I'm not saying a monolith doesn't have problems, but I think the cons of microservices get very little play.
Part of the cost consideration is deterministic results. I will pay a premium for near-guaranteed good but probably sub-optimal results and will actively avoid betting on people I haven't met and don't know exist.
In my hiring, I hire now to solve problems we expect to hit after 4 quarters. It almost never makes sense to hire anyone into a full-time role for any project in a shorter timeframe. If you were wrong about the specific problems you expect to have in a year, you have a person who is trained in your development environment, tooling, and projects, and you already budgeted to use them in-depth in a year. There's no emergency. There is time to pivot. But if you're wrong about the need to hire someone now full time, you front load all of the risk and if it doesn't work out, you are stuck with an employee you do not need (and stuck is the right word. Have you ever terminated someone? It is harder than you think it is, and I don't mean just for emotional reasons).
Buy hardware over people. Treat the people you have as if the business depends on them. Let them know that it does. Everyone is happier this way.
> Isn’t that the cost of one engineer already?
Only for very cheap engineers and very large values of “a few”. $120k/year is pretty low total compensation for an engineer (and the cost of an engineer exceeds their total comp because there is also gear, and the share of management, HR, and other support they consume) and amounts to $10k/month.
More importantly, it's critical to think in terms of opportunity cost. Like I said, we couldn't hire engineers fast enough at that time, so if I put someone on this work it would be taking them off some other important project. Plausibly for a fast-growing startup that means eschewing work that's worth $1-2m/eng-yr or more (just looking at concrete increases in company valuation, not present value of future gains). So we're talking on the order of $100k/eng-mo opportunity cost.
Amazon is extremely and perversely obsessed with, and good at, building decoupled systems at scale, which in essence means lots and lots of individual separate "tall" systems, instead of monolithic "wide" systems.
So IMO, Amazon subscribes to a "forest-of-'tall'-services" philosophy. And even at that meta level, I would say the forests are better off when they grow taller, rather than wider.
Maybe there are situations where this actually helps, although the resulting datastructure to me looks more like a multi-key cache.
Yes, I agree that this is the fate of most. But I refuse to believe it's inevitable; rather, I think it comes from systemic flaws in our design thinking. Most of what we learn in a college database course, most of what we read online, most all ideas in this space, transfer poorly to "wide" design. People don't realize this because those approaches do work well for tall applications, and because they're regarded religiously. This is why I call them so much harder.
Yes exactly. It is not inevitable, I’ve worked on several “enterprise” software suits that did not suffer from this problem. However! They all had that period in their history where they did, and this is why:
Early on in a companies history there will be a number of “big” customers from whom most of the revenue is coming. To keep those customers and money flowing, often bespoke features are added for these customers and these accumulate over time. This is equivalent in character to maintaining several forks of an OSS project. Long term no forward progress can be made due to all time ending up in maintenance.
The solution to this sorry state is to transition to an “all features must be general for the product” and ruthlessly enforce this. That will also mean freezing customer specific “branches” and there will be a temporary hit to revenue. Customers need to be conditioned to the “no bespoke features” and they need to be sold on the long term benefits and be brought along for the ride.
This then enables massive scaling benefits, and the end of all your time in maintenance.
And if you are needing to massively shard because you're Facebook or Twitter, then it's not much of a hot take at all. But it's also massively oversimplified advice. Because the tradeoffs between joins and denormalization depend entirely on each specific scenario, and have to be analyzed use case by use case. In many cases, joins still win out -- e.g. retrieving the profile name and photo ID for the author of each post being displayed on your screen.
I'm just worried that people without experience will see your advice and think it's a good rule of thumb for their single-server database, because they think joins are scary and you've provided some kind of confirmation.
Yes, sometimes the pressure comes from management etc., but more often than not it would be premature optimisation to add the archiving, so it's a matter of finding a balance and "predicting" at what point the archiving needs to happen.
Table partitioning can help too but only so much.
In all seriousness, I won't work for an organization that works the way you describe. It's a red flag and a sign of organizational issues, personality issues, and ineffective management. Don't need to waste my time at a place like that.
Caching, in any form, is the last thing you want to reach for because it’s always more nuanced than you anticipate.
To clarify, when asking the question it’s after drilling through the layers from the frontend -> backend -> query and the actual query is on the screen along with some table metrics as a guide.
Not every material view is updatable, and a view doesn't need to be materialized to be updatable.
They're not always talented at the things we may want them to be, unfortunately. And that's true of both the ones I agree and disagree with.
You'd be surprised.
"Reaching positions of great power after mediocre lives" is the very art of career politics.
This allows for the forward-compatible evolution of the logical data model which may necessitate extreme changes to the physical data model to keep everything performant. The client application(s) aren't affected by all the changes.
These models shouldn't be significantly different from each other. How you store the data physically should be is very much how it's represented logically. And then how the UI represents the data model to the user.
If you have 2 tables -- a summary and a detail for example -- that's going to be similarly represented to the user in the UI. If you break that summary down into another table (because you need to allow, say, more addresses) you're going to bubble that right up the UI for saving and loading. For querying, you might have a materialized view that summarizes the detail data when viewing a bunch of summaries together but that's in addition to rest of the model.
I'm going to need some kind of example to understand the advantage of this.
As for the market, various sources have the "enterprise software market", whatever that means, at somewhere around $100 billion to $300 billion. We also see companies trying over and over to do this kind of thing. The demand is clearly there.
Certainly the mandate "help run the business" is a wide concern, and that's an OK working definition of "enterprise", and what most existing solutions are trying to do. There are hundreds of interconnected concerns, lots of things to coordinate, etc.
There are other wide concerns, though. Almost anything in engineering and science. Take, for example, the question "how can we reduce our greenhouse gas emissions?" which a lot of companies are asking (or being forced to ask). If you wanted to build a SAAS product for helping companies reduce their GHG, you've got a wide problem, because there are a thousand activities that can emit GHG, and any given company is going to be doing dozens of them at once. But each company is different. Each state and country thinks of things differently. You might not even have the same calculations state-to-state.
Hard problems in science and engineering are just naturally cross-disciplinary, meaning your system has to know a lot of things about a lot of subjects. There are just thousands of little complicating differences and factors. If you're trying to solve a problem like this, absolutely do not de-normalize your database.
Nowadays there are bunch of regulations on handling user data that one cannot do without knowing but when these companies started that was not an issue.
My point is market for "wide" applications is huge but it is much more fragmented. Of Course SAP and Salesforce are taking cut in that by having "one app for everything"
To get contracts you have to have specialized knowledge in specific area that your SaaS app would provide more value than configuring some crappy version in SAP. So you cannot just make an app in your basement and watch people sign up, but you have to spend a lot of leg work getting customers. That is why it is not really "hot" area for startups, because there is a lot of good money there but not unicorn money and most likely you won't be able to have 2 or 3 different specialist niche products so you could diversify investment but you would have to commit to a niche which makes it also not really interesting for a lot of entrepreneurs who most likely would lie to jump to something more profitable when possible.
Just my experience, but essentially these target industries, not necessarily consumers or singular entities. Hence the term "enterprise". As someone who worked on a fairly reasonable ERP for academic purposes, even just calculating a GPA is extremely complicated in the backend:
* There are multiple schemes for calculating GPAs
* Each scheme needs to support multiple grading types (A-F, pass/fail, etc)
* Each scheme needs to support multiple rounding rules
* Displays of GPAs will need to be scaled properly based on the output context
* GPA values will need to be normalized for use in calculations in other parts of the system
* State legislatures mandate state-specific usages of GPAs which must be honored for legal compliance
* All GPA calculations must have historical context in case the rules changes so that old transcripts can be revived correctly
* Institutions themselves will have custom rules (maybe across schools or departments) for calculations which must be incorporated into everything else
* This pretty much has to work every time
I would compare this to most modern "tall" applications which would more-than-likely offer you maybe up to 3 different GPA options with some basic data syncing or something. They might offer multiple rounding types if they thought that far. These apps are generally extremely niche and typically work for very basic workloads. They can capture a lot of easy value for entry-level stuff but immediately fail at everything else.
https://www.alexdebrie.com/posts/dynamodb-single-table/
Im really curious about real life performance on different databases, especially in situation where RAM is smaller than database size.
The sophistry of his argument is extreme.
Yes, of course there are always "unknown unknowns"-- but the statement "there is no evidence that Iraq is supplying WMDs to terrorists" is not a statement made in a vacuum, in which all permutations of known/unknown are equally likely.
There's no reason to think you can't do both of those with the same statement.
I’m characterizing what I recall Rumsfeld concretely doing, not what is abstractly possible for one to do.
Then you add in a party system and it gets more complicated. Realistically, you don’t get to be the United States Secretary of Defense (twice) if you’re the kind of person who will ignore the will of the party and whoever is President.
If yes, you just evaluate the choice based on that probability (and other things you knew at the time), not on the actual outcome.
Prediction markets are one way to make these kinds numbers known.
I was about to make the same point. I hear you though. In my case CI/CD, application framework, programming language, etc. was common. We manage to shoot ourselves in the foot multiple times because (a) our CI/CD was _way_ too smart, allowing for _way_ to many things to happen for us (SRE team) in non-standard ways and (b) Helm (Duh!) ... allows you to put deployment logic in there which leads to a mess.
I think the op's solution of looking more closely at the hot paths and solving for those is a far better solution than re-architecting the application in ways that could, or can, create unintended consequences. People don't consider that enough, at all.
Don't forget that hot path resolution is the antithesis of 'premature optimization'.
> you could mitigate some of that with good management and access patterns
the CTO fired me for making those sorts of suggestions about better management, and then got fired himself a couple months later... ¯\_(ツ)_/¯... even with the macro events, their stock is down 72% since it opened, which doesn't surprise me in the least bit having been on the inside...
Is that, uhh, rose-tinted glasses?
2020: https://steve-yegge.medium.com/saying-goodbye-to-the-best-gi...
Previously, 2018: https://steve-yegge.medium.com/why-i-left-google-to-join-gra...
[I don't have any knowledge of Grab or the people involved other than reading these two blog posts, or of the parent commenter].
I came along as an aquihire of a Vietnamese team that I just happened to be managing at the time. Great team of people. I negotiated a stupid high salary, probably because they weren't used to someone negotiating at all (highest eng in all of Singapore) and that was part of why they were upset at me, I was making more than that VP of Eng and stirring the pot with comments about their poor architecture decisions.
Yegge was a good hire, but probably wrong company for him given the political differences. I think Yegge started a bit after I was fired. I remember thinking to myself that he's either not going to be very effective or he won't last long. To his credit, I think he lasted longer than I would have bet he would have. They had had another ex FB CTO much earlier before me, that was a train wreck [1] and ended up suing the company. They were without a CTO for a long time, probably thought Yegge could fill that roll and ended up hiring Mark instead.
There was a definite distinction between the Singapore and US/Seattle teams, at least the short time while I was there, they pretty much didn't talk to or like each other at all. It made getting those API calls almost impossible.
Decent SQL databases offer materialized views, which probably give you what you want without all the headache of maintaining denormalized tables yourself.
Nobody says you have to have only one view of your data though. You can have a normalised view of your data to write, and another denormalised for fast reads (you usually have to, at scale). Something like event sourcing is another way (which is actually pushing invariants to application level, in a structured way)
Foreign Keys.
Of course, now you have a new set of problems, but referential integrity isn't one of them.
Turning a single table update into a 10 table one could tip your lock contention to the point where you are write bound or worse start hitting retries.
Certainly it makes sense to move rarely updated fields to where they are used makes sense.
Similarly "build your table against your queries not your ideal data model" is always sage advice.
Apropos of Eisenhower, there is an incredible (fiction) book by Larry Collins, called Fall from Grace. It is about a brilliant long term plan and actions by British and French secret services to deceive the Germans about where the final Allied invasion would happen on the shores of France near the end of WWII. According to the novel, the ruse helped win the war.
Interwoven with a doomed romance.
https://www.google.com/search?q=fall+from+grace+larry+collin...
I read the full book some years ago, and it was gripping, though slow in parts.
I heard it in this form.
...
Recursion> In the first emergency meeting of the National Security Council on the day of the attacks, Rumsfeld asked, "Why shouldn't we go against Iraq, not just al-Qaeda?" with his deputy Paul Wolfowitz adding that Iraq was a "brittle, oppressive regime that might break easily—it was doable," and, according to John Kampfner, "from that moment on, he and Wolfowitz used every available opportunity to press the case."
https://en.wikipedia.org/wiki/Donald_Rumsfeld#Military_decis...
Anyway, another historical point besides what the other commenters have said is that Rumsfeld believed in “transformation” which meant you could do more with less in modern war. He was totally wrong about it.
It wasn’t his fault Turkey didn’t let the US attack from the north, but other than that, the fuck up is his responsibility, among others.
The occupation afterward was where the clusterfuck came in, and (somehow) none of the preparation had been directed toward that.
But thanks to enough loop holes, they can get into 'special military operations' (to misappropriate a recent term).
Calling them "special military operations," poor taste aside, especially with a focus on the formal process used, ignores the fact that all of those conflicts were entered and conducted with the full knowledge and involvement of Congresss.
[0]: https://stackoverflow.com/questions/27748053/how-to-log-sql-...
(I don't have any experience with AsyncSession, so cannot contribute something more specific)
Let's juxtapose them and see:
Von Moltke:
"No battle plan survives contact with the enemy."
Tyson:
"Everybody has a plan until you get hit in the face."
Pretty much the same meaning, and Von Moltke's quote is three words shorter, so no, Tyson's quote is not simpler.
Also, Tyson was ungrammatical, IMO:
"Everybody" vs. "you" in the same sentence, referring to the same entity.
Grammar experts, correct me if I am wrong.
I was just being a little pedantic for fun. I don't do that often.
So peace and order was not even disturbed, at least for me.
Enjoy.
I googled it and at least the top few links don't seem relevant.
> Also, Tyson was ungrammatical, IMO:
> "Everybody" vs. "you" in the same sentence, referring to the same entity.
Seems perfectly understandable to English speakers. (And that's pretty close to how English grammar is defined by descriptive linguists.)
Have a look at http://fine.me.uk/Emonds/ for an exploration of these kinds of concepts.
"Everybody" seems to be in the third person and "you" is in the second person, so I thought it was a mismatch (since in the same sentence, etc.), and so was ungrammatical.
Let anyone tell me if I am wrong, would like to know.
And see my reply to sbuk, it was just in fun.
Wait, really? That makes _so much sense._ It also makes me upset that all of my attempts to sway other SRE orgs over to Nuclear Navy practices have been met with doubt.
- ex nuke submariner
While we are at it, that infamous Dunning-Kruger study showed didn't even claim what people like to pretend it claimed. In addition the more nuanced claim they did make is not supported by the evidence they collected and presented in their paper. (Their statistics are pretty much useless, and as with any social science study, it has a small 'n' and it doesn't replicate.)
But the mythology 'Dunning-Kruger effect' is too good to pass up in Internet discussions, so it survives as a meme.
So, the current fashion of denouncing Dunning and Kruger doesn't jive with me. It was too obvious to discount and I had no idea of the concept when I saw it my own data. I think the misunderstanding has to do with the idea that it's about dumb people being dumb. It's about all of us. We all get it wrong. Even the smart ones. Paradoxically, the smart ones just get it wrong in the less desirable direction.
I think that academic fashionistas may be too clever by half here. Unless you have original data to back up a claim, the internet points aren't worth it. Focus on getting things right.
"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 of have come down to "It didn't occur to us that could happen."
To me the corporate version of the unknown known is when a a project is certainly doomed, for reasons everyone on the ground knows about, yet nobody wants to say anything and be the messenger that inevitably gets killed, as long as paycheck keeps clearing. An exec ten thousand feet from the ground sets a “vision” which can’t be blown off course by minor details such as reality, until the day it does.
Theranos is a famous example of this but I’ve had less extreme versions happen to me many times throughout my career.
Another example of unknown knowns might be the conflict between companies stated values (Focus on the User) and the unstated values that are often much more important (Make Lots of Money)
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.
Would that not be an unknown unknown?
An alternative I've heard before is using triggers with regular tables, so updating one automatically updates the relevant other ones.
Great suggestion! I had a role where I helped a small team develop a full stack, data-heavy application. I felt pretty good about the individual layers but I felt we could have done a better job at achieving cohesion in the big picture. Do you have any resources where people think about these sorts of things deeply?
> We have suggested using denormalization as an intermediate step between logical and physical modeling, to be used as an analytic procedure for the design of the applications requirements criteria ... The guidelines and methodology presented are sufficiently general, and they can be applicable to most databases ... denormalization can enhance query performance when it is deployed with a complete understanding of application requirements.
PDF: https://web.archive.org/web/20171201030308/https://pdfs.sema...
I struggle with the value of low/no code vs learning to code and providing common libraries.
The mechanical rules are just an imperfect attempt at capturing parts of the richness of real world language, or more precisely: language variants of different dialects and speakers.
Of course, there's a whole world of class markers overlaid here as well. If you want to sound middle-class educated in most of the English speaking world, you have to avoid "ain't" and say things like "It is I" or "Bob and I went shopping.", instead of the more natural "It's me!" or "Bob and me went shopping." That's what Emond calls 'Grammatically Deviant Prestige Constructions'. The whole point is that they aren't part of a naturally learnable variant of English, so they can only be acquired by schooling.
Most people who speak prestige-English over-generalise, and also say things like "She likes Bob and I.".
See fine.me.uk/Emonds/ for details.
Nope. Not important to me.
My question was in reply to the comment above by sbuk, excerpted below:
>It was midnight and a few beers after celebrating a birthday. I'm sorry I offended your grammatical sensibilities. But you really did go full orange site there, didn't you!
create table users (
id serial primary key not null,
created_at timestamp not null default now()
);
create table users_telephones (
user_id int references users(id) not null,
is_primary boolean not null default true,
telephone varchar not null
);
insert into users
select i, NOW() + (random() * (interval '90 days')) + '30 days' from generate_series(1, 10000000) i;
insert into users_telephones select id, true, random() :: text from users limit 10000000; -- all users have a primary telephone
insert into users_telephones select id, false, random() :: text from users limit 200000; -- some users have a non primary telephone
create index on users(created_at);
create index on users_telephones(user_id);
create index on users_telephones(user_id, is_primary) where is_primary;
select count(*) from users;
count
----------
10000000
(1 row)
Time: 160.911 ms
select count(*) from users_telephones;
count
----------
10200000
(1 row)
Time: 176.361 ms
select
*
from
users u
join users_telephones ut on u.id = ut.user_id
where
ut.is_primary
order by
created_at
limit
10;
id | created_at | user_id | is_primary | telephone
---------+----------------------------+---------+------------+--------------------
9017755 | 2023-09-11 11:45:37.65744 | 9017755 | t | 0.7182410419408853
6061687 | 2023-09-11 11:45:39.271054 | 6061687 | t | 0.3608686654204689
9823470 | 2023-09-11 11:45:39.284201 | 9823470 | t | 0.3026398665522869
2622527 | 2023-09-11 11:45:39.919549 | 2622527 | t | 0.1929579716250771
7585920 | 2023-09-11 11:45:40.256742 | 7585920 | t | 0.3830236472843005
5077138 | 2023-09-11 11:45:41.076164 | 5077138 | t | 0.9058939392225689
1496883 | 2023-09-11 11:45:42.459194 | 1496883 | t | 0.1519510558344308
9234364 | 2023-09-11 11:45:42.965896 | 9234364 | t | 0.8254433522266105
6988331 | 2023-09-11 11:45:43.130548 | 6988331 | t | 0.9577098184736457
7916398 | 2023-09-11 11:45:43.559425 | 7916398 | t | 0.9681218675498862
(10 rows)
Time: 0.973 msThis is only fast because 100% of users have a phone number as a primary contact, so the join filter is essentially meaningless. If in the contact table, the filtered number is a small percentage of the total (e.g. most users have an email as their primary contact, not a phone number), but still a good size (e.g. there’s still hundreds of thousands to millions of phone primary contacts), it’s a much harder query.
It’s probably also fast because you have a warm cache - e.g. there’s enough memory for the DB to have the indexes 100% in memory, which is just not feasible with large DBs in the real world, where you can easily have >100GB of indexes + hot data, and the DB can’t keep it all in memory. In most real world scenarios, having to somewhat frequently read pages of indexes off disk, into memory, to satisfy queries, is common.
Try it again, with the exact same data, but:
- Search for users with a non-primary phone contact (you have 200,000 of these, and 10,000,000 users)
- Give the DB say 1/3 the memory of your total index size, so the complete indexes can’t be in memory
- Run the query right after starting PG up, to ensure the cache is cold (with a hot cache, almost everything is fast, but in real world situations with lots of users the cache isn’t consistently hot)
That's the point? Sure there is a scale where it's infeasible, but you can quite easily (albeit it's pricey) get DB instances with hundreds or thousands of GiB of RAM. Even if you can't get everything into it, your working set is often not the size of the entire data set. My company's main MySQL primary has around 800 GiB of storage, and only about 1/3 of that in RAM. Disk read IOPS are usually 0.
Nevertheless, I recreated this in Postgres 15.
The total index size of the two tables is 790 MB according to `\di+*`, so I'll set `shared_buffers` to 263 MB, then restart and re-run.
For reference, time with current settings is about 6.8 msec for `is_primary`, and 1395 msec for `NOT is_primary`.
After a restart, I ran the query for `NOT is_primary` first, which took 1740 msec. The first run of the query with `is_primary` took 21 msec.
My DB is hosted on older hardware, and the files themselves live on NVMe drives presented via Ceph over 1GBe.
EDIT: I forgot that Postgres uses OS cache for a lot of its memory, not just its own. Re-did this, running `sync; sync; sudo sh -c 'echo 3 > /proc/sys/vm/drop_caches'` in between shutting down/starting up Postgres. 16726 msec and 79 msec, respectively. So yes, a lot slower, but a. I don't think this is realistic for a production database b. I'm still not clear about how you think JOINs enter into this. The slowdown comes entirely from having to run a table scan.
But, yeah, exactly. Everyone thinks they need to optimise the life out of this stuff at the beginning but the db can do a lot with normalised data and the appropriate indexes.
Side note - is_primary isn’t required in the partial index itself since they’ll all be “true” due to the where clause.
Probably nitpicking but these types of measures are usually tricky to interpret because there is a high chance your indexes (maybe even rows) are still on PostgreSQL shared buffers and OS cache and might not reflect real usage performance.
To get a more "worst-case" measure, after your inserts and indexes creation, you can restart your database server + flush OS pages cache (e.g. drop_caches for Linux), then do the measure.
Sometimes the difference is huge, although I don't suspect it will be in this case.
I did have to do a few manual updates after data load because the aforementioned program can't make foreign keys yet, and also for bools (which MySQL stores as tinyint(1)) I'm randomly generating them via `id & 1`, which isn't what you had.
Also, I gave `hn_phone` its own auto-increment int as a PK, so I could have a non-unique index on `user_id`. In MySQL, if you create a table without a PK, you get one of these, in descending order of precedence:
* The first indexed UNIQUE NOT NULL column promoted to PK
* An invisible, auto-generated, auto-incrementing integer column called `my_row_id` as PK (MySQL >= 8.0.30, if sql_generate_invisible_primary_key=1)
* A hidden index called `GEN_CLUST_INDEX` created on a super-invisible (i.e. doesn't show up in table definition) column called `ROW_ID`, but that column is shared across the entire DB so please don't do this
It's worth noting that since the first 10,000,000 rows all have `is_primary` set, this can finish extremely quickly. If you invert that match with these tables, you have to do a table scan on `hn_phone`, and the time jumps up to about 5650 msec. If you change the `hn_phone` index to be a composite on (`user_id`, `is_primary`) and then rewrite the query to use a subquery instead of a join, the time drops to around 7 msec. You might see a slight speed-up if you index `created_at` in descending order if that was the normal access pattern.
Anyway:
OS: Debian Bullseye 5.10.0-23-amd64
Virtualized: Yes (Proxmox)
CPU: E5-2650 v2 @ 2.60GHz
Allocated Core Count: 16
Allocated RAM: 64 GiB PC3-12800R
Disk: Samsung PM983 1.92 TiB via Ceph
Filesystem: XFS
Mount Options: defaults,noatime
MySQL Version: 8.0.34
MySQL Options (non-default):
innodb_buffer_pool_instances = 16
innodb_buffer_pool_chunk_size = 134217728
innodb_buffer_pool_size = 17179869184
innodb_numa_interleave = 1
innodb_sync_array_size = 16 # this shouldn't apply here, but listing anyway
innodb_flush_method = O_DIRECT
innodb_read_io_threads = 16
innodb_write_io_threads = 16 # this shouldn't apply here, but listing anyway
CREATE TABLE `hn_user` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `user_created_at` (`created_at`)
);
CREATE TABLE `hn_phone` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`user_id` int unsigned NOT NULL,
`is_primary` tinyint(1) NOT NULL DEFAULT '1',
`phone` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
CONSTRAINT `hn_phone_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `hn_user` (`id`)
);
mysql> SELECT COUNT(*) FROM hn_user UNION SELECT COUNT(*) FROM hn_phone;
+----------+
| COUNT(*) |
+----------+
| 10000000 |
| 10200000 |
+----------+
2 rows in set (1.20 sec)
mysql> SELECT
u.id,
u.created_at,
ut.is_primary,
ut.phone
FROM
hn_user u
JOIN hn_phone ut ON u.id = ut.user_id
WHERE
ut.is_primary
ORDER BY
u.created_at DESC
LIMIT 10;
+---------+---------------------+------------+--------------------+
| id | created_at | is_primary | phone |
+---------+---------------------+------------+--------------------+
| 6906106 | 2023-08-12 06:08:25 | 1 | +61 02 5317 2261 |
| 6906106 | 2023-08-12 06:08:25 | 1 | +254 20 294 205 |
| 6738922 | 2023-08-12 06:07:12 | 1 | +61 02 1247 3361 |
| 6738922 | 2023-08-12 06:07:12 | 1 | +44 0131 8386 4494 |
| 7449553 | 2023-08-12 06:03:55 | 1 | +61 02 7649 6731 |
| 7449553 | 2023-08-12 06:03:55 | 1 | +61 02 7893 9835 |
| 6908862 | 2023-08-12 05:51:52 | 1 | +81 03 6743-6893 |
| 6908862 | 2023-08-12 05:51:52 | 1 | +44 0131 8414 7888 |
| 4134961 | 2023-08-12 05:51:42 | 1 | +1 614-908-1719 |
| 4134961 | 2023-08-12 05:51:42 | 1 | +44 0131 9898 8958 |
+---------+---------------------+------------+--------------------+
10 rows in set (0.00 sec)
mysql> WITH latest_event AS (
SELECT
event_id
FROM
performance_schema.events_statements_history_long
WHERE
sql_text LIKE 'SELECT u.id%'
ORDER BY
event_id DESC
LIMIT 1
)
SELECT
event_name,
TRUNCATE(
TIMER_WAIT / POW(10, 9),
3
) AS 'duration (msec)'
FROM
performance_schema.events_stages_history_long stg
JOIN latest_event ON stg.nesting_event_id = latest_event.event_id
UNION
SELECT
"total",
TRUNCATE(
TIMER_WAIT / POW(10, 9),
3
)
FROM
performance_schema.events_statements_history_long stmt
JOIN latest_event ON stmt.event_id = latest_event.event_id;
+------------------------------------------------+-----------------+
| event_name | duration (msec) |
+------------------------------------------------+-----------------+
| stage/sql/starting | 0.261 |
| stage/sql/Executing hook on transaction begin. | 0.003 |
| stage/sql/starting | 0.016 |
| stage/sql/checking permissions | 0.006 |
| stage/sql/checking permissions | 0.005 |
| stage/sql/Opening tables | 0.134 |
| stage/sql/init | 0.008 |
| stage/sql/System lock | 0.023 |
| stage/sql/optimizing | 0.034 |
| stage/sql/statistics | 0.087 |
| stage/sql/preparing | 0.074 |
| stage/sql/executing | 0.74 |
| stage/sql/end | 0.003 |
| stage/sql/query end | 0.003 |
| stage/sql/waiting for handler commit | 0.025 |
| stage/sql/closing tables | 0.019 |
| stage/sql/freeing items | 0.176 |
| stage/sql/cleaning up | 0.003 |
| total | 1.654 |
+------------------------------------------------+-----------------+
19 rows in set (0.00 sec)
select
*
from
user
where
exists (
select
true
from
contact_method cm
where
cm.contact_id = contact.id
and cm.method = 'phone'
and cm.primary
)
order by
created_at desc
limit 10
—- partial index is even faster
create index primary_phone on contact_method (contact_id) where method = 'phone' and primary;There are some other tricks you can use if you're clever/lucky as well. If you're just using integer IDs (which is reasonable if your system isn't distributed) then you could order by userid on you ContactMethod table and still get the same speed as you would with no join.
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.
The benchmark: https://gist.github.com/yashap/6d7a34ef37c6b7d3e4fc11b0bece7...
Note: I think in almost all cases you should start with a denormalized schema and use joins. But when you hit cases like the above, it's fine to denormalize just for these specific cases - often you'll just have one or a few such cases in your entire app, where the combination of data size/shape/queries means you cannot have efficient queries without denormalizing. And when people say "joins are slow", it's often cases like this that they're talking about - it's not the join itself that's slow, but rather that cross-table compound indexes are impossible in most RDBMSes, and without that you just can't create good enough indexes for fast queries with lots of data and cold caches.
* normalized/join version needs to read 5600 pages
* normalized/join version with an additional UNIQUE INDEX .. INCLUDE (type) needs to read 4500 pages
* denormalized version only needs to read 66 pages, almost 100x fewer
Related to this pagination use case, when using mysql, even the denormalized version may take minutes: https://dom.as/2015/07/30/on-order-by-optimization/
Requires a bit of brainpower to set up a system around it, but it makes your application logic dramatically simpler.
(You don't have to remember to update `foo.bar` every time you write a function that touches `moo.bar`, and if you run migrations in SQL, the updates will also cascade naturally).
It's really high up on my personal wish list for Postgres to support incrementally-materialized views. It doesn't seem like it would be impossible to implement (since, as I suggested, you can implement it on your own with triggers), but IDK, I assume there are higher-priority issues on their docket.
FWIW, I don’t think joins are bad, I’m 100% for normalized DB schemas with joins. But I’ve done tonnes of performance work over the past ~10 years, and run into a bunch of real world cases where, when caches are cold (which does happen frequently with large datasets and limited budgets), queries similar to the above (join two tables, read a page of data, sorting on one table and filtering on the other) take 10s of seconds, sometimes even minutes with very large datasets. In those cases, the only solution has been to denormalize so we can create a compound index, with filter key(s) as the prefix, sort key as the suffix, which makes the query consistently fast. I am not at all suggesting this as the default, better to default to normalized with joins, and only do this for these specific cases. Generally a company just has one or a few cases where this is necessary, can just do the ugly denormalization + compound indexes for these few hot spots. But when ppl say “joins are slow”, these cases are examples of where it’s true.
Re: your above 17 second query, if you do something like adding fields to the user table for primary and secondary contact method (denormalizing), and then create a compound index with the necessary filter keys first, and the sort key second, I think you’ll find the query (which no longer needs a join) is quite fast even if caches are ice cold.
postgres=# CREATE INDEX sec_phone_created_at ON hn_phone_new (phone_secondary, created_at) WHERE phone_secondary IS NOT NULL;
postgres=# SELECT * FROM hn_phone_new WHERE phone_secondary IS NOT NULL ORDER BY created_at LIMIT 10;
id | created_at | phone_primary | phone_secondary
--------+---------------------+--------------------+--------------------
58816 | 1995-05-23 03:22:02 | +49 030 522866-87 | +1 159-445-4810
49964 | 1995-05-23 03:23:00 | +61 02 7440 8606 | +254 20 925 892
171828 | 1995-05-23 05:06:47 | +380 32 393-35-89 | +49 030 429376-29
78333 | 1995-05-23 05:31:22 | +380 32 147-11-20 | +52 55 6409 5253
24264 | 1995-05-23 06:47:21 | +44 0131 6506 1823 | +49 030 610965-83
96662 | 1995-05-23 06:57:03 | +52 55 1473 0538 | +61 02 5414 8204
15023 | 1995-05-23 07:55:37 | +44 0131 7959 1581 | +44 0131 8491 6194
52029 | 1995-05-23 08:59:19 | +380 32 430-77-54 | +254 20 374 856
20518 | 1995-05-23 09:51:14 | +380 32 264-21-79 | +52 55 7787 0236
80273 | 1995-05-23 14:59:26 | +61 02 8863 4466 | +33 01 16 10 78 56
(10 rows)
Time: 2258.807 ms (00:02.259)
postgres=# DROP INDEX sec_phone_created_at;
postgres=# CREATE INDEX created_at_sec_phone ON hn_phone_new (created_at, phone_secondary) WHERE phone_secondary IS NOT NULL;
Time: 221.392 ms
Time: 365.961 ms
Limit (cost=8.44..8.45 rows=1 width=61)
Output: id, created_at, phone_primary, phone_secondary
-> Sort (cost=8.44..8.45 rows=1 width=61)
Output: id, created_at, phone_primary, phone_secondary
Sort Key: hn_phone_new.created_at
-> Index Scan using sec_phone_created_at on public.hn_phone_new (cost=0.42..8.43 rows=1 width=61)
Output: id, created_at, phone_primary, phone_secondary
(7 rows)
Limit (cost=0.42..8.43 rows=1 width=61)
Output: id, created_at, phone_primary, phone_secondary
-> Index Scan using created_at_sec_phone on public.hn_phone_new (cost=0.42..8.43 rows=1 width=61)
Output: id, created_at, phone_primary, phone_secondary
(4 rows)
But I think this does show how, for specific data/queries, sometimes you do have to denormalize, so that you can create the ideal compound index, for specific problem queries. Should still go with normalized schemas and joins as a default, but if problem queries pop up like this that are taking 10, 20, 30 seconds sometimes (when caches are cold), compromising a bit on clean schemas/code for performance makes sense.
I also created a benchmark here, for Postgres: https://gist.github.com/yashap/6d7a34ef37c6b7d3e4fc11b0bece7...
BTW, although it wouldn’t have helped for your specific benchmark schema creation of TYPES, I’ll plug my genSQL tool [0] for generating random data. It’s primarily designed around MySQL, but it can produce CSVs easily, which every DB can load.
Turns out a lot of random() calls in most languages is slow af, so mine avoids that by (mostly) batching them in a C library. Should be able to create a million somethings in under 10 seconds on modern hardware in Python 3.11.