Why I Enjoy PostgreSQL – Infrastructure Engineer's Perspective

[1]: https://github.com/ankane/strong_migrations#checks

MS SQL fellow here, rather than postgres, but the concepts should be the same. No docs I can immediately point you at, but some feeling for how I [don't] do things:

I still wouldn't perform most schema updates in live production, I'd wait for a maintenance window.

The key benefit from my PoV is that the changes are transactional so either all work or they rollback and you are back where you started instead of some mysterious mid-way point. Adding indexes is fine, as long as you have spare IO capacity for the process to chew if performed on a large table, and many index alterations can be done online too, and adding/modifying procs, views, and similar objects can be similarly undisruptive if properly wrapped in transactions (so active sessions never see a half updated set of parts), but I'd not make actual table changes “properly live” in production.

> I would expect major caveats in such a feature, like long lived table locks

In DBs with transactional DDL some table operations are practically lock-free or just happen so fast that they might as well be, such as (usually) adding a NULLable column or making an existing one NULLable, and some other operations are, despite being long-winded, sometimes possible to perform online (the new structure is added, then a final sync done for changes made while that happened, then switched over to, and the old parts cleaned up now the new are in use), but I'd never risk it unless I really really had to make the change ASAP and absolutely couldn't arrange a maintenance break in good time.

> For example concurrent index creation is harmless on its own, but combined with another fast but locking schema change

There should only ever be your process making schema changes, as the application and other users shouldn't be at all, so in theory you don't need to worry about two changes competing for locks or causing lock escalation like that. Several concurrent online index creations/modifications are fine, as long as you can afford the extra IO load that will impose, particularly if the objects being touched are on different storage (or otherwise have separate IO quotas) so the two index changes won't compete with each other for IO, but not table changes and similar: keep them to one at once, while nothing else is happening.

I kid, I kid, but yeah it feels like there's some room for improvement when it comes to the infrastructure side of working with Postgres

• Materialized views with indexing are an easy way to solve many speed issues where you'd like to use a fancy query quickly.

• The GEOGRAPHY data type is great for data integrity, but often slower for queries. I've made sure our primary data is stored as GEOGRAPHY then added expression indices [1] casting to GEOMETRY. Spatial joins and filters can be done on the casted column where faster queries are needed.

• Source data is often very high resolution. If you don't need it, simplifying high accuracy data (5m or whatever) to something much lower resolution (500m, 1km, or whatever) in a derived view or table using PostGIS' simplification functions can greatly improve spatial predicate performance.

[1] https://www.postgresql.org/docs/14/indexes-expressional.html

Now.. which order was the correct one? I have forgotten after not doing gis stuff for some years now.

I'm loving the cockroachdb upgrade process for our cluster - you just do a zero-downtime rolling restart to the next major version, with the ability to roll back to the old version if you see issues, and then when ready allow the database to apply it's migrations internally to upgrade the data structures, which is also zero-downtime.

I did not, that's really interesting thanks!

> If you must use Microsoft SQL Server, at least deploy it on Linux

For customers where we'll still be managing the DB, that's definitely an option I'll push for checking out.

That said, management can trump peak performance. We've been a Windows shop for 20+ years, if support judges it too hard to learn the idea is DOA.

As an employee of MongoDB we treat "catastrophic" failures of our software pretty seriously. If you have experienced a failure we want to hear about. Paying customer or no...

I see a recommendation of using pgbouncer almost on every tutorial and how-to and feel it is really a vital point. As a newbie user I want to be on a safe side with that. Also, heavily concurrent app architecture, even with connection pooling, may easily produce tens of connections with moderate load and jump to hundreds during peaks. I know from the experience that API users tend not to think about load they create on the other side, just hacking together series of wrapped calls.

Can you elaborate on "countless postmortems", please? On my work we recently had a 12 hours downtime on one of our 20 Mongo databases, but it was caused by a mistake during recovery from storage overflow. I will give a credit to Postgres here, by the way: on disk with 0 bytes free it still works in read-only mode.

Also, it is possible to horizontally scale postgres. It's not for the light of heart, but the easiest way is with read only replicas, which is pretty easy (especially with RDS), and read heavy loads are where postgres really shines, and also most apps out there.