Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jhkZn-00063Y-H4 for pgsql-general@arkaria.postgresql.org; Sun, 07 Jun 2020 01:58:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jhkZm-0006o2-3i for pgsql-general@arkaria.postgresql.org; Sun, 07 Jun 2020 01:58:18 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jhkZl-0006nv-Q6 for pgsql-general@lists.postgresql.org; Sun, 07 Jun 2020 01:58:17 +0000 Received: from mail-ot1-x341.google.com ([2607:f8b0:4864:20::341]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1jhkZf-0006Oa-DH for pgsql-general@lists.postgresql.org; Sun, 07 Jun 2020 01:58:16 +0000 Received: by mail-ot1-x341.google.com with SMTP id t6so1595357otk.9 for ; Sat, 06 Jun 2020 18:58:11 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=ltSsAZMtSFuJ3hL5F9AFLUypPcLcjU5aZTUAHr+kslg=; b=IjgcAqytaP9fnh9aB1z/bW2AeixkJewEBk1V30n8nFIeTEW/JF98nXBtZtQkR+dtGw GrPbmT+5vyYWJDF8Y8HKXVpuF0SnRibD2tBJTRqF1zGO97VhJZPUwoeCHQOcJlJWBW+T XV+hG7f0ZqQq2gzw44DhVxbI85+iq7e++XfDarOnpzQAruddpQ0Z2RFwbr8uBYlePHoL FjkrhGsXdV7dh7syeLrliqRc70Q5DdJDf9LroojRA/dKpJZpuzsaNGWHKKbyfMEyKaya sMbDlfqeikZ39WwC85v6r6mHq7NQwLjJnn6bI2DiRfOjgQCVzFcjNHsXcx3oVJYpfgmg QV9g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=ltSsAZMtSFuJ3hL5F9AFLUypPcLcjU5aZTUAHr+kslg=; b=VAgTC9B+5nHQVDQqx8qD2Yi2o3re8/8UO7zAfaKdMFQIt4Dm7g+TsNKkaBepkapRuj nCIVwtfszBdf7Cm+rVEsLZALL1Cc7LPUsVjqk0wofWTgXzAwL+ZLdGFqhkXZWLLP1icf P+JOikG6X5ObnNGhpLoqYgt5w23T5L7BiYTCYEj1bBzt/mToEnWwPyAJeEqZx1h0ADBA shvAVoEFiufxRILLM+CgKJ6Ui+7PIebT4KAtS1S1j5D9Tmj2/iNWy+3kLJI/JjC/N2Pk 3Twk4CJvdq5uoxkfT9TaYXm0IeOoLWjpqp4iwRiVXYumShKmF8l/W8v0ZYm4+FNJGsV5 YNmQ== X-Gm-Message-State: AOAM531Vpdnk76wpUMeWfV70KdzbFnkdJ5zkKr+jlCYiJasBVCwGPnL8 DF62YGwKYPUtgL2APa6P2u6+jTP6ysRv7w== X-Google-Smtp-Source: ABdhPJzErfxQpGzfwsUrGxQwZd/h9Y+ckYPoretZyEZ7+5GfKUhcCDldMJok4cW81PFQS2bnwmBrMA== X-Received: by 2002:a9d:67ce:: with SMTP id c14mr11236199otn.337.1591495090436; Sat, 06 Jun 2020 18:58:10 -0700 (PDT) Received: from [192.168.1.10] (ip70-186-126-197.no.no.cox.net. [70.186.126.197]) by smtp.googlemail.com with ESMTPSA id h187sm355953oib.7.2020.06.06.18.58.09 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sat, 06 Jun 2020 18:58:09 -0700 (PDT) Subject: Re: When to use PARTITION BY HASH? To: pgsql-general@lists.postgresql.org References: From: Ron Message-ID: Date: Sat, 6 Jun 2020 20:58:08 -0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.4.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------B1BBA4480A61B6EA214532CB" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------B1BBA4480A61B6EA214532CB Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 6/5/20 8:51 AM, Jeff Janes wrote: > On Fri, Jun 5, 2020 at 6:12 AM Oleksandr Shulgin > > 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. --------------B1BBA4480A61B6EA214532CB Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit 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> 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.
--------------B1BBA4480A61B6EA214532CB--