4 ms·

ALTER is in itself an odd command, useful only in development, never in production, and bringin with it deep architectural implications and an endless list of p

• genwin 13y ago
  The ALTER doesn't have to be in the embedded code. It can be a separate utility.
  • PommeDeTerre 13y ago
    The migration libraries mentioned in the article basically are the "separate utility" that you describe. Yet apparently the developers of these libraries are unable to implement this functionality properly on their own, for whatever reason. So they want the SQLite developers to do so instead.
• deleted 13y ago
  [deleted]
• gaadd33 13y ago
  Why is ALTER never useful in production?
  • mateo411 13y ago
    I wouldn't say that ALTER TABLE is never useful in production, but you should try to avoid it, especially if you have large tables. Many implementations of ALTER TABLE, lock the table, which means that no process can write to the table, until the ALTER TABLE command completes. If the ALTER TABLE statement takes minutes, or a few hours to complete, then you will have degraded performance or downtime.
    • gaadd33 13y ago
      Oh yeah that's very true, many SQL engines seem to lock tables whenever a DDL statement is run. I do like that Postgresql tends not to, it is a huge change from the hoops you have to jump through to add columns in a production MySQL scenario.
• spatz2013 13y ago
  tell that Drupal when they go from one version to the next.
• kalininalex 13y ago
  "ALTER is in itself an odd command, useful only in development, never in production" How would you then ever modify an existing database?
  • kanzure 13y ago
    > How would you then ever modify an existing database? You would use techniques like: https://github.com/soundcloud/large-hadron-migrator https://github.com/soundcloud/large-hadron-migrator """ That is until your tables grow to millions of records. At this point, the locking nature of ALTER TABLE may take your site down for an hour or more while critical tables are migrated. In order to avoid this, developers begin to design around the problem by introducing join tables or moving the data into another layer. Development gets less and less agile as tables grow and grow. To make the problem worse, adding or changing indices to optimize data access becomes just as difficult. Side effects may include black holes and universe implosion. There are few things that can be done at the server or engine level. It is possible to change default values in an ALTER TABLE without locking the table. The InnoDB Plugin provides facilities for online index creation, which is great if you are using this engine, but only solves half the problem. At SoundCloud we started having migration pains quite a while ago, and after looking around for third party solutions, we decided to create our own. We called it Large Hadron Migrator, and it is a gem for online ActiveRecord and DataMapper migrations. The basic idea is to perform the migration online while the system is live, without locking the table. In contrast to OAK and the facebook tool, we only use a copy table and triggers. Lhm requires a monotonically increasing numeric Primary Key on the table, due to how the Chunker works. """
    • kalininalex 13y ago
      OK, I guess the issue is with the "never in production". One size rarely fits all. I can have a lookup table with three rows in my database, for which Large Hadron Migrator is hardly an appropriate solution. ALTER will work just fine. For large tables - yes, a more complex migration solution is in order.
    • yxhuvud 13y ago
      .. or you use a real database engine that support schema changes in background transactions, like postgres. Also, it is good to remember that not all production databases will grow large. Many stay small with a limited number of rows, but will still need to be updated as the product is updated.