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

81–90 of 329 posts

Re: Ways to shoot yourself in the foot with Postgres

#81

Post by reitanuki »

mschuster91 wrote: post #72
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.

From an admin perspective: Updates are a hot, complex mess which means I put them off until it's no longer feasible to do so (=because some software requires a newer version). MySQL is easy: apt-get update/docker stop && docker rm && docker run/kubectl apply, depending on your stack that is literally all you need to do. PostgreSQL in contrast is hell. You have to shut down the existing database server, install the ne…

You do not need to copy the DB, see pg_upgrade

Re: Ways to shoot yourself in the foot with Postgres

#82

Post by matthijs »

Using `truncate` in combination with `cascade` is another that I found unexpected:

"Automatically truncate all tables that have foreign-key references to any of the named tables, or to any tables added to the group due to CASCADE."

So it will simply clear out other tables that reference table to truncate, even if you have `on delete set null` and the foreign key column is null.

https://www.postgresql.org/docs/current/sql-truncate.html

Re: Ways to shoot yourself in the foot with Postgres

#84

Post by winrid »

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…

haha, this example is actually from Mongo, but it's pretty rare.

Re: Ways to shoot yourself in the foot with Postgres

#85

Post by Zanfa »

bakuninsbart wrote: post #75
smcleod wrote: post #56

Earlier quoted context omitted.

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.

Can you point to some good resources on how to write better postgres queries? Or give examples of common pitfalls?

I don't have any other good recommendations other than learning how to read and use EXPLAIN [1]. This should typically be the first tool when you have a slow query.

[1] https://www.postgresql.org/docs/current/sql-explain.html

Re: Ways to shoot yourself in the foot with Postgres

#86

Post by bbojan »

bakuninsbart wrote: post #75
smcleod wrote: post #56

Earlier quoted context omitted.

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.

Can you point to some good resources on how to write better postgres queries? Or give examples of common pitfalls?

Start with EXPLAIN ANALYZE then work from there. You can use tools where you paste the output of it and it shows you the data in a more easy to understand format.

Re: Ways to shoot yourself in the foot with Postgres

#87

Post by arichard123 »

Here's one, postgres default has this:

  seq_page_cost = 1.0
  random_page_cost = 4.0
Which is fine if you are using spinning disks to store your data. It makes postgresql prefer sequential scans over index usage

I think it's time the default were changed to suit SSDs, where a random page cost is the same as a sequential one.

  seq_page_cost = 1.0
  random_page_cost = 1.0

Re: Ways to shoot yourself in the foot with Postgres

#88

Post by jashmatthews »

arichard123 wrote: post #87

Here's one, postgres default has this: seq_page_cost = 1.0 random_page_cost = 4.0 Which is fine if you are using spinning disks to store your data. It makes postgresql prefer sequential scans over index usage I think it's time the default were changed to suit SSDs, where a random page cost is the same as a sequential one. seq_page_cost = 1.0 random_page_cost = 1.0

The defaults do suck but common storage options like SSDs or Elastic Block Storage still do sequential IO substantially faster than random.

Re: Ways to shoot yourself in the foot with Postgres

#89

Post by h1fra »

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 that will take memory and space disk for nothing.

- Broad indices are easier to maintain but if you can have multiple smaller indices with WHERE condition it will be much faster

- You can speed up, by a huge margin, big string indices with md5/hash index (only relevant for exact match)

- Postgres as a queue is definitely working and scales pretty far

- Related: be sure to understand the difference between transaction vs explicit locking, a lot of people assume too much from transaction and it will eventually breaks in prod.

Re: Ways to shoot yourself in the foot with Postgres

#90

Post by Already__Taken »

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!

I do find SQL not easy in this regard. alter table add constraint is a totally different command than alter table. gotchas like that.
Post reply on HN