surjection

5 ms·

1.
by surjection 3y ago
Yes, for those pgroll migrations that require a new column + backfill, starting the migration can be expensive. Backfills are done in fixed size batches to avoid long lived row locks, but the operation can still be expensive in terms of tim
2.
by surjection 3y ago
There's nothing about this in the docs :) Backfills are done in fixed size batches to avoid taking long-lived row locks on many rows but there is nothing in place to control the overall rate of backfilling. This would certainly be a ni
3.
by surjection 3y ago
Any pgroll operations[0] that require a change to an existing column, such as adding a constraint, will create a new copy of the column and backfill it using 'up' SQL defined in the migration and apply the change to that new colum
4.
by surjection 3y ago
The bloat incurred by the extra column is certainly present while the migration is in progress (ie after it's been started with `pgroll start` but before running `pgroll complete`). Once the migration is completed any extra columns are
5.
by surjection 3y ago
An example would make this more concrete. This migration[0] adds a CHECK constraint to a column. When the migration is started, a new column with the constraint is created and values from the old column are backfilled using the 'up&#x2
6.
by surjection 3y ago
Allowing the database and the application to be out of sync (to +1/-1 versions) is really the point of pgroll though. pgroll presents two versions of the database schema, to be used by the current and vNext versions of the app while sy
7.
by surjection 3y ago
There is no code to do this because it's actually a nice feature of postgres - if the underlying column is renamed, the pgroll views that depend on that column are updated automatically as part of the same transaction.
8.
by surjection 3y ago
I don't see the need to keep your application consistent with both schema versions. During a migration pgroll exposes two Postgres schema - one for the old version of the database schema and another for the new one. The old version of
9.
by surjection 3y ago
You're right. I wish schema wasn't such an overloaded term :) In order to access either the old or new version of the schema, applications should configure the Postgres `search_path`[0] which determines which schema and hence whic
10.
by surjection 3y ago
Another pgroll author here :) I'm not very familiar with pg-osc, but migrations with pgroll are a two phase process - an 'in progress' phase, during which both old and new versions of the schema are accessible to client appli
11.
by surjection 3y ago
Do you mean the extra configuration required to make applications use the correct version of the database schema, or something else?
12.
by surjection 3y ago
We are looking to build integrations with other tools but for now isn't recommended to use pgroll alongside another migration tool. To try out pgroll on a database with an existing schema (whether created by hand or by another migratio
13.
by surjection 3y ago
Hi, one of the authors of pgroll here. Migrations are JSON format as opposed to pure SQL for at least a couple of reasons: 1. The need to define up and down SQL scripts that are run to backfill a new column with values from an old column (
14.
Show HN: Spawn – Throwaway Databases for CI and Development(spawn.cc)
4 points by surjection 5y ago|1 comments
15.
by surjection 5y ago
We're a small team working on Spawn, a SaaS for provisioning ephemeral databases for CI pipelines and development workflows. Spawn supports multiple database engines, database instances start in seconds regardless of data size and can