No title
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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:
Show quoted text
On 21 December 2016 at 09:59, Yogesh Sharma <yogeshraj95@gmail.com
<javascript:;>> 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>:
Show quoted text
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,
YogeshOn 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,
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:
Show quoted text
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
<javascript:_e(%7B%7D,'cvml','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,
YogeshOn Wednesday, December 21, 2016, Alban Hertroys <haramrae@gmail.com
<javascript:_e(%7B%7D,'cvml','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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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>:
Show quoted text
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,
YogeshOn 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,
YogeshOn 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.