pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedupdating database
4+ messages / 4 participants
[nested] [flat]
* updating database
@ 1998-11-04 16:26 Charles Day <chaday@symix.com>
0 siblings, 0 replies; 4+ messages in thread
From: Charles Day @ 1998-11-04 16:26 UTC (permalink / raw)
To: pgsql-sql
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 also leaves our database useless for several hours starting at
midnight. I thought about using 2 databases, updating one while using the
other, then swapping them every day. I also thought about having one
db.out, then the other in.
Is there a better way to do this?
Thanks in advance.
Charles Day
^ permalink raw reply [nested|flat] 4+ messages in thread
* Updating database
@ 1998-11-05 14:06 Barracuda <cuda_fx@yahoo.com>
0 siblings, 0 replies; 4+ messages in thread
From: Barracuda @ 1998-11-05 14:06 UTC (permalink / raw)
To: pgsql-sql
Please help:)
We have a knowledge base that needs updated every night from a large
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 also leaves our database useless for
several hours starting at midnight. I thought about using 2
databases, updating one while using the other, then swapping them
every day. I also thought about having one db.out, then the other in.
Is there a better way to do this?
Thanks in advance.
Charles Day
_________________________________________________________
DO YOU YAHOO!?
Get your free @yahoo.com address at http://mail.yahoo.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: updating database
@ 1998-11-05 15:24 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 1998-11-05 15:24 UTC (permalink / raw)
To: Charles Day <chaday@symix.com>; +Cc: pgsql-sql@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
^ permalink raw reply [nested|flat] 4+ messages in thread
* RE: [SQL] Updating database
@ 1998-11-05 18:43 Jackson, DeJuan <djackson@cpsgroup.com>
0 siblings, 0 replies; 4+ messages in thread
From: Jackson, DeJuan @ 1998-11-05 18:43 UTC (permalink / raw)
To: Barracuda <cuda_fx@yahoo.com>; pgsql-sql
> Please help:)
>
> We have a knowledge base that needs updated every night from a large
> 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 also leaves our database useless for
> several hours starting at midnight. I thought about using 2
> databases, updating one while using the other, then swapping them
> every day. I also thought about having one db.out, then the other in.
>
> Is there a better way to do this?
>
> Thanks in advance.
>
> Charles Day
>
Why don't you just use another table to COPY the data into. Then at the
end of the copy either
LOCK, DELETE FROM, INSERT INTO, then VACUUM ANALYZE
or
DROP, ALTER to rename the copy, recreate INDEXES, then VACUUM ANALYZE
I'd do a time test to see which uses the least amount of time.
Enjoy,
-DEJ
> _________________________________________________________
> DO YOU YAHOO!?
> Get your free @yahoo.com address at http://mail.yahoo.com
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~1998-11-05 18:43 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-11-04 16:26 updating database Charles Day <chaday@symix.com>
1998-11-05 14:06 Updating database Barracuda <cuda_fx@yahoo.com>
1998-11-05 15:24 Re: updating database Tom Lane <tgl@sss.pgh.pa.us>
1998-11-05 18:43 RE: [SQL] Updating database Jackson, DeJuan <djackson@cpsgroup.com>
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