For a project we have to work with a table where around 30 mln. records per year will be inserted. But 90% of the work happens on the records of max 1 year old. So to guarantee a good performance I want to use table partitioning (http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html). It should be transparent to the database user (Mendix), but I'm not sure if it is totally transparent. Question: are there any experiences with postgres table partitioning in combination with Mendix. Does it / will it work, in other words: is it indeed completely transparent. It would be nice, and a big step in performance improvement, if Mendix would support partitioning out of the box.
We did something like this by doing a daily VACUUM ANALYZE on 2 tables in our database. These tables were heavily used by delete and insert scripts and without the VACUUM ANALYZE, it didn't work properly. The duration of our flow was 10 times slower without the VACUUM.