Side effects of moving an index to a new tablespace
As per Ondrej's suggestion, our current thinking/planning at this point is
to move indices instead of tables.
We have lots of them, they are much smaller than the tables, and that will
allow us to do the migrations more incrementally.
One area where the documentation is not very detailed - What are the side
effects and/or constraints put in place while an index is being moved? I
assume that the index will not be available to the query planner/engine
during that time period. Are there other ways in which the table (and other
indices) are affected or locked?
Cheers,
jason
Hi,
On 10 January 2012 09:16, Jason Buberel <jason@altosresearch.com> wrote:
We have lots of them, they are much smaller than the tables, and that will
allow us to do the migrations more incrementally.
In your case I would keep data and indexes on different table spaces
(and lower random_page_cost).
One area where the documentation is not very detailed - What are the side
effects and/or constraints put in place while an index is being moved? I
assume that the index will not be available to the query planner/engine
during that time period. Are there other ways in which the table (and other
indices) are affected or locked?
yes, you are right there is not too much about "alter index" locking
in the docs. When I did this last time (PG 8.4) 'alter index' acquired
'ACCESS EXCLUSIVE' lock.
--
Ondrej Ivanic
(ondrej.ivanic@gmail.com)
2012/1/10 Ondrej Ivanič <ondrej.ivanic@gmail.com>:
One area where the documentation is not very detailed - What are the side
effects and/or constraints put in place while an index is being moved? I
assume that the index will not be available to the query planner/engine
during that time period. Are there other ways in which the table (and other
indices) are affected or locked?yes, you are right there is not too much about "alter index" locking
in the docs. When I did this last time (PG 8.4) 'alter index' acquired
'ACCESS EXCLUSIVE' lock.
To avoid it I usually do CREATE INDEX ... TABLESPACE ... / DROP INDEX
... / ALTER INDEX ... RENAME ...
--
Ondrej Ivanic
(ondrej.ivanic@gmail.com)--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sergey Konoplev
Blog: http://gray-hemp.blogspot.com
LinkedIn: http://ru.linkedin.com/in/grayhemp
JID/GTalk: gray.ru@gmail.com Skype: gray-hemp