3 ms·

hey HN, repo is here: https://github.com/xataio/pgroll https://github.com/xataio/pgroll Would love to hear your thoughts!

• aschleck 3y ago
  Cool stuff! Do you have any thoughts about how this compares to https://github.com/fabianlindfors/reshape https://github.com/fabianlindfors/reshape?
  • tudorg 3y ago
    Great question! Reshape was definitely a source of inspiration, and in fact, our first PoC version was based on it. We decided to start a new project for a couple of reasons. First, we preferred it to have it in Go, so we can integrated it easier in Xata. And we wanted to push it further, based on our experience, to also deal with constraints (with Reshape constraints are shared between versions).
• gvkhna 3y ago
  Great to see more innovation in this space! How does this compare to? https://github.com/shayonj/pg-osc https://github.com/shayonj/pg-osc
  • exekias 3y ago
    Hi there, I'm one of the pgroll authors :) I could be mistaken here, but I believe that pg-osc and pgroll use similar approaches to ensuring no locking or how backfilling happens. While pg-osc uses a shadow table and switches to it at the end of the process, pgroll creates shadow columns within the existing table and leverages views to expose old and new versions of the schema at the same time. Having both versions available means you can deploy the new version of the client app in parallel to the old one, and perform an instant rollback if needed.
    • brycethornton 3y ago
      Does pgroll have any process to address table bloat after the migration? One of the (many) nice things about pg-osc is that it results in a fresh new table without bloat.
      • 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 applications, and a 'complete' phase after which only the latest version of the schema is available. To support the 'in progress' phase, some migrations (such as adding a constraint) require creating a new column and backfilling data into it. Triggers are also created to keep both old and new columns in sync. So during this phase there is 'bloat' in the table in the sense that this extra column and the triggers are present. Once completed however, the old version of this column is dropped from the table along with any triggers so there there is no bloat left behind after the migration is done.
        • pritambaral 3y ago
          > ... so there there is no bloat left behind after the migration is done. This is only true after all rows are rewritten after the old column is dropped. In standard, unmodified Postgres, DROP COLUMN does not rewrite existing tuples.
        • brycethornton 3y ago
          Thanks for the reply. My question was specifically about the MVCC feature that creates new rows for updates like this. If you're backfilling data into a new column then you'll likely end up creating new rows for the entire table and the space for the old rows will be marked for re-use via auto-vacuuming. Anyway, bloat like this is a big pain for me when make migrations on huge tables. It doesn't sound like this type of bloat cleanup is a goal for pgroll. Regardless, it's always great to have more options in this space. Thanks for your work!
    • 3y ago
• nwhnwh 3y ago
  Is it possible to make it work using SQL only in the future? Also, what about if the user can just maintain one schema file (no migrations), and the lib figures out the change and applies it?
  • 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 (eg when adding a constraint). 2. Each of the supported operation types is careful to sequence operations in such a way to avoid taking long-lived locks (eg, initially creating constraints as NOT VALID). A pure SQL solution would push this kind of responsibility onto migration authors. A state-based approach to infer migrations based on schema diffs is out of scope for pgroll for now but could be something to consider in future.
    • aseering 3y ago
      Thanks for releasing this tool! I actually interpreted the question differently: Rather than manipulating in SQL, would you consider exposing it as something like a stored procedure? Could still take in JSON to describe the schema change, and would presumably execute multiple transactions under the hood. But this would mean I can invoke a migration from my existing code rather than needing something out-of-band, I can use PG’s existing authn/authz in a simple way, etc.
  • gorkish 3y ago
    > Also, what about if the user can just maintain one schema file (no migrations), and the lib figures out the change and applies it? Because that only solves the DDL issues and not the DML. It is still useful though. I use a schema comparison tool that does exactly this to assist in building my migration and rollback plans, but when simply comparing two schemas there is no way to tell the difference (for example) between a column rename and a drop column/add column. The tooling provides a great scaffold and saves a ton of time.
• potamic 3y ago
  The link to the introductory blog post here appears to be broken https://xata.io/blog/pgroll-schema-migrations-postgres https://xata.io/blog/pgroll-schema-migrations-postgres