Utilizing PostgreSQL in a startup environment to design transaction and analytics databases from scratch.

One of the most rewarding projects I worked on was to streamline and automate our database patch release process. I worked on it during personal time. With a mix of SSH forwarding, Perl scripts, shell scripts and SQL commands, I was able to write about a dozen scripts to handle the various phases of the release process: parsing JSON server configuration file, pushing codebase tarball to deployment and database servers, checking replication status, generating patch commands to run remotely via ssh, commands to put new objects into replication, verifying deployment and creating documentation about the release for use by other business departments.The reason this project was so satisfying is because it saved me and my co-workers hours of work every week since all the steps were previously done manually by ssh'ing to each database server (20-ish) to perform the work.* Designed and developed database objects utilizing TDD (test-driven development) for several domestic and international web applications on teams of developers and business stakeholders in a fast-paced, agile environment, with an emphasis on data quality.* Instituted database standards in an SOA environment deployed across multiple database clusters.* Technologies:** Databases: PostgreSQL** Languages: SQL, PostgreSQL PL/pgSQL, Unix/Linux shell, r u b y, Perl** Testing Suites: PgTap, rSpec** Source control: GitHub, Perforce** OS / Platforms: Linux

Being the first full-time DBA at Opus, I enjoyed implementing several performance-enhancing improvements: partial and functional indexes, table inheritance/partitioning, tuned default_statistics_target settings, trigger-based materialized views, and set-returning functions in place of complex views. I also implemented PITR backup/recovery scheme which allowed for high-availability and failover scenarios.One of the biggest challenges that I had significant satisfaction in accomplishing was performing a nearly-no-downtime upgrade of our PostgreSQL servers running on HPUX. With the versions of PostgreSQL, HPUX and the replication suites at the time, there were no upgrade paths available to us. I set out to write my own upgrade tool. By analyzing table usage statistics, I was able to customize how I would migrate the data to the new PostgreSQL clusters. For highly volatile tables, I used a combination of backfilling/watermarks and trigger-based change tracking. For small lookup tables, I used a cutover-copy methodology. For pre-go-live verification, I used a Perl utility, called pg_comparator that would checksum rows to validate table contents. With foreign key validation turned off during the loading, I was able to basically do non-binary checkpoints of the data. I did not need each change, just the snapshot of the changed data as of a certain sync time. It was eventual-consistency asynchronous replication. The only downtime needed was less than 10 minutes to briefly verify the data replication of the last few transfer sets. After significant testing, we used this tool to upgrade 15 database servers without any problems!Technologies* Databases: PostgreSQL, Microsoft SQL Server* Languages: SQL, PostgreSQL PL/pgSQL, Unix/Linux shell, Perl, T-SQL* Testing Suite: PgTap* Source control: SVN, CVS* OS / Platforms: Linux, HPUX, Windows

In this greenfield database setting, I was able to implement a warehouse environment and processes as well as user-acceptance testing, demo, staging and fail-over environments. In order to verify all database changes were rolled out to all environments, I developed a way to 'diff' database schema definitions. When the schema structure is output from PostgreSQL's pg_dump, the order is almost never the same as a another database's. Thus a diff would not be effective by itself to compare schemas. I wrote a Perl script that parses a schema output file and will prefix each line with identifying information about the object. It then can sort the output into a file that can then be compared via diff to another output file. This proved helpful in versioning schemas as well as finding any changes that were made outside of the release process.Technologies:* Databases: PostgreSQL, Oracle* Languages: SQL, PostgreSQL PL/pgSQL, Unix/Linux shell, Perl, Oracle PL/SQL* Source control: CVS* OS / Platforms: Linux, Sun Solaris