pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Dynamic creation of list partitions in highly concurrent write environment
3+ messages / 2 participants
[nested] [flat]

* Dynamic creation of list partitions in highly concurrent write environment
@ 2023-06-01 10:14  Jim Vanns <jvanns@ilm.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Jim Vanns @ 2023-06-01 10:14 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

Hello everyone.

We are attempting to evaluate list partitioning over hash partitioning
(which we currently use) to better facilitate dropping tables that
distinctly model devices we wish to purge. We don't want to use a DML
statement for cleanup since these tables can contain billions of rows
otherwise. Using PG15, we've hit a snag;

We're attempting to use declarative partitioning syntax in a function run
as an after trigger; we hit deadlocks. We try the older
inheritance alternative, we still hit deadlocks. I appreciate this is
generally because creating tables isn't a concurrently supported operation.
Here's the general approach we have, without code and with hypothetical
model, since I'm writing this hastily;

table devices; <-- After trigger on here
table routes; <-- To create physical partition for this logical table
table route_for_device_N; <-- The dynamically created partition

We don't know the device names and thus partitions up front, hence the need
for dynamic creation. How do you go about doing this or is it folly!?

Cheers

Jim

-- 
Jim Vanns
Principal Production Engineer
Industrial Light & Magic, London

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Dynamic creation of list partitions in highly concurrent write environment
@ 2023-06-01 13:32  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Jim Vanns <jvanns@ilm.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2023-06-01 13:32 UTC (permalink / raw)
  To: Jim Vanns <jvanns@ilm.com>; +Cc: pgsql-general@lists.postgresql.org

Jim Vanns <jvanns@ilm.com> writes:
> Here's the general approach we have, without code and with hypothetical
> model, since I'm writing this hastily;

> table devices; <-- After trigger on here
> table routes; <-- To create physical partition for this logical table
> table route_for_device_N; <-- The dynamically created partition

> We don't know the device names and thus partitions up front, hence the need
> for dynamic creation. How do you go about doing this or is it folly!?

Sounds like folly, TBH.  You're going to end with a ridiculous number
of partitions of the routes table, which will not be good for performance.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Dynamic creation of list partitions in highly concurrent write environment
@ 2023-06-01 13:45  Jim Vanns <jvanns@ilm.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 3+ messages in thread

From: Jim Vanns @ 2023-06-01 13:45 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@lists.postgresql.org

Yeah, that's a known concern - hence the evaluation as part of  the list
vs. hash comparison ;)

Jim

On Thu, 1 Jun 2023 at 14:32, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Jim Vanns <jvanns@ilm.com> writes:
> > Here's the general approach we have, without code and with hypothetical
> > model, since I'm writing this hastily;
>
> > table devices; <-- After trigger on here
> > table routes; <-- To create physical partition for this logical table
> > table route_for_device_N; <-- The dynamically created partition
>
> > We don't know the device names and thus partitions up front, hence the
> need
> > for dynamic creation. How do you go about doing this or is it folly!?
>
> Sounds like folly, TBH.  You're going to end with a ridiculous number
> of partitions of the routes table, which will not be good for performance.
>
>                         regards, tom lane
>


-- 
Jim Vanns
Principal Production Engineer
Industrial Light & Magic, London

^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2023-06-01 13:45 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-06-01 10:14 Dynamic creation of list partitions in highly concurrent write environment Jim Vanns <jvanns@ilm.com>
2023-06-01 13:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-06-01 13:45   ` Jim Vanns <jvanns@ilm.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox