Re: Postgresql 12, 512 partition by hash. Slow select

> On Apr 5, 2020, at 2:50 PM, Arya F <arya6000(at)gmail(dot)com> wrote:
>
> The table at some point will have more than 1 billion rows, the
> information stored is international residential addresses. Trying to
> figure out a way of spreading the data fairly evenly thought out
> multiple partitions, but I was unable to come up with a way of
> splitting the data so that Postgres does not have to search though all
> the partitions.
>

If you have to use partitions, I would split it by country using population for analysis. I understand that address and population are different, but I would expect some correlation.

The largest 14 countries each have a population of 100 million or more and represent about 62% of the world population. That means the rest of the world should fit easily into another 14 partitions.

It seems like it could be fairly easily evened out with a little bit of data analysis.

You could probably refine this to be no more than 20 partitions.

Now China and India could be a problem and need to be split, but I would not do that unless necessary. China and India both have 6 nationally recognized regions that could be used if needed.

Neil
-
Fairwind Software
https://www.fairwindsoft.com