West 2008 Talks
Building search.postgresql.org | Video
This talk will discuss how search.postgresql.org uses the PostgreSQL Full Text Indexing, first tsearch2 in 8.2 and now the native Full Text Indexing in 8.3, to deliver searches of web and list archives with much better performance and capability than the previous generic web indexing tools used. It will discuss how the system is built, how context sensitive indexing helps a lot in keeping performance and results good, as well as how the full text searching feature in PostgreSQL was set up.
Can we do anything more in 6 months? | Video
Joshua D. Drake - Keynote
This talk discusses the progress of the community over the six months from East 2008 to West 2008. Specific topics are working with higher education and tertiary communities such as Drupal.
web framework, ORM, sprocs, rules and views - loosely coupled and in harmony (with perl)
Matt S Trout
This is the tale of building an app with a moderately complex DB schema and strict performance requirements on the single most complex query in the application, mixing and matching straightforward DBIx::Class ORM code to automate away the simple stuff, postgres views and triggers to create a transparent OODB-style setup using multiple table inheritance and to allow for
complex queries to be optimised at the database level without the ORM getting in the way, and how we mixed the two together to create an app that not only meets the performance requirements set out at the start of the project but substantially exceeds them, and is still fast to add features to and easy to maintain.
Code Sprint: Idea to open source in 3 hours!
(3 hour tutorial/code sprint)
Curious about PostgreSQL internals and the developers who make it happen? Wonder what it would be like to contribute to an open source project? Want to get your hands dirty with testing, documentation or the code?
Join us for 3 hours of hacking on PostgreSQL! Led by programming and subject area experts, this session will pair you up with other participants and current contributes to work on and contribute back to the project.
Developing a PL (Procedural Language) for PostgreSQL | Video
(90 minute tutorial) Writing a PostgreSQL procedural language is an effective way to get used to the PostgreSQL source code and how to function within it, and provides some interesting insights into things like interpreter/compiler development, PostgreSQL's context-based memory model, and SPI. This tutorial will cover how to use Flex and Bison, the tokenizer and parser systems at the heart of PL/LOLCODE (as well as PL/pgSQL, the PostgreSQL query parser, and several contrib modules), the Server Programming Interface, how Datums move around inside PostgreSQL, and how language handlers work.
End to End XML: Simplify development by leveraging XML in Postgres and Adobe Flex | Video
Scott Bailey & Dave Metzler
(90 minute tutorial)
Advances in XML technologies have totally revolutionized the way database driven applications are made. Relational databases and client side applications can now natively "talk" XML, eliminating the need to have a middle-tier translate the data between the database and the client. Learn how to read and write XML in Postgres and Adobe Flex. Then tie these three technologies together to build applications. Never before have we been able to do so much, so easily.
Filesystems, RAID and More Video
Mark Wong & Gabrielle Roth
Sharing of data from filesystem testing using 'fio' on a Linux based system. The data includes results from using various Linux filesystems, software RAID, hardware RAID, and volume management (LVM). Other aspects includes setting RAID stripe sizes, filesystem readahead sizes, and effects of partition alignment.
Getting Smart about the New World of PostgreSQL Replication
Until recently PostgreSQL replication was a popular place for bullies to kick sand on the project. But no more! Far from being a 98-pound weakling, PostgreSQL has a broad number of replication options with more on the way. This talk explains how database replication works and all the different ways it is implemented for PostgreSQL. You'll find this information essential in order to understand which replication solutions work for you.
Inside PostgreSQL Shared Memory | Video
This talk covers how PostgreSQL uses shared memory to communicate between database sessions. This includes I/O buffers, locking, and transaction information.
GUCs: A Three Hour Tour | Video
(3 Hour Tutorial)
PostgreSQL now has 187 GUCs, most of which are server configuration settings, and begins to rival Oracle in complexity of administrator options. Take a high-speed tour of all of the options with core team member Josh Berkus.
Linux-HA + DRBD + PostgreSQL | Video
Through the use of Linux-HA and DRBD you can make a highly available PostgreSQL cluster. Linux-HA is a widely used high availability solution for managing resources. DRBD provides a method for syncing block devices through a TCP/IP network.
Modeling the Hierarchical Nature of Data | Video
Richard Broersma Jr.
While our perceptions of reality are defined in terms of entities and their associated relationships, often we has humans tend to view our reality in terms of classifications. Incorporating hierarchical modeling into our modelling toolkit gives us additional ways to model data as we naturally perceive it.
OLAP Made Easy: Windowing Functions in SQL.
Until now, writing reporting queries was an exercise in frustration. In 8.4, you will have a new set of tools to make this easy and fun, two words you might not have thought of to describe report writing in the past. Discover windowing!
Matt S. Trout
A short introduction to the new Perl5 including DBIx:Class and the new Perl5 Non profit for advocacy!
PostgreSQL as an Application Server
PostgreSQL can serve as two layers of the traditional three-layer application cake, improving performance, security and giving the implementor one less thing to have to configure.
This is an abbreviated subset of a four-day training course is ideally suited for DBAs to become comfortable in managing a PostgreSQL database. It covers the fundamental concepts and skills needed to install and configure the database. Topics will include:
- PostgreSQL Terminology
- Process Architecture
- Data File Architecture
- Initial Configuration
PostgreSQL Optimizer Exposed - Video
The planner/optimizer is arguably one of the most complicated components of PostgreSQL. Visual Planner is a new tool designed to shed more light on the planner and the complex details held within. Developed recently as part of the Google Summer of Code program, this tool describes every plan considered by the planner and presents these details in a clear and concise way. Discover how the tool works and learn a bit more about the planner along the way.
PostgreSQL Unit Testing with pgTAP | Video
David E. Wheeler
This talk introduces pgTAP, a library of PL/pgSQL functions for unit testing your database. Got custom functions you need to test? Triggers? New data types, enums, or domains? This is the test suite for you!
Proprietary to PostgreSQL: Moving Your Data to Open Source
Most of us have been required to work with closed source database systems at some point in the past or present. With luck, you'll get the opportunity to change that! But moving from a foreign system to PostgreSQL isn't the easiest of tasks.
Proprietary to PostgreSQL lays out the steps to start you on the path to enlightenment ... or at least, the path to migrating your data to run on open source systems. Most of us have been required to work with closed source database systems at some point in the past or present. With luck, you'll get the opportunity to change that! But moving from a foreign system to PostgreSQL isn't the easiest of tasks. Proprietary to PostgreSQL lays out the steps to start you on the path to enlightenment ... or at least, the path to migrating your data to run on open source systems.
Streaming Database Systems: exploit natural ordering to scale up. | Video
Data volumes are growing rapidly, and it's not always feasible to store the data and then query it quickly enough to act on the results. Streaming Database Systems are the solution that will scale to these huge data volumes without sacrificing arbitrary queries or the power of SQL.
'top' for PostgreSQL - Video
pg_top allows you to monitor PostgreSQL processes to view the currently running SQL statement of a process, the query plan of a currently running SELECT statement, locks held by a process, user table statistics, and user index statistics.
SQLAlchemy and PostgreSQL Video
Jason Kirtland & Jonathan Ellis
(90 minute mini-tutorial)
SQLAlchemy is the Python database toolkit and object-relational mapper for people who like SQL. This talk will introduce the toolkit and illustrate some of the finer features for PostgreSQL users: standards-based SQL generation, exploiting advanced SQL features, powerful object relational mapping and querying for even the most twisted schema, and how to make integrating with legacy databases more pleasant.
Trees and More in SQL
Many people have tried to work with tree structures in SQL. In 8.4, you will have a new set of tools to make this easy and fun, two words you might not have thought of to describe tree handling in the past.