Robert Haas wrote: > Sounds great! I made the comment during my talk at PGCONF.EU that > partitioned tables in PostgreSQL are really just a bunch of tables > glued together, but that over time "we'll make the glue better", and I > think the improvements on which you are working will go a long way in > that direction.
Thanks -- yes, that's my intention, to improve our partitioning story. Many other projects related to improving the glue are already underway by others, so I think we're making great progress on the overall partitioning front. > With regard to indexes, presumably we can only have a unique index if > the index definition matches the partition key definition. Otherwise, > we can only guarantee per-partition uniqueness, unless (1) we > implement "global" indexes, which sounds like a patch that would take > a fair amount of work, or (2) every index insertion goes and checks > for conflicting values in all of the "sibling" indexes. This latter > approach was suggested to me by Andres, and certainly solves a bunch > of problems with vacuuming and dropping indexes, but seems like it > would probably have lousy insert performance unless the number of > partitions is very small. Actually, we discussed unique constraints before. What I proposed is to implement a useful subset: only allow a unique constraint if the partition columns are in it, which means that enforcing the constraint on each partition independently is enough to satisfy the constraint globally. This doesn't solve all cases where you want UNIQUE on partitioned tables, but the reason to implement this subset is precisely that it doesn't have those drawbacks, so it's quick and efficient. Another point is that global indexes are not only a lot of work, but they also come with additional drawbacks such as the necessity to clean up when partitions are dropped/detached, which is a huge pain. > With regard to foreign keys, it seems like there are two directions to > worry about. An "outbound" foreign key needs to cascade, I think, > more or less like an index does, with every child inheriting the > foreign key from the partition root. An "inbound" foreign key just > needs a unique index to point at and depend on. Given the work you > are doing on unique indexes, I'm guessing that you are talking about > supporting the latter, not the former, but the way you phrased it > sounds like the opposite. Actually, I intend to do both. I have a prototype patch which seems to work, though I haven't hammered it heavily yet. Some of the cascading options are not implemented -- as I recall I implemented it for the CREATE TABLE PARTITION OF case but not yet the ALTER TABLE ATTACH PARTITION one. > I haven't thought much about the possibility of supporting FOR EACH > ROW triggers, but it sounds like a good idea if we can do it. I'm not > sure how that will work, though, if users directly access the leaf > partitions. Yeah, I'm not sure yet either. Needs more thought than I've given it so far. I have a prototype patch for this also, and the basic case seems to give reasonable results, but I haven't tried to find any weird corner cases either. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
