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

▲

s08148692 34 minutes ago | parent | next [-]

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

▲

smolder an hour ago | parent | prev | next [-]

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.

	▲	traceroute66 an hour ago \| parent [-]
		> 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).

▲

CodesInChaos 2 hours ago | parent | prev | next [-]

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

	▲	smitty1110 an hour ago \| parent \| next [-]
		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.
	▲	FreakLegion an hour ago \| parent \| prev \| next [-]
		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.
	▲	traceroute66 an hour ago \| parent \| prev [-]
		> 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

▲

boxed 43 minutes ago | parent | prev [-]

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