The simplest CTEs are amazing for making readable queries, but CTEs can go far beyond being just a temporary view during a query. Recursive CTEs can essentially do a ‘for’ loop right in SQL - here’s a great example highlighting how that can be used.
A look at some handy features in Postgres that would be ‘hard to replicate in MySQL’ including sequences, inserting and selecting data with one query (using RETURNING), arrays, and user-defined data types.
General belief is that if you have locks on your database, you’re inviting problems, but the reality is more nuanced. Postgres has a variety of lock types which mean a variety of things and have various pros and cons as we see here.
Imports the contents of your MongoDB database cluster into a PostgreSQL instance, using an oplog tailer to keep the SQL mirror live up-to-date. This lets you run production services against a Mongo database, and then run offline reporting using SQL.
Indexes are what will generally make your queries run faster, at least for most web apps making small queries. There’s some general rules of thumb that you can use as a guide - here’s a great run down of those rules as well as some queries to help you analyze your DB.