Обсуждение: vacuuming and reindexing

Hi Everybody,

I have a table that I add rows every evening.
A rule I have for this table is that no row
ever gets deleted.  It has 600+ million rows.

The last time I did it, it took nearly 4 hours
to vacuum and 13 hours 40 minutes to reindex.

My rudimental understanding of vacuuming is
that when the rows get "deleted" it is only
marked as "deleted" but the data is left alone
until the act of vacuuming takes place.  The
vacuuming really gets rid of rows and pushes
the data in such the way there is no "hole."
And therefore after vacuuming, it is necessary
to reindex (and analyze) the table.

Is this a correct understanding?

If it is (and as new rows get added new indexes
are also built for the new rows), the fact that
there is no deletion means there is no necessity
for vacuuming this particular table?

I appreciate any thoughts on this matter.

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu

do you also update rows of that table?

because that action delete old one's and insert new one's..

if you are just inserting rows and not updating or deleting, i think is correct your point.


Regards,
--
"Sin desafíos la vida es una rutina, una lenta agonía"

     Diógenes Caraballo

Hi Diógenes,

Thanks for your response.

> do you also update rows of that table?
> because that action delete old one's and insert new one's..

Yes, I do.  Here's the definition of the table:

                                            Table "public.genotype"
     Column      |            Type             |                           Modifiers                          
-----------------+-----------------------------+---------------------------------------------------------------
 genotypeid      | integer                     | not null default nextval('genotype_genotypeid_seq'::regclass)
 subjectid       | integer                     |
 markerid        | integer                     |
 allele1id       | integer                     |
 allele2id       | integer                     |
 datecreated     | timestamp without time zone | not null
 datereplaced    | timestamp without time zone | not null
 ignore          | character(1)                | not null default 'N'::bpchar
 inconsistent    | character(1)                | not null default 'N'::bpchar
 sourcetablename | character varying           | not null
 sourceid        | character varying           | not null
Indexes:
    "genotype_pkey" PRIMARY KEY, btree (genotypeid, datecreated)
    "genotype_genotypeid_idx" btree (genotypeid)
    "genotype_markerid_idx" btree (markerid)
    "genotype_source_idx" btree (sourceid, sourcetablename)
    "genotype_subjectid_idx" btree (subjectid)

The column datereplaced gets updated when new and/or conflicting
data arrives.  So much for my bright idea...

Thanks just the same.

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu

-----Original Message-----
From: Diogenes Caraballo [mailto:diogns@gmail.com]
Sent: Sat 3/21/2009 6:31 AM
To: Tena Sakai
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] vacuuming and reindexing

do you also update rows of that table?

because that action delete old one's and insert new one's..

if you are just inserting rows and not updating or deleting, i think is
correct your point.


Regards,
--
"Sin desafíos la vida es una rutina, una lenta agonía"

     Diógenes Caraballo


On Fri, Mar 20, 2009 at 18:03, Tena Sakai <tsakai@gallo.ucsf.edu> wrote:

>  Hi Everybody,
>
> I have a table that I add rows every evening.
> A rule I have for this table is that no row
> ever gets deleted.  It has 600+ million rows.
>
> The last time I did it, it took nearly 4 hours
> to vacuum and 13 hours 40 minutes to reindex.
>
> My rudimental understanding of vacuuming is
> that when the rows get "deleted" it is only
> marked as "deleted" but the data is left alone
> until the act of vacuuming takes place.  The
> vacuuming really gets rid of rows and pushes
> the data in such the way there is no "hole."
> And therefore after vacuuming, it is necessary
> to reindex (and analyze) the table.
>
> Is this a correct understanding?
>
> If it is (and as new rows get added new indexes
> are also built for the new rows), the fact that
> there is no deletion means there is no necessity
> for vacuuming this particular table?
>
> I appreciate any thoughts on this matter.
>
> Regards,
>
> Tena Sakai
> tsakai@gallo.ucsf.edu
>
>
>
>