PostgreSQL Schema Change Guide

https://gist.github.com/jcoleman/1e6ad1bf8de454c166da94b67537758b

by luu • 4 years ago

271 30 4 years ago

gist.github.com

This post is absolutely terrific and has been been my main reference for Reshape, an automated, zero-downtime schema migration tool: https://github.com/fabianlindfors/reshape

robertlagrant• 4 years ago

I was thinking about that. Nice to know there was a link!

jfbaro2• 4 years ago

Reshape is super promising! Great work!!

fabianlindfors4• 4 years ago

Thank you! :)

jfbaro2• 4 years ago

Could reshape help with temporal table schema migration? https://stackoverflow.com/questions/71131800/how-to-avoid-br...

fabianlindfors4• 4 years ago

I don't believe Postgres supports temporal tables and as Reshape is Postgres-only, it won't help.

The principle behind Reshape might be applicable to temporal tables though but I don't have enough experience with them to say.

ansible• 4 years ago

So all applications only interact with views and don't use the actual tables themselves?

fabianlindfors4• 4 years ago

That's right! "Simple" views (ones mapping directly to a table) are quite powerful in Postgres and an application interacting with them won't know the difference.

ziml772• 4 years ago

Something that doesn't appear to be mentioned in here that bit me recently is that views follow a table through renames. Ended up having views that were subtly broken because they were pointing to the tables that I had renamed to have an _old suffix instead of the newly created ones I had expected them to point to.

giraffe_lady3• 4 years ago

Postgres is one of the very few technologies that I think is like, actually very good. But it is full of stuff like this. If I had thought about it for a minute it's possible I could have predicted this behavior, but maybe not!

takeda• 4 years ago

Maybe there are issues, but this behavior is something I would personally expect and I believe this is the right way.

I wouldn't want database to lookup the table/column id every time an extra lookup of the name is performed.

hashimotonomora• 4 years ago

All objects update their reference after a renaming. If you rename a column, foreign keys referencing that column have the name updated.

giraffe_lady3• 4 years ago

I commented elsewhere that the view renaming thing was surprising to me, but when you put it like this it makes sense. The view references the oid or whatever, some internal representation I try to pretend doesn't exist.

It's kind of surprising in some cases when I haven't seen it before (like views) but is consistent and the alternative would be a lot harder to work with.

teej• 4 years ago

Redshift added a feature to create views “WITH NO SCHEMA BINDING” to solve this but the Postgres team hasn’t added an equivalent yet.

djrobstep• 4 years ago

This is a very good example of why you should be using a schema diff/comparison tool.

Run your rename on a copy of your production schema, then run a diff tool to see what's actually changed - in this case it would show a view definition had changed and even autogenerate the replace statement you would need to add to fix the script.

Ozzie_osman• 4 years ago

What specific tools would you recommend for schema diffs?

eljimmy• 4 years ago

Worth noting that with Postgres 12+ you can REINDEX CONCURRENTLY so no need for the 3-step process mentioned in this gist.

jtc3313• 4 years ago

Ah, good point. I’ll add it to my list to update the canonical version on Medium (https://medium.com/p/20d3749ed680).

We’re still running PG11 so I haven’t been as good about getting some of the newer stuff added to the guide.

rattray• 4 years ago

Should have (2019) in the title.

jtc3313• 4 years ago

Author here!

This was also published here: https://medium.com/p/20d3749ed680, and I’ve updated it there at various times (though I’ll admit it doesn’t have the new features in PG13-14 yet) but had forgotten about the Gist version with those updates.

MaxGabriel• 4 years ago

Is there any notable 13/14 stuff worth mentioning?

jtc3313• 4 years ago

I don't see anything relevant in the PG13 release notes, but in the release notes for 14 there are these items:

- "Allow partitions to be detached in a non-blocking manner. The syntax is ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY, and FINALIZE." I didn't cover partitioning in the guide, but this is a real improvement to DDL with partitions.

- "Allow index commands using CONCURRENTLY to avoid waiting for the completion of other operations using CONCURRENTLY." This doesn't add new lower-lock level DDL, but it does improve the speed of existing such DDL.

- "Allow vacuum to be more aggressive in removing dead rows during minimal-locking index operations. Specifically, CREATE INDEX CONCURRENTLY and REINDEX CONCURRENTLY no longer limit the dead row removal of other relations." Similarly this doesn't add new lower-lock level DDL, but it does improve the safety of existing such DDL by eliminating a case where concurrent index creation could result in performance degradation of the system as a whole.

why-el• 4 years ago

Multiple concurrent index creations on a single table will not return from either CREATE INDEX CONCURRENTLY ... statement until the slowest one completes.

I am not sure this is true anymore, or at least, it was improved in v14 to only apply when "the indexes involved are partial or have columns that are not simple column references." [1]

Another thing worth noting here is that the exclusive locks taken on system catalogues during a DDL change also replicate to your replicas (necessarily, ha!) and they will have the same behavior described, i.e., they will line up behind queries. So for instance if you run long ETL queries against a replica, the DDL lock will line up behind it and no other queries can run until the ETL query is done and the DDL lock goes through.

[1] https://www.postgresql.org/docs/14/sql-createindex.html

AlisdairO• 4 years ago

As they note table lock acquisition can be super painful. It's possible to write a script to use pg_stat_activity to detect that the table alteration is waiting on a lock and kill conflicting work. For some workloads the kill is unacceptable, but on highly active systems killing off a few operations is usually much less damaging than letting sessions pile up on a lock wait.

Personally I would love it if it were possible to set a mode on postgres whereby access exclusive locks break other locks and cause the transactions holding them to roll back (similar to what happens with vacuums).

PostgreSQL Schema Change Guide

Related Stories