Re: Plans for partitioning of inheriting tables

Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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?

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?

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.

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.
-
https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-INHERITANCE-MAINTENANCE misses out on a sentence not to forget to adapt the
triggers/rules.

Kind regards

Thiemo