From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Scott Marlowe <scott.marlowe@gmail.com>
To: growse <nabble@growse.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: How do I bulk insert to a table without affecting read performance on that table?
Date: Thu, 31 Jan 2008 17:06:20 -0600
Message-ID: <47A2000C.EE98.0025.0@wicourts.gov> (raw)
In-Reply-To: <dcc563d10801260539r33c08e45o1932b5c6f2ac2907@mail.gmail.com>
References: <15099164.post@talk.nabble.com>
<dcc563d10801251806r1a1d202anb1da4c30f6be9a09@mail.gmail.com>
<15107074.post@talk.nabble.com>
<dcc563d10801260539r33c08e45o1932b5c6f2ac2907@mail.gmail.com>
>>> On Sat, Jan 26, 2008 at 7:39 AM, in message
<dcc563d10801260539r33c08e45o1932b5c6f2ac2907@mail.gmail.com>, "Scott Marlowe"
<scott.marlowe@gmail.com> wrote:
> On Jan 26, 2008 5:42 AM, growse <nabble@growse.com> wrote:
>> Scott Marlowe-2 wrote:
>> > Can you import to another table then
>> >
>> > begin;
>> > alter table realtable rename to garbage;
>> > alter table loadtable rename to realtable;
>> > commit;
>> >
>> > ?
>>
>> This is a possibility. My question on this is that would an ALTER TABLE real
>> RENAME TO garbage be faster than a DROP TABLE real?
>
> I don't know. They're both pretty fast. I'd do a test, with parallel
> contention on the table and see.
We do something similar (using DROP TABLE) on a weekly cycle.
We get occasional errors, even with the database transaction.
I wonder whether we might dodge them by using the rename, and
then dropping the old table after a brief delay.
-Kevin
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-performance@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, scott.marlowe@gmail.com, nabble@growse.com
Subject: Re: How do I bulk insert to a table without affecting read performance on that table?
In-Reply-To: <47A2000C.EE98.0025.0@wicourts.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