PGCon2009 - Final Release
PGCon 2009
The PostgreSQL Conference
| Speakers | |
|---|---|
|
Norman Yamada |
| Schedule | |
|---|---|
| Day | Talks - second day - 2009-05-22 |
| Room | DMS 1150 |
| Start time | 10:00 |
| Duration | 01:00 |
| Info | |
| ID | 146 |
| Event type | Lecture |
| Track | DBA |
| Language used for presentation | English |
Reconciling and comparing databases
Using schemas, Slony, DBI-link, pgTAP and other tools to compare different databases
The Millburn Corporation is a hedge fund which uses complex data-driven trading models based on the daily prices of various commodities, currencies and other inputs. As part of our application development process, we use independent stage and test instances of our production database to let us have a smoother and less mistake-prone deployment of new models and price streams. In this talk, I will discuss our setup, which makes heavy use of schemas, Slony, DBI-link, pgTAP and custom deployment tools in order to compare and reconcile data across databases.
Most of our applications make use of various input tables (e.g., prices, market_hours) to write to output tables (e.g., signals). Using Slony, we replicate our production database to all the other nodes. Stage and test have output tables in independent schemas. Then by changing search paths, it's easy to compare the differences between our production database and each node.
For comparisons between stage and test, which cannot be compared in the same database, we use DBI-Link. I will show how using ROW() comparison and recordset casting allowed us to build a custom data deployment tool that allows the creation, deployment and reversal of changesets from one database instance to another.
I will also discuss more complex instances of data modeling-- where, for example, only certain prices in a large dataset should be changed. In these cases, we make use of inherited tables with non-overlapping sequences and custom accessor functions to access different price streams in different schemas.
Lastly, I will also discuss how we deploy schema changes, function changes and trigger changes, which are more complex and cannot be compared through DBI-link. This will include a discussion of our pgTAP test framework for functions, which is still under very active development at the moment, but I hope will be mature by PgCon.