PGConf US 2017 Program

2017-03-28
08:00
PgConf US Organizers Registration
Registration

presented by PgConf US Organizers

Registration to the greatest PostgreSQL Conference on earth.

08:00 - 09:30 Newport Grand Ballroom
09:00
Joshua D. Drake Pacific Crest Trail: The postgresql.conf from A-Z
The in and out of the postgresql.conf file.

presented by Joshua D. Drake

Everybody uses it, almost nobody knows what each parameter means. Just like the Pacific Crest Trail, most people have heard of it but few people have taken the 3-4 months to seek inner peace and understanding by hiking this gorgeous section of the United States. Let's take a 3-4 hour hike and explore the postgresql.conf. We will cover the desert of memory settings, to the mountains of the backg...

more

09:00 - 12:00 America Training
Ozgun Erdogan Lukas Fittl Designing a Multi-tenant Database for Scale with PostgreSQL

presented by Ozgun Erdogan and Lukas Fittl

If you’re building a SaaS application, you probably already have the notion of tenancy built in your data model. Typically, most information relates to tenants/customers/accounts and your database tables capture this natural relation. With smaller amounts of data (10s of GB), it’s easy to throw more hardware at the problem and scale up your database. As these tables grow however, you need to th...

more

09:00 - 12:00 Liberty III Training
Ryan Smith Creating blockchain platforms and future directions

presented by Ryan Smith

Ryan Smith - In 2013 I co-founded Chain and serve as CTO. In 2010 I joined the Heroku engineering team to help build the world’s greatest PAAS. Before joining Heroku I earned a B.S. in Mathematics from the University of Missouri Kansas City.

Chain is a technology company that partners with organizations to build, deploy, and operate blockchain networks that enable breakthrough financial ...

more

09:00 - 09:20 Liberty I Regulated Industry Summit
Christophe Pettus PostgreSQL When It's Not Your Job

presented by Christophe Pettus

PostgreSQL has become the default database for most green-field development projects, and is the data storage architecture behind many success stories, such as Instagram. Despite a reputation as being complex and fiddly, Postgres is easy to install, administer, maintain, and use... with just a little bit of orientation. This is that orientation. PostgreSQL has a reputation as being complex and ...

more

09:00 - 12:00 Enterprise Training
09:40
Chris Dannen Blockchain, Distributed Security and Regulated Industries
A panel discussion on Blockchain, Distributed Security and Regulated Industries

presented by Chris Dannen

The internet of things will grow rapidly to accommodate life-saving devices from autonomous cars to medical alert pendants. Enterprises must consider how their architecture will change as devices come online. Blockchain provides protocols that might hold the solution, but challenges remain. What should CIOs and CTOs know before they begin?

Moderator: Chris Dannen, Founder, Iterative Inst...

more

09:40 - 10:30 Liberty I Regulated Industry Summit
10:40
Diana Henderson Secure Containers, Bridging Mainframes to Cloud, (IBM)
Secure Containers, Bridging Mainframes to Cloud, (IBM)

presented by Diana Henderson

Blockchain is positioned to revolutionize all industries, and the mainframe platform is playing an important role by providing multiple levels of security for Blockchain applications.

The IBM Blockchain on Bluemix, High Security Business Network (HSBN) is designed to deliver the security, compliance, and audit-ability that firms need in highly regulated industries where data privacy is par...

more

10:40 - 11:30 Liberty I Regulated Industry Summit
11:40
Kai Huang Public Clouds & Risk Assessments for Regulated Industries

presented by Kai Huang

Cloud computing presents many new and exciting opportunities for businesses, including compute liquidity across geographies, faster time-to-market, and dynamic cost structure. However, along with these benefits come new threats and risks. These include threats relating to security and confidentiality, operational risks and legal risks. This talk discusses the threat model that Goldman Sachs con...

more

11:40 - 12:20 Liberty I Regulated Industry Summit
12:30
PgConf US Organizers AWS Sponsored Lunch
Amazon Web Services is feeding you!

presented by PgConf US Organizers

12:30 - 13:30 Liberty I Regulated Industry Summit
13:00
Leo Hsu and Regina Obe Getting Stuff done with PostGIS

presented by Leo Hsu and Regina Obe

If you want to follow along on your laptop, you should already have PostGIS 2.3 and PostgreSQL 9.6 installed as well as QGIS desktop software. For those who don't have time to prep but still want to follow along, we'll have a test server available with ability to run queries using pgAdmin4. This workshop will cover the following topics: Overview of the 2 core vector types geometry and geography...

more

13:00 - 16:00 Liberty III Training
Hans-Jürgen Schönig Detecting performance problems and fixing them

presented by Hans-Jürgen Schönig

Are you facing bad performance? maybe you are not sure how to approach performance problems systematically? This training is what you have been looking for. During this training you will learn: - How to detect performance problems - Tracking down slow queries - Hunting I/O problems - Optimizing postgresql.conf for speed - Optimizing storage - Finding slow stores procedures - Playing with lockin...

more

13:00 - 16:00 America Training
Shaun Qualheim Engineering cloud infrastructure for HIPAA-compliance
Engineering cloud infrastructure for HIPAA-compliance

presented by Shaun Qualheim

Healthcare providers continuously search for better ways to deliver solutions to patients at a lower cost; PostgreSQL and Amazon Web Services (AWS) provide elastic, scalable, and cost-effective solutions towards that end. In the United States, providers and other covered entities must comply with the Health Insurance Portability and Accountability Act of 1996 (HIPAA). We’ll discuss what you nee...

more

13:00 - 13:50 Liberty I Regulated Industry Summit
Ilya Kosmodemiansky Linux IO internals for PostgreSQL administrators

presented by Ilya Kosmodemiansky

Input-output performance problems are on every day agenda for DBAs since the databases exist. Volume of data grows rapidly and you need to get your data fast from the disk and moreover - fast to the disk. For most databases there is a more or less easy to find checklist of recommended Linux settings to maximize IO throughput. In most cases that checklist is good enough. But it is always better ...

more

13:00 - 16:00 Enterprise Training
14:00
Yaser Raja Glenn Poston HIPAA Compliant, Scalable and Highly Available PostgreSQL Architecture in AWS

presented by Yaser Raja and Glenn Poston

In this talk we will be sharing Clearcares' DB re-architecture journey to achieve the following key goals: - Ability to increase / decrease the PostgreSQL read servers as per the load - High availability of the database tier - HIPAA compliance

This talk will focus on the following: - The challenges faced with the old architecture - The requirements for the new architecture - Some of th...

more

14:00 - 14:40 Liberty I Regulated Industry Summit
14:40
PgConf US Organizers RIS: Coffee Break
Coffee Break

presented by PgConf US Organizers

Coffee Break for RIS

14:40 - 15:10 Liberty I Regulated Industry Summit
15:10
Chad Hardin How to Make Security on NoSQL More than just an afterthought
How to Make Security on NoSQL More than just an afterthought

presented by Chad Hardin

When designing big data systems on top of noSQL databases, security is all too often bolted on as an afterthought. However, robust and flexible security is possible if some basic design principles are followed. We walk you through one implementation that is built with security in mind from the ground up, leveraging cell-level access control with Apache Accumulo, as well as overall multi-laye...

more

15:10 - 15:50 Liberty I Regulated Industry Summit
16:00
Jason O'Donnell Secure Technical Implementation Guide (STIG) For PostgreSQL

presented by Jason O'Donnell

The United States Defense Information System Agency Standards Branch plays a critical role in enhancing the security posture of the United States Department of Defense’s (DoD) security systems through its Security Technical Implementation Guides (STIGs). The STIGs provide DoD and other United States federal agencies with guidance on how to harden computer systems and protect cyber infrastructur...

more

16:00 - 16:40 Liberty I Regulated Industry Summit
16:50
Simon Riggs Security Enhancements in Postgres 10
Security Enhancements in Postgres 10

presented by Simon Riggs

Security Enhancements in Postgres 10

16:50 - 17:30 Liberty I Regulated Industry Summit
18:00
PgConf US Organizers Speaker Dinner

presented by PgConf US Organizers

The Speaker Dinner! Speakers and +1s only!

Location: http://vb3restaurant.com/

Time and Date: Tuesday March 28th 6:00-9:00

18:00 - 22:00 Newport Grand Ballroom
2017-03-29
08:00
PgConf US Organizers Registration
Registration

presented by PgConf US Organizers

This is Wednesday's registration

08:00 - 09:30 Newport Grand Ballroom
09:30
PgConf US Organizers Welcome to PGConf US 2017

presented by PgConf US Organizers

Welcome to PGConf US 2017

09:30 - 09:50 Newport Grand Ballroom
09:50
Stephen O'Grady Keynote: The Power of Convenience

presented by Stephen O'Grady

One of the most frequently overlooked, and yet most important, of technology adoption is convenience. As a feature that we all grasp intuitively, and appreciate as users, it is rarely the focus of product management decisions. We'll examine why it's important, market examples of the convenient versus inconvenient, and what to do about it.

09:50 - 10:10 Newport Grand Ballroom
10:10
Mark Porter AWS Keynote

presented by Mark Porter

AWS Keynote

10:10 - 10:30 Newport Grand Ballroom
10:40
Kevin Kempter Enterprise Data Architecture with PostgreSQL

presented by Kevin Kempter

This presentation will address Enterprise Data Architecture methods and components with PostgreSQL based on our experience in the trenches. Topics include:

Monitoring automation

  • Backup & recovery automation

  • The DB assessment

  • Defining capacity and scaling points

  • Scaling options

  • The Data Architecture Lifecycle

10:40 - 11:30 America Operations
Jeff Klukas Analytics Infrastructure Powered by Logical Decoding and Kafka

presented by Jeff Klukas

Logical decoding provides a comprehensive, real-time stream of the changes happening within a Postgres instance, which has tremendous potential for analytics use cases. This talk gives an overview of some common patterns for building pipelines to feed a data warehouse, then dives into the details of Simple's Postgres -> Kafka pipeline, how we use it to warehouse Postgres change history to Amazo...

more

10:40 - 11:30 Liberty III Use Cases
Joe Conway Intro to PostgreSQL
a jumpstart to up and running

presented by Joe Conway

A crash course to get you up and running with PostgreSQL. We will cover:

  • Installation
    • initialization
    • startup
    • session managment
  • Basic configuration
    • postgresql.conf
    • pg_hba.conf
  • psql use/hints
  • SQL syntax and quoting
  • Data types

10:40 - 11:30 Liberty I Operations
Ozgun Erdogan Designing your SaaS Database for Scale with Postgres

presented by Ozgun Erdogan

If you’re building a SaaS application, you probably already have the notion of tenancy built in your data model. Typically, most information relates to tenants / customers / accounts and your database tables capture this natural relation. With smaller amounts of data, it’s easy to throw more hardware at the problem and scale up your database. As these tables grow however, you need to think abou...

more

10:40 - 11:30 Liberty II Development
11:40
Abdullah Uz Tansel Time in Databases

presented by Abdullah Uz Tansel

Big data is of interest to many applications and it naturally involves time. The presentation explores time ontology and addresses the subtle issues in managing temporal data and temporal knowledge: comparing database states at two different time points, capturing the periods for concurrent events and accessing to times beyond these periods, sequential semantics, handling multi-valued attribute...

more

11:40 - 12:30 Liberty II Development
Michael Meskes POWER Platform - The New Home For PostgreSQL

presented by Michael Meskes

The IBM POWER platform is an interesting operational basis for mission critical PostgreSQL installations. With the broad adoption of Linux in the POWER ecosphere with the support of Little Endian distributions like Debian or Ubuntu nowadays, this opens the door for the whole PostgreSQL world gaping wide. This talk will share experiences in packaging, scaling and operating PostgreSQL on Linux on...

more

11:40 - 12:30 America Operations
Andres Freund Speeding up Query Execution

presented by Andres Freund

PostgreSQL query execution performance is pretty good for many typical OLTP (i.e. individually short and concurrently issued) type queries, but performance for queries processing large amounts of data (e.g. OLAP queries) sometimes is a bit lacking. Some of those concerns can e addressed by increasing parallelism, and work on that is in progress, with the first queries being parallelized in Post...

more

11:40 - 12:30 Liberty I Internals
Chris Sinjakli Lessons Learned the Hard Way: Postgres in Production at GoCardless

presented by Chris Sinjakli

From the beginning, GoCardless has used Postgres for the stuff that matters. It’s the primary store behind our payments API. After 5 years, it’s fair to say we’ve seen some things. From spikes in latency as resources run out, through the weird contortions ORMs put your database through, and on to the perils of running a high-availability cluster - this talk has it all. In the talk, we’ll run th...

more

11:40 - 12:30 Liberty III Use Cases
12:30
PgConf US Organizers Wednesday Lunch
Wednesday Lunch

presented by PgConf US Organizers

This is the Wednesday Lunch.

12:30 - 13:30 Newport Grand Ballroom
13:30
Peter Geoghegan Sort vs. Hash: A Duality

presented by Peter Geoghegan

PostgreSQL 9.5 and 9.6 significantly improved upon the performance of both hash joins, and sort operations. Sorts are often used as input to GroupAggregate nodes and merge joins. While both approaches have various strengths and weaknesses, and are essential components of the PostgreSQL executor, their relative importance has somewhat shifted over the years. This happened due to trends in CPU an...

more

13:30 - 14:20 Liberty I Internals
AJ Bahnken Gophers Riding Elephants: Writing PostgreSQL tools in Go

presented by AJ Bahnken

This talk will start with an overview of Go, then dive into some examples of using it to work with Postgres. We'll show basics like running queries, then demonstrate how Go makes difficult things easy out of the box, such as inspecting Postgres's TCP wire protocol and providing retry mechanisms and monitoring for restores (including fun stories, tips and tricks). Join us and see why Go - with i...

more

13:30 - 14:20 Liberty II Development
Dan Robinson Designing The Right Schema To Power Heap

presented by Dan Robinson

Heap's analytics infrastructure is built around PostgreSQL. The most important choice to make when building a system this way is the schema you'll use to represent your data. This foundation will determine what sorts of read queries will be fast, your write throughput, what indexing strategies will be available to you, and what data inconsistencies will be possible. With the wrong choice, you w...

more

13:30 - 14:20 Liberty III Use Cases
Ilya Kosmodemiansky PostgreSQL worst practices

presented by Ilya Kosmodemiansky

This talk is prepared as a bunch of slides, where each slide describes a really bad way people can screw up their PostgreSQL database and provides a weight - how frequently I saw that kind of problem. Right before the talk I will reshuffle the deck to draw twenty random slides and explain you why such practices are bad and how to avoid running into them.

13:30 - 14:20 America Operations
14:30
Alexander Kukushkin Patroni - HA PostgreSQL made easy

presented by Alexander Kukushkin

Patroni is a high availability solution for PostgreSQL developed by Zalando to manage hundreds of databases in the cloud, as well as in traditional data centers. It implements automatic failover and works together with Etcd, Zookeeper or Consul to store and retrieve Postgres cluster information in a consistent way and ensure there is only one leader at a time, avoiding split-brains. In more tha...

more

14:30 - 15:20 America Operations
Anastasia Lubennikova In-core compression: how to shrink your database size in several times

presented by Anastasia Lubennikova

Currently PostgreSQL doesn't compress data in many cases when it's possible and can significantly reduce not only disk and memory usage but also number of IO operations. A common view on this problem is that it can be solved on file system level. Unfortunately in Linux, which is probably a most commonly used server OS these days, corresponding file systems are either not yet ready for usage in ...

more

14:30 - 15:20 Liberty I Internals
Max ten Dam Migration from Oracle to native PostgreSQL

presented by Max ten Dam

Migration Solution to migrat an Oracle application with complex code to native PostgreSQL leaving no footprints, not using emulation layers: straightforward Open Source community domain. How we can automatically convert PL/SQL, nested functions, packages, globals to native PostgreSQL formats

14:30 - 15:20 Liberty II Development
Joshua D. Drake Softlayer vs RDS/AWS: A price to performance perspective
Where is the line in the sand against dollars to performance sense.

presented by Joshua D. Drake

It seems like everyone is talking about Amazon Web Services. I deployed! Don't get me wrong, Amazon offers a great service but what are you really getting for your money? This talk discusses the price to performance ratio between these two cloud offerings, when it makes sense to use each one and even a little bit of ideology of why you should or shouldn't. Let's talk shop with real numbers and ...

more

14:30 - 15:20 Liberty III Use Cases
15:20
PgConf US Organizers Wednesday Coffee Break
Wednesday Coffee Break

presented by PgConf US Organizers

15:20 - 15:50 Newport Grand Ballroom
15:50
Bruce Momjian MVCC Unmasked

presented by Bruce Momjian

Multi-Version Concurrency Control (MVCC) allows Postgres to offer high concurrency even during significant database read/write activity. MVCC specifically offers behavior where "readers never block writers, and writers never block readers". This talk explains how MVCC is implemented in Postgres and highlights optimizations which minimize the downsides of MVCC. This talk is for advanced users.

15:50 - 16:40 Liberty I Internals
Louis Royster Transitioning from MSSQL to PostgreSQL, our trails and tribulations.

presented by Louis Royster

My company made the transition from MSSQL to PostgreSQL, which meant converting over 10,000 stored procedures and functions. Lets talk about some of the issues we faced, some of our best practices, and some of our bigger mistakes that were made.

15:50 - 16:40 Liberty III Use Cases
Tess Rinearson High-Availability Postgres with etcd

presented by Tess Rinearson

Chain’s enterprise blockchain networks use Postgres for storage. We rely on Postgres’s replication features to ensure that clients can always access their assets and create transactions, and wrote a service that manages Postgres replicas. This service was heavily inspired by Joyent’s Manatee project, but uses different technology; namely, Go and etcd instead of Node and Zookeeper. In this talk,...

more

15:50 - 16:40 America Operations
Stephen Frost IDENTIFYING SLOW QUERIES AND FIXING THEM!

presented by Stephen Frost

We'll be looking at PostgreSQL configuration options (postgresql.conf) for logging and basic tuning parameters, going over EXPLAIN and EXPLAIN ANALYZE output for select queries (and the auto_explain module), what the EXPLAIN output means in terms of how the query is being executed, and then going over ways to improve the queries, including index creation, rewriting the query to allow PG to use ...

more

15:50 - 16:40 Liberty II Development
16:50
Andreas Scherbaum Tour de Data Types: VARCHAR2 or CHAR(255)?

presented by Andreas Scherbaum

Ever wondered what might be the best data type for a certain use case? Is it better to use INTEGER or BIGINT, and should you use TIMESTAMP or is it better to use TIMESTAMPTZ? When is the last time you migrated a legacy database and wondered what to use as replacement for VARCHAR2 or CHAR(255)? How to store IP addresses or geographical data, does that fit into VARCHAR or is there something more ...

more

16:50 - 17:40 Liberty II Development
Jan Wieck Peeking into the Black Hole called PL/pgSQL - The new PL Profiler

presented by Jan Wieck

The new PL profiler allows you to easily get through the dark barrier, PL/pgSQL puts between tools like pgbadger and the queries, you are looking for. Query and schema tuning is tough enough by itself. But queries, buried many call levels deep in PL/pgSQL functions, make it torture. The reason is that the default monitoring tools like logs, pg_stat_activity and pg_stat_statements cannot penetra...

more

16:50 - 17:40 Liberty I Development
Kunal Jain Yun Guo DevOps Tools for managing PostgreSQL

presented by Kunal Jain and Yun Guo

Cvent, Inc. is the world's leading provider of cloud-based software for meetings and event management. From online event registration to meeting site selection to web surveys, we manage it all. In early 2015, our technology team started the move to SOA and microservices and adopted SCRUM methodology: an iterative and incremental agile software development framework for managing product developm...

more

16:50 - 17:40 America Operations
Drew Engelson Pgcrypto avast!: A case-study in Django's password hashers

presented by Drew Engelson

This talk with outline what happened when I needed to migrate hundreds of thousands of end user logins from a legacy application to a Django application for a client (National Geographic). The primary challenge was that each user's password was originally created with the Postgres pgcrypto.crypt() function and only the one-way hash is stored, which is unusable by Django which it's own algorithm...

more

16:50 - 17:40 Liberty III Use Cases
18:00
PgConf US Organizers Reception: Food, Drinks, Games, and PostgreSQL Fun!

presented by PgConf US Organizers

Come join us for an evening of PostgreSQL fun. Don't miss this opportunity to mingle with friends and supporters of the PostgreSQL community! We will have some food, drinks and awesome video games.

18:00 - 22:00 Newport Grand Ballroom
2017-03-30
08:00
PgConf US Organizers Thursday Registration

presented by PgConf US Organizers

This is the Registration for Thursday

08:00 - 09:00 Newport Grand Ballroom
09:00
Anthony Davis LXD, the Container Hypervisor

presented by Anthony Davis

Avoid common database performance pitfalls of running PostgreSQL on Ubuntu's Open Source LXD containers.

09:00 - 09:50 America Operations
Alexis Guajardo Google Workshop: Cloud SQL

presented by Alexis Guajardo

Google Cloud has added PostgreSQL to it's managed services under Cloud SQL. Explore PostgreSQL offerings on Google Cloud and learn what underpins Google's Cloud SQL managed services. We'll examine the architecture of Persistent Disk and discuss its implications. The storage layer, called Persistent Disk, behaves differently than many block storage devices: add capacity to increase IOPS; grow t...

more

09:00 - 09:50 Liberty II
Vasiliy Chernov DataGrip, SQL client from JetBrains

presented by Vasiliy Chernov

In this session, Vasiliy Chernov will tell about DataGrip — an SQL client from JetBrains. He'll show basic features, some so-called tips and tricks, and demonstrate how DataGrip helps PostgreSQL developers in their everyday work.

09:00 - 09:50 Enterprise Operations
10:00
Sara Nieto The dark side of the universe from Oracle to PostgreSQL

presented by Sara Nieto

In 2020, the European Space Agency will launch a spacecraft to map the geometry of the dark Universe and discover the origin of the accelerating expansion, this mission is called Euclid. Euclid faces two main challenges from the point of view of the data management: 1. The unprecedent volume of data that the mission will produce, in the order of PB per year. 2. The transfer of these data from a...

more

10:00 - 10:50 Liberty III Use Cases
David Rader Columns and Clouds - Hybrid databases
Faster queries, smaller data, and elastic archive for large databases using FDWs

presented by David Rader

Dealing with a large database? Want your queries to run faster? How about saving space in large tables? What if you don't want to delete old history records - can you keep them in the cloud?

In this talk about using advanced Postgres features to create hybrid databases we'll show how to use partitioning and Foreign Data Wrappers (FDW) to speed up queries, make your database smaller and stret...

more

10:00 - 10:50 America Operations
Jim Nasby Python + Jupyter + Postgres

presented by Jim Nasby

Jupyter notebooks are a powerful tool for interacting with code. They provide a combination of documentation and interaction that works well for demos, education, presentation, as well as data exploration. This talk will demonstrate two methods for interacting with Postgres via both python and Jupyter notebooks.

10:00 - 10:50 Enterprise Development
Scott Mead Schema management with Flyway
Simplified schema management

presented by Scott Mead

#Schema Management# Schema Management is a complex, multi-disciplinary topic that frequently typically stymies both Dev and Ops teams. In this talk, I will walk you through which practices do and don't work. We'll spend time looking at how a schema management tool Flyway can simplify your DevOps life.

10:00 - 10:50 Liberty II Development
Peter Eisentraut Logical Replication in PostgreSQL 10

presented by Peter Eisentraut

Built-in logical replication is coming to PostgreSQL 10! This talk will introduce this new headline feature, demonstrate the use, and discuss the current status and future plans for logical replication in PostgreSQL.

10:00 - 10:50 Liberty I Internals
11:00
Oleg Bartunov Jsonb in Postgres - the present and future

presented by Oleg Bartunov

Jsonb in Postgres is one of the attractive feature for modern application developers, which allows efficient work with json documents without sacrificing strong consistency and ability to use all the power of proven relational technology. The rich Jsonb infrastructure in Postgres, which includes a lot of functions, operator, indexes and other details, might be difficult to users, that is why we...

more

11:00 - 11:50 Liberty I Internals
Jignesh Shah PostgreSQL High Availability in a Containerized World

presented by Jignesh Shah

Lot of applications and databases are now getting containerized. In this talk we will look at highly available deployments of PostgreSQL using containers. We will discuss various issues that were encountered and how they were resolved.

11:00 - 11:50 America Operations
Lukas Fittl Scaling Python and Postgres

presented by Lukas Fittl

This talk covers scaling Python + PostgreSQL

11:00 - 11:50 Enterprise Operations
Leo Hsu and Regina Obe Top 10 Problems Solved by PostGIS

presented by Leo Hsu and Regina Obe

We'll cover 10 problems where we've found PostGIS to be the easiest and fastest tool for the job. Areas covered will be: Proximity Analysis Statistical aggregation of data by location Mapping Routing Geocoding Unlikely uses for PostGIS -- non-spatial problems solved by re-imagining the problem as a spatial one. Extra focus on new tricks made possible by PostGIS 2.3+ and PostgreSQL 9.6+.

11:00 - 11:50 Liberty III Use Cases
David Steele Audit Logging for PostgreSQL

presented by David Steele

Many of us have had auditors ask for information that is difficult to provide with core PostgreSQL. Who made changes to table X in the last six months? When was table Y's DDL last modified and was that during a maintenance window? Who has viewed table Z? An extension available for PostgreSQL 9.5, pgAudit, seeks to answer these and many other questions by providing more detailed logging and fine...

more

11:00 - 11:50 Liberty II Development
12:00
PgConf US Organizers Thursday Lunch
Thursday Lunch

presented by PgConf US Organizers

12:00 - 13:00 Newport Grand Ballroom
13:00
Alvaro Hernandez Migrating off of MongoDB to PostgreSQL

presented by Alvaro Hernandez

MongoDB is a successful database in the NoSQL space, mostly used for OLTP-type workloads. However, due to the lack of ACID (transactions in particular) and significant performance issues with OLAP/DW workloads, more and more MongoDB users are considering migrating off of MongoDB to a RDBMS, where PostgreSQL is the usual choice. This represents a significant opportunity for the PostgreSQL ecosys...

more

13:00 - 13:50 Liberty II Development
Christophe Pettus Django and PostgreSQL: Two Great Tastes

presented by Christophe Pettus

The Django web framework has added a lot of powerful PostgreSQL-specific features around full-text search, migrations, and indexing. We'll go over what's available now, common use cases, and what's coming in Django 1.11.

13:00 - 13:50 Enterprise Development
Hans-Jürgen Schönig Processing 1 billion rows per second

presented by Hans-Jürgen Schönig

Everybody is talking about Big Data and about processing large amounts of data in real time or close to real time. However, to process a lot of data there is no need for commercial software or for some NoSQL stuff. PostgreSQL can do exactly what you need and process A LOT of data in real time. During our tests we have seen that crunching 1 billion rows of data in realtime is perfectly feasible,...

more

13:00 - 13:50 Liberty III Use Cases
Jim Nasby All the dirt on VACUUM: 9.6 edition

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 Visibility and Freez...

more

13:00 - 13:50 Liberty I Internals
Scott Mead From Dev to prod, The ABC's of PostgreSQL backups
Strategic Backup with PostgreSQL

presented by Scott Mead

Most deployments of PostgreSQL start with a developer and a good idea. The good idea spreads and before you know it, you have a production system on your hands. We will cover the ABC's of PostgreSQL backups:

  • Logical Backups
  • Physical Backups
  • Incremental / Differential Backups
  • Optimizing for space
  • Available tools

The trick is to leverage each of these in to a consistent...

more

13:00 - 13:50 America Operations
14:00
Robert Haas Next-Generation Parallel Query

presented by Robert Haas

PostgreSQL 9.6 features a basic implementation of parallel query, but there's much more to be done. In this talk, I'll discuss new features and capabilities proposed for v10, such as dynamic shared memory allocation, Gather Merge, Parallel Index Scan, Parallel Bitmap Heap Scan, Parallel Hash Join, Parallel Append, and Partition-wise Join. I'll also offer a preview of further work that may be un...

more

14:00 - 14:50 Liberty I Internals
Christophe Pettus Corruption War Stories

presented by Christophe Pettus

Every instance of database corruption is unique. In this talk, we'll talk about some specific, real-world examples of PostgreSQL database corruption, and how they were repaired. We'll step through each one with how we detected it, how we isolated the issue, and what we did to patch it... and what did and didn't work. We'll also go over how to avoid those specific issues, and general approaches ...

more

14:00 - 14:50 Liberty III Use Cases
Grant McAlister Amazon RDS for PostgreSQL: What’s New and Lessons Learned

presented by Grant McAlister

In this session we will begin with a quick overview of the Amazon RDS service and how it achieves durability and high availability. Then we will do a deep dive into the exciting new features we recently released, including 9.6, snapshot sharing, enhancements to encryption, vacuum, and replication. We will also explore lessons we have learned managing a large fleet of PostgreSQL instances, inclu...

more

14:00 - 14:50 America Operations
Denis Magda Turbocharge your SQL queries in-memory with Apache® Ignite™

presented by Denis Magda

Learn how to boost performance 1,000x and scale to over 1 billion transactions per second with in-memory storage of hundreds of TBs of data for your SQL-based applications.

Apache Ignite is a unique NewSQL platform that is built on top of a distributed key-value storage and provides full-fledged SQL support. Denis will show how Apache Ignite handles auto-loading of SQL schema and data, SQL ...

more

14:00 - 14:50 Liberty II Development
Jon Banafato An Introduction to SQLAlchemy and Friends
A match in ORM heaven

presented by Jon Banafato

SQLAlchemy is a database toolkit for Python. This talk will cover the basics of working with SQLAlchemy's core, ORM, and third-party plugins to make working with your database easy as py.

14:00 - 14:50 Enterprise Development
14:50
PgConf US Organizers Thursday Coffee Break
Thursday Coffee Break

presented by PgConf US Organizers

This is Thursday's coffee break. You are going to need it.

14:50 - 15:20 Newport Grand Ballroom
15:20
PgConf US Organizers What is in PostgreSQL 10.0
A round table

presented by PgConf US Organizers

A round table of known -hackers to discuss the upcoming 10.0 release of PostgreSQL.

15:20 - 16:10 Newport Grand Ballroom
16:10
David Rader Migrating to PostgreSQL: Lessons Learned

presented by David Rader

Discussion of some of the key challenges people experience as they migrate their databases from a commercial solutions like Oracle, Sybase or SQL Server to PostgreSQL.

16:10 - 16:30 Newport Grand Ballroom
16:30
Robert Haas Moving PostgreSQL Forward

presented by Robert Haas

PostgreSQL has a reputation for developing features in a slow and methodical way, producing very high-quality work, but sometimes taking years before a particular feature reaches full maturity. Why does this happen? What impact does it have on PostgreSQL use and adoption? What would be required for the PostgreSQL development community to move faster?

16:30 - 16:50 Newport Grand Ballroom
16:50
Simon Riggs New Security Features in PostgreSQL 10.0

presented by Simon Riggs

Discussion of the new security features in PostgreSQL 10.0 - what features made it in and what features didn't, plus new releases of external security tools. Plus a recap of some important security features often overlooked by developers, and warnings to avoid certain options, and why.

16:50 - 17:10 Newport Grand Ballroom
18:00
David Rader Scott Mead Jan Wieck Denis Lussier OpenSCG Workshop: Hands on Oracle to PostgreSQL Migration

presented by David Rader, Scott Mead, Jan Wieck, and Denis Lussier

Join the OpenSCG team for a hands on look on how to migrate an application from Oracle to PostgreSQL.

18:00 - 18:50 Liberty II
Bruce Momjian EDB Workshop: 5 Things Developers Need to Know About Digital Transformation with Postgres

presented by Bruce Momjian

As new business requirements grow, solutions with open standards are growing as well to support developer needs. During this workshop, Bruce Momjian, Co-Founder of the PostgreSQL Global Development Group and Senior Database Architect at EnterpriseDB, will walk through the most important things developers need to know to stay ahead of digital transformation.

Learn about: - Data management s...

more

18:00 - 18:50 Liberty III
Debra Cerda Collaboration and Community in PostgreSQL

presented by Debra Cerda

Background to present/questions to be answered: What is the difference between a contributor and a committer? What are the differences and collaboration between the U.S. PostgreSQL Association, PostgreSQL Europe, and the PostgreSQL Global Development Group? Where can I find PostgreSQL resources? How can I be involved? Challenges exist in our community in in on-boarding new members whether as ad...

more

18:00 - 18:50 Liberty I Use Cases
Jesse White Automating Production-Ready Databases in Docker

presented by Jesse White

In this talk we'll address how to manage state, persistence, and availability in a highly performant database cluster. In order to understand how cloud infrastructure and Docker orchestration play a role in providing application availability, we'll build the pieces of a Dockerized database.

18:00 - 21:00 America Operations
19:00
Debra Cerda Jim Nasby Data Rescue

presented by Debra Cerda and Jim Nasby

Data rescue is an opportunity for programmers, scientists, archivists, activists, and volunteers of all kinds to identify, back-up, and help preserve publicly accessible federal data resources in the public interest, in the event they are removed from public view and use.

19:00 - 22:00 Liberty I Operations
2017-03-31
09:00
Peter Eisentraut 2ndQuadrant Workshop: Logical replication deep dive
A look deep inside pglogical, BDR, in-core logical replication

presented by Peter Eisentraut

This talk will take a closer look at the implementation of logical replication solutions for PostgreSQL: pglogical, BDR, and in-core logical replication in PostgreSQL 10. We will talk about the roles of logical decoding, replication slots, replication origins, commit timestamps, custom WAL records, and other pieces that make these systems work. We'll contrast these systems with trigger-b...

more

09:00 - 09:50 Liberty I
PgConf US Organizers Friday Registration
Friday Registration

presented by PgConf US Organizers

This is the last day of the conference. Register away!

09:00 - 10:00 Newport Grand Ballroom
10:00
Corey Huinker ETL Confessions

presented by Corey Huinker

ETL (Extract, Transform, Load) is the industry term for importing data from external sources into a database. However, more often the pattern is ELT - Extract, Load, Transform. This talk covers methods of loading external data into PostgreSQL and reshaping it to fit local needs. The talk addresses popular commercial tools, but focuses mostly on custom coding, specifically: *) Identifying bottle...

more

10:00 - 10:50 Liberty II Development
David Kohn TOASTing an Elephant : Building a Custom Data Warehouse Using PostgreSQL

presented by David Kohn

At Moat we add ~500 million rows each day to just a few tables in our data warehouse. Previously, we used Postgres for data for the last month (which takes ~15TB of disk), and we love its ability to handle concurrency and its cost compared to the commercial column-stores that we use for our historical data. By using a combination of composite-types, arrays and Postgres' TOAST mechanism we were ...

more

10:00 - 10:50 Liberty III Use Cases
Will Leinweber a Practical Postgres Protocol Primer

presented by Will Leinweber

It's unlikely that you will need to implement the Postgres wire protocol yourself, but understanding it will give you insight into how Postgres operates you'll not get otherwise. And as far as binary protocols go, this one is surprisingly not awful! In this talk we'll cover - motivations for using the raw protocol vs linking libpq - (and some protocol features not available in libpq!) - authent...

more

10:00 - 10:50 Liberty I Internals
David Christensen Ch-ch-ch-changes! PostgreSQL replication past, present, and future

presented by David Christensen

A high-level overview of replication in PostgreSQL. Covers a brief history of PostgreSQL replication, compares and contrasts technologies available at the present day (Streaming Replication, PITR, Slony, Bucardo, pglogical, BDR), and looks forward to the future of replication in PostgreSQL.

10:00 - 10:50 America Operations
11:00
Simon Riggs Restrictions on PostgreSQL Cloud DBaaS

presented by Simon Riggs

PostgreSQL has many good features and works well in a variety of environments. Questions are frequently asked about "Why does PostgreSQL have this restriction?", and typical answers are along the lines of "err... PostgreSQL allows that, its just your cloud provider that blocks you from using that". So let's have a look at what restrictions are imposed by various clouds - what downsides are ther...

more

11:00 - 11:50 America Operations
Michał Gutkowski 600k queries per second in PostgreSQL - we do it in TomTom

presented by Michał Gutkowski

Co-speaker Rafał Hawrylak. In this talk, we will present how we, at TomTom, created a large scale application based on PostgreSQL. What changes when traffic grows from MBs to GBs per day, environment from 1 server to several powerful machines and volume of data to 30TB? We want to share how PostGIS enabled us to create a geospatial platform. How we achieved high read scalability and allow datab...

more

11:00 - 11:50 Liberty III Use Cases
Gavin M. Roy RDS or: How I Learned to Stop Worrying and Love the Cloud

presented by Gavin M. Roy

At first blush, running PostgreSQL in the cloud can be a scary proposition. When using Amazon Web Services RDS for PostgreSQL, one loses low-level control for the operational management of PostgreSQL. Conversely, when you go in understanding the shortcomings and how to work around them, it can be a very liberating experience. In 2016, I became a cloud convert. Where I was hesitant to embrace Po...

more

11:00 - 11:50 Liberty II Development
Tomas Vondra PostgreSQL BI Performance & News from XL project

presented by Tomas Vondra

The talk presents discusses performance improvements in analytical / BI workloads in recent PostgreSQL releases, illustrated with results from multiple major versions, and also about what additional improvements might happen in the near future. I will also discuss news from the Postgres-XL,, the distributed PostgreSQL fork, and how it compares to the current PostgreSQL in this area.

11:00 - 11:50 Liberty I Internals
12:00
PgConf US Organizers PostgreSQL Job Fair

presented by PgConf US Organizers

The PGConf US 2017 Job Fair will take place on Friday, March 31, 2017 from 12:00pm to 3:00pm in the Enterprise Room - The Westin Jersey City Newport.

The job fair is open to the public. For those looking for work, please bring resumes and business cards.

12:00 - 15:00 Enterprise
PgConf US Organizers Friday Lunch
Friday Lunch

presented by PgConf US Organizers

The last day of the conference. Let's rock n roll.

12:00 - 13:00 Newport Grand Ballroom
13:00
Keith Fiske Managing OS Provided PostgreSQL Packages

presented by Keith Fiske

Compiling PostgreSQL from source is a relatively easy thing to do compared to most open source projects. But for production systems, using the packages the operating systems provide is generally the recommended way to go. The problem is none of them end up managing PostgreSQL quite the same, let alone anything like the original source compile. This talk will cover how some of the major Unix-bas...

more

13:00 - 13:50 Liberty III Operations
Joe Conway How Green Was My Valley
Advanced Spatial Analytics

presented by Joe Conway

Increasingly today, collected data includes a spatial component. PostgreSQL + PostGIS, combined with R via PL/R, provides an awesome tool-chain for advanced spatial analytics on such data! This presentation will introduce the audience to PL/R, discuss the pros and cons of this approach to spatial data analysis, then walk through examples of spatial analysis using PostgreSQL, PostGIS, and PL/R.

13:00 - 13:50 Liberty II Development
Kisung Kim Implementing Graph Database based on PostgreSQL

presented by Kisung Kim

In this presentation, we will share our experiences related to implementing a graph database based on PostgreSQL. The outline will be as follows: 1. What is the graph database? 2. Bitnine's solution for the graph data: Agens Graph 3. Implementation issues related to PostgreSQL 4. Future roadmap The graph database is a kind of databases optimized for processing the connected data, which is not a...

more

13:00 - 13:50 Liberty I Internals
Payal Singh Provisioning and Automating High Availability Postgres on AWS EC2

presented by Payal Singh

RDS is great. But sometimes you feel the need for more flexibility and customization that is just not possible with RDS. In this talk I will talk about why one might want to go with setting up and maintaining a HA postgres cluster on EC2 instead of RDS, following which I will go through each of the steps for setting up such a system and automating installation and upgrades, replication, backups...

more

13:00 - 13:50 America Operations
14:00
Denish Patel Advanced Postgres Monitoring

presented by Denish Patel

Hands on session to setup Advanced Postgres monitoring using collectd and integrate with one of the SAAS based monitoring tool.

14:00 - 14:50 Liberty III Operations
Dave Cramer Exploiting the hidden talents of JDBC for maximum performance

presented by Dave Cramer

Describe and explain the correct patterns to get the maximum performance out of the JDBC driver. Explain some of the anti patterns and why they don't work. Explain the custom connection parameters and how they work and interact with PostgreSQL.

14:00 - 14:50 Liberty II Development
Mike Freedman TimescaleDB: Designing a scalable time-series database on PostgreSQL

presented by Mike Freedman

Time-series data is emerging everywhere, from IoT sensors and industrial machines, to transportation and logistics, to devops and monitoring, to finance. We have found that many users start by storing their time-series data in PostgreSQL, but then lose its query power and ecosystems by migrating to some NoSQL or time-series architecture once they reach a certain scale. Yet this "SQL or scale"...

more

14:00 - 14:50 America Development
Dave Page Evolution of pgAdmin: pgAdmin4

presented by Dave Page

In this talk we’ll look at the evolution of the most popular Open Source management tool for PostgreSQL from pgAdmin III to the upcoming pgAdmin 4. We’ll look at the reasons for the re-write, technology choices, requirements, development methodology, and end with a demonstration of the new application.

14:00 - 14:50 Liberty I Internals
15:00
PgConf US Organizers Lightning Talks

presented by PgConf US Organizers

Lightning Talks are strictly-timed 5 minute presentations intended to further expand the PostgreSQL community and spark discussion among conference participants. The intent is for these talks to provide a venue in support of new ideas and newcomers to our community. Lightning Talks are a great way to get early feedback on a work in progress, to demo a new tool or technique, to find potential co...

more

15:00 - 15:30 Newport Grand Ballroom
15:30
PgConf US Organizers Final Remarks and Conference Close

presented by PgConf US Organizers

Time to say goodbye and hints about next year

15:30 - 15:50 Newport Grand Ballroom