> On Sep 16, 2017, at 11:18 AM, Rafal Pietrak <rafal@xxxxxxxxx> wrote: > > Dear robjsargent@xxxxxxxxx, > > W dniu 16.09.2017 o 17:19, Gmail pisze: >> >> >>> On Sep 16, 2017, at 8:23 AM, Rafal Pietrak <rafal@xxxxxxxxx> wrote: >>> >>> >>> >>> W dniu 16.09.2017 o 15:45, Adam Brusselback pisze: >>>> Here is the last discussion I saw on >>>> it: https://www.postgresql.org/message-id/flat/90261791-b731-a516-ab2a-dafb97df4464%40postgrespro.ru#90261791-b731-a516-ab2a-dafb97df4464@xxxxxxxxxxxxxx >>>> <https://www.postgresql.org/message-id/flat/90261791-b731-a516-ab2a-dafb97df4464%40postgrespro.ru#90261791-b731-a516-ab2a-dafb97df4464@xxxxxxxxxxxxxx> >>>> >>> >>> I can see the discussion is packed with implementation details. That's >>> promising :) >>> >>> Thenx! >>> >>> >> For those of us scoring at home, here's what I have: >> 1 You have a 6-deep hierarchy over 17 document types >> you concede that standard master-detail/inheritance accomplishes what you need w.r.t to documents > > I do have 17 "process tables" ... they are "class-B" tables, they DONT > need any hierarchy. One of them contain payment details and has FK do a > document (in one of the 12 tables of "class-A", which are in 6 levels of > hierachy) which this payment covers. They get multiplicated ONLY because > PK in those 12 "class-A" tables must be accessed separately. And those I > have. It goes like this: > > CREATE TABLE T1 (id int, b date); -- level 1 > CREATE TABLE T2 (c text) INHERITS (T1); > CREATE TABLE T3 (d text) INHERITS (T1); > CREATE TABLE T4 (e text, tm date) INHERITS (T1); > CREATE TABLE T5 (f text) INHERITS (T1); -- level 2 > CREATE TABLE T6 (ca text) INHERITS (T2); > CREATE TABLE T7 (db text, db2 text) INHERITS (T3); > CREATE TABLE T8 (ec text, db4 test) INHERITS (T4); > CREATE TABLE T9 (fd text) INHERITS (T5); -- level 3 > CREATE TABLE T10 (db11 text) INHERITS (T7); -- level 4 > - quick question: do you have more than one table inheriting from T2? We all skin cats different ways, of course. Often with such things, especially with ORM tools, one puts a "type" field in the master table indicating which exact implementation if referenced for each row. Helps in reporting counts as well. I assume the columns "c text" are just placeholders for discussion, not the actual structure. > ... still counting? And I haven't yet touch any of the 12 leaf tables > I'm using. > >> 2 You don't have enough document instances to warrant partitioning > > I don't. My couple of thousands of documents is just nothing. I don't > have "enough documents" (that's why I say I can live with a performance > hit). true, true, true. > > But I do have enough document-content variety for that. I could collapse > some of the hierarchy at the expense of some columns getting NULL for > certain rows - but that's just nonesens. I'm not doing that. We pick our poisons, I guess. I sure might be tempted to denormalize some of those if it made life much easier/faster. Hard to say from what's been presented - still assuming we're seeing pseudo-tables. > >> 3 Your remaining problem is your workflow > > Sorry I don't understand this point. I meant that your document tables are fine (as described above) and that you were still having trouble with the persistence aspects of what happens to the documents. > >> 4 You have an academic interest in multi-table indexing >> > > Yes. so what? > Nothing. Just trying to see if I'm following your thread. > As nobody have tried to advise me to change my schema, from my point of > view the discussion goes just fine. And I've got some very interesting > answers. Is there a problem with that? > > What exactly are you trying to say? > -R > All your documents are represented in your "T1" table. So your processing can always refer to that table - which is excellent. T1 guarantees unique ids across all other T-tables. Activity related records have no need to be under the same unique ID space (though personally I'm a fan of UUID anyway). I'm not seeing where you would benefit from the title of this thread. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
