In continuation to a question posted by me on Is it a good idea to move high-volume and highly-accessed tables to a separate database?, am looking out for different techniques/solutions available for database archiving in PostgreSQL.

Few solutions I can think of are:

  1. Table partitioning
  2. Separate tablespace and/or schema
  3. Moving archived records/tables to a different harddisk

Any other suggestions/pointers/solutions are really welcome and appreciated.

NOTE: We're running PostgreSQL v9.1.3 on CentOS5.2

1 Answer 1


My suggestion about archiving:

  1. Create archive_tablespace (if you want you can separate hardware on archive)
  2. Create tables. For example we want to archive table posts.

    create table  posts_all ( LIKE public.posts)  ;
    create table  posts_archive () inherits  ( public.posts_all)  ;
    alter table  public.posts  inherits ( public.posts_all ) ;

    After that we will have 2 new tables: public.posts_all (with the same columns like in posts) to query all posts (archive and production) and public.posts_archive to query all archive posts. Public.posts will inherits from posts_all.
    Inserts should go in a old way (to table public.posts) unless you will write triggers on posts_all to redirect inserts to posts table. If you have partitioning it will be more complicated. With working application and before old data migration you don't have to change anything in application code to work with this approach.

  3. Create schema archive for logical separation. My suggestion will be to separate archive data by some time period (year or month) if possible (archive_2005).

  4. Create archive tables in archive_year schema

    create table archive_2005.posts (
      check(record_date >= '2005-01-01 00:00:00'::timestamp 
        and record_date <  '2006-01-01 00:00:00'::timestamp)
    ) inherits (posts_archive) tablespace archive_tablesapce;

    After that you will have new table posts in schema archive_2005 and postgresql planer will know that data there is only in designed time period. If you query by another time period postgresql will not search in this table.

  5. Create functions/procedures/triggers to move data to archive tables.

  6. Do archive once for a time period (year here) and vacuum old table or do it automatically by triggers (heavier on autovacuum). There are many advantages and disadvantages in both techniques.

If implemented:

  1. Can query archive (select * from posts_archive), all (select * from posts_all) and production (select * from public.posts) data separately
  2. Can do dump archive schemas separately and drop cascade on them in easy way. pg_dump -s archive_2005 datase_name drop schema archive_2005 cascade; --be careful because it removes all related tables
  3. Old data separated physically by tablespace and logically by schema.
  4. Quite complicated structure to manage archiving process
  5. Can create different indexes on production and archive tables to optimize queries to both (smaller and specialized indexes = faster queries and less space required)
  6. If you have partitioned tables (by year or month) archive process will be just to move whole table to archive_tablespace or just alter it to inherit from posts_archive (I didn't test this)
  7. If you don't want to access old (archived) data you don't have to change anything in application.

This is general technique and you should adapt it to your needs. Any suggestions to improve this?

Further reading: PostgreSQL inheritance, partitioning

  • I couldn't able to clearly understand the 2nd step Create tables (table posts example):. Can you explain that specific step on how many tables are there in total and how the inheritance between tables are related to each other?
    – Gnanam
    Nov 15, 2012 at 5:37
  • Edited answer. I hope it's enough to understand and implement archiving.
    – sufleR
    Nov 15, 2012 at 9:44
  • In real-time application, there will be more than one dependent/child tables connected/related to with the parent/master table. So the steps outlined here is automatically applicable to all its dependent/child tables as well? Is my understanding correct?
    – Gnanam
    Nov 15, 2012 at 10:15
  • Yes. This is only one table example. I have this implemented in 100GB database but only for few biggest tables.
    – sufleR
    Nov 15, 2012 at 10:26
  • So in this case, which table will be normally empty (posts, posts-all or posts-archive), that exists just to represent the entire data set?
    – Gnanam
    Nov 15, 2012 at 10:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.