Обсуждение: [GENERAL]
Dear All,
Thanks for your support.
I am using postgresql 9.3.6 on RHEL6.6 machine.
In my production environment, Some script are executing automatically through cron.
These script are performing insert and update operation.
Also, every hour,i am performing VACUUM and REINDEX operation on table.
Is there any solution available to perform only REINDEX operation and other insert or updation operations from other script will not perform during REINDEX execution?
OR
Preferably, during REINDEX operation, other script execution for insert/update may wait until reindex will not completed.
Regards,
Yogesh
On 21 December 2016 at 09:59, Yogesh Sharma <yogeshraj95@gmail.com> wrote: > Also, every hour,i am performing VACUUM and REINDEX operation on table. Why are you running REINDEX every hour? That's a very unusual thing to do, you'd need a pretty good reason for that. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
Dear Alban,
In my production system, there are lot of read write operation performed every hour.
So, i am thinking, if i can add check during REINDEX operation nothing update and insert operation performed.
Is it possible?
Regards,
Yogesh
On Wednesday, December 21, 2016, Alban Hertroys <haramrae@gmail.com> wrote:
On Wednesday, December 21, 2016, Alban Hertroys <haramrae@gmail.com> wrote:
On 21 December 2016 at 09:59, Yogesh Sharma <yogeshraj95@gmail.com> wrote:
> Also, every hour,i am performing VACUUM and REINDEX operation on table.
Why are you running REINDEX every hour? That's a very unusual thing to
do, you'd need a pretty good reason for that.
--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.
if you want to reduce the impact of reindex in your inserts and updates operations why don't you try drop index and CREATE INDEX CONCURRENTLY.
As Alban said you I don't recommend you to execute reindex every hour, at least try to reduce the scope of the reindex to the minimal object.
2016-12-21 12:51 GMT+01:00 Yogesh Sharma <yogeshraj95@gmail.com>:
Dear Alban,In my production system, there are lot of read write operation performed every hour.So, i am thinking, if i can add check during REINDEX operation nothing update and insert operation performed.Is it possible?Regards,Yogesh
On Wednesday, December 21, 2016, Alban Hertroys <haramrae@gmail.com> wrote:On 21 December 2016 at 09:59, Yogesh Sharma <yogeshraj95@gmail.com> wrote:
> Also, every hour,i am performing VACUUM and REINDEX operation on table.
Why are you running REINDEX every hour? That's a very unusual thing to
do, you'd need a pretty good reason for that.
--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.
Dear All,
On Wednesday, December 21, 2016, Jaime Soler <jaime.soler@gmail.com> wrote:
I understood below point and i will do the same on my system.
Can i close db session before REINDEX operation and again start db session after REINDEX completed?
Regards,
Yogesh
On Wednesday, December 21, 2016, Jaime Soler <jaime.soler@gmail.com> wrote:
if you want to reduce the impact of reindex in your inserts and updates operations why don't you try drop index and CREATE INDEX CONCURRENTLY.As Alban said you I don't recommend you to execute reindex every hour, at least try to reduce the scope of the reindex to the minimal object.2016-12-21 12:51 GMT+01:00 Yogesh Sharma <yogeshraj95@gmail.com>:Dear Alban,In my production system, there are lot of read write operation performed every hour.So, i am thinking, if i can add check during REINDEX operation nothing update and insert operation performed.Is it possible?Regards,Yogesh
On Wednesday, December 21, 2016, Alban Hertroys <haramrae@gmail.com> wrote:On 21 December 2016 at 09:59, Yogesh Sharma <yogeshraj95@gmail.com> wrote:
> Also, every hour,i am performing VACUUM and REINDEX operation on table.
Why are you running REINDEX every hour? That's a very unusual thing to
do, you'd need a pretty good reason for that.
--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.
On 21 December 2016 at 12:51, Yogesh Sharma <yogeshraj95@gmail.com> wrote: > Dear Alban, > > In my production system, there are lot of read write operation performed > every hour. That does not explain why you're performing REINDEX on that system; rather, it explains why you should NOT be performing REINDEX. VACUUM is what you should be using. You can VACUUM either using autovacuum with a sufficiently high frequency that it doesn't fall behind too much, or by manually running VACUUM ANALYSE on specific tables after batch operations. Which choice suits you best depends on your workload. In batches it is often also useful to run (plain) ANALYSE every so often on the relevant tables to update the statistics, but only when the batch process is not insert-only. > So, i am thinking, if i can add check during REINDEX operation nothing > update and insert operation performed. > Is it possible? I have no idea what you're saying. > On Wednesday, December 21, 2016, Alban Hertroys <haramrae@gmail.com> wrote: >> >> On 21 December 2016 at 09:59, Yogesh Sharma <yogeshraj95@gmail.com> wrote: >> > Also, every hour,i am performing VACUUM and REINDEX operation on table. >> >> Why are you running REINDEX every hour? That's a very unusual thing to >> do, you'd need a pretty good reason for that. >> >> -- >> If you can't see the forest for the trees, >> Cut the trees and you'll see there is no forest. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
well if you would like to cancel o terminate a session before start inserting or updating data , you can use pg_cancel_backend or pg_terminate_backend using pid returning from select pid pg_stat_activity where state='active' ..
2016-12-21 13:28 GMT+01:00 Yogesh Sharma <yogeshraj95@gmail.com>:
Dear All,I understood below point and i will do the same on my system.Can i close db session before REINDEX operation and again start db session after REINDEX completed?Regards,Yogesh
On Wednesday, December 21, 2016, Jaime Soler <jaime.soler@gmail.com> wrote:if you want to reduce the impact of reindex in your inserts and updates operations why don't you try drop index and CREATE INDEX CONCURRENTLY.As Alban said you I don't recommend you to execute reindex every hour, at least try to reduce the scope of the reindex to the minimal object.2016-12-21 12:51 GMT+01:00 Yogesh Sharma <yogeshraj95@gmail.com>:Dear Alban,In my production system, there are lot of read write operation performed every hour.So, i am thinking, if i can add check during REINDEX operation nothing update and insert operation performed.Is it possible?Regards,Yogesh
On Wednesday, December 21, 2016, Alban Hertroys <haramrae@gmail.com> wrote:On 21 December 2016 at 09:59, Yogesh Sharma <yogeshraj95@gmail.com> wrote:
> Also, every hour,i am performing VACUUM and REINDEX operation on table.
Why are you running REINDEX every hour? That's a very unusual thing to
do, you'd need a pretty good reason for that.
--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.