 | |
Temporarily dropping indexes for mass operations
comp.databases.postgresql comp.databases.postgresql
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Temporarily dropping indexes for mass operations -
04-01-2009
, 03:14 AM
#2
| |||
| |||
|
|
I'm considering the implications of dropping indexes temporarily during mass operations such as imports and generation of sample data. The idea is to avoid unnecessary incremental updates to indexes that aren't used by the mass operation anyway. I'm wondering how this interacts with transactions. Assuming that the import/generation process is the only user of the DB, is it a good idea to do everything, including dropping and creating of the indexes, in a single transaction? Or no transaction at all? |
index, commit.
Quote:
|
If there are other processes accessing the DB, how are they affected by the dropped indexes? Do they see the indexes as they were before the import/generation transaction started? |
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
#3
| |||
| |||
|
|
I'm considering the implications of dropping indexes temporarily during mass operations such as imports and generation of sample data. The idea is to avoid unnecessary incremental updates to indexes that aren't used by the mass operation anyway. I'm wondering how this interacts with transactions. Assuming that the import/generation process is the only user of the DB, is it a good idea to do everything, including dropping and creating of the indexes, in a single transaction? Or no transaction at all? |
to run everything inside a transaction is that you can (and must) rollback
everything if there is an error. That way you cannot end up with
an inconsistent database if the import goes wrong.
Mind that you cannot run VACUUM inside a transaction. That is of
course irrelevant if all you do is add data.
Quote:
|
If there are other processes accessing the DB, how are they affected by the dropped indexes? Do they see the indexes as they were before the import/generation transaction started? |
Dropping an index will take an Access Exclusive Lock on both table and
index. SELECT requires an Access Share Lock on the table. These two locks
are incompatible.
So the DROP INDEX will have to wait for all previous SELECT or DML
statements to finish, and all following SELECT or DML statements will be blocked
until the transaction is committed or rolled back.
Yours,
Laurenz Albe
#4
| |||
| |||
|
|
Michael Schuerig <michael (AT) schuerig (DOT) de> wrote: I'm considering the implications of dropping indexes temporarily during mass operations such as imports and generation of sample data. The idea is to avoid unnecessary incremental updates to indexes that aren't used by the mass operation anyway. I'm wondering how this interacts with transactions. Assuming that the import/generation process is the only user of the DB, is it a good idea to do everything, including dropping and creating of the indexes, in a single transaction? Or no transaction at all? Yes, start a transaction, drop the index, insert the data, recreate the index, commit. |
Quote:
|
If there are other processes accessing the DB, how are they affected by the dropped indexes? Do they see the indexes as they were before the import/generation transaction started? Other transactions can use the index, we have MVCC, yes! |
Michael
--
Michael Schuerig
mailto:michael (AT) schuerig (DOT) de
http://www.schuerig.de/michael/
#5
| |||
| |||
|
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.