Hacker News Reader

Comment by traceroute66 | Hacker News Reader

Postgres isn't slow, your schema and queries are.

There. Fixed it for you.

Too many times I see people making the same avoidable mistakes time and time again.

They get stuck in the dream of "portable" schemas and don't use their brain to design a database-specific schema.

They have no clue how to write SQL queries (or again, have stupid dreams of "portable" queries). As a result performance suffers.

Then they come to your desk complaining the databse is slow, and insisting that they absolutely MUST have a Redis caching layer.

I'm sick and tired of people using databases as blackbox dumping grounds. If slow storage was always the problem, my life would be far less stressful. Since the prescription would be easier for the doctor to write. ;)

View on HN

You can spend a week crafting the most optimal query possible, making sure the plans are perfect and all the best index types are used

And then you push it to production and get completely different results

Statistics change everything. Queries which produced beautiful plans in local and staging environments breakdown when given production statistics, some users get the same plans you did, others get nasty nested loops, indexes don't get used, and debugging and fixing it requires poking around in prod statistics which is never a good place to be. Don't even get me started on how RLS impacts query optimisation

Postgres is great but the authors refusal to add plan hints is a decision that comes to bite me at least a few times a year

Mostly agree although you should probably spend a bit more time testing on a more realistic setup.

At least run some plans on the real database with real sized data and real statistics.

Similar to how mocks/stubs are great until they aren't.

That won't magic away statistics problems, but it's still good practice to acknowledge the lower app environments tend to not reflect performance of the production one.

Yeah, a lot of people can't figure out how to structure databases or what indices to add to them, but no, that is not the only problem just because it's the one you deal with.

> that is not the only problem just because it's the one you deal with

Sure. But to be clear: I am not denying storage is a genuine problem for some people.

I am just saying in the majority of cases it's probably not "the storage". For most people "the storage" will be quite far down the list of likely causes of a "slow" database.

Throw all the Enterprise SSDs or other storage solutions you like at a shitty schema and it will still struggle under load (or sometimes just struggle full stop).

I think we agree.

A good schema and good queries won't save you if your storage is physically far enough away :P

Good storage won't save you if your schema and queries are bad enough, either.

Which postgres specific schema/query features do you use to significantly improve performance?

> Which postgres specific schema/query features do you use to significantly improve performance?

Erm. Isn't this kind of a schema specific question ?

I mean, sometimes I see schemas that are so goddam awful that the answer to your question would be "anything, literally anything".

Other times its the old story of failing to use or misconfiguring indexes.

And then, yes you end up in the realms of Postgres specific stuff. What you do there clearly depends on what you're looking at.

But the point is, I could probably count on one hand the number of times the problem has been "the storage" or "the database". Most of the time its "the developer". :D

The parent post emphasizes how portable schemas or queries cost performance. Now I understand important parts of the schema are postgres specific (e.g. the configuration of non-trivial indexes), but I think most of my queries happen to be portable. So I wondered where people sacrifice performance in the name of portability, and why that's so common that it's worth calling out.

Column Tetris is one example: https://stackoverflow.com/questions/2966524/calculating-and-....

Optimizing column layout has been enough for me to leave OLAP workloads in Postgres a few times when I otherwise would've had to spin up something dedicated for them.

Sit down, think about the queries you are using, and think about how the tables are updated. Now take that and rethink your indexes. Use the right index type for the right situation, don’t just slap a basic index on a serial id field and call it a day. And while you’re at it, think about the ID fields you use, sometimes better options exist. UUIDv7 is now built in, use it.

Lots of things in PGSQL benefit from thinking about how your use case is manipulating the underlying B-tree, if you work in performance sensitive stuff the devil is really in the details. And having to rebuild indexes that have decayed over time is painful.

Having indexes fitting the query is of course important, but it's not postgres specific.

PGHero an pgtuner are two of our favorite tools to use when optimizing PGSQL

Reply on news.ycombinator.com