PGConf US 2017 [PgConf.US] Program

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.

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...

presented by PostgresConf Organizers

This is the Registration for Thursday

presented by PostgresConf Organizers

This is the Wednesday Lunch.

presented by PostgresConf Organizers

presented by PostgresConf Organizers

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

presented by PostgresConf Organizers

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

presented by PostgresConf Organizers

presented by PostgresConf Organizers

Coffee Break for RIS attendees

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.

presented by PostgresConf 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.

presented by PostgresConf 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.

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...

presented by PostgresConf Organizers

The Speaker Dinner! Speakers and +1s only!

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

presented by Lukas Fittl

To many developers the database is a black box. You expect to be able to put data into your database, have it to stay there, and get it out when you query it... hopefully in a performant manner. When its not performant enough the two options are usually add some indexes or throw some hardware at it.

We'll walk through a bit of a clearer guide of how you can understand how your database i...

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.

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...

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.

presented by Simon Riggs

Security Enhancements in Postgres 10

presented by Anthony Davis

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

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...

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...

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...

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.

presented by PostgresConf Organizers

This is Wednesday's registration

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+.

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?

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...

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 ...

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.

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 ...

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

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...

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.

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 ...

presented by PostgresConf Organizers

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

presented by PostgresConf Organizers

Registration to the greatest PostgreSQL Conference on earth.

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.

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.

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...

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.

presented by Mark Porter

AWS Keynote

presented by PostgresConf Organizers

Welcome to PGConf US 2017

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

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...

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 ...

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.

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...

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...

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...

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...

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...

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.

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.

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...

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,...

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...

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...

presented by Drew Engelson

This talk outlines 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 algorithms fo...

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...

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.

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...

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...

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...

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...

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...

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...

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 ...

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...

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...