Date: Sat, 2 May 2020 09:00:32 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Arya F <arya6000@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Best partition type for billions of addresses
Message-ID: <20200502140032.GI28974@telsasoft.com>
References: 
 <CAFoK1ayT3Ma8e_d+AXEBVO6wGffH0bs_LDeY2+9X5NRcGYp_SA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAFoK1ayT3Ma8e_d+AXEBVO6wGffH0bs_LDeY2+9X5NRcGYp_SA@mail.gmail.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk

On Sat, May 02, 2020 at 09:20:06AM -0400, Arya F wrote:
> I need to store about 600 million rows of property addresses across
> multiple counties. I need to have partitioning setup on the table as
> there will be updates and inserts performed to the table frequently
> and I want the queries to have good performance.

I dug up the last messages about this:
https://www.postgresql.org/message-id/flat/CAFoK1aztep-079Fxmaos6umR8X6m3x1K_aZLGtQGpYxfENh9%3DA%40mail.gmail.com
https://www.postgresql.org/message-id/flat/CAFoK1azkv1Z%3DRr7ZWrJjk0RQSoF6ah%2BQMpLSSyBs1KsEiQ3%3Dvw%40mail.gmail.com
https://www.postgresql.org/message-id/CAFoK1axr_T6nB8ZAq8g2QBcqv_pE%3DdsZsxyjatz8Q67k1VKAnw%40mail.gmail.com


> From what I understand hash partitioning would not be the right
> approach in this case, since for each query PostgreSQL has to check
> the indexes of all partitions?

Indexes are separate from partitioning.  Typically, the partitioned columns are
indexed, but it's not required.

If the partition key isn't used in your typical query, then partitioning didn't
help you, and you chose the wrong partition strategy/key.

> Would list partitioning be suitable? if I want PostgreSQL to know
> which partition the row is it can directly load the relevant index
> without having to check other partitions. Should I be including the
> partition key in the where clause?

It sounds like you're thinking about this backwards.

What are your typical queries ?  That should determines the partition strategy
and key, not the other way around.  You should maybe think about whether there
are views/functions/joins of the partitioned column.

For example, at telsasoft, our report queries *always* say "tbl.start_time >=
t1 AND tbl.start_time < t2", so I partitioned our tables BY RANGE(start_time),
so a typical report hits only a single table.  And, start_time has an index on
it, so a typical query over 1-2 days will only hit a fraction of that table.

-- 
Justin