Hacker News Reader

> 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.

Carefully designing your application around an underpowered database is almost surely more complicated than just paying for a more powerful database.

We're not talking about K8s or sharding. We're talking about adding more cores and RAM to the single database node that they're already using.

Imagine how much easier the backend dev's life would be if they could just write SELECT FROM JOIN WHERE like everyone else, and not have to pore over query plans and design/implement/test a bunch of custom joining routines. This path sounds like a high-effort artisanal approach to something where it doesn't seem warranted. It's penny-wise and pound-foolish in exactly the way you (very nicely) described.

It's true, but its great experience for future things when the costs actually matter - I cut my teeth at a biz that refused to pay more for database hardware and now my performance skills pay the bills at much nicer companies.

Surely it's great for you as the developer to get paid extra to learn transferable skills.

That's not necessarily true of the business.

Absolutely, I did not feel it as a positive at the time and recommended not that much more money for significantly better utilization of human time, but you take what you can get with intractable CEOs.

Imagine how much easier the frontend dev's life would be if they could just demand whatever horrifying schema they want, and it would be delivered to them. Oh wait, that's GraphQL, and it worked as well as it sounds.

I am begging people to learn SQL; it is not a hard language, and it hasn't changed in decades.

any good book ?

I bought https://theartofpostgresql.com/ few years a go. It goes more into intermediate/advanced category and I have been quite happy with it.

I was fortunate to have a Database Basics course in the university that was 80% database schema design and 20% SQL queries and indexes. Reasoning for such a split was that fixing a bad database schema is much harder than fixing bad queries.

thanks too

For beginner learning? https://sqlbolt.com/ is a decent place to start.

Intermediate and advanced too, if you can, that would be lovely.

https://modern-sql.com/ has some info about more recent additions to SQL to make life easier/write higher level or more composable queries.

Once you have the basics of SQL down (like how to write a query), you want to understand what the database actually does with your queries, so you can predict what the query plan "ought to be"/what you want, and you can read the query plans to try to understand why you did or didn't get the plan you expect.

https://use-the-index-luke.com/

And then once you understand the fundamentals, your database's manual is the place to go. Especially different index types (including their internals!) and what kinds of queries they support. The above link goes into depth about b-trees, but there are other types, and you can use b-trees as a reference point to contrast with others.

https://www.postgresql.org/docs/current/index.html

thank's a lot

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).

> 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.

Indeed! One place I worked at, there was something like 10x (forget exactly) gains to be had simply by editing the MySQL config to match the workload. Seems like nobody bothers anymore. There is so much money to be saved by reducing excess operating costs.

Yep. Also, this is what I specialize in if anyone wants a senior engineer that knows operating systems and databases! ^_^

> Performance optimization has become a lost art.

It's really not very hard, basically add indexes wherever lookups are done but don't go overboard, especially with tables that see a lot of inserts. The DB will tell you what it's doing if you ask, it's all easily investigated. If your joins resist optimisation your data structure is probably off.

> I won't even touch on proper design, which requires someone with significant database expertise from the get-go

In more extreme cases this is true, but a few basic principles and an understanding of databases will get you 95% of the way there and is quite intuitive. Something as basic as "don't copy data around, each individual entity exists in one place, and use its key to refer to it otherwise" will help enormously.

> The DB will tell you what it's doing if you ask, it's all easily investigated.

It will tell you _what_ it's doing, but not _why_ it's doing it. Unraveling the mysteries of the query planner is a dark art that is still firmly in the realm of graybeards.

Exactly and that's where it gets hairy and you need deep expertise.

> It's really not very hard, basically add indexes

It's really not but you'd be surprised how many people don't know the basics of how indexes work. I've had people tell me that their queries couldn't be optimized further because "they had already added an index for every column of the table".

It doesn't help that SQL, by design, hides the actual algorithm doing the data access from the users while simultaneously relying on them to add indexes to achieve performance, which is in my humble opinion the worst mistake of SQL.

That is not a SQL mistake, but an implementation choice. Most modern databases can determine where indexes should be added, and add these automatically. E.g.: https://www.oracle.com/news/connect/oracle-database-automati...

This is news to me. Can any other DBs do this?

(I only skimmed -- far too much "Joan explains that" to signal ratio.)

SQL server: https://learn.microsoft.com/en-us/sql/relational-databases/a...

Interesting read on a project implementing this for Postgres: https://pganalyze.com/blog/automatic-indexing-system-postgre...

Not for postgres or mysql.

I love to ask this very simple question in interviews: if a database index makes queries faster, why not add an index for every column on a table?

I want them to say “the heck are you talking about, because that’s not how anything works?”… it does trip some people up though that just legitimately don’t understand what’s happening.

As a former DBA and optimization expert, the more appropriate response is NOT "add more indexes", but rather to profile and analyze your query plans first, then make decisions off those; e.g. a long running query might be causing deadlocks, another query can be optimize with different joins or ctes, etc.

Adding indexes can increase I/O time (more writes), increase DB file sizes (more stuff to store) etc, which are not inconsequential. Sometimes it's also as simple as moving indexes to a tablespace stored on an SSD.

Rounding out the end of 2023 where a 15 TB pcie gen 4 drive is $1800, it's hard for me to accept the fact that SSD storage for databases (or at least the indexes) isn't just a given.

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...

Stack Overflow is the poster child of over-optimizing DB engineering while neglecting every other product feature that end users actually want, so not sure if that should be your example.

Somehow they managed to become one of the most important websites for software developers despite neglecting every product feature you miss.

They have never made a dollar of profit in their 15 years of operation and just sold themselves to a private equity firm for pennies. "Most important website for software developers" doesn't really mean anything unless one can also be a sustainable company in the process.

Failure to monetize is usually the exact opposite of failure to please users.

Was it because of "neglecting every other product feature that end users actually want"?

There is a case to be made they took it to an extreme, but there is too much going in the other direction as well. It's not like most teams are working on the meat of the product anyway - they probably spend 40% of their time running a small project on Kubernetes and getting rid of daily security warnings from NPM.

Have you seen their code base? They did not over optimize the DB engineering by any means that I could tell.

Having a simple monolithic database is not optimizing your DB engineering by any means.

edit: and inventing a better ORM doesn't count either.

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.

I've always thought there should be a meta "AWS Calculator" Calculator tool, which takes the output from the official AWS calculator and applies an algorithm to make it more realistic to factor in the things people forget about (mostly data transfer)

I/O, backups, and such. Just having the instances cost money. Using them costs even more!

And egress at AWS.

Do you expect a database to do much egress?

Depends on the queries and the application.

Even just the case of sitemap access alone can very well read half a database's tables and many of each table's columns in the act. That being done dozens of times a week between various search vendors and yeah your egress from just SEO can exceed the size of your database alone. Just depends on the nature of your data.

Then consider paging views, infinite scroll.

Then toss in something like Algolia or other third-party tools ingesting your data.

But it really boils down to your application. You might have NONE of these and egress is just crumbs.

Egress is defined as outbound traffic to the public internet.

Unless your database is publicly accessible (which it really should not be), its egress should be zero. What your /application/ returns to the public internet, is a different conversation.

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.

> 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 much would they pay an average SRE to deal with random fires operating a poorly designed, poorly instrumented and poorly understood system?

This. Optimized system means the devs took the time to understand the system, know the (most frequent) usage patterns and will not be nagged by 'something is slow' problems every so often. Of course, as with everything, there is a danger of going overboard. (And industry is firmly headed in the 'just throw more people/hardware/AI at the problem' direction anyway).

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.)

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.

Link? ta

> 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.

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

> 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.

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.