When talking to people about data archival strategies, a question that comes up regularly is ‘how do I even get the archived data?’ This is in part because ~modern~ web frameworks like Rack-on-Rails do not always make multiple data sources a straight forward task. True to form, for every problem a web framework can create, postgres can usually fix it.
Say we have a main database – ‘burrito_store’ that holds the main transactions and an archive database ‘burrito_archive’ that holds stale records…ok, forget pretending, lets just get straight to code.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
On the archive database:
1 2 3 4 5 6 7 8 9 10 11 12 13 14
At this point, we should be able to query and interact with the desired table from either database. Now, we can just create a basic view to make things easier for our poor application:
1 2 3 4 5 6 7 8 9 10 11
Viola! It works! If you look at the cost of the foreign scan, you will see that this operation doesn’t come cheap or free. In this example, burrito_archive even lives on the same server, so you can imagine how performance amplifies as you get further from main datastore. That may or may not be important to you at this time and at a minimum, this strategy can at least get your Proof of Concept going while you check out gems like Octopus or additional decorators for your connection string/models.
Note: This is just a real quick and dirty way of doing this with very low technical overhead. Yes, other options exist depending on a variety of factors.