PGConf Local: Seattle Program

2017-08-11
09:00
Jim Mlodgenski Migrating from Oracle to PostgreSQL
A step by step guide

presented by Jim Mlodgenski

Whether it is for cost reasons or simply convince, many people are migrating their databases from Oracle to PostgreSQL. Depending on your application making the switch can be extremely simple or impossible. This tutorial will teach how to identify the differences and how to successfully migrate an application. We will walk through a sample migration using the AWS tool-chain which includes SCT a...

more

09:00 - 12:00 Redwood Trainings
Bruce Momjian Mastering PostgreSQL Administration

presented by Bruce Momjian

In this course you will learn the essential details of PostgreSQL configuration, security, maintenance, monitoring, tuning, backups, recovery. The course is designed for people with experience in database administration but who are new to the Postgres platform.

09:00 - 12:00 Willow Trainings
14:00
Robert Bernier Scaling PostgreSQL Using Linux Containers in the Enterprise
A Complete And Comprehensive Walkthrough Of Building And Maintaining Container Based Clusters

presented by Robert Bernier

EXPERIENCE LEVEL: Expert

SESSION TRACK: Infrastructure

DETAILS: COURSE DESCRIPTION: Automation is THE big thing these days. Software delivery and infrastructure changes in regards to provisioning, creating and tearing down servers, is no longer a niche activity for only the largest and most complex networks. Nowadays, everybody is racing to automate their environments and achi...
more

14:00 - 17:00 Willow Trainings
Joshua D. Drake Postgres Performance and Maintenance

presented by Joshua D. Drake

When you are optimizing PostgreSQL it is usually maintenance that goes by the wayside. How do we fix autovacuum? Where did all of this bloat come from? Why am I getting IO spikes? How do I get RDS to behave?! Why are commits so slow on replication? The answer to all of these questions is understanding the relationship between proper Postgres maintenance and performance. Join us for a 3 hour jau...

more

14:00 - 17:00 Redwood Trainings
2017-08-12
09:30
Jim Mlodgenski Introduction to JavaScript Stored Procedures

presented by Jim Mlodgenski

One of the great features of PostgreSQL is its pluggable language handler allowing many different types of stored procedure languages including JavaScript aka PL/V8. This allows web developers to leverage their existing coding skills when writing database sever-side code. In this talk we will discuss:

  • How to create a PL/V8 function
  • Interacting with database objects
  • Returning results ...
more

09:30 - 10:20 Willow dev
Joshua D. Drake POSTGRESQL V10: AN AMPLIFIED VERSION OF POSTGRES

presented by Joshua D. Drake

Let's talk about the amazing Postgres v10. In this presentation we will discuss all the major highlights of what is upcoming in the new release. We will cover everything from Replication, performance features to new noSQL functionality. Let's take a ride down this roller coaster and discover why Postgres v10 should be the center of your data universe.

09:30 - 10:20 Redwood ops
10:30
Jennifer Scheuerell Under the hood: API integrations and more in an energy efficiency PostgreSQL + Django application

presented by Jennifer Scheuerell

For the last two years, Sound Data has been developing a PostgreSQL + Django application for the California Public Utility Commission (CPUC) to securely manage their $1B per year investment in energy efficiency and demand side management. The application is known as the California Energy Data and Reporting System (Cedars), and manages data reporting for three different reporting requirements fo...

more

10:30 - 11:20 Willow dev
Jim Nasby All the dirt on Vacuum
A detailed look at how vacuum works

presented by Jim Nasby

The use of Multi-Version Concurrency Control (MVCC) is perhaps one of the most powerful features PostgreSQL has to offer, but it can be a source of confusion for new and experienced users alike. In this talk we will provide an in-depth walkthrough of why Postgres needs to vacuum and what vacuum does.

Topics

  • MVCC details
  • HOT overview
  • Identifying tuples to be vacuumed/frozen
  • ...
more

10:30 - 11:20 Redwood ops
11:30
Shaun Thomas BDR Around the Globe
Geographically Distributed Postgres

presented by Shaun Thomas

BDR: What does it do? Does it do things? Let's find out!

Non trigger-based Bi-Directional Replication has been a long-awaited Postgres feature for years, and with good reason. Who doesn't want multi-master freedom and indemnity? In regard to features, BDR adds quite a lot to Postgres that a DBA might leverage while building their dream architecture. Foremost among these is the fact BDR is ...

more

11:30 - 12:20 Willow ops
Bruce Momjian Postgres Window Magic
Making SQL window functions understandable

presented by Bruce Momjian

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.

The slides are here: https://momjian.us/main/writings/pgsql/window.pd...

more

11:30 - 12:20 Redwood dev
13:20
Grant McAlister Tuning PostgreSQL for High Write Workloads

presented by Grant McAlister

In this session we will cover a number of the way that you can tune PostgreSQL to better handle high write workloads. We will cover both application and database tuning methods as each type can have substantial benefits but can also interact in unexpected ways when you are operating at scale. On the application side we will look at write batching, use of GUID's, general index structure, the c...

more

13:20 - 14:10 Redwood ops
Basil Bourque Flyway - Database migration made easy
Use Flyway to evolve your Database Schema easily and reliably across all your instances

presented by Basil Bourque

Introduction to Flyway, the open-source tool for migrating your database changes between your dev-test-QA-prod systems. Evolve your database schema easily and reliably across all your instances. Like version control for your code, but for your database. Every table added, every column altered, and every index created, all applied automatically as needed to each db instance.

If you manage t...

more

13:20 - 14:10 Willow dev
14:20
Kevin Kempter Enterprise Data Architecture with PostgreSQL

presented by Kevin Kempter

We'll discuss architecture and scaling to include:

  • monitoring & "Monthly DB Reports"
  • backup / recovery
  • replication & HA
  • DR sites and connection pooling
  • Capacity planning & metrics
  • Scaling
  • Ongoing Architecture Monitoring & Lifecycle

14:20 - 15:10 Redwood ops
Lloyd Albin Using GIS in PostgreSQL

presented by Lloyd Albin

In this presentation we will go over the old school math and the new school PostGIS method of doing the same math for calculating the distance and cost of an aircraft flight. You will notice how much easier it is to use PostGIS.

  • Installing PostGIS
    • Cloud Solutions
    • From Source
  • Aviation Math
    • Airplane Performance
    • Great Circle Math
  • PostGIS Math
    • Updating your table...
more

14:20 - 15:10 Willow dev
15:40
Steve Hetzel Using Kubernetes, Docker, and Helm to Deploy On-Demand PostgreSQL Streaming Replicas
PostgreSQL running in Containers with Kubernetes – some practical use cases and dispelling myths regarding databases in Containers

presented by Steve Hetzel

There are many use-cases for running databases in containers – this presentation will focus on pragmatic examples of using Kubernetes to orchestrate Postgres streaming replicas for on-demand disaster recovery and horizontal scaling.

Containers are big news – they are the latest evolution in infrastructure technology and are poised to represent the next major shift in database workflows. As ...

more

15:40 - 16:30 Willow ops
Ryan Murphy Trees/Hierarchical Data in the SQL Database
How to make nested relationships in SQL more easily and fluently

presented by Ryan Murphy

Learn tools and best practices for constructing, querying, visualizing and manipulating trees and hierarchical data in SQL.

We'll compare various design patterns for how to store the hierarchical relationships in the schema, and explore trade-offs and practical use cases for different approaches. Learn how PostreSQL features such as JSON / JSONB / array types, the WITH RECURSIVE keyword, an...

more

15:40 - 16:30 Redwood dev
16:40
Sunil Kamath Develop intelligent apps on the Azure platform using the Azure Database for PostgreSQL
Managed PostgreSQL database for app developers

presented by Sunil Kamath

Azure Database for PostgreSQL is a managed database service built for developers using the community PostgreSQL database technology you love. Learn how you can leverage the managed service and the key capabilities that the service offers which enables you as a developer to focus on developing apps. Using customer stories and experiences including live demos, we will walk through best practices ...

more

16:40 - 17:30 Redwood ops
QUAN-HA LE Mainur Rahman Semantic Search Web System on PostgreSQL
PostgreSQL Semantic Search

presented by QUAN-HA LE and Mainur Rahman

Web search engines often federate many user queries to relevant structured databases. For example, a recruitment-related query might be federated to a jobseekers-and-employers database containing their resumes and skills. The relevant structured data items are then returned to the user along with web search results. Though each structured database is searched in isolation, the search often prod...

more

16:40 - 17:30 Willow dev