pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Charles Day <chaday@symix.com>
Cc: pgsql-sql@hub.org
Subject: Re: updating database
Date: Thu, 05 Nov 1998 10:24:47 -0500
Message-ID: <12362.910279487@sss.pgh.pa.us> (raw)
In-Reply-To: <199811050400.XAA09995@hub.org>

Charles Day <chaday@symix.com> writes:
> We have a knowledge base that needs updated every night from a text file.
> Currently a script deletes everything from a table and adds the new data.
> This procedure seems to slowly cause the database to loose performance
> daily.

It would, if you neglect to vacuum the table afterward --- vacuum is
needed to reclaim discarded space inside the table.

The fastest way to bulk-load a table in PostgreSQL is:
	DROP table (yes, really)
	CREATE table again
	COPY table from stdin
	CREATE any indexes defined on table
	VACUUM ANALYZE table
Note that the indexes are best built after the loading step,
so that they are computed "en masse" instead of piecemeal as
each tuple is loaded.  You can see examples of this procedure
in the reload scripts generated by pg_dump.

I do, however, wonder whether bulk-loading is the right approach
for you at all.  Surely most of the table rows don't change from
one day to the next?  If you can teach your script to just delete
and add the tuples that actually changed, you'd probably find that
things are faster --- and you wouldn't have the problem of the
database being unusable during the update.

			regards, tom lane



view thread (4+ messages)  latest in thread

Message-ID: <12362.910279487@sss.pgh.pa.us>
Permalink:  ../12362.910279487@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/12362.910279487@sss.pgh.pa.us

 · 

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-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, chaday@symix.com, pgsql-sql@hub.org
  Subject: Re: updating database
  In-Reply-To: <12362.910279487@sss.pgh.pa.us>
  References: <199811050400.XAA09995@hub.org> <12362.910279487@sss.pgh.pa.us>

* 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