- 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.
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.
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.
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.
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.
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
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.
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.
> 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.
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.
Most business logic would be better expressed in the language of relational algebra (plus some extensions) than via OOP.
...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.
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.
- 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).
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.
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
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
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.
