Date: Thu, 18 Aug 2016 15:26:35 -0700 (MST)
From: pinker <pinker@onet.eu>
To: pgsql-performance@postgresql.org
Message-ID: <1471559195448-5917136.post@n5.nabble.com>
In-Reply-To: <0805fb3c-ed48-bece-690c-5e60c7b40e65@BlueTreble.com>
References: <1471434303741-5916781.post@n5.nabble.com>
 <0805fb3c-ed48-bece-690c-5e60c7b40e65@BlueTreble.com>
Subject: Re: Big data INSERT optimization - ExclusiveLock on extension of
 the table
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


> 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-tp5916781p5917136.html
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