West 2007 Talks
Welcome to 8.3, Stupid Solaris Tricks
Ruby on Rails Essentials for PostgreSQL Enthusiasts
Has all the hype got you jazzed to develop Ruby on Rails applications on PostgreSQL? Is virtually everything you find about Rails MySQL- specific? Come to this talk to learn all you need to know to make Rails and PostgreSQL work together harmoniously. Topics will likely include:
- The ins and outs of Rails migrations
- How to add support for foreign key constraints
- Managing partial indexes and other PostgreSQL-specific
- Working with views
- Monkey patching for fun and profit
- Supporting multi-column primary keys
- The joys and pains of Rails collections
- Working with time zones
- The antiquity of the Ruby PostgreSQL driver
- Enforcing constraints in Rails *and* in the database
- Getting Rails to execute *your* queries instead of its
- Saved queries for "fat models"
David E. Wheeler is CTO of Portland-based Values of n, creators of the newly launched PostgreSQL-and-Rails-powered iwantsandy.com, home of Sandy, your personal email assistant. David is also maintainer and lead developer of Bricolage, a content management and publishing system powered by Perl and PostgreSQL. For his next trick, he may or may not build a Python or Lisp or BASIC-powered Web application, but whatever it is, it will likely be built on PostgreSQL. Unless it's a blog, in which case he'll just use SQLite.
Scaling PostgreSQL Performance with uni/cluster
Middleware technologies provide a means to build highly available and scalable database clusters using off-the-shelf PostgreSQL database servers. This talk starts with an overview of middleware cluster architectures using Continuent's commercial uni/cluster product as an example. We then present clustered PostgreSQL performance numbers using specialized benchmark tools we have developed and plan to release shortly into open source. The talk will conclude with some lessons for how to improve horizontal scaling of PostgreSQL, including plans for a new open source offering that we hope will be of interest to many PostgreSQL users.
Continuent sells advanced clustering products to improve availability and scaling of open source databases.
Continuent sponsors the open source Sequoia database clustering project, which is the basis for the commercial uni/cluster offering. Continuent is an active participant in the PostgreSQL community.
Robert Hodges is CTO of Continuent, where he has worked since early 2006. He has extensive experience with databases, including 7 years working at Sybase on object oriented databases and transactional messaging. He has also built and operated large hosted web applications on enterprise databases. Robert is currently responsible for all aspects of Continuent's technical strategy.
Understanding Query Execution in PostgreSQL
This talk will describe the query execution techniques implemented by PostgreSQL. In most database systems, queries are first parsed, then optimized (planned), and finally executed. This talk will describe the design and implementation of the query executor, the final stage in that process. The query executor is responsible for taking the query plan produced by theoptimizer, and executing the physical operations described by the plan in an efficient manner.
Specific topics will likely include:
- a brief summary of the lifecycle of a query in PostgreSQL and the role of the query optimizer
- the "iterator model", which is the interface used to communicate data and exchange control flow in the query executor
- scan evaluation
- basic sequential and index scans
- bitmap scans
- predicate evaluation
- evaluating subqueries and set-returning functions
- join evaluation
- nested loops, hash joins, sort-merge joins
- outer joins
- aggregate evaluation
- grouping via sorting
- grouping via hashing
- how to understand the output of EXPLAIN
Neil Conway has contributed to PostgreSQL since 2002, and has been a CVS committer since 2003.
During that time, Neil has been a high school student, an intern at Red Hat, a university student, and an employee of Fujitsu Australia. Originally from Toronto, Neil recently completed his undergraduate degree in Computing Science at Queen's University in Kingston, Ontario. He is currently a Sr. Software Engineer at Truviso, Inc. in the Bay Area, working on their PostgreSQL-based data stream management system.
A introduction to using system tools to identify what the system is doing. This will go over using top and iostat to determine what the system is physically doing, and then using tools like ps and querying the PostgreSQL system catalog tables to determine what queries are running, what locks have been acquired, and where the tables and indexes are physically located on the system in order to correlate the physical activity to what the database is doing.
Mark Wong's experience includes TPC benchmarking and analyzing performance of database systems.
PL/Proxy and Horizontal Scaling
PostGIS (Geographic Databases)
This work is licensed under a Creative Commons Attribution-No Derivative Works 3.0 United States License.
I will present a quick introduction to PostGIS in an example driven format; I will not cover installing PostGIS, nor will I have much time to cover making printable maps (though I hope to alluded to it at the end). My presentation will consist of six sections. (1) An overview of geographic databases: what vector geographic data is, how it is stored, how it is represented in text, and what a projection is; I will also touch on raster data, though we won't cover it in any detail. (2) Then I will cover how to get data into a PostGIS database: importing a shapefile and importing text data (from a GPS?). (3) Then I will cover how PostGIS keeps track of the projection of the data Ã the
"SRID": what is, how to find it, how to set it. (4) Then I will show three examples of queries on geographic data: finding the istance and angle of points to various geographic features, using non-geographic data that has a convenient join key (zip code) to count points within polygons, and then an exploration of several of queries that show off some of the functions in PostGIS. (5) Then I will cover how to modify geographic data: adding rows, merging polygons, creating lines from points, adding new derived geographic columns, selecting into temporary tables, and possibly writing triggers that manipulate geographic data. (6) We will end with how to output geographic data: a Google mash-up, tables that can be used in spreadsheets or statistical programs, viewing data with map programs (QGIS), other command line tools for geographic data, and possibly some talk on PostGIS as it works with mapserver.
Webb Sprague is a Ph D student studying culture and family formation in a small Oregon town. He uses PostgreSQL for his job (programmer in the UC Berkeley Demography Department), and for storing population data for his research. He has written several web-applications, generally using Python, Postgres, Apache, and Linux. He has been a PostgreSQL user for almost seven years.
Babel of Procedural Languages
IM IN UR DATABASE, USIN' LOTSA PLS
David Fetter is based in the San Francisco Bay Area and has worked in various commercial enterprises, non-profits and educational institutions. He has worked extensively with Oracle, PostgreSQL, MySQL, Perl, PHP, PL/SQL, PL/PgSQL, PL/Perl and (of course!) vim on transaction processing and business intelligence systems. In his free time, he brews beer, rides his bicycle, and helps run several organizations for computer professionals including the San Francisco Perl Users' Group and the San Francisco PostgreSQL Users' Group.
PostgreSQL Partitioning, semantics, pitfalls and implementation
PostgreSQL table partitioning allows you to scale your data to significantly larger volumes than would otherwise be possible. Getting started with table partitions in and of itself is not complicated, but if you want to get maximum benefits from your setup you can't just follow the documentation. This talk will walk through the ins and outs of table partitioning including:
- What table partitioning is, and how it is implemented in
- Rules, Constraints, Inheritance and how it all comes
- When and why to use triggers for partitioning.
- Automated partition maintenance.
Learn how to setup and maintain table partitioning in PostgreSQL with hands on examples. We'll walk through manipulating SQL, writing rules and trigger, and show you how you
can use plpgsql to automate your partition maintenance.
Robert Treat works as a database architect at open source powerhouse OmniTI. Over the years he has run PostgreSQL operations in high-transactional OLTP environments and large-scale (TB+) ODS systems, interfacing with a wide variety of technology solutions pushing the boundaries on the traditional ideas of LAMP systems. A veteran in the industry, Robert has spoken at many industry conferences including OSCon, PGCon, and PHPWorks, and co-authored the book Beginning PHP and PostgreSQL 8.