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

Ways to shoot yourself in the foot with Postgres

philbooth.me

Post reply on HN
Print view View original

321–329 of 329 posts

Re: Ways to shoot yourself in the foot with Postgres

#321

Post by Calamitous »

lopatin wrote: post #174
Calamitous wrote: post #173

Most common one I’ve seen in the last 5-10 years: using a JSON column instead of putting in a lookup table, or instead of properly analyzing and normalizing your data. That’s a mistake that you’ll be paying for for a while.

How much slower is it in your experience?

It’s not even the speed, it’s writing queries. The syntax is clunkier, and since you don’t have a nice clean schema, you end up doing a lot of jumping through hoops to check for the presence of keys/proper data types, etc. Or doing your basic data validation in your app instead of letting the database do its job.

Re: Ways to shoot yourself in the foot with Postgres

#322

Post by klysm »

hot_gril wrote: post #297
klysm wrote: post #264

Earlier quoted context omitted.

> it has many caveats and shouldn't be used often I’d argue it isn’t used enough given its isolation advantages

Serializable mode is only useful in low throughput cases. You take a big performance hit and can easily have a bunch of queries waiting one each other until the queue fills up. I haven't used it in forever, I just make sure my stuff works with the default isolation mode. You also can't slap it on like a band-aid for unsure users. The DB client has to implement retry logic, which most Postgres libs don't seem to have,…

Yes you take the performance hit, but many workloads are low enough throughput for it to be completely fine.

I’m not sure how you could detect such anomalies though without running the same serialization checking logic within postgres

Re: Ways to shoot yourself in the foot with Postgres

#323

Post by aeyes »

klysm wrote: post #263
aeyes wrote: post #148

Earlier quoted context omitted.

This is ancient knowledge and I would have agreed with you 15 years ago, today the only reason to not use an ORM is analytical queries. Since the Postgres planner doesn't really allow you to tune your query there aren't many ways to construct your query in a way which would to a much worse execution plan. Over the years we have migrated most raw SQL back to using the ORM without taking performance hits, pretty much t…

Do CTEs actually force an order of execution? I thought the point was that they are declarative

Before Postgres 12 yes - always, starting with Postgres 12 you have to add "AS MATERIALIZED" to force the order.

Re: Ways to shoot yourself in the foot with Postgres

#324

Post by dx034 »

vikingerik wrote: post #157
sohnakukkar wrote: post #132

Earlier quoted context omitted.

How much SQL Server costs?

You can google it, but the short answer is Enterprise Edition is on the order of tens of thousands of dollars per CPU.

Maybe they'll change it at some point. I believe if SQL Server was free, it would dominate the business. I last used it 7+ years ago and back then it was (from my subjective experience) better than Postgres is now. And I love Postgres. But SQL Server was a dream to work with. Feature rich with amazing tooling. You can get most features in Postgres with plugins and manual work, but SQL Server does it all for you.

Re: Ways to shoot yourself in the foot with Postgres

#325

Post by glasshug »

jaggederest wrote: post #68
samokhvalov wrote: post #53

Earlier quoted context omitted.

What do you do if you need to check index ideas, or new table design?

Generally indexes are cheap, if built concurrently, so I often build all the possible indexes (for relatively modest sized data types, load, and tables) and look at the planner statistics in production to validate which indexes are useful for the query load. That only works if you have a read-heavy usage pattern, for write-heavy usage patterns it can sometimes be better not to have an index at all (one of the things…

Indexes can multiply your storage cost though.

Re: Ways to shoot yourself in the foot with Postgres

#326

Post by hot_gril »

klysm wrote: post #322
hot_gril wrote: post #297

Earlier quoted context omitted.

Serializable mode is only useful in low throughput cases. You take a big performance hit and can easily have a bunch of queries waiting one each other until the queue fills up. I haven't used it in forever, I just make sure my stuff works with the default isolation mode. You also can't slap it on like a band-aid for unsure users. The DB client has to implement retry logic, which most Postgres libs don't seem to have,…

Yes you take the performance hit, but many workloads are low enough throughput for it to be completely fine. I’m not sure how you could detect such anomalies though without running the same serialization checking logic within postgres

Actually I agree. There are probably lots of low-throughput, high-consequence things like that.

For the sampling, you'd have to run the same logic. The idea is just to do it infrequently as to not totally ruin your overall performance. Idk if this would work.

Re: Ways to shoot yourself in the foot with Postgres

#327

Post by hot_gril »

hot_gril wrote: post #238
rwmj wrote: post #129

The main tip I learned from using PostgreSQL (or relational databases in general) is never use an ORM . They cause far more trouble than they are worth and it's far easier to see what is going on when you're writing SQL queries directly.

It's not just the fact that it hides the queries. ORMs are all-around cancerous. I've been on several teams that's tried to use one, and there were regrets every time.

And by regrets I don't mean "this ORM wasn't worth," I mean "the ORM ruined everything to the point where we have to rewrite it."

Re: Ways to shoot yourself in the foot with Postgres

#328

Post by klysm »

hot_gril wrote: post #326
klysm wrote: post #322

Earlier quoted context omitted.

Yes you take the performance hit, but many workloads are low enough throughput for it to be completely fine. I’m not sure how you could detect such anomalies though without running the same serialization checking logic within postgres

Actually I agree. There are probably lots of low-throughput, high-consequence things like that. For the sampling, you'd have to run the same logic. The idea is just to do it infrequently as to not totally ruin your overall performance. Idk if this would work.

It’s definitely an interesting idea, I wonder if it’s easy to implement such a thing.

Re: Ways to shoot yourself in the foot with Postgres

#329

Post by j45 »

orthoxerox wrote: post #259
j45 wrote: post #258

Earlier quoted context omitted.

There are some good business process management tools that could manage that mapping instead of the clever SP, but you have me intrigued. Are there any links that could be helpful to work through the programs and caveats of such a SP? Part of it for me is creating a table that can store the process in the database to be able to traverse it reasonably.

It was basically a more complicated version of this: https://stackoverflow.com/questions/68809885/how-to-select-f...

Nice, thanks!
Post reply on HN