pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Navindren Baskaran <navin@decube.io>
To: pgsql-general@lists.postgresql.org
Subject: Re: Postgres Index and Updates
Date: Thu, 02 Mar 2023 10:07:46 +0100
Message-ID: <2da7581775414e7a9f0c6caa69ac0d602ba70e61.camel@cybertec.at> (raw)
In-Reply-To: <CABNOo1aGiNac1NzodpnQ4VPwvPH0=F9+dMidJrx3d0v4fyBHLg@mail.gmail.com>
References: <CABNOo1aGiNac1NzodpnQ4VPwvPH0=F9+dMidJrx3d0v4fyBHLg@mail.gmail.com>

On Thu, 2023-03-02 at 15:53 +0800, Navindren Baskaran wrote:
> We would like to understand in which scenario an index on a table will be rebuilt.

I assume that you are meaning "updated" or "modified" rather than rebuilt from scratch.

> Example if we have a table, which has two columns and one if it is indexed,
> which of the below operation trigger an index rebuild:
> 
> 1. updating the value of the indexed column.
> 2. updating the value of the non-indexed column.

If the indexed column is updated, a new index entry has to be added.

If the other column is updated, it depends.  If the updated column is not
indexed and there is enough room for the new row version in the same
table block, the index doesn't have to be modified.  Otherwise it is.

See https://www.cybertec-postgresql.com/en/hot-updates-in-postgresql-for-better-performance/

Yours,
Laurenz Albe





view thread (3+ messages)  latest in thread

Message-ID: <2da7581775414e7a9f0c6caa69ac0d602ba70e61.camel@cybertec.at>
Permalink:  ../2da7581775414e7a9f0c6caa69ac0d602ba70e61.camel@cybertec.at/
Also on:    postgresql.org/message-id/2da7581775414e7a9f0c6caa69ac0d602ba70e61.camel@cybertec.at

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: laurenz.albe@cybertec.at, navin@decube.io
  Subject: Re: Postgres Index and Updates
  In-Reply-To: <2da7581775414e7a9f0c6caa69ac0d602ba70e61.camel@cybertec.at>
  References: <CABNOo1aGiNac1NzodpnQ4VPwvPH0=F9+dMidJrx3d0v4fyBHLg@mail.gmail.com> <2da7581775414e7a9f0c6caa69ac0d602ba70e61.camel@cybertec.at>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox