Hacker News Reader

Comment by TekMol | Hacker News Reader

Adding a column to a table might be a single line change in the application. No matter if you are using SQL or NoSQL.

Say your users table looked like this:

    id name country

And now you want to add their city:

    id name country city

This might cause the whole table to be locked and copied in SQL. While it might need no action at all in a NoSQL DB.

View on HN

> This might cause the whole table to be locked and copied in SQL.

The answer is always the same to these kinds of complaints: just use Postgres please.

If you don’t mind me asking, how does Postgres fix this? Do they have a more sophisticated locking mechanism, or maybe a copy offline until it’s ready kind of a system?

If the added column is nullable or if you provide a constant default value, the table is not copied, neither online nor offline.

And the code dealing with the missing "city" field across your whole codebase just appears out of nowhere now? :)

Dealing with schema changes is complex - NoSQL just moves it elsewhere where its easier to forget about it.

That’s what default field values and ORMs are for...

You still have to decide if you want to use the default value or something else at every place.

> This might cause the whole table to be locked and copied in SQL

That was indeed the case in the past, but not so much anymore except for certain situations. MySQL, for example, has had support for in-place table alterations for a while [1]. I've used it in production and it works very well IME.

[1] https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl.ht...

“might cause”, yes. But at least with Postgres that only happens if you add a default value to the new column. You can add the default value in your application instead, just like you would with your average NoSQL DB. Then, when you have low load on your system, you can migrate the rows in batches to have a default value, and eventually remove the application default.

Since Pg12, even adding a column with a default doesn't lock the table anymore - that only happens if you later change or remove the default

But if you don't control the client, you will now have to deal with client side migration and server database version management. If you create an additional v3, you will need to decide to either keep v1->v2->v3 code or v1->v2, v1->v3. Also, reporting.

Can’t you do this concurrently these days? Also, managing scheme in the database was the least of my issues when deleting/adding fields. You still need to make sure your clients are resilient against null or missing fields in the responses, and your scheduled jobs don’t query the data which isn’t there anymore. Point is, not having to alter the db scheme doesn’t make such a big difference. You still need to make sure your system overall is build in way that allows smooth data migrations.

> a single line change in the application

That's if you only have one application accessing your data.

Once you get multiple apps adding and retrieving data from the same data store, having the database do some work starts to make more sense.

Reply on news.ycombinator.com