Re: Plans for partitioning of inheriting tables - Mailing list pgsql-general

On 11/1/24 01:41, thiemo@gelassene-pferde.biz wrote:
> 
> Adrian Klaver <adrian.klaver@aklaver.com> escribió:
> 
> 
>> It is just not the way you want to do it, see:
>>
>> https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-USING-INHERITANCE
> 
> Thanks for your patience. Maybe I am not clever enough to understand 
> you. I shall try to explain what I try to do.
> 
> In my project, I have several tables. Each table has some basic 
> technical attributes. For the time being, those are the surrogate key 
> (ID) and a timestamp (ENTRY_PIT) to track the point in time when a 
> record was inserted into the table. To improve consistency and reduce 
> effort, I created a template table those attributes get inherited from 
> by all other tables, e.g. TOPO_FILES. TOPO_FILES can contain 
> GeoTIFF/raster data from different sources. For ease of data management, 
> e.g. wipe all the data of one source, I tried to partition it by 
> SOURCE_ID. And there the error rises that it is not possible to 
> partition a table that is an heir of another table.
> 
> I feel, you are trying to make me partition TOPO_SOURCES by using 
> inheritance, but I cannot see... now I do see how I could achieve my 
> desires. However, there pop up questions in my mind.
> 
> To me, it seems, that partitioning using inheritance will not reduce 
> maintenance but greatly increase it. It feels to me very much that I 
> build manually with inheritance, what is done with the partitioning 
> clause. Am I mistaken?

 From here:

https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE

5.12.2.3. Limitations

"Individual partitions are linked to their partitioned table using 
inheritance behind-the-scenes. However, it is not possible to use all of 
the generic features of inheritance with declaratively partitioned 
tables or their partitions, as discussed below. Notably, a partition 
cannot have any parents other than the partitioned table it is a 
partition of, nor can a table inherit from both a partitioned table and 
a regular table. That means partitioned tables and their partitions 
never share an inheritance hierarchy with regular tables."


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. What I getting at 
is that you need to start thinking of another way of doing this if this 
is a current project. The choices are:

1) Declarative partitioning, where you cannot have your partition parent 
inherit from another table.

2) Partition by inheritance where you build the structure manually.



> 
> In the description, there is the statement that instead of triggers, one 
> could use rules. I am quite sure that, quite a while ago, I was advised 
> in one of the mailing lists against the use of rules other than for 
> inserts as the workings of update and delete rules are almost 
> impenetrable. For me, at least, they were. Are my memories wrong about 
> that?

Yes, I would stay away from rules. They are included in the 
documentation for completeness. You have enough on your plate without 
trying to figure out what rules do.

> 
> 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.

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?

> 
> Remarks to the documentation:
> - There are examples for the insert path. However, not for the update or 
> delete path. I feel, that those tend to be the more complex ones, 
> especially if my memory is correct about the advice to avoid update and 
> delete rules.


 From the docs:

"The schemes shown here assume that the values of a row's key column(s) 
never change, or at least do not change enough to require it to move to 
another partition. An UPDATE that attempts to do that will fail because 
of the CHECK constraints. If you need to handle such cases, you can put 
suitable update triggers on the child tables, but it makes management of 
the structure much more complicated."

So yes, they would be more complicated as you are looking at possibly 
changing tables.

Personally, I think you are heading to declarative partitioning. Either 
via your own scripts or something like 
pg_partman(https://github.com/pgpartman/pg_partman).


> - 
> https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-INHERITANCE-MAINTENANCE misses out on
asentence not to forget to adapt the triggers/rules.
 
> 
> Kind regards
> 
> Thiemo
> 
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com