On Tue, Feb 5, 2013 at 10:19 AM, Lukas Eder <[email protected]> wrote:
> > >> > The difference between the above example and what I'm trying to > >> > implement is > >> > the insert logic will be done in the Java code and not by a trigger. I > >> > was > >> > looking for suggestions on how best to use jOOQ for this > >> > >> Hmm, these are some interesting points that might even lead to a new > >> feature within jOOQ. Essentially, you want to decide within the Java > >> application (within a DAO layer), whether your inserted record should > >> be inserted into a table [A]_1 or [A]_2, while the application layer > >> only knows about [A]. When selecting (depending on a relevant > >> predicate), you'd issue a UNION over [A]_1 and [A]_2, again with the > >> application layer only knowing about [A]. Is that it? > >> > > Yes thats exactly it. In the case of Postgres 9.2+ you get the select for > > free - Postgres's query planner now uses table CHECK() conditions when > > planning and so a SELECT on [A] would work as expected. > > Very interesting. I thought that Oracle was the only database that > really implemented partitioning "for free". Good to know > I should also mention that since Postgres 8 sometime, selecting on [A] was equivalent of of a select / union on [A]_1 and [A]_2, but its since 9.2 that Postgres's query planner has gotten smarter about using table conditionals to make that select smart. > > > The insert does not, > > at this time, come for free. Testing shows that using a trigger in the > DB to > > do the inserts is very slow, my own testing shows that doing the same > logic > > on the Java side is considerably faster and can be batched. > > OK > > >> > and the interface is so very tightly bound > >> > to the record and table interfaces of jOOQ it looks like I would have > to > >> > drop all the type safety. > >> > >> I'm not sure if I'm correctly understanding your ideas here. Why would > >> jOOQ's DAOs make you drop type safety? > >> > > > > Not to say that any of this isn't correct! I know I'm trying to do > something > > different then most people here - but when you have ~1 billion rows per > year > > being inserted into the DB, you get interesting problems to solve. ;-) > > I have done that with Oracle, although we used some lower-level > features (lower than SQL) to get the data loaded... It's interesting > nonetheless. :-) > Well Postgres has the COPY operation for bulk loading of data, but that is not my use case - this is a live feed of data which is being stored, so its important to keep insert time small. > > > I can see two obvious choices - > > > > Solution #1 > > [...] > > Solution #2 > > [...] > > Hoping you can prevent me from traveling down the wrong path(s). > > Yes, there are a lot of possible "wrong" paths here. First off, I > think it is important to understand that partitioning should be seen > as a storage-level feature, if implemented correctly. A good example > for this are Oracle's "PARTITION BY" DDL clause, which does not change > the logical structure of the partitioned table. In other words, > outside of the storage engine, there is only [A]. [A]_1 and [A]_2 are > not visible, except maybe to the CBO and some query hints. > > From what you're telling me, Postgres isn't that far advanced yet, and > some of the partitioning into [A]_1 and [A]_2 has to be done above the > storage-level, i.e. at the SQL-level (using triggers) or at the DAO > level (using Java code, phrasing the correct SQL). > Yes Postgres's partitioning requires considerable efforts on the developers part. > > Nonetheless, I think you should avoid disclosing the existence of > [A]_1 and [A]_2 anywhere above some "last minute" SQL patching > performed by some hook that you provide jOOQ with. Your application > should only ever perform actions on [A], as [A] is the only relevant > entity from a logical perspective. Storage facts should be hidden > entirely. > > In other words, this is more or less your Solution #2: > > > Solution #2 > > Build all [A] and then pass them to a custom DAO which then converts the > [A] > > TableRecord or POJO into [A]_1 or [A]_2 and pass them to their respective > > DAO. > > Yes. Based on your partitioning criteria (e.g. some date), you can > divide the set of [A] into various buckets, generate SQL for that > bucket and batch insert each bucket. > > > - How to convert [A] into [A]_* ? I've no idea, I hadn't seen anything > > obvious in the interfaces. > > Maybe, runtime table mapping could help, here? > > http://www.jooq.org/doc/2.6/manual/sql-building/factory/runtime-schema-mapping/#N1061E > > This feature was originally designed to allow for adding table > prefixes in environments where the database / schema is shared among > several applications - e.g. on a shared hosting provider. It could > work just the same for you. > > If you want, we could also discuss Solution #1, but I feel that it > will be much harder to do correctly. Specifically because the _1 and > _2 suffixes used for partitioning should probably be dynamic...? > > I think I understand what you're suggesting here - I'm going to try some of this out over the next couple of days. Best I can come up with is to use the date as a generated prefix - 2013_01_TABLE_A 2013_02_TABLE_A 2013_03_TABLE_A ... Of course, jOOQ itself won't even need to know about the child tables then, only the parent table TABLE_A. Thank you again for the suggestions and help. Pete -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
