public inbox for [email protected]  

help / color / mirror / Atom feed

From: [email protected]
To: PostgreSQL General <[email protected]>
Subject: Re: Plans for partitioning of inheriting tables
Date: Fri, 01 Nov 2024 09:41:06 +0100
Message-ID: <20241101094106.Horde.TwPSs9YUgJ5v-_M-4ltwoeh@webmail.gelassene-pferde.biz> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>


Adrian Klaver <[email protected]> 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-MAINTENAN... misses out on a sentence not to forget to adapt the  
triggers/rules.

Kind regards

Thiemo

view thread (13+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Plans for partitioning of inheriting tables
  In-Reply-To: <20241101094106.Horde.TwPSs9YUgJ5v-_M-4ltwoeh@webmail.gelassene-pferde.biz>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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