Does a Postgres pg_restore restructure the table in any significant way?
up vote
1
down vote
favorite
We've noticed that, when we take a pg_dump of a table, the restored table performs much better than the original. We haven't been able to figure out why.
Note that we vaccuum regularly, meaning the original table has been vacuumed. And so I don't think that's the cause of the difference.
Does anyone know what happens during a pg restore? Are the table rows maybe being re-ordered (as happens during a cluster)?
We're using Postgres 9.3.
postgresql
asked Nov 17 at 17:34
Suzette S
62
New contributor
Suzette S 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 |
up vote
1
down vote
favorite
We've noticed that, when we take a pg_dump of a table, the restored table performs much better than the original. We haven't been able to figure out why.
Note that we vaccuum regularly, meaning the original table has been vacuumed. And so I don't think that's the cause of the difference.
Does anyone know what happens during a pg restore? Are the table rows maybe being re-ordered (as happens during a cluster)?
We're using Postgres 9.3.
postgresql
asked Nov 17 at 17:34
Suzette S
62
New contributor
Suzette S is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
Possible duplicate of PostgreSQL: VACCUM FULL vs pg_dump and restore
– Raymond Nijland
Nov 17 at 17:36
Not a dup. As mentioned in the original post, we vacuum regularly. And so that doesn't account for the diff.
– Suzette S
Nov 17 at 17:46
Did you read the answerd answer from the link i've provided? "Assuming that you create a new database and restore the dump, the effect of both operations would be pretty much the same." .. Besides you can setup auto vaccuum or setup FULL vaccuum which do you use.. And what are you doing with your table is it a table with alot of writes or a table with alot or reads or is het mixed..
– Raymond Nijland
Nov 17 at 17:53
Yes, I read the answer. Don't see how it relates to my question. Actually, sorry. My mistake. Misread the answer from the linked question. That could be it. If a restore is doing a vacuum full, then that would explain the difference.
– Suzette S
Nov 17 at 18:14
Don't use PostgreSQL 9.3. As to your question, please provideEXPLAIN (ANALYZE, BUFFERS)output for the slow and the fast query.pg_restoredoesn't optimize the table any more thanVACUUM (FULL)does.
– Laurenz Albe
Nov 18 at 11:43
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
We've noticed that, when we take a pg_dump of a table, the restored table performs much better than the original. We haven't been able to figure out why.
Note that we vaccuum regularly, meaning the original table has been vacuumed. And so I don't think that's the cause of the difference.
Does anyone know what happens during a pg restore? Are the table rows maybe being re-ordered (as happens during a cluster)?
We're using Postgres 9.3.
postgresql
asked Nov 17 at 17:34
Suzette S
62
New contributor
Suzette S is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
We've noticed that, when we take a pg_dump of a table, the restored table performs much better than the original. We haven't been able to figure out why.
Note that we vaccuum regularly, meaning the original table has been vacuumed. And so I don't think that's the cause of the difference.
Does anyone know what happens during a pg restore? Are the table rows maybe being re-ordered (as happens during a cluster)?
We're using Postgres 9.3.
postgresql
postgresql
asked Nov 17 at 17:34
Suzette S
62
New contributor
Suzette S is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Nov 17 at 17:34
Suzette S
62
New contributor
Suzette S is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited Nov 17 at 17:48
asked Nov 17 at 17:34
Suzette S
62
New contributor
Suzette S is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Nov 17 at 17:34
Suzette S
62
asked Nov 17 at 17:34
Suzette S
62
62
New contributor
Suzette S is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Suzette S is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Suzette S is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
Possible duplicate of PostgreSQL: VACCUM FULL vs pg_dump and restore
– Raymond Nijland
Nov 17 at 17:36
Not a dup. As mentioned in the original post, we vacuum regularly. And so that doesn't account for the diff.
– Suzette S
Nov 17 at 17:46
Did you read the answerd answer from the link i've provided? "Assuming that you create a new database and restore the dump, the effect of both operations would be pretty much the same." .. Besides you can setup auto vaccuum or setup FULL vaccuum which do you use.. And what are you doing with your table is it a table with alot of writes or a table with alot or reads or is het mixed..
– Raymond Nijland
Nov 17 at 17:53
Yes, I read the answer. Don't see how it relates to my question. Actually, sorry. My mistake. Misread the answer from the linked question. That could be it. If a restore is doing a vacuum full, then that would explain the difference.
– Suzette S
Nov 17 at 18:14
Don't use PostgreSQL 9.3. As to your question, please provideEXPLAIN (ANALYZE, BUFFERS)output for the slow and the fast query.pg_restoredoesn't optimize the table any more thanVACUUM (FULL)does.
– Laurenz Albe
Nov 18 at 11:43
add a comment |
1
Possible duplicate of PostgreSQL: VACCUM FULL vs pg_dump and restore
– Raymond Nijland
Nov 17 at 17:36
Not a dup. As mentioned in the original post, we vacuum regularly. And so that doesn't account for the diff.
– Suzette S
Nov 17 at 17:46
Did you read the answerd answer from the link i've provided? "Assuming that you create a new database and restore the dump, the effect of both operations would be pretty much the same." .. Besides you can setup auto vaccuum or setup FULL vaccuum which do you use.. And what are you doing with your table is it a table with alot of writes or a table with alot or reads or is het mixed..
– Raymond Nijland
Nov 17 at 17:53
Yes, I read the answer. Don't see how it relates to my question. Actually, sorry. My mistake. Misread the answer from the linked question. That could be it. If a restore is doing a vacuum full, then that would explain the difference.
– Suzette S
Nov 17 at 18:14
Don't use PostgreSQL 9.3. As to your question, please provideEXPLAIN (ANALYZE, BUFFERS)output for the slow and the fast query.pg_restoredoesn't optimize the table any more thanVACUUM (FULL)does.
– Laurenz Albe
Nov 18 at 11:43
1
1
Possible duplicate of PostgreSQL: VACCUM FULL vs pg_dump and restore
– Raymond Nijland
Nov 17 at 17:36
Possible duplicate of PostgreSQL: VACCUM FULL vs pg_dump and restore
– Raymond Nijland
Nov 17 at 17:36
Not a dup. As mentioned in the original post, we vacuum regularly. And so that doesn't account for the diff.
– Suzette S
Nov 17 at 17:46
Not a dup. As mentioned in the original post, we vacuum regularly. And so that doesn't account for the diff.
– Suzette S
Nov 17 at 17:46
Did you read the answerd answer from the link i've provided? "Assuming that you create a new database and restore the dump, the effect of both operations would be pretty much the same." .. Besides you can setup auto vaccuum or setup FULL vaccuum which do you use.. And what are you doing with your table is it a table with alot of writes or a table with alot or reads or is het mixed..
– Raymond Nijland
Nov 17 at 17:53
Did you read the answerd answer from the link i've provided? "Assuming that you create a new database and restore the dump, the effect of both operations would be pretty much the same." .. Besides you can setup auto vaccuum or setup FULL vaccuum which do you use.. And what are you doing with your table is it a table with alot of writes or a table with alot or reads or is het mixed..
– Raymond Nijland
Nov 17 at 17:53
Yes, I read the answer. Don't see how it relates to my question. Actually, sorry. My mistake. Misread the answer from the linked question. That could be it. If a restore is doing a vacuum full, then that would explain the difference.
– Suzette S
Nov 17 at 18:14
Yes, I read the answer. Don't see how it relates to my question. Actually, sorry. My mistake. Misread the answer from the linked question. That could be it. If a restore is doing a vacuum full, then that would explain the difference.
– Suzette S
Nov 17 at 18:14
Don't use PostgreSQL 9.3. As to your question, please provide
EXPLAIN (ANALYZE, BUFFERS) output for the slow and the fast query. pg_restore doesn't optimize the table any more than VACUUM (FULL) does.– Laurenz Albe
Nov 18 at 11:43
Don't use PostgreSQL 9.3. As to your question, please provide
EXPLAIN (ANALYZE, BUFFERS) output for the slow and the fast query. pg_restore doesn't optimize the table any more than VACUUM (FULL) does.– Laurenz Albe
Nov 18 at 11:43
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Suzette S is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
Suzette S is a new contributor. Be nice, and check out our Code of Conduct.
Suzette S is a new contributor. Be nice, and check out our Code of Conduct.
Suzette S is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
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%2fstackoverflow.com%2fquestions%2f53353774%2fdoes-a-postgres-pg-restore-restructure-the-table-in-any-significant-way%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
1
Possible duplicate of PostgreSQL: VACCUM FULL vs pg_dump and restore
– Raymond Nijland
Nov 17 at 17:36
Not a dup. As mentioned in the original post, we vacuum regularly. And so that doesn't account for the diff.
– Suzette S
Nov 17 at 17:46
Did you read the answerd answer from the link i've provided? "Assuming that you create a new database and restore the dump, the effect of both operations would be pretty much the same." .. Besides you can setup auto vaccuum or setup FULL vaccuum which do you use.. And what are you doing with your table is it a table with alot of writes or a table with alot or reads or is het mixed..
– Raymond Nijland
Nov 17 at 17:53
Yes, I read the answer. Don't see how it relates to my question. Actually, sorry. My mistake. Misread the answer from the linked question. That could be it. If a restore is doing a vacuum full, then that would explain the difference.
– Suzette S
Nov 17 at 18:14
Don't use PostgreSQL 9.3. As to your question, please provide
EXPLAIN (ANALYZE, BUFFERS)output for the slow and the fast query.pg_restoredoesn't optimize the table any more thanVACUUM (FULL)does.– Laurenz Albe
Nov 18 at 11:43