Detect when a CREATE INDEX CONCURRENTLY is finished in PostgreSQLDropped an index and recreated it in...
Sauna: Wood does not feel so hot
Identical projects by students at two different colleges: still plagiarism?
What happens if you declare more than $10,000 at the US border?
Is opening a file faster than reading variable content?
Why is quixotic not Quixotic (a proper adjective)?
Was Opportunity's last message to Earth "My battery is low and it's getting dark"?
How to encircle section of matrix in LaTeX?
Is Screenshot Time-tracking Common?
Cryptic cross... with words
How can guns be countered by melee combat without raw-ability or exceptional explanations?
How should I ship cards?
Is it common to refer to someone as "Prof. Dr. [LastName]"?
80-bit collision resistence because of 80-bit x87 registers?
I hate taking lectures, can I still survive in academia?
Why don't reads from /dev/zero count as I/O?
STM32 PWM problem
How do I know my password or backup information is not being shared when creating a new wallet?
How can I portray body horror and still be sensitive to people with disabilities?
Discouraging missile alpha strikes
Why do we divide Permutations to get to Combinations?
What is the reason behind this musical reference to Pinocchio in the Close Encounters main theme?
TikZtree with asymmetric siblings
Is layered encryption more secure than long passwords?
Does changing "sa" password require a SQL restart (in mixed mode)?
Detect when a CREATE INDEX CONCURRENTLY is finished in PostgreSQL
Dropped an index and recreated it in PostgreSQL 9.2, not used anymorePostgreSQL partial index unused when created on a table with existing dataSpeed up COUNT(*) when using an indexQuery planner slow to use newly created index on database under heavy loadIs there a way to show the creation statement for an index in PostgreSQLIn PostgreSQL, can you read from an index during a ALTER INDEX … SET TABLESPACE operation?Postgresql Upgrade from 9.1.9 to 9.4.5 pg_stat_activity.procpid causes failureHow to create some GIN index concurrently in PostgresqlHow to restore a pg_dumpall dump without CREATE INDEX?Can rebuilding an index create fragmentation?
If I create an index CONCURRENTLY in PostgreSQL, how can I see when it is finished?
I am attempting to rebuild indexes to solve index bloat, and I need to keep the old index around for a while until the new one has finished, so I need to know when it's finished.
This is PostgreSQL 9.2/3ish
postgresql index concurrency
edited Mar 11 '16 at 11:44
dezso
22.2k116096
asked Mar 11 '16 at 10:18
RoryRory
21249
add a comment |
If I create an index CONCURRENTLY in PostgreSQL, how can I see when it is finished?
I am attempting to rebuild indexes to solve index bloat, and I need to keep the old index around for a while until the new one has finished, so I need to know when it's finished.
This is PostgreSQL 9.2/3ish
postgresql index concurrency
edited Mar 11 '16 at 11:44
dezso
22.2k116096
asked Mar 11 '16 at 10:18
RoryRory
21249
9
If I'm not mistaken,concurrentlydoes not mean "in the background". It will still run synchronously - it simply does not take locks on the table to allow concurrent modification to the underlying table. So as soon as yourcreate indexis finished, the index is built
– a_horse_with_no_name
Mar 11 '16 at 10:23
@a_horse_with_no_name ah yes, that seems to be correct. If you add this as an actual answer, I can accept it.
– Rory
Mar 11 '16 at 13:11
add a comment |
If I create an index CONCURRENTLY in PostgreSQL, how can I see when it is finished?
I am attempting to rebuild indexes to solve index bloat, and I need to keep the old index around for a while until the new one has finished, so I need to know when it's finished.
This is PostgreSQL 9.2/3ish
postgresql index concurrency
edited Mar 11 '16 at 11:44
dezso
22.2k116096
asked Mar 11 '16 at 10:18
RoryRory
21249
If I create an index CONCURRENTLY in PostgreSQL, how can I see when it is finished?
I am attempting to rebuild indexes to solve index bloat, and I need to keep the old index around for a while until the new one has finished, so I need to know when it's finished.
This is PostgreSQL 9.2/3ish
postgresql index concurrency
postgresql index concurrency
edited Mar 11 '16 at 11:44
dezso
22.2k116096
asked Mar 11 '16 at 10:18
RoryRory
21249
edited Mar 11 '16 at 11:44
dezso
22.2k116096
asked Mar 11 '16 at 10:18
RoryRory
21249
edited Mar 11 '16 at 11:44
dezso
22.2k116096
edited Mar 11 '16 at 11:44
dezso
22.2k116096
edited Mar 11 '16 at 11:44
dezso
22.2k116096
22.2k116096
asked Mar 11 '16 at 10:18
RoryRory
21249
asked Mar 11 '16 at 10:18
RoryRory
21249
asked Mar 11 '16 at 10:18
RoryRory
21249
21249
9
If I'm not mistaken,concurrentlydoes not mean "in the background". It will still run synchronously - it simply does not take locks on the table to allow concurrent modification to the underlying table. So as soon as yourcreate indexis finished, the index is built
– a_horse_with_no_name
Mar 11 '16 at 10:23
@a_horse_with_no_name ah yes, that seems to be correct. If you add this as an actual answer, I can accept it.
– Rory
Mar 11 '16 at 13:11
add a comment |
9
If I'm not mistaken,concurrentlydoes not mean "in the background". It will still run synchronously - it simply does not take locks on the table to allow concurrent modification to the underlying table. So as soon as yourcreate indexis finished, the index is built
– a_horse_with_no_name
Mar 11 '16 at 10:23
@a_horse_with_no_name ah yes, that seems to be correct. If you add this as an actual answer, I can accept it.
– Rory
Mar 11 '16 at 13:11
9
9
If I'm not mistaken,
concurrently does not mean "in the background". It will still run synchronously - it simply does not take locks on the table to allow concurrent modification to the underlying table. So as soon as your create index is finished, the index is built– a_horse_with_no_name
Mar 11 '16 at 10:23
If I'm not mistaken,
concurrently does not mean "in the background". It will still run synchronously - it simply does not take locks on the table to allow concurrent modification to the underlying table. So as soon as your create index is finished, the index is built– a_horse_with_no_name
Mar 11 '16 at 10:23
@a_horse_with_no_name ah yes, that seems to be correct. If you add this as an actual answer, I can accept it.
– Rory
Mar 11 '16 at 13:11
@a_horse_with_no_name ah yes, that seems to be correct. If you add this as an actual answer, I can accept it.
– Rory
Mar 11 '16 at 13:11
add a comment |
1 Answer
1
active
oldest
votes
You can get list of invalid indexes.
SELECT * FROM pg_class, pg_index WHERE pg_index.indisvalid = false AND pg_index.indexrelid = pg_class.oid;
If you see your index in this query it means the index won't work and you have to recreate index.
Don't do REINDEX. It won’t re-create the index concurrently, it will lock the table for writes while the index is being created, the best solution is to drop the invalid index and recreate it CONCURRENTLY
answered 3 hours ago
Zaytsev DmitryZaytsev Dmitry
1114
New contributor
Zaytsev Dmitry is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f131945%2fdetect-when-a-create-index-concurrently-is-finished-in-postgresql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can get list of invalid indexes.
SELECT * FROM pg_class, pg_index WHERE pg_index.indisvalid = false AND pg_index.indexrelid = pg_class.oid;
If you see your index in this query it means the index won't work and you have to recreate index.
Don't do REINDEX. It won’t re-create the index concurrently, it will lock the table for writes while the index is being created, the best solution is to drop the invalid index and recreate it CONCURRENTLY
answered 3 hours ago
Zaytsev DmitryZaytsev Dmitry
1114
New contributor
Zaytsev Dmitry is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
You can get list of invalid indexes.
SELECT * FROM pg_class, pg_index WHERE pg_index.indisvalid = false AND pg_index.indexrelid = pg_class.oid;
If you see your index in this query it means the index won't work and you have to recreate index.
Don't do REINDEX. It won’t re-create the index concurrently, it will lock the table for writes while the index is being created, the best solution is to drop the invalid index and recreate it CONCURRENTLY
answered 3 hours ago
Zaytsev DmitryZaytsev Dmitry
1114
New contributor
Zaytsev Dmitry is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
You can get list of invalid indexes.
SELECT * FROM pg_class, pg_index WHERE pg_index.indisvalid = false AND pg_index.indexrelid = pg_class.oid;
If you see your index in this query it means the index won't work and you have to recreate index.
Don't do REINDEX. It won’t re-create the index concurrently, it will lock the table for writes while the index is being created, the best solution is to drop the invalid index and recreate it CONCURRENTLY
answered 3 hours ago
Zaytsev DmitryZaytsev Dmitry
1114
New contributor
Zaytsev Dmitry is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
You can get list of invalid indexes.
SELECT * FROM pg_class, pg_index WHERE pg_index.indisvalid = false AND pg_index.indexrelid = pg_class.oid;
If you see your index in this query it means the index won't work and you have to recreate index.
Don't do REINDEX. It won’t re-create the index concurrently, it will lock the table for writes while the index is being created, the best solution is to drop the invalid index and recreate it CONCURRENTLY
answered 3 hours ago
Zaytsev DmitryZaytsev Dmitry
1114
New contributor
Zaytsev Dmitry is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 3 hours ago
Zaytsev DmitryZaytsev Dmitry
1114
New contributor
Zaytsev Dmitry is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 3 hours ago
Zaytsev DmitryZaytsev Dmitry
1114
answered 3 hours ago
Zaytsev DmitryZaytsev Dmitry
1114
1114
New contributor
Zaytsev Dmitry is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Zaytsev Dmitry is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Zaytsev Dmitry is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f131945%2fdetect-when-a-create-index-concurrently-is-finished-in-postgresql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
9
If I'm not mistaken,
concurrentlydoes not mean "in the background". It will still run synchronously - it simply does not take locks on the table to allow concurrent modification to the underlying table. So as soon as yourcreate indexis finished, the index is built– a_horse_with_no_name
Mar 11 '16 at 10:23
@a_horse_with_no_name ah yes, that seems to be correct. If you add this as an actual answer, I can accept it.
– Rory
Mar 11 '16 at 13:11