添加没有表锁的新列？ [英] Add new column without table lock?
问题描述
在我的项目中，有2300万条记录和大约6个字段已被索引到该表中。
之前我测试过为思考斯芬克斯搜索添加增量列，但它转向保持整个数据库锁定一个小时。之后当文件被添加并且我尝试重建索引时，这是一个持有数据库锁约4个小时的查询：
update user_messages set delta = false where delta = true
为了让服务器起作用，我创建了一个从数据库转储新的数据库，并将其提升为数据库，以便服务器可以实时转换。
现在我所看到的是在表中添加增量列与表锁是有可能吗？一旦添加了列 delta ，那么当我运行index rebuild命令时，为什么会执行上述查询？为什么它会阻塞服务器这么久？
PS .:我在Heroku上使用Postgres和ika db模型。 在PostgreSQL中更新意味着写一行的新版本。您的问题并未提供所有信息，但这可能意味着需要编写数百万行新记录。
如果您的数据模型和可用磁盘空间允许， CREATE 在后台创建一个新表，然后在一个事务中： DROP 旧表 RENAME 新的。
此 dba.SE 后面的相关答案。
另外，如果大部分表受到影响，请在执行质量 UPDATE 之前删除所有索引，因为所有索引也必须更新。删除它们并在大规模更新完成后重新创建它们会更快。
最后，在后台创建新表格时：一次应用所有更改，或者您可以创建受影响行的多个更新版本。
免责声明：添加一个没有 DEFAULT （= DEFAULT NULL ）通常不会创建一个新行并且本身非常便宜。向它添加值会创建新行。
详细信息。
如果由于约束条件无法删除原始表，另一个快速方法是构建一个临时表， TRUNCATE 原始的和质量 INSERT 新行 - 排序，如果这有助于性能。所有在一个交易。像这样：
BEGIN
SET temp_buffers = 1000MB; - 或者你可以暂时备用的东西
CREATE TEMP TABLE tmp AS
SELECT * FROM tbl LIMIT 0; - 复制表格的布局
ALTER TABLE tmp ADD column delta boolean; - NOT DEFAULT
INSERT INTO tmp（col1，col2，...，delta）
SELECT col1，col2，...，FALSE
FROM tbl; - 复制现有行加上新值
- ORDER BY ???
- 在此删除所有索引
TRUNCATE tbl; - 空表 - truncate超快
ALTER TABLE tbl ADD列delta delta布尔型DEFAULT FALSE; - 不是NULL？
INSERT INTO tbl
SELECT * FROM tmp; - 插回幸存的行。
- 在此处重新创建所有索引
COMMIT;
In my project having 23 million records and around 6 fields has been indexed of that table.
Earlier I tested to add delta column for Thinking Sphinx search but it turns in holding the whole database lock for an hour. Afterwards when the file is added and I try to rebuild indexes this is the query that holds the database lock for around 4 hours:
"update user_messages set delta = false where delta = true"
Well for making the server up I created a new database from db dump and promote it as database so server can be turned live.
Now what I am looking is that adding delta column in my table with out table lock is it possible? And once the column delta is added then why is the above query executed when I run the index rebuild command and why does it block the server for so long?
PS.: I am on Heroku and using Postgres with ika db model.
Updating in PostgreSQL means writing a new version of the row. Your question does not provide all the information, but that probably means writing millions of new rows.
If your data model and available disk space allow for it, CREATE a new table in the background and then, in one transaction: DROP the old table, RENAME the new one.
Details and caveats in this related later answer on dba.SE.
Also, if a major proportion of the table is affected, remove all indexes before doing the mass UPDATE, because all indexes have to be updated too. It is faster to delete them and recreate them after the mass UPDATE is done.
And finally, while creating the new table in the background: Apply all changes at once, or you create multiple updated versions of the affected rows.
Disclaimer: Adding a new column without DEFAULT (= DEFAULT NULL) will not normally create a new row and is very cheap per se. Adding values to it creates new rows.
Details in the maunal.
If you cannot remove the original table because of constraints, another fast way is to build a temporary table, TRUNCATE the original one and mass INSERT the new rows - sorted, if that helps performance. All in one transaction. Something like this:
BEGIN
SET temp_buffers = 1000MB; -- or whatever you can spare temporarily
CREATE TEMP TABLE tmp AS
SELECT * FROM tbl LIMIT 0; -- copy layout of table
ALTER TABLE tmp ADD column delta boolean; -- NOT DEFAULT
INSERT INTO tmp (col1, col2, ... , delta)
SELECT col1, col2, ... , FALSE
FROM tbl; -- copy existing rows plus new value
-- ORDER BY ???
-- DROP all indexes here
TRUNCATE tbl; -- empty table - truncate is super fast
ALTER TABLE tbl ADD column delta boolean DEFAULT FALSE; -- NOT NULL?
INSERT INTO tbl
SELECT * FROM tmp; -- insert back surviving rows.
-- recreate all indexes here
COMMIT;
这篇关于添加没有表锁的新列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
