Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t6wck-00GaBt-7L for pgsql-general@arkaria.postgresql.org; Fri, 01 Nov 2024 18:43:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1t6wci-00GSJ8-0H for pgsql-general@arkaria.postgresql.org; Fri, 01 Nov 2024 18:43:52 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t6wch-00GSH4-Fe for pgsql-general@lists.postgresql.org; Fri, 01 Nov 2024 18:43:52 +0000 Received: from mail-lj1-x22e.google.com ([2a00:1450:4864:20::22e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1t6wce-0040IZ-Il for pgsql-general@lists.postgresql.org; Fri, 01 Nov 2024 18:43:50 +0000 Received: by mail-lj1-x22e.google.com with SMTP id 38308e7fff4ca-2fb5740a03bso22392801fa.1 for ; Fri, 01 Nov 2024 11:43:48 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1730486626; x=1731091426; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=tByhPcOg5GYNShSa5GKciv2wU6uo08psAEligMsVwIc=; b=Bpzj6EbfdrdDv2Fy0T0+N969ATqyO4CIH+Tfa6yvNjg1R2NHFdcqBlMiJ0H9XMREYL tCgfUx30SwuZ9RaBYqDycz1xoI76eX3tQRdEEdtbzxy8nZ2Rr7vgVUQTsV6/l/dplwId Zu3NR98rd/iCtm17ViGYNxl6d81sFrWaSvBS/+SOTbRAixDbN5I0C3JDhQuCaaXXdr3K 5/16IXMDzzIgrmtkG7re0ZjtI1sa/y5zSPAlsnO1RyvdwXkwoEpGD8hxdMwHX4Xnb4lk hwqW5/B+khXznD3Yd9V1aV9Ct9DILs5QQOyMEtjG6l4Mr2TKolWky74Ms64lYQj03KXb xObw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1730486626; x=1731091426; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=tByhPcOg5GYNShSa5GKciv2wU6uo08psAEligMsVwIc=; b=f7NK5cGNQ+sJKvjhJXAQ3dhy5kBe6khV8Zm6Knqc9WSQcuJewB+YVQpkvpxQxRvZQJ nVVrWKFZmAVDP7jYcvW0z1IgFTuCQDOQki2GLcxc7mO3rTfDZfbntu3AmbEStzann0Ig 4tSj0KykKcuErybfI7GXbJFAcZ1r+ZWMSnD1MQRQvKSS6ur6MuYxvZR1Us9OfEY8bQ3T 6iHweEAmhQIGmPeNASD2m8Q65RfBM4LWCGEJqZ/SRJDgeHdc9WBZi8Ehu99IJYenetD1 QlRl3oymjEl37qPrTXSbHFWKDa1NwR9Si+XPSrr7bT6CQuMbhrxjnLXsdV0Moko37BC+ IhdA== X-Forwarded-Encrypted: i=1; AJvYcCVoSeSqPQMrSYUqWvY5F3DFaLwqY9yiyiMfHNGGwWJRzOsJytgOI/7aylTUcaZXtpMgq4V5wwem1gPJktOf@lists.postgresql.org X-Gm-Message-State: AOJu0YwKSuIyS8X/veSA6EdpomsS0s7gZp7NMvXimCIDbvHtwb0iCfND A6FMhujbk5XLLm1VqwLMNdVzrbavBX1ITytR/RTYCfcCPoTiegUvSz2lDva22r4B2QzJ8z9f+8P no8JuDV76kJCUk5MxcapDQmmrRA== X-Google-Smtp-Source: AGHT+IGpbt841ZZAhvICPpehvSizw0swla02akNM2Icd1FFaZeJuL5oLoqfLkokPHCOuTWYjfr1X+Vgsbow3iJwgcgs= X-Received: by 2002:a2e:bea5:0:b0:2fa:dcb6:fa7a with SMTP id 38308e7fff4ca-2fd0590bdc8mr63285291fa.11.1730486625326; Fri, 01 Nov 2024 11:43:45 -0700 (PDT) MIME-Version: 1.0 References: <5757acdc-39ed-4642-a449-26e47b1bcf95@gelassene-pferde.biz> <20241101094106.Horde.TwPSs9YUgJ5v-_M-4ltwoeh@webmail.gelassene-pferde.biz> <14c1eb8c-a853-43b3-a1ea-b105bfd6c99b@aklaver.com> <20241101182110.Horde.P_w6KptTjJNeM1X9Asg5bE3@webmail.gelassene-pferde.biz> <6bc57377-52e1-496a-b9d6-4cd9dc84d92e@aklaver.com> In-Reply-To: <6bc57377-52e1-496a-b9d6-4cd9dc84d92e@aklaver.com> From: =?UTF-8?Q?Torsten_F=C3=B6rtsch?= Date: Fri, 1 Nov 2024 19:43:33 +0100 Message-ID: Subject: Re: Plans for partitioning of inheriting tables To: Adrian Klaver Cc: thiemo@gelassene-pferde.biz, PostgreSQL General Content-Type: multipart/alternative; boundary="000000000000975ef70625de500c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000975ef70625de500c Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thiemo, it looks to me like you are using inheritance just to make sure your SOURCES and TOPO_FILES tables have some common columns. If you are not actually querying the TEMPLATE_TECH table and expect to see all the rows from the other 2 tables in that one table combined, then you could use CREATE TABLE (LIKE ...) instead of inheritance. That way your "child" tables would become normal tables and you could use declarative partitioning on them. Even if you are querying the TEMPLATE_TECH table, you could still do that by turning the TEMPLATE_TECH table into a view which performs a UNION ALL over the other tables. Just my 2 cents -- Torsten On Fri, Nov 1, 2024 at 7:01=E2=80=AFPM Adrian Klaver wrote: > > > On 11/1/24 10:21 AM, thiemo@gelassene-pferde.biz wrote: > > > > Adrian Klaver escribi=C3=B3: > > > >> Changing that would count as a major change. Even if you where to > >> convince the developers to make the change the earliest it would > >> released would be with the next major release in Fall of 2025. That > >> assumes you can convince then early enough or at all. > > > > I was not trying to convince anyone to do anything about the > > implementation of declarative partitioning. I have been just curious if > > there were plans. If I have raised the impression of the former, I am > > sorry. > Even if there where plans, any changes would happen in the future and > would not be help the now problem. > > > >>> Is there experience on the efficiency/speed comparing partitioning > >>> with inheritance using triggers/rules and using the declarative way? > >>> I don't think that partition speed is an issue in my case, as I have > >>> fairly few records that are in themselves rather big. > >> > >> Hard to say without some firm numbers and/or testing. > > > > Sure, I was hoping those test would have been done some day. But in the > > end, to me, it is not important. > > That is contradicted by your statement below: > > "I mean to say that I believe that loading that much data into one field > will take much more time than runtime difference of > trigger/rules/declarative partitioning solutions would to sort the data > into the correct partition." > > Either performance is important or it is not. > > If TILE is referring to the same thing you are dealing with in related > question on psycopg list then you are talking about bytea storage. You > should take a look at: > > https://www.postgresql.org/docs/current/storage-toast.html > > In any case assuming you are not entering/reading/updating all the bytea > data at one time then you are looking at fetching only that bytea data > that are filtered by other attributes of the rows. I would strongly > suggest running some tests on a single table with the data and see if > you can live with the performance results before complicating things > with partitioning. > > > > >> Also this "... I have fairly few records that are in themselves rather > >> big" could use some explanation. In other words what makes you think > >> that partitioning is the answer to this issue? > > > > I was not thinking that partitioning was the answer to a performance > > problem. Partitioning might be an answer to the maintenance of records, > > specifically if entire sources are affected. The size of the tif files > > to get loaded into the raster attribute TILE range from 112 kB to 32 MB= . > > I am complete unaware of the inner storing mechanisms of raster in > > PostGIS, but on first sight, it seems that the rest of a records of > > TOPO_FILES is negligible compared to the TILE. The total number of file= s > > to be loaded in my case are 3273, even though that only encompasses a > > small part of the world, I do not think, the latter would surpass 10000= 0 > > records. Not much for a database table, afaik. I mean to say that I > > believe that loading that much data into one field will take much more > > time than runtime difference of trigger/rules/declarative partitioning > > solutions would to sort the data into the correct partition. > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > > > --000000000000975ef70625de500c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thiemo,

it looks to me like you are usi= ng inheritance just to make sure your SOURCES and TOPO_FILES tables have so= me common columns. If you are not actually querying the TEMPLATE_TECH table= and expect to see all the rows from the other 2 tables in that one table c= ombined, then you could use CREATE TABLE (LIKE ...) instead of inheritance.= That way your "child" tables would become normal tables and you = could use declarative partitioning on them.

Even i= f you are querying the TEMPLATE_TECH table, you could still do that by turn= ing the TEMPLATE_TECH table into a view which performs a UNION ALL over the= other tables.

Just my 2 cents

--
Torsten

On Fri, Nov 1, 2024 at 7:01=E2=80=AFPM Adri= an Klaver <adrian.klaver@ak= laver.com> wrote:


On 11/1/24 10:21 AM, thiemo@gelassene-pferde.biz wrote:
>
> Adrian Klaver <adrian.klaver@aklaver.com> escribi=C3=B3:
>
>> Changing that would count as a major change. Even if you where to =
>> convince the developers to make the change the earliest it would <= br> >> released would be with the next major release in Fall of 2025. Tha= t
>> assumes you can convince then early enough or at all.
>
> I was not trying to convince anyone to do anything about the
> implementation of declarative partitioning. I have been just curious i= f
> there were plans. If I have raised the impression of the former, I am =
> sorry.
Even if there where plans, any changes would happen in the future and
would not be help the now problem.


>>> Is there experience on the efficiency/speed comparing partitio= ning
>>> with inheritance using triggers/rules and using the declarativ= e way?
>>> I don't think that partition speed is an issue in my case,= as I have
>>> fairly few records that are in themselves rather big.
>>
>> Hard to say without some firm numbers and/or testing.
>
> Sure, I was hoping those test would have been done some day. But in th= e
> end, to me, it is not important.

That is contradicted by your statement below:

"I mean to say that I believe that loading that much data into one fie= ld
will take much more time than runtime difference of
trigger/rules/declarative partitioning solutions would to sort the data into the correct partition."

Either performance is important or it is not.

If TILE is referring to the same thing you are dealing with in related
question on psycopg list then you are talking about bytea storage. You
should take a look at:

https://www.postgresql.org/docs/current/s= torage-toast.html

In any case assuming you are not entering/reading/updating all the bytea data at one time then you are looking at fetching only that bytea data
that are filtered by other attributes of the rows. I would strongly
suggest running some tests on a single table with the data and see if
you can live with the performance results before complicating things
with partitioning.

>
>> Also this "... I have fairly few records that are in themselv= es rather
>> big" could use some explanation. In other words what makes yo= u think
>> that partitioning is the answer to this issue?
>
> I was not thinking that partitioning was the answer to a performance <= br> > problem. Partitioning might be an answer to the maintenance of records= ,
> specifically if entire sources are affected. The size of the tif files=
> to get loaded into the raster attribute TILE range from 112 kB to 32 M= B.
> I am complete unaware of the inner storing mechanisms of raster in > PostGIS, but on first sight, it seems that the rest of a records of > TOPO_FILES is negligible compared to the TILE. The total number of fil= es
> to be loaded in my case are 3273, even though that only encompasses a =
> small part of the world, I do not think, the latter would surpass 1000= 00
> records. Not much for a database table, afaik. I mean to say that I > believe that loading that much data into one field will take much more=
> time than runtime difference of trigger/rules/declarative partitioning=
> solutions would to sort the data into the correct partition.
>

--
Adrian Klaver
adrian.klave= r@aklaver.com


--000000000000975ef70625de500c--