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

261–270 of 329 posts

Re: Ways to shoot yourself in the foot with Postgres

#261

Post by eitland »

steve-chavez wrote: post #252
rwmj wrote: post #135

Earlier quoted context omitted.

Kind of goes without saying that any framework/library you use must not allow you to write SQL injection vulnerabilities, and if it does you should stop using it right now.

SQL injection is always possible with an ORM, since they always allow executing raw SQL as an escape hatch.

True. But unlike with the alternative that many end on, raw sql, it doesn't funnel anyone into a place where writing

  "select * from sometable where id=" + id
feels like a logical next step unless you actually have studied the subject or read the manual ;-)

Re: Ways to shoot yourself in the foot with Postgres

#262

Post by klysm »

Someone wrote: post #222
klysm wrote: post #186

Earlier quoted context omitted.

I know nothing about partial indices in Postgres, but it seems like for indexing a Boolean, you either index the true or false values right? I feel like Postgres could intelligently choose to pick the less frequent value

Is that correct? I would think that, even with NOT NULL Boolean field, the physical table has three kinds of rows: those with a true value, those with a false value, and those no longer in the table (with either true or false , but that doesn’t matter) If so, you can’t, in general, efficiently find the false rows if you know which rows have true or vice versa. You also can only use an index on rows with true values t…

Yeah that seems more like how it would work, I’m curious about the internals there

Re: Ways to shoot yourself in the foot with Postgres

#263

Post by klysm »

aeyes wrote: post #148
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.

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

Re: Ways to shoot yourself in the foot with Postgres

#264

Post by klysm »

hot_gril wrote: post #241

The two biggest ones imo were not mentioned: 1. Contrary to popular belief, Postgres isn't fully ACID (specifically the "I") with the default isolation mode. For example, selecting the sum of a column then inserting conditionally on that creates a race condition. Serializable mode is fully isolated, but it has many caveats and shouldn't be used often, so you should instead become familiar with what's isolated and wha…

> it has many caveats and shouldn't be used often

I’d argue it isn’t used enough given its isolation advantages

Re: Ways to shoot yourself in the foot with Postgres

#267

Post by beckler »

RowanH wrote: post #70
ak39 wrote: post #26

"2. Push all your application logic into Postgres functions and procedures" Why are functions and procedures (an abstraction layer at db layer) considered harmful to performance when the same abstraction layer will be required at the application layer (introducing out of process overhead and possibly network traffic)? I don't agree with this advice. (Or I don't understand it.)

Worst mistake I've ever made was implementing logic in the db - made for horrible debugging. It was only a few small bits of logic, but man, the amount of gotchas years later not realising something was there.. certainly I think you either have to all/most of your logic in the DB or none. Definitely not a sprinkling..

I once worked for a place years ago that had these crazy data integrity bugs. There were about 35 different bugs, and we suspected that most of them were pieces of legacy code not cleaned up from acquisitions (it was a healthcare company).

Anyways, if you were able to solve one, you got a $5k bonus and a two week vacation. While I was there, someone figured one of them out, and it was due to logic in the DB. ...but I have a feeling that all of them were due to logic in the DB.

Post reply on HN