Bruce Momjian

Postgres Feature Presentations

Nulls Make Things Easier?

Duration: 30 minutes, 45 minutes with questions
Nulls are a very useful but also very error-prone relational database feature. This talk is designed to help applications developers better manage their use of nulls. It covers the use of nulls in relational databases, with a focus on Postgres behaviour. It covers three-value logic, comparing nulls, mapping nulls to strings, indexing nulls, and aggregates.

Programming the SQL Way with Common Table Expressions

Duration: 30 minutes, 45 minutes with questions
SQL is a declarative language, meaning the user submits an SQL command and the database determines the optimal execution. Common Table Expressions (CTEs) allow queries to be more imperative, allowing looping and processing hierarchical structures that are normally associated only with imperative languages. This talk will help developers use implement CTE queries in their applications and allow operations that normally could only be done in application code to be done via SQL queries.

Rapid Upgrades With Pg_Upgrade

Duration: 30 minutes, 45 minutes with questions
Pg_Upgrade allows data to be transferred between major Postgres versions without a dump/restore. It does this by transferring the user data and version-dependent data separately. This talk explains the internal workings of pg_upgrade and includes a pg_upgrade demonstration.

The Magic of Hot Streaming Replication

Duration: 30 minutes, 45 minutes with questions
This talk explores the much-anticipated Postgres 9.0 features of hot standby and streaming replication. It explains how these features work, how to configure them, and their current limitations. It includes a hands-on demonstration that can be done either by the instructor or by students.

PostgreSQL Replication Solutions

Duration: 45 minutes, 75 minutes with questions
This talk is for people who need some kind of replication and want to learn more about the replication options available for PostgreSQL.  Replication is not a single technology but rather a group of needs and solutions that has to be tailored to each usage for optimal performance and functionality.  This talk outlines the major needs for replication and major solutions, including those currently implemented in PostgreSQL and those in development.

Securing PostgreSQL From External Attack

Duration: 30 minutes, 45 minutes with questions
This talk explores the ways attackers with no authorized database access can steal Postgres passwords, see database queries and results, and even intercept database sessions and return false data. Postgres supports features to eliminate all of these threats, but administrators must understand the attack vulnerabilities to protect against them. This talk covers all known Postgres external attack methods.

Flexible Indexing with Postgres

Duration: 45 minutes, 1 hour with questions
When considering database indexing, many people are confused by the many Postgres indexing structures available, and the many data-type-specific index lookup methods. For example, brin allows for efficient indexing of many columns. gin indexing specializes in the rapid lookup of keys with many duplicates — an area where traditional btree indexes perform poorly. This is particularly useful for json and full text searching. GiST allows for efficient indexing of two-dimensional values and range types. This talk explores the various indexing features of Postgres and when to use them.

YeSQL: Battling the NoSQL Hype Cycle with Postgres

Duration: 45 minutes, 1 hour with questions
Relational databases are regularly challenged by new technologies that promise to make SQL obsolete, but the new technologies often fade into obscurity. This talk explores how new NoSQL technologies are unique, and how existing relational database systems like Postgres are adapting to handle NoSQL workloads.

Non-Relational Postgres

Duration: 45 minutes, 60 minutes with questions
Postgres has always had strong support for relational storage. However, there are many cases where relational storage is either inefficient or overly restrictive. This talk shows the many ways that Postgres has expanded to support non-relational storage, specifically the ability to store and index multiple values, even unrelated ones, in a single database field. Such storage allows for greater efficiency and access simplicity, and can also avoid the negatives of entity-attribute-value (eav) storage. The talk will cover many examples of multiple-value-per-field storage, including arrays, range types, geometry, full text search, xml, json, and records.

Postgres Window Magic

Duration: 45 minutes, 60 minutes with questions
Normal SQL queries return rows where each row is independent of the other returned rows. SQL window functions allow queries to return computed columns based on values in other rows in the result set. This presentation explains the many window function facilities and how they can be used to produce useful SQL query results.