Hacker News Reader

Comment by dmytton | Hacker News Reader

Flexibility is useful in development. Once you deploy then things will change less frequently so the real benefit is when you do have to change something, you're not running a big ALTER statement.

View on HN

In the enterprise world (at least in the Microsoft world) we have tools to manage these sorts of things. The one I'm currently using even for my small pet projects is called SQL Server Data Tools[1]. You basically just have to modify the original table creation scripts (add a column or whatever) and it actually generates all the change scripts for you. Even if the change is complex it handles making temp tables and transferring the data into the new table. It also notifies you and lets you handle cases where the change may lead to data loss.

I guess my point is that the SQL world is still innovating but it doesn't get that much attention. Unless I have a specific use-case for storing similar entities together with different fields then a schemaless DB is not the right tool for the job.

[1]http://msdn.microsoft.com/en-us/data/tools.aspx

> Once you deploy then things will change less frequently so the real benefit is when you do have to change something, you're not running a big ALTER statement.

Instead, you're writing a bunch of code to deal with data that may be in the old format, or may be in the new format, or may be in the new-new format.

I don't see that as an improvement.

In practice, you're writing code that has to deal with records that were updated by versions 3, 5, and 7 of your code but not 4 or 6. After a few years you can be sure somebody got it wrong at some point, and now you have records in a few out of the 2^n states where not even the developers can anticipate the system's behavior. The fix is to make ALTER incremental and less painful, not to stop writing down and checking your schema.

And both MySQL and PostgreSQL have put in work to make ALTER less painful by reducing the situations where a full table rewrite is necessary. In PostgreSQL you can both add and remove columns without the table being rewritten as long as you do not set a default value. You can also increase the lengths of varchars without any rewrite.

Reply on news.ycombinator.com