Date: Tue, 20 Jul 2021 14:13:34 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Pavel Borisov <pashkin.elfe@gmail.com>,
	Thomas Munro <thomas.munro@gmail.com>,
	Anastasia Lubennikova <a.lubennikova@postgrespro.ru>,
	Rahila Syed <rahilasyed90@gmail.com>,
	Michael Paquier <michael@paquier.xyz>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
	Fabien COELHO <coelho@cri.ensmp.fr>, Amul Sul <sulamul@gmail.com>
Subject: Re: [PATCH] Automatic HASH and LIST partition creation
Message-ID: <20210720191334.GH19498@telsasoft.com>
References: 
 <CAH2L28vRKPw+uf3Voiy6zy5QUghTPueErNTz_xgpkxEKnmfL1A@mail.gmail.com>
 <CALT9ZEH4YotxVz4n4CdeFO7fvkg1EczZJUAWpfzERNZzm4X6fg@mail.gmail.com>
 <CALT9ZEGG11+p3URZgKbKz62Vu5Fj10OKq4P59V=coiNnr7eO1g@mail.gmail.com>
 <6ad2294a-cedc-2f3c-13bd-1ceceb56e4f7@postgrespro.ru>
 <CA+hUKGL3ymvLb67Hsjh3scGFwRStcwzOkdLtuDwEF+nz_vbyyQ@mail.gmail.com>
 <CALT9ZEHSqRaANsDe2kmnk5W3MBgiq8Nu0Z5T88-jXzDW3tmh=w@mail.gmail.com>
 <20210302202617.GD29832@telsasoft.com>
 <CA+TgmoZS3cjrWoTvvnCQjas-VXnPtiN8y+FNqP-0FobSS_0fYA@mail.gmail.com>
 <CALT9ZEEA-_1LthvsKAy0bNnyhzUm1g3pE=nk708_pX8DsfQQ8g@mail.gmail.com>
 <CA+TgmoYH_x4sJySQHii55aS-czv89W7p_4ieFHQwkUXQWZM-7Q@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CA+TgmoYH_x4sJySQHii55aS-czv89W7p_4ieFHQwkUXQWZM-7Q@mail.gmail.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20210720191334.GH19498%40telsasoft.com>
Precedence: bulk

On Tue, Jul 20, 2021 at 02:42:16PM -0400, Robert Haas wrote:
> The bigger issue IMHO with on-the-fly
> partition creation is avoiding deadlocks in the presence of current
> inserters; I submit that without at least some kind of attempt to
> avoid deadlocks and spurious errors there, it's not really a usable
> scheme, and that seems hard.

I was thinking that for dynamic creation, there would be a DDL command to
create the necessary partitions:

-- Creates 2021-01-02, unless the month already exists:
ALTER TABLE bydate SET GRANULARITY='1day';
ALTER TABLE bydate CREATE PARTITION FOR VALUE ('2021-01-02');

I'd want it to support changing the granularity of the range partitions:

-- Creates 2021-01 unless the month already exists.
-- Errors if a day partition already exists which would overlap?
ALTER TABLE bydate SET granularity='1month';
ALTER TABLE bydate CREATE PARTITION FOR VALUE ('2021-01-03');

It could support creating ranges, which might create multiple partitions,
depending on the granularity:

ALTER TABLE bydate CREATE PARTITION FOR VALUES ('2021-01-01') TO ('2021-02-01')

Or the catalog could include not only granularity, but also endpoints:

ALTER TABLE bydate SET ENDPOINTS ('2012-01-01') ('2022-01-01')
ALTER TABLE bydate CREATE PARTITIONS; --create anything needed to fill from a->b
ALTER TABLE bydate PRUNE PARTITIONS; --drop anything outside of [a,b]

I would use this to set "fine" granularity for large tables, and "course"
granularity for tables that were previously set to "fine" granularity, but its
partitions are no longer large enough to justify it.  This logic currently
exists in our application - we create partitions dynamically immediately before
inserting.  But it'd be nicer if it were created asynchronously.  It may create
tables which were never inserted into, which is fine - they'd be course
granularity tables (one per month).

I think this might elegantly allow both 1) subpartitioning; 2) repartitioning
to a different granularity (for which I currently have my own tool).

-- 
Justin