Operating on a minimal two-core Postgres instance: Query optimization insights

How did you arrive at $4000?

I used https://calculator.aws/#/estimate and my number is over an order of magnitude less than $8000 for 2 instances with SSDs, 4GB RAM, and 2 cores.

How much would they pay an average SRE to deal with random fires operating a poorly designed, poorly instrumented and poorly understood system?

Reserved m6a.xlarge(4vcpu, 16 GB RAM) costs <$1000. Assuming most companies use reserved for things like DB. So yeah their savings is like less than $1000, at which point it feels more like a developer satisfaction project rather than having any ROI on money terms.

The balancing act between one-time developer hours and the recurring costs associated with scaling up database resources can be challenging. If sufficient funds are available, scaling up may seem like an easy decision. However, investing in the development of expertise in efficient database operations is usually more cost-effective in the long run. Additionally, acquiring the skills to debug queries and optimize database configurations can ultimately save on developer hours.

> How much do they pay an average software developer?

Let's say that you pay a lot. Like you expend $20.000 USD doing this.

But we forget the cost power of software.

Your monthly savings extend to the infinity.

Your dev is now freer to do other things that are precluded in a more complex scenario ("you save pennies here, but expend pounds elsewhere"), because complexity DEMANDS MORE ATTENTION.

This is something I have observed all my life. I have never regretted to optime]ize for operational costs even if this means I "waste" a lot of time for this. I work in a more cutthroat environment than most startups (ERPs), where my time to deploy to production something is measured in hours, and is an environment that is filled by the graveyard of my competitors that have drink all the kool-aids.

Complex operational environments ALWAYS have turned worse/more complex than do the simplest things.

P.D: I think is similar to spending time in refactoring, cleanups, testing, security audits, training, and even resting.

All of that sounds like "costs". But you are doing this stuff alongside, just need to budget a few hours a week on this and the profits come and come.

Never regret using time on this stuff.

NEED to be smart on this? Sure. But is pure muscle and after a while is great.

And now your system can scale better, saving you enough money to justify the optimization work long-term.

The StackOverflow folks famously take their DB design and performance very seriously, and now they do not need an army of overpriced "architects" to plug the performance holes with more insane and expensive complexity.

https://stackexchange.com/performance

Coincidentally I just wrote up a post about the importance of minding your database skills:

https://renegadeotter.com/2023/11/12/your-database-skills-ar...

As someone who does DB optimization for a living - there is a tradeoff, I've seen far too many places where db instances run with default settings out of the box, queries missing basic indexes, no one even cared to run an explain analyze.

I've seen far too experienced developers with no basic understanding of how relational databases operate. And it's gotten worse over the years - as they can keep throwing hardware at the problem. Performance optimization has become a lost art. This is as much a problem of negligence as much as it is of education.

I won't even touch on proper design, which requires someone with significant database expertise from the get-go ( which most startups may not have).

You can run this on Hetzner ARM hosts for about $30/year o_O

The last line from the article: "We must not forget that 20 years ago only a fraction of the performance of current hardware resources was available, and despite these restrictions, performance database-based applications were developed."

I think you have a mental model in which these people are spending weeks and months optimizing a system they should just upgrade while the real tasks languish, but in my experience, probably all they are doing is just paying a bit of attention every so often and simply doing things that don't require 32 cores and terabytes of RAM. A lot of things don't need that power.

20 years ago you had a lot more real variance in power. If your 100MHz server couldn't handle it, maybe your 400MHz server could, and the gap between those two systems is probably even bigger than the numbers imply because that 400MHz was probably equipped with better everything else (RAM, disk, cache, better performance/cycle for the CPU) to go with it. Nowadays, with CPU speeds having stalled, it's easy to go wide and service lots of diverse queries at a time, but if you have a particular query that your lowest-end DB node is struggling with, there's a pretty decent chance that just pushing the button to go to a bigger node will have a distinctly sub-linear result on performance improvement. I've certainly seen this from a number of teams in a number of places. You can push a button to get more CPUs but you can't push a button to get much better CPUs. Some better, yes, but not like it used to be.

If you just try a little, modern commodity systems can do a lot. Even with only 4 cores and 4GB. Just try a little. Yes, there are absolutely tasks that that doesn't work with; if you've got 10,000 hours of video to re-encode that system isn't going to cut it. But a lot of developers are really bad at understanding how long things should take and don't realize that the thing that takes two minutes during CI really should take something more like 50ms because https://accidentallyquadratic.tumblr.com/ and somebody really ought to look into that rather than just bump the CI node size again hoping it solves the problem.

(And for Pete's sake, when bumping the instance size does happen to not solve the problem... turn it back down again! Despite what I just wrote I do understand there's a time and a place to spray money at the problem, but when spraying money at the problem doesn't even help, STOP.)

> The optimizations are no doubt impressive

The sad thing is that the things they discuss like monitoring and optimizing slow queries have always, until recently, been baseline competence.

It's only recently that the universal answer to something being too slow is to simply spin up another dozen, or heck make it two dozen, cloud instances to compensate.

> Even if their DB infra was only half as efficient as it is today, their costs would increase by a whopping $8000/yr.

I think the interesting comparison is not so much the same team doing things slightly less efficiently, but comparing to the average status quo in the industry.

So many companies are spending $100K+/month (month not year) on AWS costs to run very tiny minimal loads due to the attitude that efficiency never matters, just spin up more instances. Pretty soon you're talking real money. And by the time the CFO starts screaming, it's very difficult to go back to a more sane infrastructure.

> How many hours have developers spent on query monitoring

Install Percona Monitoring & Management. Clickhouse + Grafana all in one, dashboards ready to go, and free. Not hard.

> optimization, removing joins, removing aggregations?

How is this not part of the job already? Too many people seem to have it in their head that the database is an inconvenience that gets in the way of their application. It is the heart and soul of every application, because all applications boil down to CRUD. You should learn SQL, full stop. Relation algebra is not that difficult. The time people spending learning the newest stupid JS framework could instead be spent learning a language that hasn't changed in decades. It will serve you well.

These optimizations should be done regardless of db power. You don’t need an extra eng to do them, you just do them. They are part of the job.

> The annual cost of running such a machine on AWS (with SSD included) is ~$4000

Are you sure about this estimation? Because Digital Ocean offers 2vCPU + 8GB machines for $25 / month, which is more than 10 times cheaper.

... then they see the cloud bill.

Cloud companies found a way to directly monetize developers' love of complexity and overengineering. It's genius.

> "I'll just put everything in PostgreSQL and optimize later"

Sounds good to me!

A new business already has a laundry list of "I need to ${thing} to survive". Being able to move one such problem a couple of years in the future is already an attractive value before considering most businesses won't last that long anyways (for reasons unrelated to how optimized the database was). I mean if you already have the know how, confidence, and experience to do it right in the same amount of time then duh. If not, is spending precious extra time that early on really going to help you survive long enough to reach where how optimized you infrastructure spend is seems like an important problem for your cash flow?

Sometimes I think this then gets conflated with more established businesses where continuing to start every project so heavily this way does tend to create more problems than it solves.

> Anyone who says to me "I'll just put everything in PostgreSQL and optimize later" sounds just as naive as the person who says "let's build for scalability before we have any customers". Both are giving themselves problems they don't need

Both are giving themselves problems, but they are fundamentally different decisions. I will almost always take the "optimize later" approach as there's not always a "later".

For most new products, the biggest challenge is finding product market fit. Once you have that, the world is your oyster. I will happily pay a 10% tax after PMF to be 10% better at finding PMF now.

If I've found PMF, I will have more revenue, more traction, more budget, etc to fix the problems I created. If I don't have PMF, everything goes in the trash.

Especially in small/medium sized businesses, hardware is cheaper than payroll. It's most cost effective to pay people to build features (that drive revenue) while paying a bigger hardware bill than spend engineering hours optimizing hardware usage.

At some scale they'll be a break even/shift

I once ran the daily trade reconciliation of an entire US primary exchange on my laptop, using a PERL script (in 2007). I wrote it in a couple of days, and each day's processing ran in minutes. Ten years later, I witnessed a team spending weeks spinning up spark clusters to perform a job that was significantly smaller. At the time I thought I was the person who was missing some important context. Now I'm beginning to realise that I was not in fact misinformed or insane.

I don’t know if you’ve ever had to build an MVP at an early stage company

In my fuzzy sentimental memory, software bloat seemed to always self-regulate to 5-10 lines per second of logs or 2-3 screen flickers per minute, whichever applicable. Things go slower and optimizations come about, go faster and another Midleware.js comes to be.

My working theory is this is the common denominator max comfortable flickering rate for developers. There's got to be sweet spot for UI, how do I say, fluidity. And that fluid-ness usually aren't controlled for dev tools, tools themselves must evolve to stay at comfortable refresh-rate, by natural selection. That shouldn't be too weird of an idea.

> They think, it's fine to be inefficient, I'll just get a bigger X. And we are now blessed with technology and solutions that make that a possibility, when for the longest time it wasn't.

Unfortunately, the culture in my group is basically this. We never address things until they're dire inefficiencies; thus, I spend a nontrivial amount of my time addressing workloads that take 10+ hours but need to be faster (and often with minor changes are cut down to 2 hours).

Having a culture of "I'll just throw more hardware at it" / "If it takes an hour or ten, I still am going to run it and get the results tomorrow" is costing ridiculous amounts of money and lost time. It's such an uphill battle to try to change that culture.

> "Try using a server in your office to save on cloud bills"

and then company stops working when that server dies.

Maybe it means (trying to read charitably) that those are the methods, and if a suboptimal one of them (for a given query, schema, etc.) is chosen, then ...

Or it's just a continuation of the first point: any non-application join is suboptimal. (Erm..)

Edit: in fact, re-reading TFA, I think it is the latter or something like it - because the shift to application logic is mentioned as a 'countermeasure' for the 'suboptimal join methods'. I think it means deal with sometimes getting a bad plan by taking control and doing it yourself in the application. (I don't agree, just trying to understand.)

If you're joining in the application, why use a relational database?

It just means PostgreSQL can choose the wrong one for a specific instance, not that those are universally wrong.

"PostgreSQL chosing a suboptimal join methods (a 'join method' is Nested Join, Hash Join, Merge Join, etc., each of which is optimized for a specific situation) leads to unnecessarily high resource consumption.

"[And unlike virtually every other DBMS, PostgreSQL doesn't support specifying which method is used for a particular join. So even if it happen relatively infrequently, it's very difficult to correct a situation where it does choose the wrong one.]"

Sometimes it's better to do things in the application instead of caking it in an sql query.

An example would be a subquery that yields a large temp table, only to be pruned down to a single row.

Your choice is to either have understanding of the query optimizer and formulate a query that forces the subquery with a smaller temp table first, or you can bring it back to the application. Either approach works, but in once case you need to have deep-ish understanding of the optimizer and SQL, and in the other not so much (at the cost of an RTT.)

The article says "Shift logic to the application level to reduce database load."

(Emphasis mine.)

It may have been edited since you commented (perhaps because of your comment), but it seems like they aren't actually advocating to do joins at the application level.

Just to push against the crowd here pooh poohing client-side joins: this is a standard technique for scaling when you are stuck (for the time being) with a RDB and is definitely not in general "horrible". It does not mean pulling entire tables to the client then joining there. It means splitting a query that in effect walks down a chain of pointers (FKs) between tables into separate client-initiated queries. There are several benefits to this: 1. makes the design "nosql-ready" for when you design out the RDB. 2. Makes all DB queries inherently indexed and quick, allowing easy perf analysis (any non-quick query must be a bug) and alleviating connection pool starvation issues. 3. Prevents pesky devs from adding expensive queries under the radar. 4. allows more ready deployment of caching solutions. 5. recognizes that client resources scale with the number of clients, and are paid for by the client while RDB resources generally scale with difficulty and $$$, and are paid for by you.

You know, some of us are still alive today :)

Now that CTEs are no longer optimization fences in recent versions of Postgres, is there a way to somehow manually declare an optimization fence around a certain region of the query?

I've had similar problems in Snowflake with the query planner taking a bad path, and no obvious way to hint otherwise.

> The applied heuristic in this case apparently being "join tables by order of lower row count".

Not really - it's all cost based analysis. There are a few main culprits that might be at work:

1) Selectivity estimates can get very off if you have cross-column correlations or your joins are over multiple aggregations / DISTINCT or such.

2) Join order planning is exponential in the number of joins. Once there are more than geqo_threshold (default = 12) joins, we don't explore the whole space anymore.

3) If you have more than join_collapse_limit / from_collapse_limit joins, subqueries in the from list, we'll not flatten them anymore, to prevent the join ordering problem to become even harder. It might be worth trying to just increase them with your query.

Yes - I think the overall headline of this is correct (most people should just use PostgreSQL) but they should NOT go into five zoom levels of detail on optimization or anything other than really understanding and utilizing knowledge of how a relational database works (utilizing indexes etc.)

Nothing in the article was in the weeds, and you can do all of it in RDS as well if you’d like (pgstat is an available extension, I think enabled by default).

In RDS this would be possible via Parameter/Option groups

Some companies do large amounts of stored procedures for even routine tasks.

This is a common pattern with NoSQL databases, but really, the author of this article should just spend more than $10 a month on their Postgres instance.

How to operate on a tiny database server: shift all work from the database server to the application servers.

See also: webscale databases

The last place I worked had some JSON columns and doing lots of JSON operations tended to eat up all the Postgres CPU (the servers were generally 16-32 cores)

Easier to scale application CPU usage horizontally than a single instance DB.

This makes no sense and is typically the opposite of what I'd build. The more business logic (various validation rules and processing) you have in the database (via stored procedures) the less problems you have, not least because all that processing becomes ACID.

The reducing load on the database claim is nonsense since the application would have to query the database anyway and less efficiently that the database could, avoiding numerous round trips between application and database.

If you implement business logic in front end applications, you're asking for trouble. Many places I've worked didn't allow software outside the database to do anything more than call a stored procedure.

Application servers did come into fashion at one point, with predictable results: subtle timing bugs, inconsistent processing with different results depending on which application server processed the data, a general difficulty in finding the source of bugs, security problems.

Everyone would be a lot less stressed if they did everything in the database.

Here is a scenario that I've encountered a number of times.

We needed to serve up some data for an API endpoint. Great! We'll write a SQL query for it. Just need a couple of joins and where-clause parameters -- it will be speedy, and easy to maintain.

Before long, we are asked to make our query more robust, including a couple of optional parameters. Some null-coalescing in our join expressions and where-clauses is introduced. Performance immediately suffers. (I've seen the same kind of impact when introducing unions or function calls within such expressions -- the query plan can get very messy when you try to scale in this manner.)

So what do we do? Restructuring our data (maybe with a materialized view or something) is one option.

A reasonable alternative is to break up the single query into a few more specialized queries, and then use application logic to inspect parameters and choose the best query to use for each request.

Plenty of trade-offs to consider in both cases.

This meme is a bit too misleading.

In reality tons of startups have a shitty product with zillions of bugs because "move fast break things". Then later on they don't survive because the only reason people even pay for them is because the other startups are spending silly money on it. When the well dries up, it all comes crumbling down.

Turns out that startup's software is too shitty and simply not good enough to survive a cost cutting round.

People love to pretend that just adding more features always beats everything else, but it's not as clear cut as people make it seem.

> This gives me hope, my current idea I work on is osal planned for a two-core Postgres cloud instance from Hetzner.

Keep in mind that years ago having two cpus (single core cpus, with incredibly worse performance) was the pinnacle of technology, and still people managed to handle a decent amount of data.