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

101–110 of 329 posts

Re: Ways to shoot yourself in the foot with Postgres

#101

Post by oofnik »

At my first DevOps job we had defined a function which deleted old partitions from time to time. If you invoked it manually because of some automation failure with the wrong arguments, it had a habit of nuking production data, which, needless to say, happened at least once. Naturally, the function was called footgun.

We can pretend that the proliferation of managed databases, newfangled NoSQL datastores, and other abstractions preclude the need for accumulated empirical observation across a range of tech stacks and time, but sometimes there's really no substitute for greybeard wisdom.

Re: Ways to shoot yourself in the foot with Postgres

#102

Post by SigmundA »

albertopv wrote: post #24

Two years ago I moved to a new company using Postgres as THE relational db, coming from years of Sql Server I found poor query plan issues troubleshooting tools. Anyway, I don't know if it's the same in Postgres, but in Sql Server an OR condition like that could kill your performance quite easily in a relatively complex query, often I had to refactor my query to a UNION (usually with ALL to avoid a distinct step, but…

Compared to Sql server, PG's lack of true clustered indexes and no query plan re-use was very surprising, also no hints to wrangle a bad query plan!

Re: Ways to shoot yourself in the foot with Postgres

#103

Post by PeterisP »

damagednoob wrote: post #27
natmaka wrote: post #6

Pertinent: https://wiki.postgresql.org/wiki/Don%27t_Do_This

There's something ironic about having so many features that you have a dedicated page telling users which ones not to use.

I think that every platform that's old and has backwards compatibility has to have such a page - because there inevitably will be features for which we (now!) know that there are better ways to achieve the same goal, but they have to stay there for compatibility reasons.

Re: Ways to shoot yourself in the foot with Postgres

#104

Post by Diggsey »

moring wrote: post #4

> 9: Compare indexed columns with IS NOT DISTINCT FROM Does anybody know why this is the case? Usually, an index is not used if the semantics of the index do not match the semantics of the query, so "using" it cannot ever produce correct results. But the workaround presented seems to have identical semantics to IS DISTINCT FROM and still uses the index, so why isn't IS DISTINCT FROM using the index then?

I asked this specific question before on the PostgreSQL IRC, and was told that it simply wasn't implemented. There's no huge technical blocker to it being done, it's just a bit awkward to make it work the way the code is structured AIUI.

Re: Ways to shoot yourself in the foot with Postgres

#105

Post by moring »

h1fra wrote: post #89

Few tips I gathered along the years: - Configure Vacuum and maintenance_work_mem regularly if your DB size increases, if you allocate too much or too often it can clog up your memory. - If you plan on deleting more than a 10000 rows regularly, maybe you should look at partition, it's surprisingly very slow to delete that "much" data. And even more with foreign key. - Index on Boolean is useless, it's an easy mistake…

> Index on Boolean is useless, it's an easy mistake that will take memory and space disk for nothing.

Why? Is it because an index on the bool alone, with symmetric distribution, will still leave you with half the table to scan? In other words, does that statement apply to biased distribution (as mentioned by another response) or indices on multiple fields of which one is a boolean?

Re: Ways to shoot yourself in the foot with Postgres

#106

Post by klysm »

fastball wrote: post #79
it_citizen wrote: post #25

First way to shoot myself in the foot: not using it. Too often, I ruled out Postgres as a solution to a certain problem before even trying and jumped to more specialized solutions or moved the problem to the application layer. It took me years to stop underestimating what this awesome software can do.

I am of the firm opinion that Postgres + Redis are basically the only DBs you ever need.

What do you use redis for?

Re: Ways to shoot yourself in the foot with Postgres

#108

Post by klysm »

funcDropShadow wrote: post #66
turtles3 wrote: post #51

Earlier quoted context omitted.

This may be irrational but it's something that worries me about using postgres in production. Sure as a developer I love all the features, but the fact that the query planner can suddenly decide on a radically different (and incredibly inefficient) query plan makes it hard to trust. In some ways, a dumber query planner that needs coercing into the right query plan is more reassuring, in that you know it'll keep using…

But that dumber query planner will bite you when your data changes. If the relative size of multiple tables change the query might have to change to be still efficient. Postgres query planner handles that just fine. I've used Postgres for years multi TB databases and I've experienced a problem with Postgres suddenly changing plans.

Is there a “never” missing from your last sentence?

Re: Ways to shoot yourself in the foot with Postgres

#109

Post by elp »

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

While I agree with all the other commenters about debugging and scaling issues at least some of the time the stored procedure route can be very powerful.

Stored procedures will eliminate insane levels of latency if there are many records to be updated in ways that are hard to do in application layer code without repeated calls the the db. I use them a lot for DB maintenance. Often for that kind of work its also a lot simpler and easier to reason with than app layer code.

Re: Ways to shoot yourself in the foot with Postgres

#110

Post by duckmysick »

hannofcart wrote: post #80
baq wrote: post #39

I’d add ’not reading the table of contents of the manual’ to the list. I’ve probably worked with hundreds of people now who use a database daily either in code or just to explore data and can count on two hands (optimistically…) the number of folks who actually read the fine manual in any other way than googling something specific. Pro tip: read it so you know what to google for!

Googling? That's so passe. I just enter my vague question into this AI chat thingy and I try the first thing that it tells me on my production server. Has worked fine for me so far. What could possibly go wrong?

You can ask this AI chat thingy if it's a good idea to experiment on your production server. Maybe it will suggest something else.
Post reply on HN