PostgreSQL Discussion - How many insert + update should one transaction handle?

Author

How many insert + update should one transaction handle?

Yonatan Ben-Nes

2005-09-23, 7:23 am

Hi all,

Every few days I need to DELETE all of the content of few tables and
INSERT new data in them.
The amount of new data is about 5 million rows and each row get about 3
queries (INSERT + UPDATE).
Now because I need the old data to be displayed till all of the new data
will be available I'm doing all of the process of deleting the old
content and inserting the new one in one transaction.
Should I divide the insertion so ill insert the new data into a
temporary table and the transaction should be commited every
100,1000,10000 whatever queries? or maybe it doesnt matter to the server
whats the size of the transaction and its ok to handle such a process in
one transaction?

Thanks alot in advance,
Yonatan Ben-Nes

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Bruno Wolff III

2005-09-23, 11:24 am

On Fri, Sep 23, 2005 at 12:51:09 +0200,
Yonatan Ben-Nes <da@canaan.co.il> wrote:
> Hi all,
>
> Every few days I need to DELETE all of the content of few tables and
> INSERT new data in them.
> The amount of new data is about 5 million rows and each row get about 3
> queries (INSERT + UPDATE).
> Now because I need the old data to be displayed till all of the new data
> will be available I'm doing all of the process of deleting the old
> content and inserting the new one in one transaction.
> Should I divide the insertion so ill insert the new data into a
> temporary table and the transaction should be commited every
> 100,1000,10000 whatever queries? or maybe it doesnt matter to the server
> whats the size of the transaction and its ok to handle such a process in
> one transaction?

Doesn't breaking this into multiple transactions defeat your stated intent
of keeping the old data visible until all of the need data is loaded?
You should be able to do this all in one transaction. If this is a one shot
deal you might want to use vacuum full or cluster to reclaim disk space.
If it is something you regularly, then a plain vacuum with adequate FSM
setting should be adequate. You will also want to do an analyze which you
can piggyback on the vacuum.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Yonatan Ben-Nes

2005-09-23, 11:24 am

Bruno Wolff III wrote:
> On Fri, Sep 23, 2005 at 12:51:09 +0200,
> Yonatan Ben-Nes <da@canaan.co.il> wrote:
>
>
>
> Doesn't breaking this into multiple transactions defeat your stated intent
> of keeping the old data visible until all of the need data is loaded?
> You should be able to do this all in one transaction. If this is a one shot
> deal you might want to use vacuum full or cluster to reclaim disk space.
> If it is something you regularly, then a plain vacuum with adequate FSM
> setting should be adequate. You will also want to do an analyze which you
> can piggyback on the vacuum.

Well if ill use multiple transactions then ill insert them into a
temporary table as mentioned and only when all of the process will end
ill replace the old data with the new data.
But actually it doesnt really matter anyway if its ok to do one big
transaction.

Thanks Bruno,
Yonatan Ben-Nes

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Dawid Kuroczko

2005-09-23, 8:23 pm

On 9/23/05, Yonatan Ben-Nes <da@canaan.co.il> wrote:
>
> Hi all,
>
> Every few days I need to DELETE all of the content of few tables and
> INSERT new data in them.
> The amount of new data is about 5 million rows and each row get about 3
> queries (INSERT + UPDATE).

If I understand you well, in transaction you do:
DELETE FROM table; -- remove all columns, you have a table with 5m dead
tuples
then you:
INSERT into the table, -- you have 5m dead tuples + 5m new tuples
then you:
UPDATE what you've inserted -- you have 10m dead tuples + 5m new tuples
and then you:
UPDATE once again what you've inserted -- you have 15m dead tuples + 5 new
tuples
and then COMMIT;

Alternatively you want to do:
CREATE TEMPORARY temp_table and UPDATE it as you please
BEGIN;
DELETE FROM table;
SELECT INTO table * FROM temp_table;
COMMIT; -- which will leave you with 5m dead and 5m live.

Or you could even try (haven't tested it):
BEGIN;
CREATE new_table;
SELECT INTO new_table * FROM temp_table;
DROP TABLE table;
ALTER TABLE new_table RENAME TO table;
COMMIT; -- leaving you with fresh 5mln new tuples table
....with a risk of loosing all the changes made to old table after BEGIN;

Regards,
Dawid

Gnanavel S

2005-09-27, 3:23 am

On 9/27/05, Jim C. Nasby <jnasby@pervasive.com> wrote:
>
> On Mon, Sep 26, 2005 at 08:41:03PM +0200, Yonatan Ben-Nes wrote:
>

Instead of dropping it here, just rename to a different name and then after
doing the next step drop the table.
[color=darkred]
> ALTER TABLE new_table RENAME TO table;

you can do like this,

BEGIN;
CREATE new_table;
SELECT INTO new_table * FROM temp_table;
ALTER TABLE table RENAME TO temp_table_orig;
ALTER TABLE new_table RENAME TO table;
COMMIT;
drop table temp_table_orig;
[color=darkred]
>
>
> Based on http://lnk.nu/developer.postgresql.org/44b.c, line 1478 on,
> there's not a lot that happens during the ALTER TABLE. Likewise DROP
> (line 517) doesn't do much either. So basically, anything trying to
> access the old table will block for a while waiting for the update to
> happen.
>
> But keep in mind that 'a while' will depend on what's happening on the
> system. Imagine...
>
> Start long transaction involving table
> Run code above; drop aquires lock on table
>
> Everything else against table will now block, waiting for the DROP to
> happen.
> --
> Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
> Pervasive Software http://pervasive.com work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

--
with regards,
S.Gnanavel
Satyam Computer Services Ltd.

Sponsored Links