MySQL vs PostgresQL: Pros and Cons for Default Database
By: Johnathon Wright on: July 05, 2020
I start up a lot of software with a database for small to mid-size apps that don't particularly have any requirements that would lend themselves to using one or the other SQL database. I just have to pick one. Here are some things I've discovered that inform my thinking about which one to choose.
| PostGresQL | MySQL | |
|---|---|---|
| Setup: Installation | Seemed simple but apt installed a version that seemed to make odd permission decisions | wizard walks you though decisions. Works well. No odd defaults. Other than a lack of timezones. |
| Setup Permissions | they have their own system. When I asked support to explain, they said I was an idiot. | typical sql permissions |
| Support Channel: Pros | Willing to share if you ask your questions in exactly they way they want you to ask. | Willing to share and help people work through problems, but often want people to create sqllint things, which is time consuming. |
| Support Channel: Cons | more concerned with proving they are great than being helpful | can be unresponsive |
| ID indexes | Can lose track of which ID is next after bulk insert. There's a workaround. Hopefully it happens when an admin is monitoring. :`( | always knows which id is next. |
| Geography | I hear great things. | Distance and other calculations can be done using equations. Gets complicated fairly quickly. |
PostgresQL random failures
``` jw@logopolis:/projects/unity/bonsai$ docker-compose run web rake db:drop
Starting bonsaidb1 ... done
PG::ObjectInUse: ERROR: database "unity_dev" is being accessed by other users
DETAIL: There is 1 other session using the database.
: DROP DATABASE IF EXISTS "unity_dev" ```
Just trying to drop a database. Apparently previous attempts are still running?