From: Sean Davis <sdavis2@mail.nih.gov>
To: Casey T. Deccio <ctdecci@sandia.gov>
Cc: pgsql-sql@postgresql.org
Subject: Re: Building a database from a flat file
Date: Thu, 3 Mar 2005 08:23:55 -0500
Message-ID: <8014dca0db521733863bb7967ce4be95@mail.nih.gov> (raw)
In-Reply-To: <1109808921.12430.75.camel@boomerang.ran.sandia.gov>
References: <1109808921.12430.75.camel@boomerang.ran.sandia.gov>
On Mar 2, 2005, at 7:15 PM, Casey T. Deccio wrote:
> A database I am currently using is built and updated periodically from
> a
> flat csv file (The situation is rather unfortunate, but that's all I
> have right now). The schema I use is more complex than the flat file,
> so I follow a process to populate the tables with the data from the
> file. First I slurp the whole file into one temporary table, whose
> columns correspond to the columns in the file. Then I DELETE all the
> existing rows from the tables in the schema and perform a series of
> queries on that table to INSERT and UPDATE rows in the tables that are
> in the schema. Then I DELETE the data from the temporary table. I do
> it this way, rather than trying to synchronize it, because of the
> inconsistencies and redundancies in the flat file.
>
> There is more than one problem with this, but the largest is that I
> would like to perform this whole database rebuild within one
> transaction, so other processes that need to access the database can do
> so without noticing the disturbance. However, performing this set of
> events (besides populating the temporary table) within a single
> transaction takes a long time--over an hour in some cases.
>
> What are some suggestions to help improve performance with replacing
> one
> set of data in a schema with another?
Why not rebuild the entire thing in a separate "build" schema then do
only the stuff like copying tables inside the transaction block:
BEGIN;
truncate table1;
truncate table2;
--reset any sequences you feel you need to have reset;
select * into table1 from build.table1;
select * into table2 from build.table2;
vacuum analyze table1;
vacuum analyze table2;
COMMIT;
I haven't tried this method exactly, but building in a separate schema
(expensive) and then doing cheap operations like copying the table into
the working schema should minimize the amount of time you spend inside
the transaction block.
**I don't know what effect this will have on performance of the whole
process, though**.
Sean
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-sql@postgresql.org
Cc: sdavis2@mail.nih.gov, ctdecci@sandia.gov
Subject: Re: Building a database from a flat file
In-Reply-To: <8014dca0db521733863bb7967ce4be95@mail.nih.gov>
* 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