Postgres professionalism

I’m an old MySQL guy, it’s the only relational database I’ve used a lot. So it’s been fun to learn about Postgres. It’s like a database for grownups! Constraints! Views! Transactions!

MySQL has gotten better over the years, particularly with InnoDB. But fundamentally it was designed as a fast and loose database and it shows. Does it still coerce any string to an integer on insert rather than throwing errors? I can’t tell you how many 0s corrupted my database because someone inserted “apple” into an integer column, or even better null (when the column was marked not null). Also the MySQL driver for Python has always been pretty cryptic and poorly documented. It got the job done, but it didn’t lend a lot of confidence.

The psycopg2 docs are terrific, really well thought out Python driver. I love that it supports asynchronous queries, including with various coroutine libraries. The way you can extend the cursor and connection classes is quite spiffy, too. (DictCursors and named tuples! Logging cursors!) And lots of nice caveats about using the library properly, including what resources are held by cursors and connections and the like. Docs written by someone who’s really used the library in production.

I’m told MySQL is still faster than Postgres for big data. I’ve also heard Postgres replication is error prone. Nothing’s perfect, I guess. Looking forward to finding out. Of course my #1 reason to use Postgres is PostGIS: MySQL really can’t touch Postgres for geodata, it’s not even an option.