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

51–60 of 329 posts

Re: Ways to shoot yourself in the foot with Postgres

#51

Post by turtles3 »

winrid wrote: post #14
xupybd wrote: post #7

These are some good tips but I've not hit these performance issues. I work on smaller scale applications. One has been in production since 2012 the database performs very well. I guess I need to get out and work for bigger companies to experience this.

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 that query plan unless explicitly told otherwise.

Re: Ways to shoot yourself in the foot with Postgres

#52

Post by jaggederest »

xupybd wrote: post #45
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!

It took me too long to understand this. I always felt pressure to get things done so skipped reading the manual. Turns out I would have gotten more done had I just read the manual.

There's the hoary old cliche about "if I was given three hours to cut down a tree with an axe, I would spend the first hour sharpening the axe" but in many cases it's really true. You can look like a superhero just by pointing out some small feature that makes life easier.

One time I pointed out that, rather than reordering the tables to make loading work with foreign key constraints, we could pause trigger execution, load all the data, and then resume the triggers. pg_dump can even do it for you if you pick the options right (might be default), in addition to natively ordering the data correctly, but if you're messing around with large SQL files anyway, it's helpful..

Re: Ways to shoot yourself in the foot with Postgres

#53

Post by samokhvalov »

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?

Re: Ways to shoot yourself in the foot with Postgres

#54

Post by ainar-g »

call_me_g0d wrote: post #20

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

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.

Re: Ways to shoot yourself in the foot with Postgres

#55

Post by citrin_ru »

remus wrote: post #34
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 think the article is kinda mixing two points here. One the one hand, it is sensible to try and keep all your business logic in one place (could be the database, could be the application) as spreading it across multiple places can make it hard to maintain. The current trend is to do your business logic in the application and treat the db as a data storage layer. The point in the article is that if you're using this…

Why splitting logic between Postgres and an application considered worse than splitting it between multiple micro-services? A DB is a storage service with INSERT/SELECT/e.t.c. as an API. Why we cannot extend this API to include stored procedures too? Indexes are commonly used to enforce data integrity. Why we cannot use triggers to do this even better?

Re: Ways to shoot yourself in the foot with Postgres

#56

Post by smcleod »

Zanfa wrote: post #42
maxloh wrote: post #28

Earlier quoted context omitted.

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

In my experience more often than not, Postgres performance problems aren't really caused by the database, but either badly designed schemas or queries. For a lot of developers, the thinking goes that 10s of millions of rows sounds like a lot like big data, so they must start building microservices, distributed systems, use K/V stores and horizontally scale a la Google, whereas their entire dataset could actually fit…

Came here to say exactly this. Over the last 12~ years working with PostgreSQL I've dealt with quite a few performance related issues - almost all were poorly written queries.

Re: Ways to shoot yourself in the foot with Postgres

#57

Post by jaggederest »

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.

SQL Server is one of the descendants of Ingres, and PostgreSQL is, as the name might suggest, the successor project for database research after Ingres. They're both great databases really, it's a fun connection in their mutual history.

Re: Ways to shoot yourself in the foot with Postgres

#58

Post by williamdclt »

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!

Re: Ways to shoot yourself in the foot with Postgres

#59

Post by mbork_pl »

ainar-g wrote: post #54
call_me_g0d wrote: post #20

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

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

Re: Ways to shoot yourself in the foot with Postgres

#60

Post by albertopv »

jaggederest wrote: post #57
baq wrote: post #44

Earlier quoted context omitted.

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

SQL Server is one of the descendants of Ingres, and PostgreSQL is, as the name might suggest, the successor project for database research after Ingres. They're both great databases really, it's a fun connection in their mutual history.

Wow, I didn't know!
Post reply on HN