East 09 Talks

Jim Moldgenski

Architecting Your PostgreSQL Application for the Cloud

The cloud is a powerful environment for deploying applications, but like any type of infrastructure, it has strengths and weaknesses. This session will discuss how to leverage those
strengths and mitigate the weaknesses on the data tier of your application. This architectural discussion will focus not only on performance and scalability, but also on security and management within a cloud environment.

Greg Sabino Mullane

Bucardo

Bucardo is a replication system for Postgres that uses triggers to asynchronously copy data from one server to many others (master-slave) or to exchange data between two servers (master-master). We'll look at replication in general and where Bucardo fits in among other solutions, we'll take a look at some of its features and use-cases, and discuss where it is going next. We'll setup a running system along the way to demonstrate how it all works.

Monisha (Sarah) Pulimood

Building A Collaborative Environment With PostgreSQL To Enhance The Learning Experience | Slides

This talk will focus on CAFE, a Collaboration And Facilitation Environment, built on top of PostgreSQL, that we developed to support a pilot interactive journalism institute for middle schoolers. Using this environment, which is more than a content management system, we are introducing young people to
advanced computing concepts, principled reporting and project management while raising their awareness of privacy and security issues. This environment is now being used in a writing-intensive undergraduate computer science class.

Stephen Frost

Column-Level Privileges

PostgreSQL has recently gone through some changes to the privilege/GRANT system, especially with 8.4. We will discuss the implementation of column-level privileges in 8.4 and what can, and can't, be done with them, including what happens in JOINs and partial INSERTs. We will also review the various permissions which can be GRANT'd at each of the PG levels: database, schema, table, and the new-in-8.4 column-level privileges. We'll go over the default permissions, the new TRUNCATE permission and why it's a separate and distinct permission, and the implications and side-effects of certain related commands.

Joshua D. Drake

Configuring a Warm Standby, the Easy Way

Slides

A 45 minute talk discussing PostgreSQL PITR and using PITRTools.

Lacey Powers

Converting your database and application from Sybase/MSSQL to PostgreSQL

This talk will cover a practical example of how to convert your database and application from Sybase/MSSQL to PostgreSQL, including ddl, data, stored procedures, and code. Code snippets will be Java and PHP, but the concepts should be applicable to any codebase.

Chris Browne

Database Development Policies

Jignesh K. Shah

Effects of Flash and SSDs on PostgreSQL

With the advance of Flash based SSDs, we look at the impact of using such drives with PostgreSQL. Primarily the talk will talk about observed impact on PostgreSQL using some well known workloads.

John Rogelstad

I want my ERP: Money for nothing, toolkit for free

While it seems as though a new application development toolkit pops up almost weekly, until now most extensible accounting and business applications still mostly rely on expensive and proprietary technology to make customized solutions and modules. But who wants to write an Accounting system with a toolkit from scratch? Learn how Postgres and other open source tools were used to create a powerful yet friendly end user development environment for xTuple ERP that can be used by novices
and professional programmers alike to write customizations or extensions to the free PostBooks Edition, as well as the commercially licensed ERP products. Highlights include:

How Postgres object inheritance was used to build extensible business logic
Using Postgres as meta data storage for the application
Using Postgres views to implement model/view methodology
Embedding Qt Designer and OpenRPT into the business application
Using a Javascript engine to write UI business logic
Packaging it all up using XML and basic OS tools.

Jeff Brown

Grails In Practice

Grails is a full stack MVC framework for building web applications for The Java Platform. Grails makes web application development both fun and easy. This session covers all of the fundamentals of building web applications with Grails. Businesses need rich web applications and developers want to be
able to build those applications without the pain that usually comes along with doing so. Grails addresses these needs very well. Grails demolishes many of the pain points that Java developers have
almost(not quite) become numb to after years of suffering. This session covers all of the fundamentals: - Introduction To Grails-Domain Objects - Controllers - GSPs - Custom TagLibs - GORM

Jeff Brown

Grails Workshop

Grails Workshop. For those who want to get their hands dirty with Grails, this is a three hour workshop that follows up on the Grails in Practice mini-tutorial.

Gavin M Roy

Introduction to Golconde | Slides

Learn about Golconde, a queue-based data distribution system developed at myYearbook.com. In implementing data distribution via external message queues, Golconde differentiates itself from traditional PostgreSQL replication tools, moving the workload for replication management outside of PostgreSQL itself. We will review the design decisions that resulted in Golconde as well as see it in action. Golconde is a queue based replication solution for PostgreSQL written in Python. It is designed to be loosely coupled and rely upon existing enterprise messaging systems that have STOMP protocol support. Designed to scale easily and with multi-data center implementations in mind, the application and message queues for distribution live outside of the database. By decoupling Golconde from PostgreSQL it
is differentiated from existing replication solutions, moving the workload from the database tier, where CPU, RAM and IO overhead can be very expensive, to a commodity layer where the operational cost for performing the data distribution work is much less expensive. In this session we will review the architecture and implementation details of Golconde and examples of its use at myYearbook.com, including a live demonstration that illustrates the ease of setup and operation.

Mike Coyle

Introduction to the Pylons Web Application Framework | Slides

Pylons is a lightweight web framework for the Python programming language. It emphasizes flexibility and rapid development, and is built atop open standards. This mini-tutorial will explain the features, benefits, and general architecture of Pylons, and its unique value proposition among the many web application frameworks available today. It will also cover the use of PostgreSQL in Pylons applications, using the SQLAlchemy object/relational mapping library.Discussion materials will be targeted to IT professionals who are not necessarily Python developers.

Bruce Momjian

Mastering PostgreSQL Administration | Slides

Greg Sabino Mullane

Monitoring Postgres with check_postgres.pl

What should you monitor? And how? We'll look at the sort of things you should care about when watching over your Postgres databases, as well as ways to graph and analyze metadata about about your database, with a focus on the check_postgres.pl script.

Josh Williams

More Than Storage: Intro to PL/pgSQL

You give it data, it gives it back when you ask for it. But PostgreSQL can do much more for you. Meet the PL. Procedural Languages let you execute code inside the database itself. We'll look at PL/pgSQL and some of its friends. Using PL's you can combine complex database logic into a single function to execute manually or define triggers that maintain things automatically, separating out those tasks and freeing your application. It's a laz^H^H^H smart programmer's dream come true!

Robert Treat

No More Waiting, A guide to PostgreSQL 8.4

Another year, another PostgreSQL release, and once again this release packed full of new features that will help you whether you develop apps inside or outside the database, or you just need to keep them running. This talk will give an overview of the new features available in 8.4, and give you pointers to talks during the rest of the conference you'll want to focus on to get the most out of 8.4.

Larry Price

pgcrypto benchmarking

A sample database and test script for benchmarking the performance of the cryptographic operations available from the pgcrypto package on different classes of hardware is presented. Along with results from running the test in multiple hardware and OS environments. Including also some side notes for structuring search indices for encrypted fields, and a discussion of the role of cryptography in a database security plan.

Robert Treat, Greg Smith, Joshua Drake, Andrew Dunstan

Performance Roundtable

Ever wonder about PostgreSQL performance? Have a particular problem you are trying to conquer? Do you need to know best performance practices? Learn from the recognized masters in the community in this 90 minute performance round table.

Jonah Harris

Playing with Playr: The Postgres Application Testing Tool

Have you ever wondered how well your application is going to perform when moving to new server hardware or upgrading to a newer version of Postgres? Do you want to know what queries will
be faster and which ones may need tuning? Are you having a hard time getting custom scripts written just for this purpose? If so, Playr is your new best friend. Designed to identify potential issues resulting from software or hardware upgrades on myYearbook.com's high-volume databases, Playr is able test an
entire application and provide the administrator with a report detailing the performance and error-related divergence of a Postgres configuration from the production workload. This sounds good, but how does it work? Playr works by capturing your production database workload, analyzing it, and processing it into a set of binary capture files. Once the workload capture and processing has been completed, replay can be performed using one or more driver systems. By using the binary capture files, Playr is able to recreate the production workload exactly; all concurrency, timing, and commit ordering remain identical to production.

Brent Friedman

Postgresql and Java

Introductory level talk on using Postgres with
Java.

Kevin Kempter

PostgreSQL Backup/Recovery and Replication | Slides

Newbie track talk around developing a "data availability" strategy. Covered topics will be: - PostgreSQL dumps (pg_dump & pg_dumpall)- PITR (Point in time Recovery)- Warm Standby server- SLONY- Putting it all into a 'data availability' strategy.

Jonah Harris

Predicting Postgres Performance: Practical Queueing Theory for
Postgres DBAs

So, you've solved all of your current performance problems, but are you ready for tomorrow? Suppose your website traffic increases three-fold, or your company has a huge end-of-year processing job to run, at what point will your system become unusable? In this case, is it a CPU problem, I/O problem, or both? Should you add additional CPUs or can you just get faster CPUs? How fast does your I/O subsystem need to be in order to handle your workload? How much would query tuning help? This session is designed to help you answer those questions. While often misunderstood and underutilized, queueing theory has long-since provided us with the ability to model behaviors and performance characteristics of complex computing systems. Unfortunately, many people shy away from queueing theory because it is often expressed using complex mathematical equations. However, in this session, we'll cover the basics of queueing theory and demonstrate how to apply its concepts and equations to practical, real-world
situations. Specifically, we'll cover:

Queueing Theory Overview
Response Times- Baselines
Distribution Patterns
Standard Averages vs. Weighted Averages
Workload Characterization
Statistics- Sampling
Operating System Statistic Collection
Postgres Statistic Collection
Queueing System Notation
Queueing System Equations
Erlang C/Little's Law- Modeling CPU subsystems
Modeling I/O subsystems
Determining when a server will no longer be able to handle a given workload.
Determining whether a system could handle an increased workload.
Determining whether more CPUs or faster CPUs are needed.
Determining I/O subsystem needs.
Gotcha's (Physical vs. Effective CPUs, ...)

Norman Yamada

Reconciling and comparing databases using schemas, DBI-Link and Slony | Slides

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.

Joshua D. Drake

Replication using PostgreSQL Replicator

Slides

Configure the Open Source Replicator for PostgreSQL.

Aurynn Shaw

Simpycity, Pylons, and non-ORM Database Usage | Slides

Discussion on the basic philosophy of Simpycity, the benefits of a stored procedure- based architecture vs. a conventional ORM. Using Simpycity, and effective use cases. Easy to use advanced tricks to provide ORM-like functionality in Simpycity. Time allowing, experimental concepts that can be easily implemented in Simpycity (transparent 2-phase commit)

Monisha (Sarah) Pulimood

Socially Relevant Database Projects in the Undergraduate
Classroom Slides

In this session I will talk about how database concepts, including data management, privacy, security, and reliability, are reinforced through building socially relevant projects in my undergraduate database systems course and mentored research. Some examples I will talk about are a system to aid investigative reporters analysis data on crimes in a large city; a volunteer registration system for Habitat for Humanity; and a content management system for the College online magazine.

Josh Williams

The Art of Indexes | Slides

Figuring out the correct indexes to create can sometimes seem more art than science. In this session we'll decipher the multiple index types and their uses, cover multi-column versus multiple single-column indexes, and look at expression-based and partial indexes. Armed with this overview, an
understanding of EXPLAIN, and a little practice, you'll be a skilled index artist in no time.

Greg Sabino Mullane

The power of psql

All about everyone's favorite Postgres utility, psql, the best command-line database interface, period. We'll cover basic and advanced usage.

David Fetter

Trees and More in PostgreSQL: Common Table Expressions | Slides

With SQL:2008-compliant Common Table Expressions, PostgreSQL has whole new capabilities you can use and a simple syntax to use them. Find out how to do tree structures inside the database, and much, much more.

Joshua Drake

Welcome to PostgreSQL Conference East 2009 (Keynote)

Slides

A 45 minute talk welcoming the members of the talk and discussing the current focuses of PgUS.

David Fetter

Windowing Functions: Putting the TPS in TPS Reports Slides

PostgreSQL 8.4 will include SQL:2008 windowing functions, useful for those all-important TPS reports. They'll even supply the new cover sheets. In addition, they provide ways to slice, dice, and re-order results to your liking. Find out how!