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

241–250 of 329 posts

Re: Ways to shoot yourself in the foot with Postgres

#241

Post by hot_gril »

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 what isn't. See https://www.postgresql.org/docs/current/transaction-iso.html

2. timestamp (without time zone) is bad; always use timestamptz, no exceptions. Unintuitively, timestamp (without time zone) is the one that makes your DB's time zone affect your selected data. Neither one actually stores a time zone, it's just a difference in output formatting. This is a moot point if your DB's locale is set to UTC, but that's not the default.

Re: Ways to shoot yourself in the foot with Postgres

#242

Post by lswainemoore »

Whitespace wrote: post #124
jaggederest wrote: post #19

Sometimes you must `EXPLAIN ANALYZE` expensive queries in production, sadly. The behavior of postgres (even on non-bitwise copies) can be different under load. The biggest way I have seen this be true is with fragmented tables/indexes - same data but organized more compactly can change planner behavior. Article actually touches on another way that can be true - if your `work_mem` is drastically different the planner…

The article and the comments here don't make it clear why running it in production shouldn't be done. If slow_query is already running in production, why would running EXPLAIN ANALYZE slow_query be bad? Is the overhead of running EXPLAIN ANALYZE so much worse than running slow_query itself?

One reason you might not want to run it in production is if it's not a read-only query.

Re: Ways to shoot yourself in the foot with Postgres

#243

Post by MrPowerGamerBR »

philsnow wrote: post #208
MrPowerGamerBR wrote: post #141

Earlier quoted context omitted.

If you are using NOTIFY/LISTEN, keep track to check if your database does not have any long running queries. If you end up getting PostgreSQL to vacuum freeze your tables while the long running query is active, PostgreSQL will delete files from the pg_xact folder and that will bork out any LISTEN query, until you fully restart the database.

> PostgreSQL will delete files from the pg_xact folder and that will bork out any LISTEN query, until you fully restart the database that sounds like a bug; are you aware of an issue/ticket tracking that?

While I haven't found an "issue" talking about this (sorry, I don't know how PostgreSQL tracks open bugs), they do know about it since I already seen that issue being talked about on PostgreSQL's mailing list.

Here's my issue on StackExchange, for anyone that wants to delve deeper into my issue: https://dba.stackexchange.com/questions/325104/error-could-n...

Here's an thread talking about the issue, while OP's issue doesn't seem to match exactly what I was experiencing, one of the replies describes my exact issue: https://postgrespro.com/list/thread-id/2546853

Re: Ways to shoot yourself in the foot with Postgres

#244

Post by philbo »

philsnow wrote: post #210

A writing tip: even in lists of "don't"s like this, find a way to write directives/imperatives in a positive sense. Asking readers to keep mentally flipping the sense of the thing you're telling them to do just adds cognitive load and makes it harder for them to pay attention to what you want them to pay attention to. Write "do"s, not "don't"s.

Yeah, a lot of people have said this. I was wary of asserting "do's" because I'm not a Postgres expert. It felt more honest to phrase stuff in terms of my own mistakes and "don't do what I did", but of course that's confusing. And then I ruined it by still asserting "do's" in the bodies of some sections.

Re: Ways to shoot yourself in the foot with Postgres

#245

Post by philbo »

afhammad wrote: post #74

From my understanding, `work_mem` is the maximum available memory per operation and not just per connection. If you have a stored procedure with loops and/or many nested operations, that can quickly get quite big. One trick worth noting, is that you can override the working memory at the transaction level. If you have a query you know needs more memory (e.g doing a distinct or plain sorting on a large table), within…

This is a great tip, I had no idea there was `set local work_mem`. Thanks!

Re: Ways to shoot yourself in the foot with Postgres

#246

Post by philbo »

xnickb wrote: post #163

The "solution" for the number 4 is a pretty bad footgun in itself, as it is obviously prone to race conditions. Didn't read further.

Yep, a few people have made the same comment. I'll make a correction to the post soon, sorry that it upset you. As obvious as it seemed, I had no idea.

Thanks for pointing it out though, feedback from experts is the big payoff to posting from my p.o.v. and now I know a thing which I didn't know before.

Re: Ways to shoot yourself in the foot with Postgres

#247

Post by hot_gril »

philbo wrote: post #244
philsnow wrote: post #210

A writing tip: even in lists of "don't"s like this, find a way to write directives/imperatives in a positive sense. Asking readers to keep mentally flipping the sense of the thing you're telling them to do just adds cognitive load and makes it harder for them to pay attention to what you want them to pay attention to. Write "do"s, not "don't"s.

Yeah, a lot of people have said this. I was wary of asserting "do's" because I'm not a Postgres expert. It felt more honest to phrase stuff in terms of my own mistakes and "don't do what I did", but of course that's confusing. And then I ruined it by still asserting "do's" in the bodies of some sections.

I think the "don't"s are fine, but each bullet should say "don't" in that case.

Re: Ways to shoot yourself in the foot with Postgres

#248

Post by hot_gril »

mbork_pl wrote: post #59
ainar-g wrote: post #54

Earlier quoted context omitted.

Can I ask why? I generally only see the “I switched from MySQL to PostgreSQL and loving it” comments in my info-bubble, so it'd be interesting to know what people who prefer to use MySQL feel is still lacking in PostgreSQL.

Not the parent, but I have a friend who knows both Postgres and MySQL and prefers MySQL because "it is simpler and hence there are fewer ways things can get broken". (Not that I necessarily agree, but he seems to have a point.)

How is MySQL simpler? I'm a big fan of simplicity even at the cost of features, but they seem like the same level of complexity.

Re: Ways to shoot yourself in the foot with Postgres

#249

Post by hot_gril »

call_me_g0d wrote: post #20

I could never warm up to PostgresSQL. Guess I'll always stick with MySQL

This shouldn't be downvoted. If someone can give you a particular reason to use Postgres, maybe this is the place. Personally I enjoy Postgres the most after having used MySQL before, but it's only for little reasons like the EXPLAIN output being nicer.

Re: Ways to shoot yourself in the foot with Postgres

#250

Post by johnthescott »

jaggederest wrote: post #213
thomasjudge wrote: post #152

Earlier quoted context omitted.

Sybase, actually

The first Sybase grew out of the Ingres project at UCB, so transitively, SQL Server is also a descendant.

bob epstein was vp at brittion-lee when he left to form sybase. BLI built a relational database machine (IDM), which was influenced by ingres but not much was inherited, code wise. sybase used a VM/pcode architecture, very much not like ingres.

https://www.google.com/search?q=britton+lee+inc+wikipedia

Post reply on HN