Re: When to use PARTITION BY HASH?

[MichaelDBA]

Hi,

I use it quite often, since I'm dealing with partitioning keys that have 
high cardinality, ie, high number of different values.  If your 
cardinality is very high, but your spacing between values is not 
uniform, HASH will balance your partitioned tables naturally.  If your 
spacing between values is consistent, perhaps RANGE partitioning would 
be better.


Regards,
Michael Vitale

Oleksandr Shulgin wrote on 6/2/2020 1:17 PM:

Hi!

I was reading up on declarative partitioning[1] and I'm not sure what 
could be a possible application of Hash partitioning.


Is anyone actually using it? What are typical use cases?  What 
benefits does such a partitioning scheme provide?


On its face, it seems that it can only give you a number of tables 
which are smaller than the un-partitioned one, but I fail to see how 
it would provide any of the potential advantages listed in the 
documentation.


With a reasonable hash function, the distribution of rows across 
partitions should be more or less equal, so I wouldn't expect any of 
the following to hold true:
- "...most of the heavily accessed rows of the table are in a single 
partition or a small number of partitions."
- "Bulk loads and deletes can be accomplished by adding or removing 
partitions...",

etc.

That *might* turn out to be the case with a small number of distinct 
values in the partitioning column(s), but then why rely on hash 
assignment instead of using PARTITION BY LIST in the first place?


Regards,
--
Alex

[1] https://www.postgresql.org/docs/12/ddl-partitioning.html

Re: Multiple postgresql clusters with same version and separate binaries

[MichaelDBA]

To put it simply: you cannot run different major versions of PostgreSQL 
with the same binaries. 3 things need to be separate.  You named 2 of 
them: data directory and port.  The 3rd one is separate binary locations 
for each PG cluster instance running on the same host.


What I do is create a separate .profile_pgsql file for each PG cluster 
instance.  This file contains all the stuff necessary to distinguish 
between multiple PG clusters.  Here is an example for a specific Linux 
distro...


export VERSION=9.6
export PGROOT=/usr/pgsql-$VERSION
export PGBASE=/var/lib/pgsql/$VERSION
export PATH=$PGROOT/bin:$PGROOT/share:$PATH
export PGLIB=$PGROOT/lib
export MANPATH=$PGROOT/share/man:$MANPATH
export LD_LIBRARY_PATH=$PGROOT/bin:$PGBASE/share:$PATH
export PGDATA=$PGBASE/data
export PGPASSFILE=~/.pgpass
export PGUSER=postgres
export PGDATABASE=postgres
export PGPORT=5432




Regards,
Michael Vitale


Erika Knihti-Van Driessche 
Friday, January 4, 2019 9:21 AM
Hi,

I have 10+ postgresql clusters (v 9.6) running on one machine - each 
having their own data directory and port. They all share same binaries 
though, and this is now giving me some headache.. I cannot shutdown 
all clusters at the same time, so upgrading them is quite impossible.


I know that running several versions of postgres is possible on one 
machine - I suppose this would also work out with same version running 
on different locations, like /db1/PostgreSQL/10, /db2/PostgreSQL/10.. 
etc..? Has anyone any experience with this kind of problem?


All help and ideas appreciated! :-)

Thanks,
Erika

Re: Multiple postgresql clusters with same version and separate binaries

[MichaelDBA]

I did say you need to run with different binaries for different versions:

To put it simply: you cannot run different major versions of PostgreSQL 
with the same binaries.



So when I subsequently said the following it was in that context.

The 3rd one is separate binary locations for each PG cluster instance 
running on the same host.



Rui DeSousa <mailto:r...@crazybean.net>
Friday, January 4, 2019 10:53 AM



Don’t need separate binaries for each cluster; only separate binaries 
for each version needed; i.e. 9.6.1, 9.6.2, etc.


MichaelDBA <mailto:michael...@sqlexec.com>
Friday, January 4, 2019 9:32 AM
To put it simply: you cannot run different major versions of 
PostgreSQL with the same binaries. 3 things need to be separate.  You 
named 2 of them: data directory and port.  The 3rd one is separate 
binary locations for each PG cluster instance running on the same host.


What I do is create a separate .profile_pgsql file for each PG cluster 
instance.  This file contains all the stuff necessary to distinguish 
between multiple PG clusters.  Here is an example for a specific Linux 
distro...


export VERSION=9.6
export PGROOT=/usr/pgsql-$VERSION
export PGBASE=/var/lib/pgsql/$VERSION
export PATH=$PGROOT/bin:$PGROOT/share:$PATH
export PGLIB=$PGROOT/lib
export MANPATH=$PGROOT/share/man:$MANPATH
export LD_LIBRARY_PATH=$PGROOT/bin:$PGBASE/share:$PATH
export PGDATA=$PGBASE/data
export PGPASSFILE=~/.pgpass
export PGUSER=postgres
export PGDATABASE=postgres
export PGPORT=5432



Regards,
Michael Vitale


Erika Knihti-Van Driessche <mailto:erika.kni...@gmail.com>
Friday, January 4, 2019 9:21 AM
Hi,

I have 10+ postgresql clusters (v 9.6) running on one machine - each 
having their own data directory and port. They all share same binaries 
though, and this is now giving me some headache.. I cannot shutdown 
all clusters at the same time, so upgrading them is quite impossible.


I know that running several versions of postgres is possible on one 
machine - I suppose this would also work out with same version running 
on different locations, like /db1/PostgreSQL/10, /db2/PostgreSQL/10.. 
etc..? Has anyone any experience with this kind of problem?


All help and ideas appreciated! :-)

Thanks,
Erika