pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: When to use PARTITION BY HASH?
Date: Sat, 6 Jun 2020 20:58:08 -0500
Message-ID: <fa380e6c-301b-3db4-4ce3-75f7af660637@gmail.com> (raw)
In-Reply-To: <CAMkU=1xRi8eBb5=TKxLOwzScagzzBU0A+Bf33fK3xyVXCis2Qw@mail.gmail.com>
References: <CACACo5StuUuFox+65KQHB8u+51kMUcEeQuV=XmsUv7d8VnaO7A@mail.gmail.com>
	<CACxu=vLWZtmpfZqKBi9R4z7NTpd6iEKLXfEWGHxTYMECoTZSpQ@mail.gmail.com>
	<CACACo5RxgQBO_cB97yFkWwLo_-Vs3WtUei2KszGg2LkJ95uiXA@mail.gmail.com>
	<CAMkU=1w0JzvpjrNFmQJ94w1+JrF+XeY0_5f9Z9R4mZr6hOOpkg@mail.gmail.com>
	<CACACo5T4Cq1sx7iWDxQ85RFG5dQxR1i7qS8BKR5JTaPwS-_MtA@mail.gmail.com>
	<CAMkU=1xRi8eBb5=TKxLOwzScagzzBU0A+Bf33fK3xyVXCis2Qw@mail.gmail.com>

On 6/5/20 8:51 AM, Jeff Janes wrote:
> On Fri, Jun 5, 2020 at 6:12 AM Oleksandr Shulgin 
> <oleksandr.shulgin@zalando.de <mailto:oleksandr.shulgin@zalando.de>> wrote:
[snip]
>
>     For a bulk load you'd likely want to go with an empty partition w/o
>     indexes and build them later, after loading the tuples.
>
>
> That only works if the bulk load is starting from zero. If you are adding 
> a million rows to something that already has 100 million, you would 
> probably spend more time rebuilding the indexes than you saved by dropping 
> them.

It's too bad that Postgres doesn't have "deferred index updates" during bulk 
(but still transactional) loads, where the index nodes are updated /en 
masse/ every "commit count" number of rows. That's *really useful* in this 
situation, but I've only seen it in one legacy RDBMS.

-- 
Angular momentum makes the world go 'round.

view thread (22+ messages)  latest in thread

Message-ID: <fa380e6c-301b-3db4-4ce3-75f7af660637@gmail.com>
Permalink:  ../fa380e6c-301b-3db4-4ce3-75f7af660637@gmail.com/
Also on:    postgresql.org/message-id/fa380e6c-301b-3db4-4ce3-75f7af660637@gmail.com

 · 

reply
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: ronljohnsonjr@gmail.com, pgsql-general@lists.postgresql.org
  Subject: Re: When to use PARTITION BY HASH?
  In-Reply-To: <fa380e6c-301b-3db4-4ce3-75f7af660637@gmail.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