Feedback-ID: i76984098:Fastmail
Message-ID: <6bc57377-52e1-496a-b9d6-4cd9dc84d92e@aklaver.com>
Date: Fri, 1 Nov 2024 11:01:02 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Plans for partitioning of inheriting tables
To: thiemo@gelassene-pferde.biz
Cc: PostgreSQL General <pgsql-general@lists.postgresql.org>
References: <ea963edb-5437-4de6-bd2c-97c141a391b8@gelassene-pferde.biz>
 <5757acdc-39ed-4642-a449-26e47b1bcf95@gelassene-pferde.biz>
 <d9d6a2f4-379b-45f3-9005-ab047625c1a4@aklaver.com>
 <20241101094106.Horde.TwPSs9YUgJ5v-_M-4ltwoeh@webmail.gelassene-pferde.biz>
 <14c1eb8c-a853-43b3-a1ea-b105bfd6c99b@aklaver.com>
 <20241101182110.Horde.P_w6KptTjJNeM1X9Asg5bE3@webmail.gelassene-pferde.biz>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <20241101182110.Horde.P_w6KptTjJNeM1X9Asg5bE3@webmail.gelassene-pferde.biz>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/6bc57377-52e1-496a-b9d6-4cd9dc84d92e%40aklaver.com>
Precedence: bulk


On 11/1/24 10:21 AM, thiemo@gelassene-pferde.biz wrote:
> 
> Adrian Klaver <adrian.klaver@aklaver.com> escribió:
> 
>> 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 files 
> 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 100000 
> 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