Ways to shoot yourself in the foot with Postgres
philbooth.me
Ways to shoot yourself in the foot with Postgres
1–10 of 329 posts
Re: Ways to shoot yourself in the foot with Postgres
#2This configuration tuner will help address some of the articles points.
Re: Ways to shoot yourself in the foot with Postgres
#3Any good guides like this for MySQL? Unfortunately having to use it instead of Postgres at my current job.
Re: Ways to shoot yourself in the foot with Postgres
#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?
Re: Ways to shoot yourself in the foot with Postgres
#5Any good guides like this for MySQL? Unfortunately having to use it instead of Postgres at my current job.
They seem pretty generic, the same ideas/concepts can probably be taken over to mysql.
Re: Ways to shoot yourself in the foot with Postgres
#6Re: Ways to shoot yourself in the foot with Postgres
#7These 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.
Re: Ways to shoot yourself in the foot with Postgres
#8Postgres doesn't automatically create indexes for foreign keys. This may come as a surprise if you're more familiar with other databases, so pay attention to the implications as it can hurt you in a few ways.
I don't know of any database system that does this. In the case for SQL Server, the foreign keys usually get added by the ORM layer (Entity Framework migrations if you're using dotnet).
Re: Ways to shoot yourself in the foot with Postgres
#9Some additional techniques for triggers I've found helpful:
- Triggers for validation are awesome. Avoid triggers for logic if you can help it -- harder to debug and update than a server sending SQL and easier than you might think to cause performance problems and cascading triggers.
- Use custom error codes in validation triggers and add as much context as possible to the message when raising the exception. Future you will thank you.
RAISE EXCEPTION USING
ERRCODE = 'SR010',
MESSAGE = 'cannot add a draft invoice ' || new.invoice_id || ' to route ' || new.route_id;
- For better trigger performance, prefer statement-level triggers [1] or conditional before-row-based triggers.
[1]: https://www.postgresql.org/docs/current/trigger-definition.h...
Re: Ways to shoot yourself in the foot with Postgres
#10Another distinct set of similar tips that I found useful https://medium.com/productboard-engineering/eight-tips-to-re...