pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Table Partitions: To Inherit Or Not To Inherit
2+ messages / 2 participants
[nested] [flat]

* Table Partitions: To Inherit Or Not To Inherit
@ 2004-11-17 05:31 Don Drake <dondrake@gmail.com>
  2004-11-17 05:51 ` Re: Table Partitions: To Inherit Or Not To Inherit Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Don Drake @ 2004-11-17 05:31 UTC (permalink / raw)
  To: pgsql-performance

I've read the previous thread on the list regarding partitioning
mechanisms and I just wrote a plpgsql function to create the partition
tables (by date) as well as another function used to do the insert (it
determines which table will be inserted).

The creation of the partition tables uses the inherits clause when
creating.  It creates an exact copy of the table it's inheriting from,
and adds the indexes since inherits doesn't do that for me.

CREATE TABLE hourly_report_data_2004_11_16 () INHERITS (hourly_report_data)

When I query on the hourly_report_data, the explain plan shows it
query all the tables that inherited from it.  That's all great.

What's really the difference between this and creating separate tables
with the same column definition without the inherit, and then create a
view to "merge" them together?

Also, I've run into a snag in that I have a hourly_detail table, that
has a foreign key to the hourly_report_data.  The inherit method above
does not honor the foreign key relationship to the children table of
hourly_report_data.  I can't insert any data into the hourly_detail
table due to the constraint failing.

The hourly_detail table is relatively tiny compared to the enormous
hourly_report_data table, so if I don't have to partition that one I
would rather not.  Any suggestions on this?

Thanks.

-Don

-- 
Donald Drake
President
Drake Consulting
http://www.drakeconsult.com/
312-560-1574



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Table Partitions: To Inherit Or Not To Inherit
  2004-11-17 05:31 Table Partitions: To Inherit Or Not To Inherit Don Drake <dondrake@gmail.com>
@ 2004-11-17 05:51 ` Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Josh Berkus @ 2004-11-17 05:51 UTC (permalink / raw)
  To: pgsql-performance@postgresql.org, Don Drake <dondrake@gmail.com>

Don,

> What's really the difference between this and creating separate tables
> with the same column definition without the inherit, and then create a
> view to "merge" them together?

Easier syntax for queries.   If you created completely seperate tables and 
UNIONED them together, you'd have to be constantly modifying a VIEW which 
tied the tables together.  With inheritance, you just do "SELECT * FROM 
parent_table" and it handles finding all the children for you.

> Also, I've run into a snag in that I have a hourly_detail table, that
> has a foreign key to the hourly_report_data.  The inherit method above
> does not honor the foreign key relationship to the children table of
> hourly_report_data.  I can't insert any data into the hourly_detail
> table due to the constraint failing.

This is a known limitation of inherited tables, at least in current 
implementations.   I think it's on the TODO list.   For now, either live 
without the FKs, or implement them through custom triggers/rules.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2004-11-17 05:51 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-17 05:31 Table Partitions: To Inherit Or Not To Inherit Don Drake <dondrake@gmail.com>
2004-11-17 05:51 ` Josh Berkus <josh@agliodbs.com>

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