Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Better Database Migrations in Postgres

craigkerstiens.com

Post reply on HN
Print view View original

71–80 of 89 posts

Re: Better Database Migrations in Postgres

#71

Post by keithlfrost »

vog wrote: post #36
gregmac wrote: post #25

I really just want a deterministic (or sync-based) migration tool. The only two I'm aware of are Innovartis DBGhost and RedGate SQL Compare (though RedGate requires a license everywhere it runs, whereas DBGhost only requires a license to compile the package). This stems from my work years ago with databases in on-premise products. Customers would modify the database schema, causing migration "up" scripts to fail, and…

(We should coin a term for this. I propose: "idempotent database updates".) I'm also a strong proponent of idempotent database updates, and prefer those over classic migrations wherever possible. Some experience from PostgreSQL (with several years of experience in various applications): While this approach works pretty well for idempotent changes such as "add column if not exists", it is more tricky when data content…

One caveat regarding "Always put each migration into a database transaction": I've found that for very large database tables on a live system, it becomes impractical to e.g. create a new index inside a transaction, because the entire table would need to be locked for the duration of the operation.

Re: Better Database Migrations in Postgres

#72

Post by semiquaver »

The very quick exclusive lock that postgres needs for things that are normally thought of as "free" like `ALTER TABLE ADD COLUMN` without a default can still cause problems on tables that are under extremely heavy contention. If you have hundreds of clients running long transactions against a table, even this fast metadata-only lock can cause an outage due to head-of-line blocking. Our team has had good luck with this lock-polling approach for migrations against very busy tables [1]. The idea is that you combine `lock_timeout` and attempt to `LOCK TABLE IN ACCESS EXCLUSIVE MODE` in a loop prior to running the DDL. Note that this is only safe for migrations that are actually instantaneous once the lock is acquired.

[1] http://www.databasesoup.com/2015/08/lock-polling-script-for-...

Re: Better Database Migrations in Postgres

#73

Post by emidln »

rawrmaan wrote: post #3

What's everyone favorite library for doing Postgres migrations using node? I'm using knex.js and still doing migrations mostly by hand.

I use the same tool across whatever language I'm in. It requires a directory of `TIMESTAMP-LABEL.DIRECTION.sql` files and then sorts them to know the order. It compares that to a database table of completed migrations to know which to run. It is extremely stupid, and throws its hands up if an error occurs (so the user can step in to fix it). It doesn't wrap things in transactions for you (not all DDL is transactional in Postgres) so you have to manually add BEGIN; and COMMIT; if you want those things.

It is glorious. I write a nice DSL called SQL for my database and there are never any surprises. If I want to write brittle migrations, idempotent migrations, transactional migrations, or data migrations, those are all on me. Anything more than this leads to needless debugging the tools and has never resulted in more clear or concise code.

Re: Better Database Migrations in Postgres

#74

Post by LyndsySimon »

robertlagrant wrote: post #46
sandGorgon wrote: post #14

Earlier quoted context omitted.

Alembic is a standalone tool written in Python. Having spent years on rails migrations and then using Alembic, I can say that alembic is really brilliant. It behaves like git - it has a branching and merge model for Migrations (in case multiple people work on it simultaneously).

Alembic is amazing, but AFAIK only works with its author's (world-beating) ORM, SQLAlchemy. I didn't think it was a general-purpose database change tool.

I don't know Alembic well enough to know what features of SQLAlchemy it requires, but I do know that SQLAlchemy consists of quite a bit more than just the ORM features. Perhaps Alembic is using it at a lower level, and can be used with any (or no) ORM layer?

Re: Better Database Migrations in Postgres

#75

Post by Mouse47 »

jimktrains2 wrote: post #63
gregmac wrote: post #25

I really just want a deterministic (or sync-based) migration tool. The only two I'm aware of are Innovartis DBGhost and RedGate SQL Compare (though RedGate requires a license everywhere it runs, whereas DBGhost only requires a license to compile the package). This stems from my work years ago with databases in on-premise products. Customers would modify the database schema, causing migration "up" scripts to fail, and…

We used thisbat a previous job. I loved it. https://github.com/dbsteward/dbsteward You define you're schema and it figures out what tonsonbased onbyour current schema.

Can it handle stored procs / UDFs? Was unable to find this in the docs...

Re: Better Database Migrations in Postgres

#76

Post by wyc »

On this topic, here's a great reference for some common operations on high volume SQL databases:

https://www.braintreepayments.com/blog/safe-operations-for-h...

Has anyone been collating large-scale SQL best practices into a book or similar? I've found high-level overviews such as this https://github.com/donnemartin/system-design-primer, but lacks specifics to scaling SQL...I've only seen disparate blog posts like the one above.

Re: Better Database Migrations in Postgres

#77

Post by jimktrains2 »

Mouse47 wrote: post #75
jimktrains2 wrote: post #63

Earlier quoted context omitted.

We used thisbat a previous job. I loved it. https://github.com/dbsteward/dbsteward You define you're schema and it figures out what tonsonbased onbyour current schema.

Can it handle stored procs / UDFs? Was unable to find this in the docs...

Yes. I know I've done it for triggers, let me see if I can find that project.

Re: Better Database Migrations in Postgres

#78

Post by tclancy »

icebraining wrote: post #44
erichurkman wrote: post #15

What's the case where adding a JSON column causes downtime with Postgres?

What do you mean?

I think they're referring to the last item in the list at https://github.com/ankane/strong_migrations#dangerous-operat... which was linked from the article.

Re: Better Database Migrations in Postgres

#79

Post by X86BSD »

keithlfrost wrote: post #71
vog wrote: post #36

Earlier quoted context omitted.

(We should coin a term for this. I propose: "idempotent database updates".) I'm also a strong proponent of idempotent database updates, and prefer those over classic migrations wherever possible. Some experience from PostgreSQL (with several years of experience in various applications): While this approach works pretty well for idempotent changes such as "add column if not exists", it is more tricky when data content…

One caveat regarding "Always put each migration into a database transaction": I've found that for very large database tables on a live system, it becomes impractical to e.g. create a new index inside a transaction, because the entire table would need to be locked for the duration of the operation.

Is this not what PostgreSQL's MVCC was created to help mitigate?

https://www.postgresql.org/docs/current/static/mvcc-intro.ht...

Re: Better Database Migrations in Postgres

#80

Post by [deleted] »

jimktrains2 wrote: post #70
ddorian43 wrote: post #68

Earlier quoted context omitted.

You mean heap-only-tuples: https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f...

Yes, I was just about to post https://github.com/postgres/postgres/blob/master/src/backend... I misremembered it a little in my original post. (I'm not able to open your link for some reason :-\)

[deleted]
Post reply on HN