10 ms·

Squeeze the hell out of the system you have

687 points by sbmsr 3y ago

39 3y ago
Strangely obvious advice?
- iamwil 3y ago
  That no one likes to follow.
- sfink 3y ago
  Well, the advice is rarely taken in practice. It is (in my experience, and it seems common from others based on what I've heard) very very common to jump to the complicated solution at the first hint of capacity issues "because we'll need to do it eventually anyway." 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.)
- bryanlarsen 3y ago
  “Common sense is not so common.” - Voltaire
- joelshep 3y ago
  It might be obvious as far as it goes, but it's also incomplete in at least two ways. One is that as tweaks and optimizations and "supplementing the system in some way" often involves increasing its complexity, even if just a little bit at a time. It adds up with time. The more important thing is this: if you're already constrained on vertical scaling, and you don't have a firm grip on how fast your system is scaling, then you can't just stop with making the db more efficient. That's just postponing the inevitable, and possibly not for more than a couple of years. If you're in the position the author portrays, get the database under control first -- for sure -- but then get started on figuring out how you're going to stay in front of your scaling problem, whether that's rearchitecture, off-loading work to systems better suited for it, or whatever. Speaking as a former owner of a very large Amazon database that fought this battle many times, trying to buy enough dev time to build away from it before it completely collapsed. We were too content with performance improvements just like the ones described in this article, before finally recognizing we were just racing the clock.
huijzer 3y ago
> We should always put off significant complexity increases as long as possible. Reminds me of the mantra that I’ve read here to easily go for reversible things and very careful when going for irreversible things.
- sssspppp 3y ago
  Amazon’s one way vs two way door decisions echo the sentiment
- sainez 3y ago
  It is mentioned in this article about the inception of AWS's custom silicon: https://semiconductor.substack.com/p/on-the-origins-of-aws-custom-silicon https://semiconductor.substack.com/p/on-the-origins-of-aws-c... > “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.”
discussDev 3y ago
It's the boring solution. It should also only be the default answer if you are not building a super critical system to life and limb. But it certainly gives a much lower total cost of ownership. If you don't have the resources for some big redundant system, I've too often seen the complexity added by the redundant system be the issue then focusing on simplicity. If you need to add a bunch of people to support complexity but both the money and the risk assessment doesn't call for it, simpler is much better. I won't say I haven't seen the issue where eventually it was only a huge project to go forward, but I tend to think sometimes even that is less then the sum of having dealt with complexity to that point, it's dependent on a lot about what you are building.
account-5 3y ago
I suppose it seems obvious in hindsight that your first move should always be to investigate potential causes before a wholesale redesign that adds potentially unnecessary complexity to your system.
Xeoncross 3y ago
> The real cost of increased complexity – often the much larger cost – is attention. ...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... https://www.youtube.com/watch?v=y8OnoxKotPQ https://www.youtube.com/watch?v=y8OnoxKotPQ
- javajosh 3y ago
  This is a very common architectural smell, when you have uservices and "no-one knows how they all work". The whole point is that no-one can or should know how they all work; the fact that someone does in order to fix or modify the system is a strong signal that you've violated some of the rules - like single responsibility, and proper abstraction through API. But, in my experience, this is extremely common - debugging a pipeline of N microservices often requires running and building all N services locally. This is, strictly speaking, a monolith + network partitions + (infinite) build/deploy variation. An extremely challenging work environment that is ultimately beyond any mortal programmer's ability, IMO.
endisneigh 3y ago
The bit on the database performance issues leads me to my hottest, flamiest take for new projects: - 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.
- veave 3y ago
  >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. Anybody has documentation about this with examples?
  - joshstrange 3y ago
    See "Single Table Design" which I talked about in this comment above: https://news.ycombinator.com/item?id=37093357 https://news.ycombinator.com/item?id=37093357
    - deely3 3y ago
      And if you don't want to spend money, you can get basic idea from this article: https://www.alexdebrie.com/posts/dynamodb-single-table/ https://www.alexdebrie.com/posts/dynamodb-single-table/
  - 3y ago
gary_0 3y ago
No mention of caching? If your database is getting hammered with SELECTs, isn't putting a cache in front of it something that should at least be considered?
- deathanatos 3y ago
  I've been in the OP's situation, and this exact suggestion was made in my case. Welcome to one of the hardest problems in CS: cache invalidation. 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.
  - lern_too_spel 3y ago
    There are systems that will do that for you like https://readyset.io/ https://readyset.io/.
- Scarbutt 3y ago
  They mentioned adding a DB replica for reads.
sssspppp 3y ago
Love this post. I’ve been trying to tell my manager the same message for the last few months (with little success). We’re about to embark on a massive migration to “next-gen infrastructure” (read: three different Redshift clusters managed by CDK) because our overloaded Redshift cluster (already maxed out with RA3 nodes) has melted down one too many times. The next-gen infra is significantly more complex than our existing setup and I’m not convinced this migration will be the silver bullet everyone is hoping for.
iamwil 3y ago
Ugh. I had a colleague that addressed any scaling problem by putting a cache in front of the DB. Praised for solving the immediate problem, but shouldered none of the costs. </rant> 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.
- aidos 3y ago
  Interestingly, I often ask candidates about optimising a slow running db query and the majority of people jump to adding caching and very few ask if they can run an explain or see the indexes.
  - tedunangst 3y ago
    "I would make the slow query faster" seems too obvious an answer for an interview question.
    - pphysch 3y ago
      The question is usually phrased like "what would you check/do if you had a page that was taking many seconds to load".
    - aidos 3y ago
      Haha, sure, but the very first thing you should ask when faced with a slow query is to see the “explain analyse” output. 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.
- FridgeSeal 3y ago
  “Just put a cache in front of it to speed it up” is up there along with everyone’s favourite misunderstood “(premature) optimisation is the root of all evil” when it comes to performance and efficient software in my opinion. 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.
andrewstuart 3y ago
Isn’t Rails wasteful in its database access patterns?
- iamwil 3y ago
  Generally No. But it can be easy to write bad queries using ActiveRecord ORM if you're not aware of N + 1 problems.
  - romafirst3 3y ago
    100%, it makes it easy for bad programmers to write bad performing queries, but you can easily write performant code. Btw that’s a feature - letting people ramp up to full db knowledge is beneficial, you don’t want to be spending your time writing performant queries before you need to.
    - topspin 3y ago
      > it makes it easy for bad programmers to write bad performing queries 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.
      - romafirst3 3y ago
        that is what I meant by saying it's a feature. It's easy to get started and do 99% of the stuff. When you are lucky enough to hit performance problems it's easy (for someone with knowledge) to fix your performance problems within the structure of Rails. It's awesome.
        topspin 3y ago
        > it's easy (for someone with knowledge) 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.
sheepz 3y ago
Agree wholeheartedly with the conclusion of the article. 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!
- PeledYuval 3y ago
  What's your recommended way of implementing this in a simple App Server <> Postgres architecture? Is there a good Postgres plugin or do you utilize something on the App side?
  - sheepz 3y ago
    I've used pganalyze which is a non-free SaaS tool. Gives you a very good overview of where the DB time is spent with index suggestions etc. There are free alternatives, but require more work from you.
  - clintonb 3y ago
    We use Datadog, which centralizes logs and application traces, allowing us to better pinpoint the exact request/code path making the slow query.
pluto_modadic 3y ago
ah... rails easy mode discovers rails is only performant if you don't stray too far from hello world...
fritzo 3y ago
> since our work touched many parts of the codebase and demanded collaboration with lots of different devs, we now have a strong distributed knowledge base about the existing system Great to see this cultural side-effect called out.
deathanatos 3y ago
> Split up the monolith into multiple interconnected services, each with its own data store that could be scaled on its own terms. 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.
- alexchamberlain 3y ago
  The other thing worth noting is a server can read from 2 datastores - which as a sibling comment says, they ended up doing with a read replica. There's nothing preventing you from reading from Postgres and Redis in the same process!
- kreetx 3y ago
  In a way, in the article they also did a split: specific heavy select queries were offloaded to a replica.
  - agentultra 3y ago
    They could probably squeeze more depending on their workload patterns. RDBMS' typically optimize for fast/convenient writes. If your write load would be fine with a small increase in latency then you can do a lot of de-normalization so that your reads can avoid using tonnes of joins, aggregates, windows, etc at read-time. Update write path so that you update all of the de-normalized views at write time. Depending on your read load and application structure you can get a lot more scale with caching. Decent article.
- mamcx 3y ago
jakey_bakey 3y ago
[The Grug Brained Developer](https://grugbrain.dev/ https://grugbrain.dev/)
i_like_apis 3y ago
I’m reminded of one of my favorite sayings: 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.
- bachmeier 3y ago
  That's the right attitude for an employee. If management says something like that, look for a new job. It's not sustainable to compete with fewer resources than your opposition. There's a reason college sports is going through a passionate realignment right now.
  - nostrademons 3y ago
    The way to win with fewer resources than your competition is to convince them it's not a competition. Or even better, to not let them know you exist.
- KnobbleMcKnees 3y ago
  That was Donald Rumsfeld!? I always assumed this came from some techie or agile guru given how much it's used as a concept in project planning.
  - midasuni 3y ago
    And unknown unknowns is a great way to communicate with stakeholders too
    - roughly 3y ago
      Žižek has a followup to that quote: "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."
WallyFunk 3y ago
> Of course, I’m not saying complexity is bad. It’s necessary. 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. [0] https://en.wikipedia.org/wiki/Minix https://en.wikipedia.org/wiki/Minix
javajosh 3y ago
I'll probably get down-voted for saying this (again), but a key way to squeeze unimaginable amounts of performance is to lean into stored procedures. 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.
- throwdbaaway 3y ago
  What do you think about evolving the stored procedures into a stateless GRPC service that fronts the database? For the price of 1 (or 2) additional network hop, you get much better devx, while keeping most of the benefits provided by stored procedures.
  - javajosh 3y ago
    I don't think this adds much. The important thing is that your logic runs in the database--how the logic is ultimately exposed is up to you. We did a java app server calling SPs over JDBC with generated, typed bindings and this worked great. You'd have to write a similar tool to generate a gRPC server, but the logic would still be SPs in the database. That's the part that sucks for devs; the bindings are ultimately a detail.
- giantrobot 3y ago
notnmeyer 3y ago
haha, when i read their initial thoughts were write-sharding and microservices i whispered “wtf?” to myself. glad to see there was a better ending to the story though.
mandevil 3y ago
He says to avoid complexity, and the team he was on (cleaning up some bad queries) was probably improving along that axis (or at worst orthogonal to complexity) but, from having done exactly this, adding an optional 'query the read-replica' option for queries- and determining whether this query can safely go there- is definitely extra complexity which will now need to be managed into the future. Definitely less overall than a complete re-arching of the system, but this is where engineering judgement and experience come into play: would you be better off getting those select queries resolved with some other data store or with a pg read-replica? If your query can survive against the read-replica (so stale data is at least sometimes acceptable) would you be better off caching results in redis?
- gwbas1c 3y ago
  > If your query can survive against the read-replica (so stale data is at least sometimes acceptable) would you be better off caching results in redis? 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.)
dbg31415 3y ago
I feel like a lot of people do this instead of upgrading to newer versions, even maintenance patches. 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.
romafirst3 3y ago
TLDR. We were going to completely rewrite our architecture but instead we optimized a few Postgres queries. LMAO
phirschybar 3y ago
I agree with this approach. the other added benefit is that when they decided to optimize the app by eliminating or tuning queries and utilizing replicas for reads, they ultimately made the app much more performant while possibly reducing complexity. the "squeeze" mindset pays off in the long-run here. the continued optimization over time is infinitely better than adding the complexity of microservices or expanded infrastructure because the latter will simply bury and compound the potential optimizations which could AND SHOULD have been made. squeeze squeeze squeeze until you just can't squeeze any more!
nathias 3y ago
Complexity in software is bad, things can be bad and necessary. It's bad in itself, but sometimes it can provide new functionality...
iblaine 3y ago
TL;DR; do the easy things fist, in this case it was to fix bad SQL Given the options to optimize SQL, move read operations to replicas, shard data or go towards micro services, optimizing SQL is the easy choice.
- bayindirh 3y ago
  Actually, I disagree. The "TL;DR:" in the article is "first outgrow, then upgrade". In today's software development practice, efficiency is second class citizen, because moving fast and breaking things is the way to keep the momentum and be hip. 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.
sakopov 3y ago
I thought I was going to read something insightful. Instead it was a post about how to completely ignore your database performance and then consider overcomplicating everything with sharding and microservices because you didn't care to do basic profiling on your queries. I'm glad common sense prevailed, but this is really some junior-level stuff and it's being celebrated as some kind of novelty.
exabrial 3y ago
This is amazing advice. A side note is to use the hell out of replication. These things don't have to be complicated. Setup a readonly and a readwrite datasource/connection pool in your app if you have to.
TX81Z 3y ago
Really curious how much can be attributed to using an ORM.
- winrid 3y ago
  None. The ORM didn't design and type up the code.
alfor 3y ago
I wonder if moving the db on beefy dedicated hardware with ton of ram and nvme would solve the problem. Preferably physicaly connected to the web serveurs. 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.
- winrid 3y ago
  If they were on the biggest instance I doubt they want to setup a network connection to another DC with another provider which has all kinds of business/privacy policy/etc concerns. They likely already had 24.xlarge or something lol
alfalfasprout 3y ago
The problem is this is also a myopic way of looking at things. What you should be looking at is also operational complexity. What's the current burden on your org/team maintaining what you currently have? What about when you need to scale even higher? 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.
gillh 3y ago
Prioritized load shedding works well as a last resort [0]. The idea is simple - - 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-adaptive-rate-limiting https://docs.fluxninja.com/blog/protecting-postgresql-with-a...
zengid 3y ago
The solution they went with, squeezing juice out of the system by finding performance optimizations, brings me so much joy. 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!
- canucker2016 3y ago
  The problem I have with their eventual solution is that they only optimized their queries AFTER they had upgraded their instance to the largest config available. 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.
  - sgarland 3y ago
    Despite the fact that SQL is not a complex language, and relational algebra isn't that hard, people regard it as dark magic. 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."
- scottlamb 3y ago
  > 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... [1] e.g. https://research.google/pubs/pub36575/ https://research.google/pubs/pub36575/
kunalgupta 3y ago
I would definitely do the opposite of this - 3 months is a while and i think the cost of complexity would take a long time before it comparec
_ea1k 3y ago
I blame the easy availability of additional resources in the cloud for a lot of problems here. Prod db slow? Get a bigger EC2 instance. Still slow? Hmm, maybe bigger again! Why bother tuning. 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.
Joel_Mckay 3y ago
The Monolith is often a marker of several naive assumptions. 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
macNchz 3y ago
In my experience, in web apps built on top of ORMs there is often a TON of low hanging fruit for query optimization when database load becomes an issue. Beyond the basics of "do we have N+1 issues", ORMs sometimes just don't generate optimal queries. I wouldn't want to built a complex production web app without an ORM, but being able to eject from it sometimes is key. 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.
- eru 3y ago
  A big problem with ORM's is that object orientation is just not a good way to organise software nor data for most domains. Most business logic would be better expressed in the language of relational algebra (plus some extensions) than via OOP.
- nerdponx 3y ago
  I'm working on something right now with the Python ORM SQLAlchemy. It turns out that getting it to use RETURNING with INSERT is not trivial and requires you to set the non-obvious option `expire_on_commit=False`, which doesn't guarantee use of RETURNING, but is supposed to use it if your db driver and database happen to support it and the ORM happens to support it for that particular combination of driver and database. And there's no API to actually inspect the generated SQL even though it's emitted in the logs, so there's no way to enforce the use of RETURNING in your test suite without capturing and scraping your own logs (which fortunately is very easy within the Pytest framework). 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).
maxboone 3y ago
Relevant blog on improving PostgreSQL performance on ZFS: https://news.ycombinator.com/item?id=29647645 https://news.ycombinator.com/item?id=29647645
klodolph 3y ago
I have personally witnessed the “let’s build microservices to get better performance” argument. I definitely want to nip that in the bud. 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.
justinlloyd 3y ago
Squeeze what you've got, as hard as you can, then realize after squeezing for a while that if you squeezed here, here, and also... here, by changing how you think about a problem, suddenly you've got a lot left to get. 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.
- smarkov 3y ago
  Very curious to learn more about what the monolith was doing so incredibly poorly that you managed to squeeze that much performance out of it. Poorly written queries? Too many queries? Lack of any caching? Doing things synchronously when they could've been done concurrently?
  - porridgeandrice 3y ago
    +1 I'd like to know as well
    - justinlloyd 3y ago
      https://justinlloyd.li/blog/how-much-cache-you-got-on-you/ https://justinlloyd.li/blog/how-much-cache-you-got-on-you/
  - 3y ago
userbinator 3y ago
From the title, I was expecting something demoscene-related. 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.
tomxor 3y ago
I find it useful to frame performance of pure software in two fairly distinct categories: 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.
quickthrower2 3y ago
I wonder if it is better to shard by tenant (customer) in the first place. A bit more complexity upfront but not that much really. Tenants could initially share a DB on different schemas. 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.
jmmv 3y ago
Reminds me of something I wrote a while back: https://jmmv.dev/2020/01/system-rewrites-and-tuning.html https://jmmv.dev/2020/01/system-rewrites-and-tuning.html 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.
anotheraccount9 3y ago
This can work long term, as long as your system is not a group of humans. But certainly, making what appear like small adjustments can sometimes tremendously lower required resources.
user6723 3y ago
https://www.citusdata.com/ https://www.citusdata.com/
deleted 3y ago
[deleted]
recursive4 3y ago
Pick one and hire for it.
pythooooi 3y ago
It has never been as easy as today to see slow / shitty queries life. 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.
klntsky 3y ago
Changing the architecture gives objective, measurable and predictable gains. Fine-tuning can give you any improvement on a scale of 0%-100%, and you never know in advance. So it is sometimes better to invest resources in a re-design.
otar 3y ago
Loads of over-engineering decisions would be avoided if devs understood how to read EXPLAIN/ANALYZE results and do the proper indexing/query optimization. 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
- hennell 3y ago
  Simply understanding how to read explain output can be quite a task in itself though, databases are a whole other thing, especially if you barely do any SQL yourself. Tools like https://explainmysql.com 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.
  - Too 3y ago
    Understanding explain output is usually very simple. 1 Look for any occurrence of “table scan”. 2. Add index on those queried fields or limit the query by filtering on another already indexed field. 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.
- hi41 3y ago
  Do you know of any good resources to understand sql explain plan. In my current project, we are facing a lot of issues related to query performance on MS SQL server. Do we need to always specify index hint with queries. Sometimes index exists but query does not seem to be using the index. I am thinking using sql execution plan could help us understand this issue better. tia.
atmosx 3y ago
> Just think about how massive these costs are. How much feature delivery will have to be delayed or foregone to support the additional architectural complexity? 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).
- andresp 3y ago
  I got to understand from personal experience that the anti-SOA people are usually the ones who stayed at the same company their entire carers, never saw any model other than the monolithic one, see SOA as a threat to their domain knowledge within the company and simply are not able to see its downsides (because they have adapted their ways of work around it and never experienced anything better).
  - tomas789 3y ago
    I will offer myself as a counterexample. I worked at many companies, as exployee or contractor and held various positions. Dev, tech lead, manager. 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.
gslin 3y ago
The author is a SRE of HashiCorp (from LinkedIn), so I guess (yeah just guess) what he mentioned in the article is Terraform Cloud, based on the growing business.
vendiddy 3y ago
I think a lot of complexity from optimizing databases would go away if incrementally computed materialized views were widely available.
bullen 3y ago
The way I solve this is having a custom realtime distributed db and being very careful with writes. Reads can scale to infinity. http://root.rupy.se http://root.rupy.se
wlonkly 3y ago
I know it's easy to be an armchair quarterback, but I'm surprised that they were faced with considering sharding or rearchitecting into microservices before they'd set up a read replica.
rsync 3y ago
I'm late to this conversation but in case anyone is still reading ... 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.
jb3689 3y ago
Working on a database infra team has taught me that most developers don’t understand databases. Like they understand SQL and basic stuff, but they don’t understand how a database really works. Failure modes, consistency models, B-trees, caches, indexes. Turns out that stuff is important.

687 points by sbmsr 3y ago

39 3y ago

Strangely obvious advice?

iamwil 3y ago
That no one likes to follow.
sfink 3y ago
Well, the advice is rarely taken in practice. It is (in my experience, and it seems common from others based on what I've heard) very very common to jump to the complicated solution at the first hint of capacity issues "because we'll need to do it eventually anyway." 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.)
bryanlarsen 3y ago
“Common sense is not so common.” - Voltaire
joelshep 3y ago
It might be obvious as far as it goes, but it's also incomplete in at least two ways. One is that as tweaks and optimizations and "supplementing the system in some way" often involves increasing its complexity, even if just a little bit at a time. It adds up with time. The more important thing is this: if you're already constrained on vertical scaling, and you don't have a firm grip on how fast your system is scaling, then you can't just stop with making the db more efficient. That's just postponing the inevitable, and possibly not for more than a couple of years. If you're in the position the author portrays, get the database under control first -- for sure -- but then get started on figuring out how you're going to stay in front of your scaling problem, whether that's rearchitecture, off-loading work to systems better suited for it, or whatever. Speaking as a former owner of a very large Amazon database that fought this battle many times, trying to buy enough dev time to build away from it before it completely collapsed. We were too content with performance improvements just like the ones described in this article, before finally recognizing we were just racing the clock.

huijzer 3y ago

> We should always put off significant complexity increases as long as possible. Reminds me of the mantra that I’ve read here to easily go for reversible things and very careful when going for irreversible things.

sssspppp 3y ago
Amazon’s one way vs two way door decisions echo the sentiment
sainez 3y ago
It is mentioned in this article about the inception of AWS's custom silicon: https://semiconductor.substack.com/p/on-the-origins-of-aws-custom-silicon https://semiconductor.substack.com/p/on-the-origins-of-aws-c... > “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.”

discussDev 3y ago

It's the boring solution. It should also only be the default answer if you are not building a super critical system to life and limb. But it certainly gives a much lower total cost of ownership. If you don't have the resources for some big redundant system, I've too often seen the complexity added by the redundant system be the issue then focusing on simplicity. If you need to add a bunch of people to support complexity but both the money and the risk assessment doesn't call for it, simpler is much better. I won't say I haven't seen the issue where eventually it was only a huge project to go forward, but I tend to think sometimes even that is less then the sum of having dealt with complexity to that point, it's dependent on a lot about what you are building.

account-5 3y ago

I suppose it seems obvious in hindsight that your first move should always be to investigate potential causes before a wholesale redesign that adds potentially unnecessary complexity to your system.

Xeoncross 3y ago

> The real cost of increased complexity – often the much larger cost – is attention. ...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... https://www.youtube.com/watch?v=y8OnoxKotPQ https://www.youtube.com/watch?v=y8OnoxKotPQ

javajosh 3y ago
This is a very common architectural smell, when you have uservices and "no-one knows how they all work". The whole point is that no-one can or should know how they all work; the fact that someone does in order to fix or modify the system is a strong signal that you've violated some of the rules - like single responsibility, and proper abstraction through API. But, in my experience, this is extremely common - debugging a pipeline of N microservices often requires running and building all N services locally. This is, strictly speaking, a monolith + network partitions + (infinite) build/deploy variation. An extremely challenging work environment that is ultimately beyond any mortal programmer's ability, IMO.

endisneigh 3y ago

The bit on the database performance issues leads me to my hottest, flamiest take for new projects: - 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.

veave 3y ago
>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. Anybody has documentation about this with examples?
- joshstrange 3y ago
  See "Single Table Design" which I talked about in this comment above: https://news.ycombinator.com/item?id=37093357 https://news.ycombinator.com/item?id=37093357
  - deely3 3y ago
    And if you don't want to spend money, you can get basic idea from this article: https://www.alexdebrie.com/posts/dynamodb-single-table/ https://www.alexdebrie.com/posts/dynamodb-single-table/
- 3y ago

gary_0 3y ago

No mention of caching? If your database is getting hammered with SELECTs, isn't putting a cache in front of it something that should at least be considered?

deathanatos 3y ago
I've been in the OP's situation, and this exact suggestion was made in my case. Welcome to one of the hardest problems in CS: cache invalidation. 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.
- lern_too_spel 3y ago
  There are systems that will do that for you like https://readyset.io/ https://readyset.io/.
Scarbutt 3y ago
They mentioned adding a DB replica for reads.

sssspppp 3y ago

Love this post. I’ve been trying to tell my manager the same message for the last few months (with little success). We’re about to embark on a massive migration to “next-gen infrastructure” (read: three different Redshift clusters managed by CDK) because our overloaded Redshift cluster (already maxed out with RA3 nodes) has melted down one too many times. The next-gen infra is significantly more complex than our existing setup and I’m not convinced this migration will be the silver bullet everyone is hoping for.

iamwil 3y ago

Ugh. I had a colleague that addressed any scaling problem by putting a cache in front of the DB. Praised for solving the immediate problem, but shouldered none of the costs. </rant> 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.

aidos 3y ago
Interestingly, I often ask candidates about optimising a slow running db query and the majority of people jump to adding caching and very few ask if they can run an explain or see the indexes.
- tedunangst 3y ago
  "I would make the slow query faster" seems too obvious an answer for an interview question.
  - pphysch 3y ago
    The question is usually phrased like "what would you check/do if you had a page that was taking many seconds to load".
  - aidos 3y ago
    Haha, sure, but the very first thing you should ask when faced with a slow query is to see the “explain analyse” output. 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.
FridgeSeal 3y ago
“Just put a cache in front of it to speed it up” is up there along with everyone’s favourite misunderstood “(premature) optimisation is the root of all evil” when it comes to performance and efficient software in my opinion. 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.

andrewstuart 3y ago

Isn’t Rails wasteful in its database access patterns?

iamwil 3y ago
Generally No. But it can be easy to write bad queries using ActiveRecord ORM if you're not aware of N + 1 problems.
- romafirst3 3y ago
  100%, it makes it easy for bad programmers to write bad performing queries, but you can easily write performant code. Btw that’s a feature - letting people ramp up to full db knowledge is beneficial, you don’t want to be spending your time writing performant queries before you need to.
  - topspin 3y ago
    > it makes it easy for bad programmers to write bad performing queries 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.
    - romafirst3 3y ago
      that is what I meant by saying it's a feature. It's easy to get started and do 99% of the stuff. When you are lucky enough to hit performance problems it's easy (for someone with knowledge) to fix your performance problems within the structure of Rails. It's awesome.
      - topspin 3y ago
        > it's easy (for someone with knowledge) 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.

sheepz 3y ago

Agree wholeheartedly with the conclusion of the article. 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!

PeledYuval 3y ago
What's your recommended way of implementing this in a simple App Server <> Postgres architecture? Is there a good Postgres plugin or do you utilize something on the App side?
- sheepz 3y ago
  I've used pganalyze which is a non-free SaaS tool. Gives you a very good overview of where the DB time is spent with index suggestions etc. There are free alternatives, but require more work from you.
- clintonb 3y ago
  We use Datadog, which centralizes logs and application traces, allowing us to better pinpoint the exact request/code path making the slow query.

pluto_modadic 3y ago

ah... rails easy mode discovers rails is only performant if you don't stray too far from hello world...

fritzo 3y ago

> since our work touched many parts of the codebase and demanded collaboration with lots of different devs, we now have a strong distributed knowledge base about the existing system Great to see this cultural side-effect called out.

deathanatos 3y ago

> Split up the monolith into multiple interconnected services, each with its own data store that could be scaled on its own terms. 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.

alexchamberlain 3y ago
The other thing worth noting is a server can read from 2 datastores - which as a sibling comment says, they ended up doing with a read replica. There's nothing preventing you from reading from Postgres and Redis in the same process!
kreetx 3y ago
In a way, in the article they also did a split: specific heavy select queries were offloaded to a replica.
- agentultra 3y ago
  They could probably squeeze more depending on their workload patterns. RDBMS' typically optimize for fast/convenient writes. If your write load would be fine with a small increase in latency then you can do a lot of de-normalization so that your reads can avoid using tonnes of joins, aggregates, windows, etc at read-time. Update write path so that you update all of the de-normalized views at write time. Depending on your read load and application structure you can get a lot more scale with caching. Decent article.
mamcx 3y ago

jakey_bakey 3y ago

[The Grug Brained Developer](https://grugbrain.dev/ https://grugbrain.dev/)

i_like_apis 3y ago

I’m reminded of one of my favorite sayings: 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.

bachmeier 3y ago
That's the right attitude for an employee. If management says something like that, look for a new job. It's not sustainable to compete with fewer resources than your opposition. There's a reason college sports is going through a passionate realignment right now.
- nostrademons 3y ago
  The way to win with fewer resources than your competition is to convince them it's not a competition. Or even better, to not let them know you exist.
KnobbleMcKnees 3y ago
That was Donald Rumsfeld!? I always assumed this came from some techie or agile guru given how much it's used as a concept in project planning.
- midasuni 3y ago
  And unknown unknowns is a great way to communicate with stakeholders too
  - roughly 3y ago
    Žižek has a followup to that quote: "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."

WallyFunk 3y ago

> Of course, I’m not saying complexity is bad. It’s necessary. 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. [0] https://en.wikipedia.org/wiki/Minix https://en.wikipedia.org/wiki/Minix

javajosh 3y ago

I'll probably get down-voted for saying this (again), but a key way to squeeze unimaginable amounts of performance is to lean into stored procedures. 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.

throwdbaaway 3y ago
What do you think about evolving the stored procedures into a stateless GRPC service that fronts the database? For the price of 1 (or 2) additional network hop, you get much better devx, while keeping most of the benefits provided by stored procedures.
- javajosh 3y ago
  I don't think this adds much. The important thing is that your logic runs in the database--how the logic is ultimately exposed is up to you. We did a java app server calling SPs over JDBC with generated, typed bindings and this worked great. You'd have to write a similar tool to generate a gRPC server, but the logic would still be SPs in the database. That's the part that sucks for devs; the bindings are ultimately a detail.
giantrobot 3y ago

notnmeyer 3y ago

haha, when i read their initial thoughts were write-sharding and microservices i whispered “wtf?” to myself. glad to see there was a better ending to the story though.

mandevil 3y ago

He says to avoid complexity, and the team he was on (cleaning up some bad queries) was probably improving along that axis (or at worst orthogonal to complexity) but, from having done exactly this, adding an optional 'query the read-replica' option for queries- and determining whether this query can safely go there- is definitely extra complexity which will now need to be managed into the future. Definitely less overall than a complete re-arching of the system, but this is where engineering judgement and experience come into play: would you be better off getting those select queries resolved with some other data store or with a pg read-replica? If your query can survive against the read-replica (so stale data is at least sometimes acceptable) would you be better off caching results in redis?

gwbas1c 3y ago
> If your query can survive against the read-replica (so stale data is at least sometimes acceptable) would you be better off caching results in redis? 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.)

dbg31415 3y ago

I feel like a lot of people do this instead of upgrading to newer versions, even maintenance patches. 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.

romafirst3 3y ago

TLDR. We were going to completely rewrite our architecture but instead we optimized a few Postgres queries. LMAO

phirschybar 3y ago

I agree with this approach. the other added benefit is that when they decided to optimize the app by eliminating or tuning queries and utilizing replicas for reads, they ultimately made the app much more performant while possibly reducing complexity. the "squeeze" mindset pays off in the long-run here. the continued optimization over time is infinitely better than adding the complexity of microservices or expanded infrastructure because the latter will simply bury and compound the potential optimizations which could AND SHOULD have been made. squeeze squeeze squeeze until you just can't squeeze any more!

nathias 3y ago

Complexity in software is bad, things can be bad and necessary. It's bad in itself, but sometimes it can provide new functionality...

iblaine 3y ago

TL;DR; do the easy things fist, in this case it was to fix bad SQL Given the options to optimize SQL, move read operations to replicas, shard data or go towards micro services, optimizing SQL is the easy choice.

bayindirh 3y ago
Actually, I disagree. The "TL;DR:" in the article is "first outgrow, then upgrade". In today's software development practice, efficiency is second class citizen, because moving fast and breaking things is the way to keep the momentum and be hip. 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.

sakopov 3y ago

I thought I was going to read something insightful. Instead it was a post about how to completely ignore your database performance and then consider overcomplicating everything with sharding and microservices because you didn't care to do basic profiling on your queries. I'm glad common sense prevailed, but this is really some junior-level stuff and it's being celebrated as some kind of novelty.

exabrial 3y ago

This is amazing advice. A side note is to use the hell out of replication. These things don't have to be complicated. Setup a readonly and a readwrite datasource/connection pool in your app if you have to.

TX81Z 3y ago

Really curious how much can be attributed to using an ORM.

winrid 3y ago
None. The ORM didn't design and type up the code.

alfor 3y ago

I wonder if moving the db on beefy dedicated hardware with ton of ram and nvme would solve the problem. Preferably physicaly connected to the web serveurs. 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.

winrid 3y ago
If they were on the biggest instance I doubt they want to setup a network connection to another DC with another provider which has all kinds of business/privacy policy/etc concerns. They likely already had 24.xlarge or something lol

alfalfasprout 3y ago

The problem is this is also a myopic way of looking at things. What you should be looking at is also operational complexity. What's the current burden on your org/team maintaining what you currently have? What about when you need to scale even higher? 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.

gillh 3y ago

Prioritized load shedding works well as a last resort [0]. The idea is simple - - 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-adaptive-rate-limiting https://docs.fluxninja.com/blog/protecting-postgresql-with-a...

zengid 3y ago

The solution they went with, squeezing juice out of the system by finding performance optimizations, brings me so much joy. 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!

canucker2016 3y ago
The problem I have with their eventual solution is that they only optimized their queries AFTER they had upgraded their instance to the largest config available. 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.
- sgarland 3y ago
  Despite the fact that SQL is not a complex language, and relational algebra isn't that hard, people regard it as dark magic. 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."
scottlamb 3y ago
> 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... [1] e.g. https://research.google/pubs/pub36575/ https://research.google/pubs/pub36575/

kunalgupta 3y ago

I would definitely do the opposite of this - 3 months is a while and i think the cost of complexity would take a long time before it comparec

_ea1k 3y ago

I blame the easy availability of additional resources in the cloud for a lot of problems here. Prod db slow? Get a bigger EC2 instance. Still slow? Hmm, maybe bigger again! Why bother tuning. 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.

Joel_Mckay 3y ago

The Monolith is often a marker of several naive assumptions. 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

macNchz 3y ago

In my experience, in web apps built on top of ORMs there is often a TON of low hanging fruit for query optimization when database load becomes an issue. Beyond the basics of "do we have N+1 issues", ORMs sometimes just don't generate optimal queries. I wouldn't want to built a complex production web app without an ORM, but being able to eject from it sometimes is key. 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.

eru 3y ago
A big problem with ORM's is that object orientation is just not a good way to organise software nor data for most domains. Most business logic would be better expressed in the language of relational algebra (plus some extensions) than via OOP.
nerdponx 3y ago
I'm working on something right now with the Python ORM SQLAlchemy. It turns out that getting it to use RETURNING with INSERT is not trivial and requires you to set the non-obvious option `expire_on_commit=False`, which doesn't guarantee use of RETURNING, but is supposed to use it if your db driver and database happen to support it and the ORM happens to support it for that particular combination of driver and database. And there's no API to actually inspect the generated SQL even though it's emitted in the logs, so there's no way to enforce the use of RETURNING in your test suite without capturing and scraping your own logs (which fortunately is very easy within the Pytest framework). 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).

maxboone 3y ago

Relevant blog on improving PostgreSQL performance on ZFS: https://news.ycombinator.com/item?id=29647645 https://news.ycombinator.com/item?id=29647645

klodolph 3y ago

I have personally witnessed the “let’s build microservices to get better performance” argument. I definitely want to nip that in the bud. 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.

justinlloyd 3y ago

Squeeze what you've got, as hard as you can, then realize after squeezing for a while that if you squeezed here, here, and also... here, by changing how you think about a problem, suddenly you've got a lot left to get. 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.

smarkov 3y ago
Very curious to learn more about what the monolith was doing so incredibly poorly that you managed to squeeze that much performance out of it. Poorly written queries? Too many queries? Lack of any caching? Doing things synchronously when they could've been done concurrently?
- porridgeandrice 3y ago
  +1 I'd like to know as well
  - justinlloyd 3y ago
    https://justinlloyd.li/blog/how-much-cache-you-got-on-you/ https://justinlloyd.li/blog/how-much-cache-you-got-on-you/
- 3y ago

userbinator 3y ago

From the title, I was expecting something demoscene-related. 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.

tomxor 3y ago

I find it useful to frame performance of pure software in two fairly distinct categories: 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.

quickthrower2 3y ago

I wonder if it is better to shard by tenant (customer) in the first place. A bit more complexity upfront but not that much really. Tenants could initially share a DB on different schemas. 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.

jmmv 3y ago

Reminds me of something I wrote a while back: https://jmmv.dev/2020/01/system-rewrites-and-tuning.html https://jmmv.dev/2020/01/system-rewrites-and-tuning.html 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.

anotheraccount9 3y ago

This can work long term, as long as your system is not a group of humans. But certainly, making what appear like small adjustments can sometimes tremendously lower required resources.

user6723 3y ago

https://www.citusdata.com/ https://www.citusdata.com/

deleted 3y ago

[deleted]

recursive4 3y ago

Pick one and hire for it.

pythooooi 3y ago

It has never been as easy as today to see slow / shitty queries life. 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.

klntsky 3y ago

Changing the architecture gives objective, measurable and predictable gains. Fine-tuning can give you any improvement on a scale of 0%-100%, and you never know in advance. So it is sometimes better to invest resources in a re-design.

otar 3y ago

Loads of over-engineering decisions would be avoided if devs understood how to read EXPLAIN/ANALYZE results and do the proper indexing/query optimization. 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

hennell 3y ago
Simply understanding how to read explain output can be quite a task in itself though, databases are a whole other thing, especially if you barely do any SQL yourself. Tools like https://explainmysql.com 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.
- Too 3y ago
  Understanding explain output is usually very simple. 1 Look for any occurrence of “table scan”. 2. Add index on those queried fields or limit the query by filtering on another already indexed field. 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.
hi41 3y ago
Do you know of any good resources to understand sql explain plan. In my current project, we are facing a lot of issues related to query performance on MS SQL server. Do we need to always specify index hint with queries. Sometimes index exists but query does not seem to be using the index. I am thinking using sql execution plan could help us understand this issue better. tia.

atmosx 3y ago

> Just think about how massive these costs are. How much feature delivery will have to be delayed or foregone to support the additional architectural complexity? 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).

andresp 3y ago
I got to understand from personal experience that the anti-SOA people are usually the ones who stayed at the same company their entire carers, never saw any model other than the monolithic one, see SOA as a threat to their domain knowledge within the company and simply are not able to see its downsides (because they have adapted their ways of work around it and never experienced anything better).
- tomas789 3y ago
  I will offer myself as a counterexample. I worked at many companies, as exployee or contractor and held various positions. Dev, tech lead, manager. 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.

gslin 3y ago

The author is a SRE of HashiCorp (from LinkedIn), so I guess (yeah just guess) what he mentioned in the article is Terraform Cloud, based on the growing business.

vendiddy 3y ago

I think a lot of complexity from optimizing databases would go away if incrementally computed materialized views were widely available.

bullen 3y ago

The way I solve this is having a custom realtime distributed db and being very careful with writes. Reads can scale to infinity. http://root.rupy.se http://root.rupy.se

wlonkly 3y ago

I know it's easy to be an armchair quarterback, but I'm surprised that they were faced with considering sharding or rearchitecting into microservices before they'd set up a read replica.

rsync 3y ago

I'm late to this conversation but in case anyone is still reading ... 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.

jb3689 3y ago

Working on a database infra team has taught me that most developers don’t understand databases. Like they understand SQL and basic stuff, but they don’t understand how a database really works. Failure modes, consistency models, B-trees, caches, indexes. Turns out that stuff is important.