OSCON 2006: Big Bad PostgreSQLBy Matthew Eernisse
Theo Schlossnagle is a principal at OmniTI Computer Consulting, working in the areas of scalable internet architectures, database replication, and e-mail infrastructure.
This talk was on converting a really large (over 3 terabytes, largest table is 1.8 billion rows) data warehouse database from Oracle 8i to PostgreSQL. The reason for the conversion was to save in licensing costs. They wanted to move their Oracle licenses over from the data warehouse DB to use them on the online transaction processing (OLTP) system.
The reason for choosing Postgres over MySQL was that Postgres has a much longer history with the kind of advanced features he needed. They needed (and were able to hack PostgreSQL to get) the following features:
1. Data partitioning (spreading tables over multiple drives)
2. Large selects (50-million-row return sets, over 100GB of data)
2. Incremental COMMITs for really, really long queries
The bottom line was that with some patience and creating thinking, they were able to migrate a really humongous database to PostgreSQL from Oracle, and ended up saving themselves $500,000 USD in licensing costs. A pretty good chunk of change.
Theo has put his slides up here.