6 ms·
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:
by TekMol 6y ago
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.
- jchb 6y ago“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.
- phiresky 6y agoSince 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
- nlitened 6y ago> 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.
- pseudoramble 6y agoIf 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?
- funcDropShadow 6y agoIf the added column is nullable or if you provide a constant default value, the table is not copied, neither online nor offline.
- mfritsche 6y agoBut 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.
- izacus 6y agoAnd 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.
- bzb6 6y agoThat’s what default field values and ORMs are for...
- funcDropShadow 6y agoYou still have to decide if you want to use the default value or something else at every place.
- arkh 6y ago> 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.
- Jeema101 6y ago> 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.html https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl.ht...
- f6v 6y agoCan’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.