Thread: How do I bulk insert to a table without affecting read performance on that table?
Hi, I've got a pg database, and a batch process that generates some metadata to be inserted into one of the tables. Every 15 minutes or so, the batch script re-calculates the meta data (600,000 rows), dumps it to file, and then does a TRUNCATE table followed by a COPY to import that file into the table. The problem is, that whilst this process is happening, other queries against this table time out. I've tried to copy into a temp table before doing an "INSERT INTO table (SELECT * FROM temp)", but the second statement still takes a lot of time and causes a loss of performance. So, what's the best way to import my metadata without it affecting the performance of other queries? Thanks, Andrew -- View this message in context: http://www.nabble.com/How-do-I-bulk-insert-to-a-table-without-affecting-read-performance-on-that-table--tp15099164p15099164.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
Re: How do I bulk insert to a table without affecting read performance on that table?
From
"Scott Marlowe"
Date:
On Jan 25, 2008 5:27 PM, growse <nabble@growse.com> wrote: > > Hi, > > I've got a pg database, and a batch process that generates some metadata to > be inserted into one of the tables. Every 15 minutes or so, the batch script > re-calculates the meta data (600,000 rows), dumps it to file, and then does > a TRUNCATE table followed by a COPY to import that file into the table. > > The problem is, that whilst this process is happening, other queries against > this table time out. I've tried to copy into a temp table before doing an > "INSERT INTO table (SELECT * FROM temp)", but the second statement still > takes a lot of time and causes a loss of performance. Can you import to another table then begin; alter table realtable rename to garbage; alter table loadtable rename to realtable; commit; ? > > So, what's the best way to import my metadata without it affecting the > performance of other queries? > > Thanks, > > Andrew > -- > View this message in context: http://www.nabble.com/How-do-I-bulk-insert-to-a-table-without-affecting-read-performance-on-that-table--tp15099164p15099164.html > Sent from the PostgreSQL - performance mailing list archive at Nabble.com. > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >
Re: How do I bulk insert to a table without affecting read performance on that table?
From
growse
Date:
Scott Marlowe-2 wrote: > > On Jan 25, 2008 5:27 PM, growse <nabble@growse.com> wrote: >> >> Hi, >> >> I've got a pg database, and a batch process that generates some metadata >> to >> be inserted into one of the tables. Every 15 minutes or so, the batch >> script >> re-calculates the meta data (600,000 rows), dumps it to file, and then >> does >> a TRUNCATE table followed by a COPY to import that file into the table. >> >> The problem is, that whilst this process is happening, other queries >> against >> this table time out. I've tried to copy into a temp table before doing an >> "INSERT INTO table (SELECT * FROM temp)", but the second statement still >> takes a lot of time and causes a loss of performance. > > Can you import to another table then > > begin; > alter table realtable rename to garbage; > alter table loadtable rename to realtable; > commit; > > ? > >> >> So, what's the best way to import my metadata without it affecting the >> performance of other queries? >> >> Thanks, >> >> Andrew >> -- >> View this message in context: >> http://www.nabble.com/How-do-I-bulk-insert-to-a-table-without-affecting-read-performance-on-that-table--tp15099164p15099164.html >> Sent from the PostgreSQL - performance mailing list archive at >> Nabble.com. >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 5: don't forget to increase your free space map settings >> > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > > 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? -- View this message in context: http://www.nabble.com/How-do-I-bulk-insert-to-a-table-without-affecting-read-performance-on-that-table--tp15099164p15107074.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
Re: How do I bulk insert to a table without affecting read performance on that table?
From
"Scott Marlowe"
Date:
On Jan 26, 2008 5:42 AM, growse <nabble@growse.com> wrote: > > > > Scott Marlowe-2 wrote: > > > > On Jan 25, 2008 5:27 PM, growse <nabble@growse.com> wrote: > >> > >> Hi, > >> > >> I've got a pg database, and a batch process that generates some metadata > >> to > >> be inserted into one of the tables. Every 15 minutes or so, the batch > >> script > >> re-calculates the meta data (600,000 rows), dumps it to file, and then > >> does > >> a TRUNCATE table followed by a COPY to import that file into the table. > >> > >> The problem is, that whilst this process is happening, other queries > >> against > >> this table time out. I've tried to copy into a temp table before doing an > >> "INSERT INTO table (SELECT * FROM temp)", but the second statement still > >> takes a lot of time and causes a loss of performance. > > > > 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.
Re: How do I bulk insert to a table without affecting read performance on that table?
From
"Kevin Grittner"
Date:
>>> 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