We just recently switched from MySQL to PostgreSQL. God we're so glad:
- add a null column to a big table => have fun and plan your maintenance accordingly. Good luck estimating it. This is basically a no-op in Postgres
- partial indices; absolutely important for big tables. Think about ticketing systems where in general the number of "open tickets" is much lower than the closed ones
- being able to use advanced SQL features like window functions can easily be underestimated; you can't use potential you don't have
- renaming indexes, just like that. If you're into refactoring and lessen developer surprises, you want to have proper names and fix accidently mistakes or just legacy stuff
- in my experience (but I'm no DBA), the logging of PostgresSQL is much more useful then what I get with MySQL (e.g. deadlocks or slow logs)
- the EXPLAIN (ANALYZE, BUFFERS) output is ... what you get is beyond comparison to MySQL
The only technical downside we experienced with Postgres (but in comparison, these things are benign):
- limit of length of identifiers; even with aliases. This can trip ORMs which try to do some clever naming under the hood
- you cannot change the order of columns, you can only append at the end
To me, everything I do with Postgres feels much more predictable, whereas with MySQL it always felt a game of gamble.
- add a null column to a big table => have fun and plan your maintenance accordingly. Good luck estimating it. This is basically a no-op in Postgres
- partial indices; absolutely important for big tables. Think about ticketing systems where in general the number of "open tickets" is much lower than the closed ones
- being able to use advanced SQL features like window functions can easily be underestimated; you can't use potential you don't have
- renaming indexes, just like that. If you're into refactoring and lessen developer surprises, you want to have proper names and fix accidently mistakes or just legacy stuff
- in my experience (but I'm no DBA), the logging of PostgresSQL is much more useful then what I get with MySQL (e.g. deadlocks or slow logs)
- the EXPLAIN (ANALYZE, BUFFERS) output is ... what you get is beyond comparison to MySQL
The only technical downside we experienced with Postgres (but in comparison, these things are benign):
- limit of length of identifiers; even with aliases. This can trip ORMs which try to do some clever naming under the hood
- you cannot change the order of columns, you can only append at the end
To me, everything I do with Postgres feels much more predictable, whereas with MySQL it always felt a game of gamble.