アカウント名:
パスワード:
drop index→copy→create index ならば常識的な時間で処理できるんだから、内部でそれと同じことをやればいいだけだと思うんだけど…。
それを自動でやると、複数接続から同時にユニーク制約を持ったテーブルにデータを挿入したときに、整合性を保証出
もう一つ上げておきましょう。DROP->COPY->CREATEとした場合としない場合、どっちが早いかはCOPYの量に依存しますが、当然そのシーケンスの中で、ロックを取る前にインデックス再作成した方が早いかどうかを知ることはできません。極論、レコードを1
ちゃんとロールバックされてる。
ただ、UNIQUE制約のチェックが、全件insert終了後にしかかけられないため、あまりにもコストがかかりすぎているようですね。結局のところ、絶対に重複がないと確信できる状態でない限りやらない方が良さそうです。
drop 前に copy されるデータのサイズ等を知ることも可能なはずですから
「index 更新なしの copy」と「新規追加された部分のみ index 追加」
まあでも、この種のトランザクションを自動で発行してくれるツールがあればそれはそれで選択肢として良いのかもしれません。高速にコピーできることがあるとはいえ、その間テーブルを完全にロックしてしまうのではっきり言って使い勝手は良くないですが・・・
そんななので、途中からやるなら単に作成をまとめたところで状況は良くなりません。create indexの場合は最初から作るのが前提なので、作成はもっと効率よくできます。うろ覚えですが、いったんソートしてからやっていたような。
ALTER TABLE すらトランザクションに含められる PostgreSQL ならではの裏技ですね。
# もちろん、あらゆるものをロールバックできる、というのは決して簡単な問題ではないので、それはトレードオフ何じゃないかな、と思わなくもないですが。
より多くのコメントがこの議論にあるかもしれませんが、JavaScriptが有効ではない環境を使用している場合、クラシックなコメントシステム(D1)に設定を変更する必要があります。
海軍に入るくらいなら海賊になった方がいい -- Steven Paul Jobs
個人的には (スコア:2, 興味深い)
あとインデックスを含んだpg_restoreの高速化とか…無理か。
#面倒なので、OSごとHDDのミラーリング+イメージで対応してます。
Re:個人的には (スコア:3, 興味深い)
Oracle でも一つの DB をずっと使ってると行移行とかハイウォーターマークの問題とかいろいろあるわけで、データファイルの定期的なメンテナンスはいずれにせよ必須なのではないか
Re:個人的には (スコア:1)
それを自動でやると、複数接続から同時にユニーク制約を持ったテーブルにデータを挿入したときに、整合性を保証出
Re:個人的には (スコア:1)
確かにおっしゃる通り。
でも実は、PostgreSQL は DDL もトランザクションに含めることが出来ちゃったりするの
Re:個人的には (スコア:1)
もう一つ上げておきましょう。DROP->COPY->CREATEとした場合としない場合、どっちが早いかはCOPYの量に依存しますが、当然そのシーケンスの中で、ロックを取る前にインデックス再作成した方が早いかどうかを知ることはできません。極論、レコードを1
Re:個人的には (スコア:2, 参考になる)
> いいえ、それでも元々データが1件以上入っていた場合破綻します。
そうでしょうか?ちょっとテストしてみました。
=> \d test_table
Table "public.test_table"
Column | Type | Modifiers
ーーーーーーーーーーーーーーーー
id | integer |
Indexes:
"test_table_id_key" unique, btree (id)
// unique 制約だとちと面倒なので単なる unique index として用意。
=> select * from test_table;
id
----
1
(1 row)
Re:個人的には (スコア:1)
ただ、UNIQUE制約のチェックが、全件insert終了後にしかかけられないため、あまりにもコストがかかりすぎているようですね。結局のところ、絶対に重複がないと確信できる状態でない限りやらない方が良さそうです。
データ量を計測したりするコストが馬鹿にならないのでは。 たぶん、これらはやったところで今のCOPYの方が早いでしょう。なんとなれば、従来のindex付きcopyが「タプル挿入コスト+インデックスタプル挿入コスト」なのに、インデックス作成を遅らせたところで後で読むコストが増えるだけです。まあでも、この種のトランザクションを自動で発行してくれるツールがあればそれはそれで選択肢として良いのかもしれません。高速にコピーできることがあるとはいえ、その間テーブルを完全にロックしてしまうのではっきり言って使い勝手は良くないですが・・・
Re:個人的には (スコア:1)
ですです。このあたりが「裏技」っぽいなぁと(^^;
> 結局のところ、絶対に重複がないと確信できる状態でない限りやらない方が良さそうです。
> データ量を計測したりするコストが馬鹿にならないのでは。
いや、おっしゃるとおりです。たぶんいろいろちゃんと考えてみると、「デフォルトの挙動を drop→copy→create に変更する」ことは難しい、という結論になるのでしょうね。
> なんとなれば、従来のindex付きcopyが「タプル挿入コスト+インデックスタプル挿入コスト」なのに、インデックス作成を遅らせたところで後で読むコストが増えるだけです。
ええ、ふつうに考えるとそのはずなんですけど、今の PostgreSQL の index 付き copy は常軌を逸して遅いです。大量のデータを import する場合などに、index を付けたままの copy と drop→copy→create との間で、100倍くらいの処理時間の差が生じることも普通にありますよね。理論的には読むコストが増えているはずの後者の方が遅くなってもいいはずなのに…(一件ずつインデックスを挿入する、という処理のオーバーヘッドが死ぬほど大きい、ってことかなぁ…という想像をして、「新規追加された部分のみ index 追加」ならそのオーバーヘッドを回避できたりしないかしら、と考えたわけです)。
> まあでも、この種のトランザクションを自動で発行してくれるツールがあればそれはそれで選択肢として良いのかもしれません。高速にコピーできることがあるとはいえ、その間テーブルを完全にロックしてしまうのではっきり言って使い勝手は良くないですが・・・
大量のデータを bulk load する場合って、たいてい一般ユーザからのアクセスは遮断して行うことが多いんじゃないでしょうか。逆にそういう状況を仮定できない場合は確かに非常に使いにくいと思います。
大量のデータを保持しつつも更新は truncate→copy しかないような場合 (参照用 DB と更新用 DB を分けているような場合の参照側) に限れば、同じ構造を持つテーブルを2つ用意して、
1. 裏テーブルを drop index
2. 裏テーブルを truncate
3. 裏テーブルに copy
4. 裏テーブルに create index
5. 裏と表を swap (ALTER TABLE ~ RENAME TO)
までを一つのトランザクションとなるように処理すれば、表テーブルがロックされる時間は 5. から commit までのごくわずかな時間に抑えられます。ALTER TABLE すらトランザクションに含められる PostgreSQL ならではの裏技ですね。
// ストレージは2倍必要になりますけどね(^^;。
Re:個人的には (スコア:1)
そんななので、途中からやるなら単に作成をまとめたところで状況は良くなりません。create indexの場合は最初から作るのが前提なので、作成はもっと効率よくできます。うろ覚えですが、いったんソートしてからやっていたような。
# もちろん、あらゆるものをロールバックできる、というのは決して簡単な問題ではないので、それはトレードオフ何じゃないかな、と思わなくもないですが。
Re:個人的には (スコア:1)
なるほど~。そんな経緯があるのですね。非常に勉強になりました。
> むしろこれはトランザクションを含めながらほとんどのDDLがロールバックできないと言う他DBがふがいないと思っています。
例外の少なさや機能の直行性などに関していえば PostgreSQL は商用 DB を超える部分もありますよね。商用 DB や MySQL などと比べると進化が遅くていらいらする人もいるかもしれませんが、そういった部分こそまさにトレードオフなのかもしれないなぁ、なんてふと思いました。
いろいろ貴重な情報ありがとうございました。