Thread: [GENERAL] Fwd: Request to share approach during REINDEX operation

[GENERAL] Fwd: Request to share approach during REINDEX operation

From
Yogesh Sharma
Date:
Adding subject line....


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

Re: [GENERAL] Fwd: Request to share approach during REINDEX operation

From
Vick Khera
Date:
On Wed, Dec 21, 2016 at 4:02 AM, Yogesh Sharma <yogeshraj95@gmail.com> wrote:
> Also, every hour,i am performing VACUUM and REINDEX operation on table.

Why?


Re: [GENERAL] Request to share approach during REINDEX operation

From
Yogesh Sharma
Date:
Dear Vick,

I have responded on previously mail.

In my production system, there are lot of read write operation performed every hour.

Regards,
yogesh 
On Wednesday, December 21, 2016, Vick Khera <vivek@khera.org> wrote:
On Wed, Dec 21, 2016 at 4:02 AM, Yogesh Sharma <yogeshraj95@gmail.com> wrote:
> Also, every hour,i am performing VACUUM and REINDEX operation on table.

Why?

Re: [GENERAL] Request to share approach during REINDEX operation

From
Steven Winfield
Date:

> In my production system, there are lot of read write operation performed every hour.

 

Apologies if this sounds patronising but I just wanted to check - you know that indexes are updated automatically when write operations occur, right?





This email is confidential. If you are not the intended recipient, please advise us immediately and delete this message. The registered name of Cantab- part of GAM Systematic is Cantab Capital Partners LLP. See - http://www.gam.com/en/Legal/Email+disclosures+EU for further information on confidentiality, the risks of non-secure electronic communication, and certain disclosures which we are required to make in accordance with applicable legislation and regulations. If you cannot access this link, please notify us by reply message and we will send the contents to you.

Re: [GENERAL] Request to share approach during REINDEX operation

From
Yogesh Sharma
Date:
Dear Steven,
Thanks for your reply.

Yes. You are correct.

Regards,
Yogesh

On Wednesday, December 21, 2016, Steven Winfield <Steven.Winfield@cantabcapital.com> wrote:

> In my production system, there are lot of read write operation performed every hour.

 

Apologies if this sounds patronising but I just wanted to check - you know that indexes are updated automatically when write operations occur, right?





This email is confidential. If you are not the intended recipient, please advise us immediately and delete this message. The registered name of Cantab- part of GAM Systematic is Cantab Capital Partners LLP. See - http://www.gam.com/en/Legal/Email+disclosures+EU for further information on confidentiality, the risks of non-secure electronic communication, and certain disclosures which we are required to make in accordance with applicable legislation and regulations. If you cannot access this link, please notify us by reply message and we will send the contents to you.

Re: [GENERAL] Request to share approach during REINDEX operation

From
Vick Khera
Date:

On Wed, Dec 21, 2016 at 8:43 AM, Yogesh Sharma <yogeshraj95@gmail.com> wrote:
I have responded on previously mail.


apparently you started two separate threads...
 
In my production system, there are lot of read write operation performed every hour.

That still doesn't answer why you feel you need to run reindex every hour. Do you have some measurements that show the index is bloating, or not performing well?

I have a database that does hundreds of millions of insert/update per day, and I *never* run reindex because it is unnecessary.

Re: [GENERAL] Request to share approach during REINDEX operation

From
Steven Winfield
Date:

>>> In my production system, there are lot of read write operation performed every hour.

>>Apologies if this sounds patronising but I just wanted to check - you know that indexes are updated automatically when write operations occur, right?

>Yes. You are correct.


OK - I think the folks here are having a hard time figuring out why you are reindexing so frequently, especially during times when the database is already under load.

What benefit does reindexing give you? Have you tried not doing it? The same goes for vacuuming - have you tried just letting autovacuum do its job, or tweaking its parameters to meet your needs?

 

(Also, list etiquette here seems to be to reply after the previous post, rather than “top-post”)

 

Steve.





This email is confidential. If you are not the intended recipient, please advise us immediately and delete this message. The registered name of Cantab- part of GAM Systematic is Cantab Capital Partners LLP. See - http://www.gam.com/en/Legal/Email+disclosures+EU for further information on confidentiality, the risks of non-secure electronic communication, and certain disclosures which we are required to make in accordance with applicable legislation and regulations. If you cannot access this link, please notify us by reply message and we will send the contents to you.