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

21–30 of 329 posts

Re: Ways to shoot yourself in the foot with Postgres

#21

Post by hans_castorp »

jasfi wrote: post #16

PL/pgSQL needs some styling improvements: 1. Get a better name, PL/pgSQL doesn't exactly roll off the tongue. 2. Get rid of those $$ at the start and end of any PL/pgSQL, it's just verbose and ugly.

> Get rid of those $$ at the start and end of any PL/pgSQL, it's just verbose and ugly.

The Postgres parser itself doesn't know the language rules and thus can not parse the source code. The body of the function/procedure is passed to the language handler as a string without Postgres looking at it. The procedural code (PL/pgSQL, PL/python, PL/perl, plv8, ...) will not be parsed until the function/procedure is actually executed.

Today there might be better ways to implement such a dynamic system to register new languages, but I guess it will be huge effort to change this to a way that would understand the old and the new syntax.

Since Postgres 14, at least "LANGUAGE SQL" functions/procedures can be written without using dollar quoting if the SQL/PSM syntax is used.

Re: Ways to shoot yourself in the foot with Postgres

#22

Post by hans_castorp »

hrdwdmrbl wrote: post #12

I’ve even found “Is not null” queries on a Boolean column to be slow so I made a generated column that’s Boolean.

So you added a generated boolean column that uses the value from a another boolean column? Sounds like a strange concept.

Did you try a filtered index using WHERE ... IS NOT NULL instead?

Or maybe an index on an expression coalesce(the_column, false) and then use that expression the where clause?

Re: Ways to shoot yourself in the foot with Postgres

#23

Post by moring »

hans_castorp wrote: post #18
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?

> Does anybody know why this is the case? Most of the time the answer to that is: because nobody cared enough or had time enough to implement it

That might be the case, but my experience with databases (and especially PostgreSQL) is that most of the time I actually misunderstood the exact semantics of either the operation or the index. That would be a good chance to learn something :)

Re: Ways to shoot yourself in the foot with Postgres

#24

Post by albertopv »

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 it depends on the specific case).

Re: Ways to shoot yourself in the foot with Postgres

#25

Post by it_citizen »

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.

Re: Ways to shoot yourself in the foot with Postgres

#26

Post by ak39 »

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

Re: Ways to shoot yourself in the foot with Postgres

#28

Post by maxloh »

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

It is always more easier to scale horizontally at application layer (just adding more servers) than at database layer (which involves syncing data between multiple database instances).

Re: Ways to shoot yourself in the foot with Postgres

#29

Post by postdb »

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

This comes a lot from people who want to "horizontal" scaling. The camp that thinks everything should be in the middle tier (Java/C#/). Also cost on AWS is cheap for those, and expensive for RDS. In the end db will be bottle neck. Of course DevOp ppl will can also create cache layer etc to lessen the stress to the db.

Re: Ways to shoot yourself in the foot with Postgres

#30

Post by ciberado »

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

I've been out of the trenches for some time, but when I participated in projects that relied on heavily in store procedures, we felt constrained in terms of flexibility (the language options were very restricted, and we were not able to use common libraries), the tooling (the support in the IDE was not great, neither it was straightforward to debug the code) and the scalability (vertical, instead of horizontal). Also, this approach introduced a heavy coupling.

It is true that we were much more familiar with application layer technologies, but the lack of expertise can also be considered a restriction, I think.

Post reply on HN