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

61–70 of 329 posts

Re: Ways to shoot yourself in the foot with Postgres

#61

Post by vbilopav »

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 don't understand it either.

Author seems to be arguing against long functions/procedures. But if you move that to the client, presumably with ORM support - you're going to be executing more or less the same sequence of SQL queries and commands. Only difference is that when doing it on client you will have a lot of latency.

Yes, you can cache some data in between those commands to avoid same multiple queries, but if you use temp tables to do so, they will use memory only if it is available, otherwise you are limited with the actual storage size.

Only time I had memory issues with PostgreSQL when I used too much data in arrays and json's. Those are memory only variables. For example, I'd return a huge json to client and I'd run out of memory on PostgreSQL. I started streaming row by row and problem solved.

Re: Ways to shoot yourself in the foot with Postgres

#62

Post by whakim »

singlow wrote: post #36
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?

My first guess would be that IS NOT DISTINCT FROM considers nulls to match. Indexes might not contain null values. I don't know of postgres indexes are sparse or can be non-sparse? It would be hard to do use a sparse index and find null matches. I would expect this could be optimized away if the column is also not null, but then why would you use the operator in that case so maybe no one has handled it.

Indices in Postgres do contain NULL values. My guess is that it's possible to make IS DISTINCT FROM an indexable operator, but it wouldn't be straightforward given that NULLs are a bit of a special case for indexing comparisons in the sense that they totally ignore data types. IS DISTINCT FROM would probably have to redefine a bunch of existing opclasses to account for the possibility of NULL.

Re: Ways to shoot yourself in the foot with Postgres

#63

Post by grafelic »

NegativeLatency wrote: post #3

Any good guides like this for MySQL? Unfortunately having to use it instead of Postgres at my current job.

Check out https://www.percona.com/blog (filter for MySQL)

I have been a heavy user of the Percona distribution of MySQL for many years and highly recommend it. I think that most of the MySQL articles on percona.com are relevant for other MySQL distributions.

As a side note, depending on the software stack, ProxySQL can have a major positive impact on performance and scalability.

Re: Ways to shoot yourself in the foot with Postgres

#64

Post by dx034 »

baq wrote: post #44
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…

Similar in postgres, depending on version. SQL Server is a damn fine DB if you can afford it. Highly recommended.

I second that. I found it by far the most pleasant database to work with, including its tooling. Postgres is probably second. Too bad SQL Server is so expensive.

Re: Ways to shoot yourself in the foot with Postgres

#65

Post by jaggederest »

williamdclt wrote: post #58
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…

I don't _think_ the query planner takes "current load" into account. If you have: - Same resources (CPU/memory) - Same settings (eg work_mem, amongst others) - Same dataset - Same/similar statistics (gathered with ANALYZE or the autovacuum) you should get the same results. If I'm wrong, please somebody correct me!

You might be right, definitely if you run e.g. a fully cloned server on the same disk image and hardware it will behave the same, I'm thinking of times when memory pressure on the database from other queries caused caches to get evicted, things like that. It's not really the planner, it's the measured performance from lock contention and the data in memory etc.

Re: Ways to shoot yourself in the foot with Postgres

#66

Post by funcDropShadow »

turtles3 wrote: post #51
winrid wrote: post #14

Earlier quoted context omitted.

Yes, there's nothing quite like the query planner deciding to try something new and suddenly 100 application servers are DDOSing your primary :)

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.

Re: Ways to shoot yourself in the foot with Postgres

#68

Post by jaggederest »

samokhvalov wrote: post #53
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…

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 he alludes to in the article about missing foreign key indexes - indexes come with a write cost, so on a write-only table, foreign key indexes are a bad idea) but as with everything real timing data is the key.

For new table design in Serious Business Databases I do two or three step: offline replica with the same data and settings (but not 100% identical), usually in a transaction on a logical replica or main production (postgres supports DDL transactions, which is a big help, but if you alter a table in a transaction it can block tons of other transactions).

It's important to carefully construct your alter table / create table / create index statements to ensure that everything is concurrent and won't block, of course, and there are plenty of pitfalls there, but definitely doable.

Re: Ways to shoot yourself in the foot with Postgres

#69

Post by lbriner »

call_me_g0d wrote: post #20

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

Personally as someone who has only ever used SQL Server, once I accepted that Postgres is different in certain ways, it has been great. Stable, predictable and I have had no performance surprises.

For me, the big win is to have a cluster without having to pay for SQL Server Enterprise edition therefore having money to invest into the community by having a Postgres consultant help configure it for me.

Re: Ways to shoot yourself in the foot with Postgres

#70

Post by RowanH »

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..
Post reply on HN