PostgreSQL: best way to create new/duplicate existing tables every year

Question

Hi guys, referring to this question, I've decided to duplicate the tables every year, creating tables with the data of the year, something like, for example:

orders_2008
orders_2009
orders_2010
etc...

Well, I know that probably the speed problem could be solved with just 2 tables for each element, like orders_history and order_actual, but I thought that once the handler code is been wrote, there will be no difference.. just many tables.

Those tables will have even some child with foreign key; for example the orders_2008 will have the child items_2008:

CREATE TABLE orders_2008 (
    id serial NOT NULL,
    code character(5),
    customer text
);

ALTER TABLE ONLY orders_2008
    ADD CONSTRAINT orders_2008_pkey PRIMARY KEY (id);

CREATE TABLE items_2008 (
    id serial NOT NULL,
    order_id integer,
    item_name text,
    price money
);

ALTER TABLE ONLY items_2008
    ADD CONSTRAINT items_2008_pkey PRIMARY KEY (id);

ALTER TABLE ONLY items_2008
    ADD CONSTRAINT "$1" FOREIGN KEY (order_id) REFERENCES orders_2008(id) ON DELETE CASCADE;

So, my problem is: what do you think is the best way to replicate those tables every 1st january and, of course, keeping the table dependencies?

A PHP/Python script that, query after query, rebuild the structure for the new year (called by a cron job)? Can the PostgreSQL's functions be used in that way? If yes, how (an little example will be nice)

Actually I'm going for the first way (a .sql file containing the structure, and a php/python script loaded by cronjob that rebuild the structure), but i'm wondering if this is the best way.

edit: i've seen that the pgsql function CREATE TABLE LIKE, but the foreigns keys must be added in a second time.. or it will keep the new tables referencied tot he old one.

Answer 1

PostgreSQL has a feature that lets you create a table that inherits fields from another table. The documentation can be found in their manual. That might simplify your process a bit.

Answer 2

I'd recommend orders and order_history... just periodically roll the old orders into the history, which is a read-only dataset now, so you add an index to cater for every single query you require, and it should (if your data structures are half decent) remain performant.

If your history table starts getting "too big" it's probably time to start think about data warehousing... which really is marvelous, but it certainly ain't cheap.

Answer 3

As others mentioned in your previous question, this is probably a bad idea. That said, if you are dead set on doing it this way, why not just create all the tables up front (say 2008-2050)?

Answer 4

You should look at Partitioning in Postgresql. It's the standard way of doing what you want to do. It uses inheritance as John Downey suggested.

Answer 5

Very bad idea.

Have a look around partitioning and keep your eyes on your real goal:

• You don't want table sets for every year, because this is not your problem. Lots of systems are working perfectly without them :)
• You want to solve some performance and/or storage space issues.