Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Making Postgres slower

byteofdev.com

Post reply on HN
Print view View original

31–40 of 43 posts

Re: Making Postgres slower

#31

Post by guiriduro »

sschnei8 wrote: post #14
gerdesj wrote: post #9

I think this is absolute genius. If you are ever going to get to grips with optimizing something, why not do the opposite first or as a foil? How often do you really fenangle your database (or other system) properly? Are your performance enhancements really based on science or cargo culting or something else?

I always do that when working with a new cloud provider. I spend our Series A as fast as possible after which we can optimize cloud spend!

Yup that's a variation of the general 'deliver a hugely inefficient solution first, then iterate' tactic. Keeps multiple people employed much longer than a little (debatably) "premature" optimisation ahead of time, keep delivering benefits QoQ (that could have been delivered sooner, but whatever...), keep managers happy.

Re: Making Postgres slower

#32

Post by jiggawatts »

snickerdoodle12 wrote: post #4

Excellent. Now do 42,000x faster please.

My record for DB query performance improvement is 13,000:1 and I was called a liar and a cheat to my face and then the customer walked out of the meeting.

I think if someone achieved 42,000:1 they'd be accused of sorcery and burned at the stake.

Re: Making Postgres slower

#33

Post by falconertc »

Great read, and an interesting warning sign about overly-controllable configuration files. If there's no conceivable good reason to to much of this, then the ability to do so becomes bad design that fails to protect your users.

Re: Making Postgres slower

#34

Post by wonghammer »

adhamsalama wrote: post #17
vichyvich wrote: post #6

I love this! It would be great to have follow-ups and series of books about how to make things worse, as a way to learn to make things better. Maybe it could be like O’Reilly, except the covers could have shittily-drawn fantasy animals, e.g. a 7-year-old’s drawing of a unicorn with a head on each side of its body both talking on their AirPods giving away their money to scammers, making PowerPoint slides, eating too m…

https://orlybooks.com/

[dead]

Re: Making Postgres slower

#35

Post by rom16384 »

falconertc wrote: post #33

Great read, and an interesting warning sign about overly-controllable configuration files. If there's no conceivable good reason to to much of this, then the ability to do so becomes bad design that fails to protect your users.

The Postgres defaults in debian are understandably conservative to make it run everywhere. When I first installed Postgres I found it rather slow even on a good server. Only after reading guides on the web I found out why, the default RAM usage is limited to something like 32 MB which is way too low.

Re: Making Postgres slower

#36

Post by gregmac »

KronisLV wrote: post #30

I really like the idea of finding what dials can be turned and then doing so and seeing how much headroom you normally have and how far away from things breaking you are. Plus, those sorts of artificial limitations can be really helpful in finding where your systems bottleneck and what are the main optimizations that you should do (for example, I've seen N+1 problem sneak past because at the time the performance was…

> for example, I've seen N+1 problem sneak past because at the time the performance was good enough but once there was enough data it crumbled

This is a super-common problem I've had to help with many times. It generally happens with teams working on single-tenant (or single-database-tenant) products, and basically always comes back to the dev team working on a database with hundreds of things, and there's a handful of customers using 10,000+ things when it starts getting slow.

You acquire a bunch of small customers and everything is great, and it can months later before you really start to see performance impact. And often with these types of problems it just gradually slows down until finally the system hits a wall where the DB can no longer compensate and suddenly performance tanks. "It must be caused by something we just added, but it's weird, because this problem is in a totally different part of the system."

Re: Making Postgres slower

#37

Post by [deleted] »

don-code wrote: post #11
vichyvich wrote: post #6

I love this! It would be great to have follow-ups and series of books about how to make things worse, as a way to learn to make things better. Maybe it could be like O’Reilly, except the covers could have shittily-drawn fantasy animals, e.g. a 7-year-old’s drawing of a unicorn with a head on each side of its body both talking on their AirPods giving away their money to scammers, making PowerPoint slides, eating too m…

This strategy was actually used during World War II, to ensure pilots could come home safely. Weather forecasting not being what it is today, meteorologists determined what conditions would result in the _most_ lives being lost, then together with mission commanders "designed" missions to simply not meet those conditions. Source: https://medium.com/butwhatfor/suppose-i-wanted-to-kill-a-lot...

[deleted]

Re: Making Postgres slower

#39

Post by nicoburns »

gregmac wrote: post #36
KronisLV wrote: post #30

I really like the idea of finding what dials can be turned and then doing so and seeing how much headroom you normally have and how far away from things breaking you are. Plus, those sorts of artificial limitations can be really helpful in finding where your systems bottleneck and what are the main optimizations that you should do (for example, I've seen N+1 problem sneak past because at the time the performance was…

> for example, I've seen N+1 problem sneak past because at the time the performance was good enough but once there was enough data it crumbled This is a super-common problem I've had to help with many times. It generally happens with teams working on single-tenant (or single-database-tenant) products, and basically always comes back to the dev team working on a database with hundreds of things, and there's a handful…

It's one of the things where I've found code review to be the most helpful remedy. If everything gets reviewed then N+1 queries can be rejected before they even hit the main branch. They're usually pretty obvious if you're looking for them.

Re: Making Postgres slower

#40

Post by rompic »

jiggawatts wrote: post #32
snickerdoodle12 wrote: post #4

Excellent. Now do 42,000x faster please.

My record for DB query performance improvement is 13,000:1 and I was called a liar and a cheat to my face and then the customer walked out of the meeting. I think if someone achieved 42,000:1 they'd be accused of sorcery and burned at the stake.

My best was 5000:1 by updating the outdated SQLite in one of the companies' products.

I found that we were hit by a bug that was fixed 6 years before I discovered it (https://sqlite.org/src/info/6f2222d550f5b0ee7ed). Sqlite's query planner assumed that a field with a not null constraint can never be null, which isn't the case for the right hand table in a left join.

Post reply on HN