From: pinker <pinker@onet.eu>
To: pgsql-performance@postgresql.org
Subject: Re: Big data INSERT optimization - ExclusiveLock on extension of the table
Date: Thu, 18 Aug 2016 15:26:35 -0700 (MST)
Message-ID: <1471559195448-5917136.post@n5.nabble.com> (raw)
In-Reply-To: <0805fb3c-ed48-bece-690c-5e60c7b40e65@BlueTreble.com>
References: <1471434303741-5916781.post@n5.nabble.com>
<0805fb3c-ed48-bece-690c-5e60c7b40e65@BlueTreble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
> 1. rename table t01 to t02
OK...
> 2. insert into t02 1M rows in chunks for about 100k
Why not just insert into t01??
Because of cpu utilization, it speeds up when load is divided
> 3. from t01 (previously loaded table) insert data through stored procedure
But you renamed t01 so it no longer exists???
> to b01 - this happens parallel in over a dozen sessions
b01?
that's another table - permanent one
> 4. truncate t01
Huh??
The data were inserted to permanent storage so the temporary table can be
truncated and reused.
Ok, maybe the process is not so important; let's say the table is loaded,
then data are fetched and reloaded to other table through stored procedure
(with it's logic), then the table is truncated and process goes again. The
most important part is holding ExclusiveLocks ~ 1-5s.
--
View this message in context: http://postgresql.nabble.com/Big-data-INSERT-optimization-ExclusiveLock-on-extension-of-the-table-tp...
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: pinker@onet.eu
Subject: Re: Big data INSERT optimization - ExclusiveLock on extension of the table
In-Reply-To: <1471559195448-5917136.post@n5.nabble.com>
* 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