Managing two sets of data in one database

From: Jonathan Bartlett <jonathan(dot)l(dot)bartlett(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Managing two sets of data in one database
Date: 2012-03-29 16:43:52
Message-ID: CAHRTq6Q9hoDFR1kXS4znxXX=ijEKstuoQQJo1wBkmu6QNWKMBw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a database which contains two primary sets of data:

1) A large (~150GB) dataset. This data set is mainly static. It is
updated, but not by the users (it is updated by our company, which provides
the data to users). There are some deletions, but it is safe to consider
this an "add-only" database, where only new records are created.
2) A small (~10MB but growing) dataset. This is the user's data. It
includes many bookmarks (i.e. foreign keys) into data set #1. However, I
am not explicitly using any referential integrity system.

Also, many queries cross the datasets together.

Now, my issue is that right now when we do updates to the dataset, we have
to make them to the live database. I would prefer to manage data releases
the way we manage software releases - have a staging area, test the data,
and then deploy it to the users. However, I am not sure the best approach
for this. If there weren't lots of crossover queries, I could just shove
them in separate databases, and then swap out dataset #1 when we have a new
release.

Does anyone have any ideas?

Thanks,

Jon

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John R Pierce 2012-03-29 17:54:31 Re: Managing two sets of data in one database
Previous Message Carson Gross 2012-03-29 16:41:28 Re: why is pg_dump so much smaller than my database?