I'm creating a database front-end system which has to accrue sales.
At the moment, there is one table that represents discrete transactions, and another that represents individual items sold. They have a one-to-many relationship. Both tables can contain an arbitrary amount of data, though, potentially a massive amount of data ( in the order of tens of millions of rows, though it can only grow, never shrink). 99% of the time, the user will only want to access recent data. It starts off with no data.
I've been told that this necessitates partitioning. I'd like to create a table that can *dynamically* generate new tables for every, say , month worth of data, to keep things efficient. This needs to be totally automated and encapsulated to the user, as this database is used by a front-end for non-technical people.
Here is a the manual page for PostgreSQL partitioning: http://www.postgresql.org/docs/current/static/ddl-partitioning.html
My questions are:
1.is this "the way it's done"? This has to be a common sort of problem. There has to be an approach that is generally favoured. Is this it?
2.If so, how? The manual doesn't cover creating tables dynamically.
3.If not, what should I do instead?
Thanks in advance,