![]() ![]() To make this operation without locking, you can create a new table with the addition of the non-nullable column, write to both tables, backfill, and then switch to the new table. This will have the same problem, as “Add a column with a default”. Add a column that is non-nullable (unsafe if PostgreSQL < 11) Rows are not touched when this executed, and are instead updated “lazily”. UPDATE: With PostgreSQL 11 it is now possible to have DDL statements like this: ALTER TABLE users ADD COLUMN foo_factor integer NOT NULL DEFAULT 42 Įxecute in constant time. So if you intend to fill the column with mostly non default values, it’s best to add the column with no default, insert the correct values using UPDATE (correct way is to do batched updates, for example, update 1000 rows at a time, because big update will create table-wide lock), and then add any desired default. For big table this will create long running operation that locks it. Add a column with a default (unsafe if PostgreSQL < 11)Īdding a column with a default requires updating each row of the table (to store the new column value). But exists some cases, which can lock your table. This operation will not block table and can be done safety. I decided to make a list of an operations, which can be done safe (without downtime) and usafe. However, any operation that locks a table for updates for more than a few seconds means downtime for me. In general, I am ok with database operations taking a long time. Anyone who tries to make a record in this table will block, and possibly time out, causing a partial outage. For example, if I create a new index on table, I cannot create new record in this table while that index is building. If I run a bad command, it can lock out updates to a table for a long time. ![]() I have to be very careful about what database operations I run. This means, I should run a database schema migrations while the app is up and serving requests. ![]() Some of this products should have high availability and working without any downtime. PostgreSQL is an object-relational database management system, which I often to use for many products. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |