I'll write something more detailed later, when I'm not under deadline.
TL;DR Postgres table partitioning is usable in jOOQ 3 with some tricks, more testing is needed to determine how speedy it is. Also, I'm new to jOOQ, and so its likely I'm not using it optimally. Implementation: Set up Postgres tables as described in http://www.postgresql.org/docs/9.2/static/ddl-partitioning.html But don't setup any insert / update / delete triggers - all those operations will be done in Java code you write using jOOQ. First build the record R from the parent table using the constructor (not the Executor). Second use the record to determine the partition table. Third, for each partition table build a custom org.jooq.conf.Settings object which has a MappedTable that maps the parent table to the partitioned child table - this can be done lazily. Something like: R record = ... Table<R> parent = ... Settings settings = new Settings() .withRenderMapping(new RenderMapping() .withSchemata(new MappedSchema() .withInput(parent.getSchema().getName()) .withOutput(parent.getSchema().getName()) .withTables(new MappedTable() .withInput(parent.getName()) .withOutput(partioner.determinePartition(record)) ) ) ); I cached these in a Map, and pulled them out when creating the Executor, which I had to build each time I did processing because the Executor is tied to the Connection. Finally, call record.attach(settings), and record.store(). The fetching of settings, and inserts / updates / deletes can all be built into a DAO implementer if desired. For selects, if you are using Postgres 9.2 or later you can allow Postgres to use the where condition to do query planning for you. But you can always determine the partition you need in jOOQ, and do the select there, just like the other queries built using the Settings. Another trick is to use a lookup to find the right table, though you loose some of the typing. For this trick, I used Public.SCHEMANAME.getTables() to build a table map (names to Table<TableRecord<?>> objects). Which allows me to do the selects on the partition instead of on the parent table. Then simply merge the generic record into a more specific record. Table<TableRecord<?>> tablePartition = tableMaps.getTablePartition(record); TableRecord<?> record2 = create.selectFrom(tablePartition)...fetchOne(); record.from(record2); OK, thats it for now. -- 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.
