public inbox for [email protected]
help / color / mirror / Atom feedFrom: Justin Pryzby <[email protected]>
To: Robert Haas <[email protected]>
Cc: Pavel Borisov <[email protected]>
Cc: Thomas Munro <[email protected]>
Cc: Anastasia Lubennikova <[email protected]>
Cc: Rahila Syed <[email protected]>
Cc: Michael Paquier <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Fabien COELHO <[email protected]>
Cc: Amul Sul <[email protected]>
Subject: Re: [PATCH] Automatic HASH and LIST partition creation
Date: Tue, 20 Jul 2021 14:13:34 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <CA+TgmoYH_x4sJySQHii55aS-czv89W7p_4ieFHQwkUXQWZM-7Q@mail.gmail.com>
References: <CAH2L28vRKPw+uf3Voiy6zy5QUghTPueErNTz_xgpkxEKnmfL1A@mail.gmail.com>
<CALT9ZEH4YotxVz4n4CdeFO7fvkg1EczZJUAWpfzERNZzm4X6fg@mail.gmail.com>
<CALT9ZEGG11+p3URZgKbKz62Vu5Fj10OKq4P59V=coiNnr7eO1g@mail.gmail.com>
<[email protected]>
<CA+hUKGL3ymvLb67Hsjh3scGFwRStcwzOkdLtuDwEF+nz_vbyyQ@mail.gmail.com>
<CALT9ZEHSqRaANsDe2kmnk5W3MBgiq8Nu0Z5T88-jXzDW3tmh=w@mail.gmail.com>
<[email protected]>
<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>
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
view thread (38+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: [PATCH] Automatic HASH and LIST partition creation
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox