![]() Thus this method requires more total work than a standard index build and takes significantly longer to complete. When this option is used, PostgreSQL must perform two scans of the table, and in addition it must wait for all existing transactions that could potentially modify or use the index to terminate. This method is invoked by specifying the CONCURRENTLY option of CREATE INDEX. PostgreSQL supports building indexes without locking out writes. Other transactions can still read the table, but if they try to insert, update, or delete rows in the table they will block until the index build is finished. Normally PostgreSQL locks the table to be indexed against writes and performs the entire index build with a single scan of the table. This operation will not block table and can be done safety. Add a default value to an existing column (safe) As workaround, you can add a new column with needed type, change the code to write to both columns, and backfill the new column. But if column type change requires a rewrite or not depends on the datatype, in this case this operation requires updating each row of the table. Changing the length of a varchar, for example, does not lock a table. It is not strictly unsafe for all changes. Drop a column (safe)ĭropping a column is very quick, but PostgreSQL won’t reclaim the disk space until you run a “VACUUM FULL”. Rows are not touched when this executed, and are instead updated “lazily”. This workaround is incredibly onerous and need two times more space than is a table takes.Įxecute in constant time. 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) 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. 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 |