From: Holger Jakobs <holger@jakobs.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Index
Date: Sun, 10 Mar 2024 19:32:08 +0100
Message-ID: <d227ce10-a52f-6747-9138-9ed01f83ef63@jakobs.com> (raw)
In-Reply-To: <CAJk5AtY_CH+393gCf_4T=8Wcus6k10ZWFqtiwDJAH0xcopWSUA@mail.gmail.com>
References: <CAJk5AtZqnqPdoajrRM4sAEjAP=zM5d7bQ1=VY_EKJ=9vV+H+Gg@mail.gmail.com>
<d6fb9d13-c682-cb87-804c-fd8a0ed50c84@jakobs.com>
<CAJk5AtZ-DmaFXo-btJERheWivy0_S7zr8rZCpeXFtbZiCxe1GA@mail.gmail.com>
<CANzqJaCYhF-pPju=EJh_4Tq3JZOiUMw_ttXFbUW33YYgEUYVFg@mail.gmail.com>
<CAJk5AtY_CH+393gCf_4T=8Wcus6k10ZWFqtiwDJAH0xcopWSUA@mail.gmail.com>
Am 10.03.24 um 19:21 schrieb Rajesh Kumar:
> If we are deleting rows often.....I am talking ABT the possibility of
> index bloat.
>
> On Sun, 10 Mar 2024, 23:40 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
>
> What does "if index is not managed" mean?
>
> On Sun, Mar 10, 2024 at 9:11 AM Rajesh Kumar
> <rajeshkumar.dba09@gmail.com> wrote:
>
> What will happen if index is not managed in this case?
>
> On Sun, 10 Mar 2024, 17:20 Holger Jakobs, <holger@jakobs.com>
> wrote:
>
> Am 10.03.24 um 12:19 schrieb Rajesh Kumar:
> > Hi ,
> >
> > I have a table where size is increasing daily. In that
> table only
> > inserts and deletes happens. This table is referencing
> 15 other
> > tables. We have given delete cascade, so if a row is
> deleted in parent
> > table, the matching records will also be deleted in
> those child tables.
> >
> > It is taking lot of time to delete. So, I am planning to
> create index
> > on those child tables.
> >
> > My question is, apart from inserts, I am only going to
> delete records
> > from those tables. How index improves performance for
> deleting
> > records? How index is affected or how to do index
> management in this case?
>
> Hi,
>
> Since every single row which is going to be deleted in the
> child tables
> has to be found, an index can be of great help here.
>
> Actually, in most (not all!) cases an index on foreign key
> columns makes
> sense.
>
> Regards,
>
> Holger
>
> --
> Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
>
Are your deleting arbitrary rows - or are the data timeseries, where you
delete all data of weeks or months?
If so, time-partition your tables and drop partitions when necessary.
If not, tune autovacuum or vacuum full your tables and indexes every now
and then. Or re-index concurrently sometimes.
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
chat with me on Signal messenger
<https://signal.me/#eu/GpIP259bBne_JiBYJFvbZCLYJELhB-92nJfncACL1LUeBOphfXFFgLYYVXv_AGhg;
Attachments:
[image/png] yN6KW349IqGU3o59.png (551B, ../d227ce10-a52f-6747-9138-9ed01f83ef63@jakobs.com/3-yN6KW349IqGU3o59.png)
download | view image
[application/pgp-signature] OpenPGP_signature (202B, ../d227ce10-a52f-6747-9138-9ed01f83ef63@jakobs.com/4-OpenPGP_signature)
download
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 using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: holger@jakobs.com, pgsql-admin@lists.postgresql.org
Subject: Re: Index
In-Reply-To: <d227ce10-a52f-6747-9138-9ed01f83ef63@jakobs.com>
* 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