Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1d0LxM-0005nc-N5 for pgsql-performance@arkaria.postgresql.org; Tue, 18 Apr 2017 05:45:40 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1d0LxM-0000rV-24 for pgsql-performance@arkaria.postgresql.org; Tue, 18 Apr 2017 05:45:40 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1d0LxK-0000nw-PS for pgsql-performance@postgresql.org; Tue, 18 Apr 2017 05:45:38 +0000 Received: from mail-wr0-x231.google.com ([2a00:1450:400c:c0c::231]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1d0LxH-0002SS-1d for pgsql-performance@postgresql.org; Tue, 18 Apr 2017 05:45:38 +0000 Received: by mail-wr0-x231.google.com with SMTP id z109so94642384wrb.1 for ; Mon, 17 Apr 2017 22:45:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:message-id:mime-version:subject:date:in-reply-to:cc:to :references; bh=a5bX7zGQPu1kYyIPpAOh05SR+y/xkWFO51ZrhN4gwfw=; b=qnwmuLPhouvPDRJA354Y4exIzwaC72gTnVceafs5Cc4WiueMFflnT9Fu6P/2RgO0kH qI3BEYuW01fOBcxT6ZobpBU1GH/qS9RCJ/0t2lDS0GzsMShX4uC0XBsKYGudUuRsXr6V martYKlrHD6UPF1JLq8Rvu4zDUxND1hwWBKer6zIw2qmXlkJ1lCx1JzlbNlousBVO0LV vw7YriKSrBzR9mlDq1Ys2bimK1ckg25QFJHdFxeG7qNbzRYpwY5mbLsmku2XP7AGr8qk SZNd8VcpuHO7dpToqYKyH/I48/IshtU0h504xXlc63eW+HYMkYKjAfRZnonAiK4kdhof Rmbw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:message-id:mime-version:subject:date :in-reply-to:cc:to:references; bh=a5bX7zGQPu1kYyIPpAOh05SR+y/xkWFO51ZrhN4gwfw=; b=O32w3SHWFpevx+yV4AgT3SB3wA9e4dUInqGp48YRWChPmOaG9CVBF9YjpKLKxIuYDV v1SPyaAXw0TBIv9HNeQeNI4X4M8HMVihB+vMz8uhMDYnrUezB604vKHMmP9pY+xPfF/N Svp8jmOkV7EJAgQoEXqk2s8ZNhcKto7SrxyeQAnI4a8g5e7RSkRPW/2RUyh1SRwT0GMf CP0/hBBx2fox0seeNpgATmoK5HHkupMOfQ/AeAg7kR1sJL/BLTdtpcJ1FtZnqAorEWai j3ySQnZHx4vxef2z9AD3Ze0cDxuW985toBgLs2MyLk7jQaGWowBRaPleAd/qiX5cQNKW SCIw== X-Gm-Message-State: AN3rC/6hEqAnK7iBj17rMU6kf7BT4XufxKr/D6lRz8PZKFe50Q+L9U6k WBCdsIz38/ABpQ== X-Received: by 10.223.136.56 with SMTP id d53mr21616499wrd.160.1492494333013; Mon, 17 Apr 2017 22:45:33 -0700 (PDT) Received: from [192.168.1.33] (17.red-83-32-17.dynamicip.rima-tde.net. [83.32.17.17]) by smtp.gmail.com with ESMTPSA id g132sm13331854wmd.33.2017.04.17.22.45.32 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 17 Apr 2017 22:45:32 -0700 (PDT) From: Daniel Blanch Bataller Message-Id: <243F5AE4-40B0-4B2B-8EE7-AE206A93AB73@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_19F91B59-1D3E-4F5A-9A54-60907554A2FF" Mime-Version: 1.0 (Mac OS X Mail 10.2 \(3259\)) Subject: Re: Insert Concurrency Date: Tue, 18 Apr 2017 07:45:31 +0200 In-Reply-To: Cc: "pgsql-performance@postgresql.org" To: ROBERT PRICE References: X-Mailer: Apple Mail (2.3259) X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --Apple-Mail=_19F91B59-1D3E-4F5A-9A54-60907554A2FF Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Yes, postgres has partitions: https://www.postgresql.org/docs/9.6/static/ddl-partitioning.html = But this is not going to help much in the scenario you have.=20 Postgres can ingest data very very fast, 100M records in seconds - = minutes , faster than oracle can serve it in many scenarios (all I have = tested). Specially if you use COPY command=20 https://www.postgresql.org/docs/9.6/static/sql-copy.html = and even faster if you use the unlogged feature=20 https://www.postgresql.org/docs/9.6/static/sql-altertable.html = You can tune postgres to make it even faster, but it=E2=80=99s not = normally necessary, with the two advices I gave you firstly, is more = than enough, If I don=E2=80=99t remember it wrong you can move 100M = records in ~ 2 minutes. https://www.postgresql.org/docs/current/static/populate.html = But if you are going to move a record at a time you are going to be = limited by the fastest transaction rate you can achieve, which is going = to be a few hundred per second, and limited at the end by the disk = hardware you have, . Out of the box and on commodity hardware it can = take you up to then days to move 100M records. So, my recomendation is to find a way to batch record insertions using = copy, the benefits you can achieve tunning postgres are going to be = marginal compared with COPY. Regards Daniel Blanch. ww.translatetopostgres.com > El 18 abr 2017, a las 4:55, ROBERT PRICE = escribi=C3=B3: >=20 > I come from an Oracle background and am porting an application to = postgres. App has a table that will contain 100 million rows and has to = be loaded by a process that reads messages off a SQS queue and makes web = service calls to insert records one row at a time in a postgres RDS = instance. I know slow by slow is not the ideal approach but I was = wondering if postgres had partitioning or other ways to tune concurrent = insert statements. Process will run 50 - 100 concurrent threads. --Apple-Mail=_19F91B59-1D3E-4F5A-9A54-60907554A2FF Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
Yes, postgres has partitions:

https://www.postgresql.org/docs/9.6/static/ddl-partitioning.htm= l

But this = is not going to help much in the scenario you have. 

Postgres can ingest data = very very fast, 100M records in seconds - minutes , faster than oracle = can serve it in many scenarios (all I have tested).

Specially if you use = COPY command 

https://www.postgresql.org/docs/9.6/static/sql-copy.html

and even faster = if you use the unlogged feature 

https://www.postgresql.org/docs/9.6/static/sql-altertable.html<= /a>

You can = tune postgres to make it even faster, but it=E2=80=99s not normally = necessary, with the two advices I gave you firstly, is more than enough, =  If I don=E2=80=99t remember it wrong you can move 100M records in = ~ 2 minutes.

https://www.postgresql.org/docs/current/static/populate.html


But if you are going to move a record = at a time you are going to be limited by the fastest transaction rate = you can achieve, which is going to be a few hundred per second, and = limited at the end by the disk hardware you have, . Out of the box =  and on commodity hardware it can take you up to then days to move = 100M records.

So, my recomendation is to find a way to batch record = insertions using copy, the benefits you can achieve tunning postgres are = going to be marginal compared with COPY.

Regards

Daniel Blanch.
ww.translatetopostgres.com







El = 18 abr 2017, a las 4:55, ROBERT PRICE <rprice504@hotmail.com> escribi=C3=B3:

I come from an Oracle background and am = porting an application to postgres. App has a table that will contain = 100 million rows and has to be loaded by a process that reads messages = off a SQS queue and makes web service calls to insert records one row at = a time in a postgres RDS instance. I know slow by slow is not the ideal = approach but I was wondering if postgres had partitioning or other = ways to tune concurrent insert statements. Process will run 50 - 100 = concurrent threads.

= --Apple-Mail=_19F91B59-1D3E-4F5A-9A54-60907554A2FF--