Database schema changes are hard (2017) • Hacker News Board

Re: Database schema changes are hard (2017)

#91

Post by jjeaff » Tue, Mar 05, 2019, 6:34 PM UTC

wolco wrote: post #64

jjeaff wrote: post #13

So how do you deal with it when you actually need to "migrate" data? Getting a correct schema is only part of the migration process. Many times a refactoring requires a new table or field to be populated with data from the old table or field. For example, a single table has a one to one relationship that we must select from to insert data into a newly created table so we can have a one to many relationship.

I'm confused. Wouldn't this type of statement cover those cases? INSERT INTO tbl_temp2 (fld_id) SELECT tbl_temp1.fld_order_id FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

Yes, but on how many different systems and dev setups do you have to run that?

Re: Database schema changes are hard (2017)

#92

Post by tatersolid » Wed, Mar 06, 2019, 6:48 PM UTC

wolco wrote: post #74

paulryanrogers wrote: post #66

Earlier quoted context omitted.

With RDBMS the integrity constraints can make gradually changes to large data sets more difficult. It often means a lot of downtime or the code must tolerate old and new structures for a while. With NoSQL the constraints are probably already in the application layer.

I usually remove db constraints and implement constrains at the application level for both types.

When you remove constraints from an RDBMS you slow it down, sometimes catastrophically.

The query planner/compiler in all modern RDBMS use foreign key constraints, unique constraints, and check constraints to optimize their execution code.

For instance, having a foreign key constraint allows the query planner to omit any code that checks if a value is in the child table but not the parent during a join. It can sometimes avoid accessing one table or the other entirely (so-called “join elimination”).

Re: Database schema changes are hard (2017)

#93

Post by zepolen » Sun, Mar 10, 2019, 2:01 PM UTC

jjeaff wrote: post #91

wolco wrote: post #64

Earlier quoted context omitted.

I'm confused. Wouldn't this type of statement cover those cases? INSERT INTO tbl_temp2 (fld_id) SELECT tbl_temp1.fld_order_id FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

Yes, but on how many different systems and dev setups do you have to run that?

All of them. It would be part of your migration process.

Let's say you want users to have multiple emails, your scripts would be as follows:

multiple_emails.up.sql:

  CREATE TABLE user_emails (
     user_id INT NOT NULL REFERENCES (users.id),
     email VARCHAR NOT NULL
  );
  INSERT INTO user_emails SELECT id, email FROM users;
  ALTER TABLE users DROP COLUMN email;

multiple_emails.down.sql:

  ALTER TABLE users ADD COLUMN email VARCHAR;
  INSERT INTO users SELECT id, email FROM users;
  DROP TABLE user_emails;