1

Some Background

We currently have a PostgreSQL DB that is "read-only", i.e. no user is allowed to write to it and all insertions are done in a controlled manner.

For a while, these were done by manually adding this data or creating a copy of the DB locally and using pgdump to put it into a production environment.

There were obvious limitations with that and since there was the need for versioning the DB too (i.e. an addition to the contents or change of structure means we need to update the version number) we decided to use flyway.

This was good since we would get a versioning "for free" by using the migrations. We were not only using Flyway to modify the structure of tables and DB but we've been using it for inserting new data too.

The Problem

The migration files are getting quite big and if we need to apply the migrations or something similar it is becoming quite a time-intensive process, especially if we apply from scratch. Since DB management/engineering is not my forte I've decided to reach out:

The Question

Are there best practices or a way to better handle this situation? I.e. structural changes AS WELL as data addition.

My best guess was to use the migrations files only for structural changes and have the data into files (CSV or other) that get added programmatically after the migrations run. But this is ok for the first time, after that the file would be obsolete and subsequent files would be used for new data that would fit possible structural changes...

New contributor
fditz is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
2
  • Suppose you insert rows in V50. Now you create V51 with new data - will you delete all the data in V50 and re-insert it in V51? Or would you UPDATE old data and insert additional rows? Jan 13 at 18:03
  • I would say that in your example we would UPDATE. But commonly we also just INSERT new data that we bought or created for new Tables (and cannot be derived from existing data of course)
    – fditz
    Jan 14 at 6:04
0

Judging from the comments this may not be the perfect option for you, but so far I couldn't come up with something better (and I doubt it's possible).

Clean old data and re-insert from scratch every time

Flyway has a notion of Repeatable Migrations - meaning if the checksum changed, then the migration will be re-applied. If you're ready to truncate the table and re-write your old DML so that it's reapplied from the scratch each time - then this seems like the most native option.

For this to work you also need to create 2 Flyway tables:

  1. First one for DDL, let it stay as schema_version
  2. But INSERTs need to always be started after all DDLs finished, so we can't re-use the same versioning. For this you'll need to create a new instance of Flyway (and configure a different inserts_schema_version) strictly after the 1st instance finished migrating.
  3. If it's acceptable, you can start 2nd Flyway migration in the background. So the app will start right away when DDL finishes, but the data will appear later.

So:

  • Pros: newly created envs will be initialized quickly w/o applying INSERTs in the old format and then UPDATEing.
  • Cons: updating existing envs will take longer because the old data will have to be re-inserted.

Note that if you simple have more data to INSERT, then you still can just add more DML migrations instead of changing the existing ones. This way the existing envs will get migrated faster.

Your Answer

fditz is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.