Postgres Conference 2020 Program

2020-03-23
10:00
Wen Lin Junfeng Yang The Parallel Retrieve Cursor
fetch data parallelly from segments in Greenplum

presented by Wen Lin and Junfeng Yang

Greenplum database is a massively parallel processing (MPP) database based on PostgreSQL. Each cluster consists of a master node, standby master node, and some segment nodes. When queries come, a query plan is generated in master node and disptached to segments. The plan gets executed on segments and the result will be sent to master node, finally results gathered on master and return to the d...

more

10:00 - 10:50 Broadhurst/Belasco Distributed SQL
Avinash Vallarapu A detailed understanding of MVCC and Autovacuum Internals in PostgreSQL.
MVCC and Autovacuum Internals

presented by Avinash Vallarapu

  • PostgreSQL implements MVCC in a slightly different way when compared to databases like Oracle, MySQL and others. In PostgreSQL, there is a different method for managing UNDO that requires a cleanup concept called vacuum. Over a period of time, PostgreSQL implemented autovacuum that has been enhanced with every release. It is now important for PostgreSQL admins/DevOPS to understand the interna...
more

10:00 - 10:50 Oneill Ops and Administration
Kuntal Ghosh The Art of Logical Replication

presented by Kuntal Ghosh

PostgreSQL developers laid the stepping stone of logical replication in version 10 which solved a lot of shortcomings of physical replication and opened up the possibilities of new promising features in replication. In this talk, we will discuss the architecture of logical replication in PostgreSQL along with a demonstration of the usage. The talk will also include the features that have been ...

more

10:00 - 10:20 Ziegfeld Development
Bryan Dsouza Building scalable, real-time analytics applications with Azure Database for PostgreSQL
See how you can transform your real-time analytics applications with Azure Database for PostgreSQL

presented by Bryan Dsouza

In this hands-on tutorial, we will cover many of the capabilities that make PostgreSQL the world's most advanced open source relational database. Specifically, we'll dive deep on how to build scalable, real-time analytics applications using PostgreSQL in the cloud. To do this, we'll go hands-on and use Azure Database for PostgreSQL including Hyperscale.

10:00 - 10:50 Imperial/Juliard Case Studies
Hans-Jürgen Schönig PostgreSQL TDE: Fully encrypted PostgreSQL
Building more secure databases

presented by Hans-Jürgen Schönig

PostgreSQL TDE is a version of PostgreSQL which fully supports "Data At Rest Encryption". All data will be encrypted on disk and therefore PostgreSQL TDE offers additional layers of security which can hardly be achieved with standard PostgreSQL. PostgreSQL TDE is Open Source and can be used free of charge. You will learn how it works and what can be achieved at which kind of performance impac...

more

10:00 - 10:50 Carnegie/Alvin Ops and Administration
10:30
Abdullah Uz Tansel Blockchain as a Database
Blockchain

presented by Abdullah Uz Tansel

Throughout the history, exchange of trusted information among people played an essential role in every aspect of their lives: socially, economically and politically. Blockchain is an innovation in database technology for keeping temper proof (trusted) data in a permanent, immutable, decentralized, global, and trustless ledger. It combines distributed computing, databases, networks and cryptogra...

more

10:30 - 10:50 Ziegfeld Development
11:00
PostgresConf Organizers Monday Keynote

presented by PostgresConf Organizers

The Monday General Session

11:00 - 11:50 Salon 3
12:00
Jesper Pedersen pgagroal
A high-performance protocol-native connection pool for PostgreSQL

presented by Jesper Pedersen

pgagroal is a high performance protocol-native connection pool for PostgreSQL.

pgagroal is built upon libev, a high performance network library, and uses a shared-memory process model together with atomic operations for tracking state to achieve its high performance.

In this session we will explore the

  • Architecture
  • Features
  • Deployment
  • Performance
  • Roadmap

of pgagroal....

more

12:00 - 12:50 Carnegie/Alvin Ops and Administration
Robert Treat DevOps On ACID
DevOps culture for the practicing DBA

presented by Robert Treat

There are multiple paths towards DevOps nirvana, but all too many "digital transformations" overlook one of the most critical pieces of any companies infrastructure: the database. But for those involved in the area of database management, you can't ignore the changes DevOps is making in the tech industry, and you also can't close your eyes and just "sprinkle on the Kubernetes" and hope it all g...

more

12:00 - 12:50 Oneill Ops and Administration
Gavin M. Roy Enterprise DDL Management

presented by Gavin M. Roy

Managing the DDL for an organization with many developers, databases, and complex schemas can be a challenge. Over time, our approach at AWeber has evolved and now includes pglifecycle, a tool we recently released as open-source. In this talk we'll review our Docker/Git/CI based workflow and the tooling that allows for code-review, testing, and deployment of new objects and modification of exis...

more

12:00 - 12:20 Ziegfeld Development
Jobin Augustine Interesting 25 new features in PostgreSQL 12
Why PostgreSQL version 12 is the turning point and why enterprise customers should adopt it.

presented by Jobin Augustine

PostgreSQL 12 made a giant leap in many of the areas of PostgreSQL which includes partitioning, query planner, Indexing and some SQL features, etc. Among all of the interesting features for developers and admins, we shall be discussing 25 of the most interesting features with some good examples and benchmarks done This talk includes: - Partitioning improvements - Indexing Improvements 3. S...

more

12:00 - 12:50 Imperial/Juliard Ops and Administration
Rebecca Taft From Heuristics to Statistics: How we built a cost-based optimizer for CockroachDB

presented by Rebecca Taft

Using a declarative language like SQL with a relational database provides many benefits since it creates separation between the logical representation of the database and the physical representation. It gives us the ability to change the physical layout of the data without changing the queries or applications that use the database.

But it also means we need to bridge that gap somehow, a...

more

12:00 - 12:50 Broadhurst/Belasco Distributed SQL
12:30
Rafia Sabih Customizing PostgreSQL via extensions
A deep dive into Postgresql extensibility

presented by Rafia Sabih

Are you an avid user and admirer of PostgreSQL? But, you totally miss having this one little feature? Chances are you can develop it yourself in no time using extension infrastructure provided by Postgresql. Among many interesting capabilities, extensibility is another jewel Postgresql adorns itself with. Be it your specific monitoring requirement or you require a new data-type or index, all...

more

12:30 - 12:50 Ziegfeld Development
12:50
PostgresConf Organizers Monday Lunch

presented by PostgresConf Organizers

Monday Lunch

12:50 - 13:50 Salon 2/4
14:00
James E. Marca Using PostgreSQL, PostGIS, and pgRouting for street sweeping
Load, clean, store, and animate OpenStreetMap data for a street sweeping route-optimizer project

presented by James E. Marca

In responding to an RFP to optimize and improve street sweeping routes for a small city in California, we developed a solver based on Google's Operations Research Tools. While the guts of the solver uses OR-Tools, virtually everything else about the project is made possible by PostgreSQL, PostGIS, and pgRouting.

In this presentation, I talk about the various ways PostgreSQL and friends ...

more

14:00 - 14:50 Imperial/Juliard Case Studies
Avthar Sewrathan Prometheus & PostgreSQL: How and why to use PostgreSQL as long-term storage for metrics
This talk will focus on an older, but very powerful system as an option for your long-term Prometheus metrics storage: PostgreSQL.

presented by Avthar Sewrathan

Prometheus has its own time-series database specifically for system and application metrics monitoring, but by now it’s well-known that longer-term, persistent storage is required for the historical metrics. There are many systems being used for this purpose and it seems like every month or so a new system introduces itself to specifically tackle this problem. This talk will focus on an older, ...

more

14:00 - 14:50 Carnegie/Alvin Ops and Administration
Marc Linster Run Postgres Natively on Kubernetes
Easily deploy, scale, upgrade, and reconfigure Postgres clusters running in Kubernetes

presented by Marc Linster

Deploying Postgres on Kubernetes to achieve a highly available, monitored and robust environment, is challenging, it requires domain knowledge most developers don’t have. One way to reduce the complexity is to use a Kubernetes Operator, which manages custom resources such as Postgres to simplify the database life cycle. Using the EDB Postgres Operator, developers can easily deploy, scale, upgra...

more

14:00 - 14:50 Ziegfeld Development
Shaun Thomas Upgrade Techniques Through the Ages
From Dump / Restore to Logical and Beyond

presented by Shaun Thomas

Back in my day we suffered the crushing hardship of dump and restore, hours of downtime both ways, and we liked it! Wait... no we didn't! Thankfully things have advanced since then, and we'd like to talk about it. Along the way, we'll cross every milestone up to fully online major version upgrades.

There are even rumors of mixed-version operational clusters for truly zero-downtime u...

more

14:00 - 14:50 Oneill Ops and Administration
Bryn Llewellyn YugabyteDB: a distributed PostgreSQL database

presented by Bryn Llewellyn

YugabyteDB is an open-source, cloud-native, high-performance database that belongs in the emerging distributed SQL category. Its storage layer uses a “shared nothing” architecture inspired by Google Spanner that brings linear write scalability, low read latency, and intrinsic fault tolerance. And, uniquely in its category, its query layer is implemented by using the “upper half” of the Postgr...

more

14:00 - 14:50 Broadhurst/Belasco Distributed SQL
15:00
Alvaro Hernandez StackGres: Cloud-Native PostgreSQL on Kubernetes
Your secret trick to have a PosgreSQL-as-a-Service in your own infra

presented by Alvaro Hernandez

An enterprise-grade PostgreSQL requires many complementary technologies to the database core: high availability and automated failover, monitoring and alerting, centralized logging, connection pooling, etc. That is, a stack of components around PostgreSQL.

Kubernetes has enabled a new model to deploy software abstracting away the infrastructure. However, containers are not lightweigh...

more

15:00 - 15:50 Oneill Ops and Administration
Asif Rehman PostgreSQL Replication
Replication

presented by Asif Rehman

Replication is the process of copying and maintaining database objects, such as tables, in multiple databases that make up a distributed database system. There are various benefits of replication which makes it an important component of any enterprise-level database deployment. The main drivers for replication are high availability, performance, load balancing, and others.

PostgreSQL offers ...

more

15:00 - 15:50 Carnegie/Alvin Ops and Administration
Anastasia Lubennikova B-tree indexes - learn more about the heart of PostgreSQL

presented by Anastasia Lubennikova

B-tree index is the most common index type. Most if not all of the modern DBMS use it. The data structure and concerned algorithms are really mature, there are about 40 years of development. And PostgreSQL's B-tree is not an exception. It's full of complicated optimizations of performance, concurrency and so on. But there're still many ways to improve it.

This talk gives you a deep dive int...

more

15:00 - 15:50 Ziegfeld Development
Robert Treat Logical Replication Live!
(it's a live demo... what could go wrong?)

presented by Robert Treat

Postgres has a lot of ways to do replication and other forms of multi-server environments, but logical replication is one of the most useful. Want to know why? In this talk we'll talk about:

• a (very) brief history of replication in Postgres • the whats and hows of logical replication • differences between Postgres 10, 11, and 12 • good use cases for logical replication • not good use c...

more

15:00 - 15:50 Broadhurst/Belasco Distributed SQL
Kohei KaiGai Data processing more than billion rows per second
unleash the maximum capability of GPU & NVME

presented by Kohei KaiGai

Nowadays, GPU is not only for computing intensive workloads, but for I/O intensive big-data workloads also.

This talk introduces how SSD-to-GPU Direct SQL, implemented as extension of PostgreSQL, optimizes data flow from storages to processors over PCIe-bus for efficient execution of analytic/reporting workloads.

Combination of this technology with comprehensive database features (e.g, co...

more

15:00 - 15:50 Imperial/Juliard Data Science
16:10
Michael Freedman Advanced compression in TimescaleDB with hybrid row/columnar storage
TimescaleDB native compression combines the best of both worlds: (1) all of the benefits of PostgreSQL, including the insert performance and shallow-and-wide query performance for recent data from a row store, combined with (2) the compression and additional query performance -- to ensure we only read the compressed columns specified in a query -- for deep-and-narrow queries of a columnar store.

presented by Michael Freedman

Storage systems like databases and file systems have long used compression to reduce their storage footprint. Yet the most effective compression techniques were traditionally limited to column stores, where increased data-type locality provides greater options for advanced capabilities. It has often been assumed that fundamental differences between column-store and row-store architectures lead ...

more

16:10 - 17:00 Ziegfeld Development
Rafi Shamim Using benchmarks and profiles to optimize CockroachDB's execution engine
-

presented by Rafi Shamim

OLTP databases are specialized for serving high-throughput queries that read or write a small number of rows. Still, users often also expect good performance on analytic queries that read a lot of rows, like large scans, joins, or aggregations. This motivated us to seriously investigate how to improve the performance of these types of queries in CockroachDB, and to begin working on a new SQL ex...

more

16:10 - 17:00 Imperial/Juliard Development
Avinash Vallarapu Upgrading a PostgreSQL database from a legacy to newer version with no downtime.
PostgreSQL Upgrades with no downtime

presented by Avinash Vallarapu

  • It is very important to know a fact that PostgreSQL community does not support bug fixes and security fixes for older PostgreSQL versions. Currently, community only supports PostgreSQL versions starting from 9.4. So, if your database server is on 9.3 or older, you would see no bug fixes or security patches. Several benchmarks proves that the performance of a PostgreSQL database increases with...
more

16:10 - 17:00 Carnegie/Alvin Ops and Administration
Mark Wong BDR Advanced Clustering & Scaling

presented by Mark Wong

Postgres-BDR is a worlwide multi-master replication clustering solution allowing you to write to any node in the cluster. Build high availability and geographical distributed databases meeting disaster recovery needs and allowing rolling upgrades. Common use cases are covered as well as considerations for making applications work in a multi-master environment.

16:10 - 17:00 Broadhurst/Belasco Distributed SQL
dharshan rangegowda Working with JSON Data in PostgreSQL vs. MongoDB

presented by dharshan rangegowda

Developers love working with JSON. Document stores like MongoDB have proved to be very popular. Both MongoDB and PostgreSQL have built-in support for the 'JSON' data type. Understand the pros and cons of working with JSON in both of these databases - when should you use MongoDB vs. PostgreSQL? If you are already comfortable with PostgresSQL, should you continue to use it for your JSON data? In ...

more

16:10 - 17:00 Oneill Ops and Administration
2020-03-24
10:00
Colleen Gallagher Enhancing the Car Buying Experience While Protecting Customer Data
IBM Cloud Hyper Protect DBaaS Enables Innovation Without Sacrificing Security

presented by Colleen Gallagher

An automotive company while embarking on their journey to cloud, still had concerns with unauthorized access and compliance with international data protection regulations. Utilizing IBM Cloud Hyper Protect Services, including IBM Cloud DBaaS for PostgreSQL, they were able to create a new, streamlined high-performance solution that securely handles private data from connected vehicles and intern...

more

10:00 - 10:50 Imperial/Juliard Regulated Industry
Debra Cerda Being An Effective Tech Leader

presented by Debra Cerda

As technology grows and evolves, it’s critical to have the best tech staff, yet not every business founder, project manager, or sales director has a tech background.

How do you manage a team of developers, products and services when you don’t have tech experience? We'll explore this as well as - How do you bridge the knowledge gap between your technical team and your non-technical founders...

more

10:00 - 10:20 Broadhurst/Belasco Professional Development, Life and Leadership
Pat Wright Logical Replication lessons learned for the Data Warehouse
Learn what does work for logical replication and what does not work for logical replication.

presented by Pat Wright

Logical Replication was released in Postgres version 10. We have been using it for a Data Warehouse system for the last year and this talk will show you how to set it up for a Data Warehouse system and some of the pitfalls you will run into. We will have the opportunity to discuss what replication you should be using depending on the work you are doing. Perhaps Logical Replication is not the...

more

10:00 - 10:50 Ziegfeld Ops and Administration
Oleg Bartunov The anatomy of FTS engine in PostgreSQL.
Use cases, architecture and roadmap.

presented by Oleg Bartunov

PostgreSQL Full Text Search is a flexible way to search documents stored in PostgreSQL database matching a given query. Latest versions of Postgres introduced several features, which makes FTS of jsonb documents more easy. I will talk about the use cases, the architecture of FTS and how to use its flexibility and configure for best performance. The talk will includes a data types, operators wi...

more

10:00 - 10:50 Carnegie/Alvin Development
Peter Celentano Scheduled PostgreSQL jobs with AWS Batch
Re-inventing the wheel with the cloud in mind

presented by Peter Celentano

Since before PostgreSQL existed, CRON has been the standard daemon for scheduling nearly any task in the Linux ecosystem. With the rise of cloud computing and managed database instances, job scheduling has gotten slightly more complicated then running a simple PSQL scrip on our database server, and connecting through a Unix socket. While the simplest methods of running scheduled database jobs...

more

10:00 - 10:50 Oneill Ops and Administration
10:30
Pat Wright Speaking and Community Involvement for the Introvert
Learn what is needed to succeed as an introvert in the community.

presented by Pat Wright

Let’s be serious, I understand 99% of the audience for a technical conference are introverted. I’m not here to disagree with that I’m here to talk about the introvert inside of me and how I quiet it just enough to truly improve my life. We will discuss why the idea of speaking and community involvement are important to you. If you think all you need is technical skills in this market then ...

more

10:30 - 10:50 Broadhurst/Belasco Professional Development, Life and Leadership
11:00
Stephen O'Grady Tuesday Keynote

presented by Stephen O'Grady

Stephen O'Grady to present on current data topics

11:00 - 11:50 Salon 2/4
12:00
Ajay Patel Zero to Hero: Managing Postgres through DevOps
Managing Postgres through DevOps

presented by Ajay Patel

With Devop's motion and powerful automation platforms, organizations are adopting a product-centric approach for Postgres deployment and management. Whether it's Public Cloud, Private Cloud, VM's, containers Infrastructure management as code solve a lot of problems.  It also helps organizations and database administrators to save, effort and cost to make the business successful. With new automa...

more

12:00 - 12:20 Oneill Ops and Administration
manish muttreja Running postgres on kubernetes

presented by manish muttreja

Learn the ins and outs of running highly available Postrges on kubernetes for production. We will use Amazon EKS for orchestrating our kubernetes cluster.

12:00 - 12:20 Ziegfeld Ops and Administration
Debra Cerda Be the Change: Fostering Inclusion through Allyship (working title)

presented by Debra Cerda

Amplify and advocate for others to create a more dynamic and robust workforce and community. Learn what actions in this panel discussion what you can take to be an ally to others, as well as receive support for your own endeavors.

12:00 - 12:50 Broadhurst/Belasco Professional Development, Life and Leadership
Colleen Gallagher IBM Cloud Hyper Protect DBaaS Delivers Complete Data Confidentiality
Protect your sensitive data in the public cloud

presented by Colleen Gallagher

IBM Cloud Hyper Protect Database-as-a-Service offering is the next evolution of databases in a scalable and security-rich cloud environment. It allows you to retain your data in a fully-encrypted PostgreSQL database without the need for specialized skills. Hyper Protect DBaaS provides complete data confidentiality through built-in workload isolation and tamper protection from privileged user ac...

more

12:00 - 12:20 Imperial/Juliard Regulated Industry
Jobin Augustine Deeper Understanding of PostgreSQL Execution Plan : At plan time and run time
smart planner in postgresql

presented by Jobin Augustine

Right execution plans can deliver maximum performance and throughput from your PostgreSQL database. It is important to understand all factors which affect the execution plan which includes costing, datatypes, indexing, filter and join conditions, pruning, runtime exclusions, etc. Poor understanding of the planner leads to suboptimal performance. There are substantial improvements in new version...

more

12:00 - 12:20 Carnegie/Alvin Development
12:30
Hamid Akhtar Was Just About Time for JIT in PostgreSQL in v11

presented by Hamid Akhtar

Just in Time with LLVM is a performance feature introduced in v11 on *nix based operating systems. Additional packages were added to distributions which make use of LLVM and thereby provide JIT feature. As is the case with most performance features, JIT has some specific use cases where it can give a performance where it may negatively impact it. It's not for every workload type. We'll attempt ...

more

12:30 - 12:50 Carnegie/Alvin Development
Tong Zhang Bring Compression to Postgres at Zero Cost of Performance
Picking the low-hanging fruits of computational storage

presented by Tong Zhang

This proposed talk would present a solution that allows Postgres users to achieve significant data storage savings through compression at zero CPU/performance cost. The key is to deploy Postgres on new compression-capable solid-state drives (SSDs), which is developed following the current industry trend of empowering data storage devices with additional computing capability. This proposed talk ...

more

12:30 - 12:50 Ziegfeld Ops and Administration
Roland Lee Improve Write-Master and Read Replica scale, safely without application changes
Read/Write split and Replication lag detection made easy with a database proxy

presented by Roland Lee

Horizontally scaling your SQL database involves separating the write-master from read-only servers. However, writing to one node and reading from another can result in inconsistent data due to synchronization delays.

Database proxies intelligently route SQL queries to the best data source for even load distribution, while guaranteeing consistency.

In this session, we will provide a hands...

more

12:30 - 12:50 Oneill Ops and Administration
Celia Zhang Differentially Private SQL
User-level privacy for PostgreSQL

presented by Celia Zhang

Differential privacy (DP) provides formal guarantees that the output of a database query does not reveal too much information about any individual present in the database. While many differentially private algorithms have been proposed in the scientific literature, there are only a few end-to-end implementations of differentially private query engines. Crucially, existing systems assume that ea...

more

12:30 - 12:50 Imperial/Juliard Regulated Industry
12:50
PostgresConf Organizers Tuesday Lunch

presented by PostgresConf Organizers

Tuesday Lunch

12:50 - 13:50 Salon 2/4
14:00
 Tom Kincaid Andrew Dunstan MVCC and Tuple Freezing through Pictures.
If you have an update or insert intensive application, please join us.

presented by Tom Kincaid and Andrew Dunstan

Teams that deploy an insert or update intensive application with Postgres need to understand the topic of tuple freezing. In proper planning this important aspect of the Postgres MVCC architecture, can result in prolonged performance spike or an outage. Unfortunately, may people struggle with the concept of tuple freezing. Hence, DBAs and Developers become confused about how to design applica...

more

14:00 - 14:50 Oneill Ops and Administration
John Ashmead Debugging with PostgreSQL
A Strategic Approach

presented by John Ashmead

Depending on the project, debugging can take 50 to 90% of development time. But it usually gets less than 10% of the press. PostgreSQL has great tools for debugging, but they are most effective when deployed as part of an overall strategy.

We will look at strategies for debugging PostgreSQL: how to find bugs, how to fix them, and how to keep them from happening in the first place....

more

14:00 - 14:50 Carnegie/Alvin Development
PostgresConf Organizers Postgres Talent Exchange

presented by PostgresConf Organizers

Career fair, Talent exchange, Speed Mentoring etc...

14:00 - 16:00 North Prefunction Professional Development, Life and Leadership
Avinash Vallarapu Mostly mistaken and ignored PostgreSQL parameters while optimizing a PostgreSQL database
Performance Tuning

presented by Avinash Vallarapu

  • Optimizing a PostgreSQL database may be a challenge if some of the important parameters are ignored. And a bigger challenge may be the understanding of the algorithm behind setting those parameters correctly. In my experience with PostgreSQL Consulting for more than a decade, I have seen a bigger list of parameters that are wrongly set or ignored. This talk details a list of important Postgre...
more

14:00 - 14:50 Ziegfeld Ops and Administration
Joseph Regensburger Practical de-identification under the HIPAA Privacy Rule
An analyst's guide

presented by Joseph Regensburger

Under the HIPAA Privacy Rule, Protected Health Information (or PHI) is, roughly, any individually identifiable information regarding health care, health care payment, or health status. While the HIPAA Privacy Rule strictly regulates the use and disclosure of PHI by covered entities, it also provides provisions which establish when data may be deemed "de-identified" and therefore no longer bound...

more

14:00 - 14:20 Imperial/Juliard Regulated Industry
Mara Lemagie Career Panel

presented by Mara Lemagie

SQL has remained relevant across multiple decades, yet relative to the standard software engineer I to III career path, the routes taken by database professionals are often described as non-traditional. In this panel we will address the imposter syndrome that is a particular risk in this type of space. Also come for insights from top hiring managers and seasoned professionals regarding which sk...

more

14:00 - 14:50 Broadhurst/Belasco Professional Development, Life and Leadership
14:30
Bruce Momjian PostgreSQL 13: The encrypted data platform for Regulated Industry

presented by Bruce Momjian

Join PostgreSQL Core Member and Committer, Bruce Momjian in exploring the current community efforts to bring a new standard of data privacy to PostgreSQL users. In this presentation we will talk about the status of current efforts, the expectations for the upcoming release of PostgreSQL 13 and future plans. We will also touch briefly on current security trends and where PostgreSQL needs to expa...

more

14:30 - 14:50 Imperial/Juliard Regulated Industry
15:00
Mason Sharp Using PostgreSQL for Data Privacy and Security

presented by Mason Sharp

In the age of data science and machine learning, data scientists want access to data sets quickly, but organizations often need to protect private data, whether due to internal policy or government regulations.

In this talk we discuss how to leverage PostgreSQL for managing organization-wide data access while protecting privacy.

Topics include:

Row Level Security Purpose-based data ac...

more

15:00 - 15:50 Imperial/Juliard Regulated Industry
Yaser Raja Performance Impact of Idle PostgreSQL Connections
A dive into how PostgreSQL manages connections and how idle connections consume resources.

presented by Yaser Raja

This presentation will provide an overview of how PostgreSQL manages connections and how idle connections impact performance.

Following areas will be covered in this presentation:

  • Forking process used for each client connection
  • OS resources utilization
  • Results of various tests showing the resource utilization by idle connections
  • Performance impact of idle connections
  • Recomm...
more

15:00 - 15:50 Ziegfeld Ops and Administration
Henrietta Dombrovskaya How to build a PostgreSQL User Group

presented by Henrietta Dombrovskaya

Today, Chicago PUG is the third-largest in the Western Hemisphere, but it was not the case three years ago. It is easy to announce a new User Group, but how to keep it running and growing? Find out during this short presentation!

15:00 - 15:20 Broadhurst/Belasco Professional Development, Life and Leadership
Justin Reock Building the Holodeck
One Open Source Brick at a Time

presented by Justin Reock

Building the Holodeck - One Open Source Brick at a Time Trace the history of human innovation from the dark ages to the modern world, and learn how open development will take us to the next era.

Open source powers everything from drones to Mars landers. Justin Reock, who lives and breathes OSS, shares his vision on what open source truly is, what it can be, and how we all participate in th...

more

15:00 - 15:50 Carnegie/Alvin Development
Jeremy Schneider Backups, Upgrades, and Inventing a New Way to Move Data Faster
A deep dive into PostgreSQL backups, major upgrades, and transportable databases

presented by Jeremy Schneider

Reliable backups and major upgrades are one of the most important responsibilities of anyone who stores data, regardless of the technologies or platforms used. Join us for a deep dive on how PostgreSQL solves these problems. Then, after a discussion of backups and upgrades, learn how these concepts were combined to invent a new way to move data between PostgreSQL databases faster than ever befo...

more

15:00 - 15:50 Oneill Ops and Administration
15:30
Mark Drake How To Teach Beginners PostgreSQL, Painlessly

presented by Mark Drake

PostgreSQL's documentation is top-notch, and the community of Postgres users willing to share their knowledge to help others is growing every day. But even so, Postgres has a number of quirks that can be confusing for beginners, and even push some toward using another DBMS. Drawing on what I've learned as part of the Developer Education team at DigitalOcean and writing about PostgreSQL over the...

more

15:30 - 15:50 Broadhurst/Belasco Professional Development, Life and Leadership
16:10
Bryan Dsouza Hyperscale your database with Azure Database for PostgreSQL
Get the most out of your PostgreSQL database with scale and performance

presented by Bryan Dsouza

Learn about how Hyperscale can help your PostgreSQL databases breakthrough with limitless scale and performance. More to come.

16:10 - 17:00 Broadhurst/Belasco Case Studies
 Muhammad Usama PostgreSQL HA with Pgpool-II and whats been happening in Pgpool-II lately....
Load balancing and HA with Pgpool-II

presented by Muhammad Usama

Pgpool-II has been around to complement PostgreSQL over a decade and provides many features like connection pooling, failover, query caching, load balancing, and HA. High Availability (HA) is very critical to most enterprise application, the clients needs the ability to automatically reconnect with a secondary node when the master nodes goes down.

This is where Pgpool-II watchdog feature com...

more

16:10 - 16:30 Oneill Ops and Administration
Alfred Rossi A brief introduction to data privacy
concept and technique

presented by Alfred Rossi

This talk introduces the attendee to the art and science of preparing private data and its products for release into untrusted settings. We explain and discuss a number of every-day and exotic anonymization techniques, including:

  • Masking,
  • k-Anonymization and its relatives l-Diversity and t-Closeness,
  • Local Differential Privacy, and
  • Differential Privacy

What makes this talk uniq...

more

16:10 - 17:00 Imperial/Juliard Regulated Industry
Marc Linster Scalable Replication For Postgres
Create a highly available and geographically distributed Postgres infrastructures

presented by Marc Linster

Join Marc Linster (Senior Vice President of Product Development) and Zahid Iqbal to learn how EDB Replication Server uses Kafka and Zookeeper to create a highly scalable replication infrastructure. During this breakout session, presenters will highlight how EDB Postgres Replication Server (EPRS) can be used to create a highly available and geographically distributed Postgres infrastructures.

...

more

16:10 - 17:00 Ziegfeld Ops and Administration
Ibrar Ahmed A Deep Dive into PostgreSQL Indexing
PostgreSQL Indexing

presented by Ibrar Ahmed

Indexes are a basic feature of relational databases, and PostgreSQL offers a rich collection of options to developers and designers. To take advantage of these fully, users need to understand the basic concept of indexes, to be able to compare the different index types and how they apply to different application scenarios. Only then can you make an informed decision about your database index...

more

16:10 - 17:00 Carnegie/Alvin Development
16:40
Heiko Onnebrink Use Docker, Go, Rest and some more to build a PostgreSQL multi cloud self service

presented by Heiko Onnebrink

In the new world of digital readiness databases are expected to be provisioned in any cloud location. DevOps teams want to consume databases as a self-service and control all operations without the need to be database experts.

At METRO|NOM we run 3000+ Oracle databases on premise in a ticket driven organisation, far away from what DevOps team expect. There was a need to do something complet...

more

16:40 - 17:00 Oneill Ops and Administration
2020-03-25
11:10
Mariem Ayadi Alternative data where it’s needed the most. Use case: Satellite Imagery - North Africa.

presented by Mariem Ayadi

_Economic development research heavily relies on limited, expensive and sometimes “doubtful” survey data. Using free and readily available satellite imagery to empower economic development research is one solution to the problem. This talk will highlight some of those benefits and walk through a step-by-step technical example using open source tools. *source: NOAA.gov national oceanic and atmo...

more

11:10 - 12:00 Westside Ballroom Data Science
12:10
Marc Linster EDB Keynote

presented by Marc Linster

EnterpriseDB Keynote

12:10 - 12:30 Westside Ballroom
12:50
PostgresConf Organizers Wednesday Lunch

presented by PostgresConf Organizers

Wednesday Lunch

12:50 - 13:50 Westside Ballroom
14:00
Justin Reock Open Source Applied
Real World Use Cases

presented by Justin Reock

This isn't your typical case study, this is the reality of open source: One hundred percent of organizations use varying degrees of OSS, yet we still focus on one particular package or layer when it comes to sharing best practices. The reality is, when we get stuck, it's the configuration and operational interrelationships between packages that matter. This session takes open source support da...

more

14:00 - 14:50 Imperial/Juliard Development
David Sudia Taking the Elephant By the Tusks
Moving Postgres off a PaaS

presented by David Sudia

PaaS solutions like Heroku are a perfect fit when you are early stage startup, and Postgres is THE choice of database on most of these platforms. However, what do you do when your business starts to get traction, and your scale or use case begins to stretch the limitations of the PaaS offering? This talk will share the story of a startup's successful migration of a Postgres database system from...

more

14:00 - 14:50 Carnegie/Alvin Case Studies
Bryan Dsouza Build your best applications on PostgreSQL with Azure Database for PostgreSQL
Get the best out of your application on PostgreSQL with Azure

presented by Bryan Dsouza

When you build an application, choosing the right database is a start. Building on PostgreSQL helps you get the best in open source databases backing up your application. See how you can get the most out of your application on PostgreSQL with Azure.

14:00 - 14:50 Ziegfeld Case Studies
Sen Hu TBase, an open source database from Tencent
Database with massive distributed transaction processing and built-in sharding based on PostgreSQL

presented by Sen Hu

TBase is an open source massively parallel processing (MPP) database server with an architecture designed for distributed transaction workloads. It is backed by experienced Tencent distributed database team. TBase is based on PostgreSQL 10.0 and supports similar SQL Standards, features and configuration options as PostgreSQl.

TBase adopt data sharding, GTM global timestamp technique and MVCC...

more

14:00 - 14:50 Oneill Distributed SQL
 Erik Brandsberg Authentication & Authorization with Multi-User Connection Pooling for Large-scale Postgres Deployments
How to scale high Enterprise connection counts for Postgres

presented by Erik Brandsberg

Most Postgres deployments support a limited number of users to the database. Users are typically managed manually by the DBA. In this session, we will explore how database proxies manage users at high scale quickly and easily: We will cover:

Full Authentication/Authorization/Auditing (aka AAA) over database access: - Authentication of the user via Active Directory/LDAP - Group extraction a...

more

14:00 - 14:50 Broadhurst/Belasco Ops and Administration
15:00
Will Liu Scaling AWS Redshift Concurrency With PostgreSQL
working with foreign data wrapper for redshift

presented by Will Liu

Scaling AWS Redshift Concurrency With PostgreSQL: • Quick overview of Redshift and its Postgres heritage, similarities like SQL interface and differences like storage and scalability. • How we use Redshift as a data warehouse solution at Equinox to analyze data and provide insight. • Some strengths and short-comings of Redshift when it comes to data consumption and how we use Postgr...

more

15:00 - 15:50 Imperial/Juliard Development
Avinash Vallarapu High Availability and Automatic Failover in PostgreSQL.
PostgreSQL High Availability

presented by Avinash Vallarapu

  • PostgreSQL is an advanced open source database that is completely community driven. Continuous development and performance improvements while making it feature rich gave PostgreSQL a great market penetration. When a database server is deployed in production, it is important to achieve several 9's of availability. It starts with a replication setup that is stable and some tool that could manag...
more

15:00 - 15:50 Broadhurst/Belasco Ops and Administration
Morgan Howes The Great Escape: On Prem to the Cloud with Postgres
Sneaking a 14TB on prem monolith to AWS without anyone noticing.

presented by Morgan Howes

Discover how to move a 14 terabyte monolith database from a bare metal, on premise solution to Amazon Web Services (Aurora/RDS). Specifically, the focus is on how to move the data with high availability, minimal downtime, and still leave the option to go back to the old hardware. This combination of case study and tutorial goes into the technical details of how we worked through one issue aft...

more

15:00 - 15:50 Carnegie/Alvin Case Studies
David Benoit WIP - AWS Serverless with PG driving everything
Can we build an entire service with just AWS lambda and PG?

presented by David Benoit

*** This is not yet complete *** I have been working over the past year on a system to completely replace our "traditional" server applications with AWS lambda functions, and we have gotten to the point where we have almost replaced everything.

At the heart of this is postgres. But one of the major issues to solve was how to invoke AWS lambda functions when specific events happened. We ha...

more

15:00 - 15:20 Ziegfeld Case Studies
Shivram Mani Ekaterina Dontsova Cross-site Replication for Massively Parallel Postgres
Greenplum geo replication demystified

presented by Shivram Mani and Ekaterina Dontsova

Postgres provides various alternatives for replication including logical replication, synchronous and asynchronous WAL based replication, continuous archive based recovery all of which are perfect for standalone instances with the latter two more suited when your replica is geographically remote. Greenplum is a parallel distributed shared nothing database where multiple postgres instances are c...

more

15:00 - 15:50 Oneill Distributed SQL
15:30
Pilar de Teodoro Will partitioning and sharding improve performance?
We will present the results obtained from the tests ran at the ESA Science Data Center to join 2 tables storing star information based on their position in the sky under the use of open source Greenplum v.6.

presented by Pilar de Teodoro

What is the best solution to join 2 very big tables (>1TB) with billion rows and hundreds of columns with the help of distribution and partitioning? We have analysed different distribution and partition methods to achieve the best performance for a crossmatch, that is, identifying different rows from 2 tables (star catalogues) referring to the same star. Crossmatching helps scientists around th...

more

15:30 - 15:50 Ziegfeld Case Studies
16:10
Bryn Llewellyn Using stored procedures effectively in a distributed PostgreSQL database

presented by Bryn Llewellyn

YugabyteDB is an open-source, cloud-native, high-performance database that belongs in the emerging distributed SQL category. Its storage layer uses a “shared nothing” architecture inspired by Google Spanner that brings linear write scalability, low read latency, and intrinsic fault tolerance. And, uniquely in its category, its query layer is implemented by using the “upper half” of the PostgreS...

more

16:10 - 17:00 Oneill Distributed SQL
Alexander Ioffe Tired of Impedence Mismatch? Meet Quill!

presented by Alexander Ioffe

If there is one thing that can be learned from a generation of ORM frameworks, it is that treating a database like a fancy hashtable does not work, yet this is exactly what most entity-managers will do! In a proverbial sense, one can imagine that the problems of impedance mismatch are akin to those experienced when taking a Commanche helicopter and racing it around a Formula-1 race track agains...

more

16:10 - 16:30 Imperial/Juliard Development
Jose Finotto Postgresql at Gitlab.com

presented by Jose Finotto

I would like to present how we administrate Postgresql at Gitlab.com. What are our new projects, how we solve issues and how we scale our database solution in a highly demanding environment. I will share details about our topology, replication strategies, backups and how we defined and we evolve our Postgresql ecosystem, that we consist of Patroni, PGBouncer, and consul. Join this session and ...

more

16:10 - 16:30 Ziegfeld Case Studies
Pat Wright Postgres for the SQL Server DBA
Learn more about how to work with Postgres from a SQL Server Dba point of view.

presented by Pat Wright

Postgres is an amazing product and many people are quickly adopting it. This also means that many DBA’s are coming from different technical disciplines. Sometimes from Oracle, SQL Server or Mysql. Learning the tools and the differences between the systems can be daunting for some. I’ll go through the differences in the tools, administration and some of the internals between SQL Server and p...

more

16:10 - 17:00 Broadhurst/Belasco Ops and Administration
David Rader Queues on Postgres - Good, bad, and ugly
Common issues and best practices

presented by David Rader

Applications that need to push an event to a background queue or schedule an event for future processing often use a PostgreSQL database as a simple Message Queue. We'll dive in to common (anti-)patterns for implementing high volume queues in PG, how MVCC and HOT affect your queue, and best practices to make high volume queues work.

16:10 - 17:00 Carnegie/Alvin Case Studies
16:40
Jeremy Smith Building JSON from Relational Data
Explore PostgreSQL's built-in tools for creating JSON documents

presented by Jeremy Smith

JSON is the de facto format for modern APIs and has been part of Postgres since version 9.2. The ability to store, parse, and index JSON documents alongside relational data was game-changing and continuous improvements since 9.2 such as JSONB, indexing support, and the brand new JSON path expressions have made this feature better than ever.

In addition to storing JSON, Postgres provides a n...

more

16:40 - 17:00 Imperial/Juliard Development
Hector Perez PostgreSQL, TimescaleDB and Apache Camel for building the time series of a satellite data archive

presented by Hector Perez

This talk will address the research performed to display through time series the data measured by the in-situ instruments on-board the European Space Agency spacecraft Solar Orbiter. Apache Camel is used to ingest hundreds of millions of time series values in a PostgreSQL 11.5 database, with the help of the TimescaleDB extension for managing the partitions. We will present various analysis per...

more

16:40 - 17:00 Ziegfeld Case Studies
2020-03-26
10:00
Andrew Dunstan Backup PostgreSQL with Barman
Planning for when disaster happens

presented by Andrew Dunstan

A backup solution is an essential part of almost any Disaster Recovery Plan. Barman is a mature, well maintained, open source, continuous backup solution for PostgreSQL. This talk will explain the various options for running Barman, and how using it you can have robust protection against data loss from system failure or corruption. Topics will include

  • prerequisites for running Barman
  • t...
more

10:00 - 10:50 Imperial/Juliard Ops and Administration
Victoria McCormick From SQL Server DBA to Postgres DBA

presented by Victoria McCormick

Imagine you are a SQL Server DBA, only to wake up one morning to find that you must now become a Postgres DBA overnight. How can you overcome the challenges and changes that you will face to get up and running quickly?

This session is to familiarize SQL Server experienced folks with Postgres and learn strategies to be comfortable when radical changes that are being made to your development p...

more

10:00 - 10:50 Oneill Ops and Administration
Bertrand Drouvot Mass databases migrations to PostgreSQL at scale: Complexity study and migration

presented by Bertrand Drouvot

Do you want to migrate all or part of your oracle databases to PostgreSQL? How doable, difficult would it be? In this session you'll see how it is possible to prepare a mass databases migrations to PostgreSQL at scale. The complexity study will be done using free softwares (Ora2Pg, Schema Conversion Tool) and part of the AWS offering (cloudformation, autoscaling group, simple queue service, S3...

more

10:00 - 10:50 Carnegie/Alvin Migration
Bryan Dsouza Get the best security and intelligent performance on PostgreSQL with Azure
See how Azure can help you build your Postgres-based applications with security and compliance with intelligent performance

presented by Bryan Dsouza

With Azure, you get the best security, compliance, and intelligent performance features to manager your PostgreSQL. Learn how. More to come.

10:00 - 10:50 Gilbert Case Studies
Jobin Augustine Why PostgreSQL is Becoming a Migration Target in Large Enterprises
Reasons behind the rapid growth in adpoption and migration to PostgreSQL

presented by Jobin Augustine

This talk contains deeper analysis of compelling reasons why large enterprises opted to migrate to PostgreSQL. Every layer of enterprise, Executive management, Architects, Developers, and Operations team finds multiple strong reasons for selecting PostgreSQL. This is changing the PostgreSQL user-base and landscape. In other words, This talk is about why someone should migrate to PostgreSQL. T...

more

10:00 - 10:50 Ziegfeld Case Studies
Mahmoud SAKR Esteban Zimanyi MobilityDB: Managing Spatiotemporal Data in PostgreSQL
The MobilityDB Extension

presented by Mahmoud SAKR and Esteban Zimanyi

MobilityDB is an open source moving object database system (https://github.com/ULB-CoDE-WIT/MobilityDB). Its core function is to efficiently store and query mobility tracks, such as vehicle GPS trajectories. It implements the Moving Features specification from the Open Geospatial Consortium (OGC). MobiltyDB is engineered up from PostgreSQL and PostGIS, providing spatiotemporal data management v...

more

10:00 - 10:50 Wilder Development
Rebecca Bilbro Outside the Black Box
Visual Diagnostics for More Informed Machine Learning

presented by Rebecca Bilbro

Ironically, one of the most important insights into machine learning in the last decade may have been introduced in a paper not on deep learning or AI, but on databases! In their 2016 SIGMOD paper, Kumar et al. described machine learning as a search for the maximally performing “model selection triple” — i.e. the best combination of features, algorithm, and hyperparameters. “Model selection is ...

more

10:00 - 10:50 Broadhurst/Belasco Machine Learning
11:00
Joshua D. Drake Insights into Postgres, a journey since 1997

presented by Joshua D. Drake

Join JD on a mystical, fantastical and ethereal journey through the paradigm of white glove professional Postgres services since 1997.

11:00 - 11:10 Westside Ballroom
11:40
Michał Gutkowski Rafał Hawrylak How did Postgres become best-fit for TomTom Maps?

presented by Michał Gutkowski and Rafał Hawrylak

Co-speaker: Rafal Hawrylak

Maps are key ingredient of location services. TomTom has built its map-making system with trust in Postgres and it features like: ACID compliance, well known reliability and high performance. Streaming replication allows scalability and thanks to open source licence you can host a Postgres database anywhere: on your laptop, in local data-center or in the public cl...

more

11:40 - 12:30 Gilbert Case Studies
Vasilis Ventirozos From 9.0 to 11: How we (finally) upgraded from a custom built legacy Postgres to Community Postgres 11

presented by Vasilis Ventirozos

For the past several years, against everyones better judgement, we have been maintaining an end of life version of a postgres based service architecture. Based on Postgres 9.0, there was often chatter about possibly upgrading, but it was always deemed “too risky”. Of course you can only get so far patching and upgrading and tuning and tweaking, and so this year we finally got the go ahead to up...

more

11:40 - 12:30 Carnegie/Alvin Migration
Peter Celentano Yaser Raja Bi-directional replication with PGlogical
basic setup, monitoring, and lessons learned

presented by Peter Celentano and Yaser Raja

In the PostgreSQL ecosystem, we've been waiting for true bi-directonal replication for some time. While we've been promised this feature for some time, it's still not available in PostgreSQL core. There are several solutions that can provide us with this much sought-after feature, but the one we'll be talking about today is PGlogical.

PGlogical is better know in the community as one of t...

more

11:40 - 12:30 Imperial/Juliard Ops and Administration
Jemish Patel Postgres Kontained
Containerizing PostgreSQL

presented by Jemish Patel

The trend today is to run everything in containers to gain the benefits of isolation and reproduce-ability regardless of the environment or infrastructure. What does it mean to run PostgreSQL on Kubernetes? How does one achieve durability, high availability? What about resource consumption and elasticity? Can all this be self service?

This talk will highlight how you can get running quickl...

more

CANCELED 11:40 - 12:30 Oneill Ops and Administration
Shawn Sangwook Kim Byeonghun Hyeon Make Your PostgreSQL 10x Faster on Cloud in Minutes
PostgreSQL extension for cloud-native file I/O

presented by Shawn Sangwook Kim and Byeonghun Hyeon

Cloud storage has some unique characteristics compared to traditional storage mainly because it is virtualized and controlled by software. One example is that AWS EBS shows higher throughput with larger I/O size up to 256 KiB without hurting latency. Hence, a user can get only about 4 MiB/sec with 1,000 IOPS EBS volume if the I/O request size is 4 KiB, whereas a user can get about 250 MiB/sec...

more

11:40 - 12:30 Ziegfeld Ops and Administration
Ibrar Ahmed Tune your Linux Box, not just PostgreSQL
Linux Kernel Tuning for PostgreSQL

presented by Ibrar Ahmed

PostgreSQL is one of the leading open-source databases. Out of the box, the default PostgreSQL configuration is not tuned for any particular workload. The default configuration is designed in such a way that PostgreSQL can run on any system using minimum resources. Consequently, a default installation of PostgreSQL does not give optimum performance on the high-performance machine because it is ...

more

11:40 - 12:30 Wilder Ops and Administration
Mara Lemagie Data Pipelines for Data as a Service (Wide Data)

presented by Mara Lemagie

VERY Rough Draft

For work flows that require pulling in data from diverse sources (size, shape, frequency, context) of data and merging together for analysis, this talk covers the best practices we have developed for loading, tracking, validating, and merging these disparate datasets to ensure optimal flexibility. In the use cases explored all data come from external datasets with constant...

more

11:40 - 12:30 Broadhurst/Belasco Data Science
12:40
Marc Linster The future of Postgres in a multi-cloud world
Deployment options that match your cloud Postgres needs

presented by Marc Linster

Postgres has won the race. It is the undisputed leader of relational databases for new and modern applications. This presentation will reflect on how customers take advantage of this new situation, how they deal with the complexity, and how they take advantage of Postgres as the universal data platform that is available in containers, in every cloud, in every data centre and on every developer ...

more

12:40 - 13:00 Oneill Ops and Administration
Mahadevan Ramachandran Monitoring & Diagnostics of PostgreSQL
What, why and how to monitor your Postgres deployment

presented by Mahadevan Ramachandran

The PostgreSQL server consists of several complex subsystems, each of which need to be monitored and tuned to maintain optimal health and performance. This presentation walks you through the monitoring of all important aspects of your PostgreSQL deployment, highlighting what to monitor, how to monitor and why to monitor each of them:

  • backend activity
  • wal files: archiving, growth ...
more

12:40 - 13:00 Imperial/Juliard Ops and Administration
Dan Wright Migrating from OpenTSDB to Timescale
or, how Postgres let us stop worrying and love SQL

presented by Dan Wright

I will discuss the migration of our industrial IoT data (and ingest pipeline) from OpenTSDB to Postgres + Timescale. When Everactive was first getting started, we didn't understand timeseries databases well, but we knew that timeseries in SQL was well known to be a Bad Thing. As we've grown, we've discovered much about dedicated timeseries stores (and their shortcomings), and Timescale has come...

more

12:40 - 13:00 Carnegie/Alvin Migration
Gavin Fleming A PostGIS portfolio

presented by Gavin Fleming

Kartoza is a South African company with a global footprint. We specialise in geospatial solutions with FOSS, which means we use PostGIS a lot. We deploy, host and support many instance of PostGIS for many clients in support of many different applications, on premise and in cloud.

This talk will take you through some of the ways we and our clients use PostGIS, from supporting Django web appli...

more

12:40 - 13:00 Gilbert Case Studies
Leanne Baldwin Turbine - Data import, export, and transformation library for PostgreSQL and Pandas
Turbine is an open source library that works with Jupyter and helps streamline data analysis and engineering in PostgreSQL and Pandas

presented by Leanne Baldwin

Bazean has developed Turbine, a data import, export and transformation library for PostgreSQL and Pandas. This library will be open-sourced by February 2020. Turbine provides easy database access capabilities for data scientists and analysts to be able to visually compare multiple sets of data from different Postgres databases on different cloud providers and other external sources including CS...

more

12:40 - 13:00 Wilder Data Science
Hillel Avni OLTP Workload Acceleration with Memory Optimized Tables on PostgreSQL
A Huawei Database

presented by Hillel Avni

We present Memory Optimized Tables on PostgreSQL which exploit the growing amount of memory and cores of modern hardware.

12:40 - 13:00 Ziegfeld Development
Shaktisikha Sahoo Postgres In a world of Docker Containers
Postgres In a world of Docker Containers

presented by Shaktisikha Sahoo

We will discuss how Postgres, the leading open source relational database, can be positioned as the centrepiece of a pivot from traditional service-based architectures to a micro-services based approach that is in full support of a Containerised world. The presentation will touch on the needs of developers and operators, an approach to containerisation technology.

We will further discuss abo...

more

12:40 - 13:00 Broadhurst/Belasco Data Science
13:00
PostgresConf Organizers Thursday Lunch

presented by PostgresConf Organizers

Thursday Lunch

13:00 - 14:00 Westside Ballroom
14:00
Chaula Jain Workshop to use Postgres and ArcGIS for beginners

presented by Chaula Jain

This would be a hands on workshop for people in the GIS industry to switch to PostgreSQL. It would be for people starting out for the first time to use PostgreSQL and who have little knowledge of databases.The PDF has some of the slides. Bring your computers and this is for newbies.

14:00 - 17:00 Oneill Development
Leo Hsu Regina Obe Getting Stuff done with PostGIS and pgRouting
Answering WHERE and HOW with your data

presented by Regina Obe and Leo Hsu

This training will teach you how to use PostGIS related extensions to answer questions about WHERE and HOW TO GET THERE with data. The main focus will be on the postgis and pgrouting extensions as well as tools for loading spatial data. We will also cover techniques for getting the most performance out of your spatial and routing queries. Though most queries will also work on lower versions, w...

more

14:00 - 17:00 Booth/Edison Data Science
Abbas Butt Data Security in PostgreSQL - Deep Dive
Approaches and Configuration

presented by Abbas Butt

This tutorial will explore the options that are available for data security in PostgreSQL version 12 on Linux. The tutorial will go into reasonable details. For each topic the tutorial will provide detailed steps required for setup and testing.
The main topics that will be discussed are as follows:

  1. Introduction to Cryptography
  2. SSL, TLS, GSSAPI and OpenSSL
  3. Client Side E...
more

14:00 - 17:00 Wilder Ops and Administration
Jignesh Shah PostgreSQL Extensions: A Deeper Look

presented by Jignesh Shah

PostgreSQL is designed to be easily extensible. For this reason, extensions loaded into the database can function just like features that are built in. In this session, we will learn more about PostgreSQL extension framework, how are they built, look at some popular extensions, management of these extensions in your deployments.

14:00 - 14:50 Imperial/Juliard Ops and Administration
Mike Fowler Migrating with Debezium
Exploiting Change Data Capture to minimise migration downtime

presented by Mike Fowler

You've made the decision to migrate to PostgreSQL, converted your schemas and fixed the syntax differences but how do you move your data? For a long time your only option was a careful dump and restore but many businesses can no longer afford such a lengthy outage to accommodate you. Over the last few years many database platforms have exposed change data capture (CDC) mechanisms that allow you...

more

14:00 - 14:50 Carnegie/Alvin Migration
Celia Zhang Royce Wilson Bryant Gipson Janet Whiteman Differentially Private SQL
User-level privacy for PostgreSQL

presented by Celia Zhang, Royce Wilson, Bryant Gipson, and Janet Whiteman

Differential privacy (DP) provides formal guarantees that the output of a database query does not reveal too much information about any individual present in the database. While many differentially private algorithms have been proposed in the scientific literature, there are only a few end-to-end implementations of differentially private query engines. Crucially, existing systems assume that ea...

more

14:00 - 17:00 Ziegfeld Development
Simon Riggs Introduction to PostgreSQL and Kubernetes

presented by Simon Riggs

Discover the new way of thinking about applications, IT processes, and systems in general using Kubernetes – the open source container orchestration platform. Join Simon Riggs, CEO of 2ndQuadrant, to receive an introduction to the platform for automating deployment, scaling, and operations of application containers across clusters of hosts.

Starting with basics on Docker, the training course...

more

14:00 - 17:00 Gilbert Ops and Administration
Bruce Momjian Exploring Common Table Expressions and Window Functions

presented by Bruce Momjian

Developers are often challenged to deliver results that are hard to implement using simple SQL queries. Fortunately, complex SQL capabilities exist in the SQL standard --- common table expressions and window functions.

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

14:00 - 17:00 Odets Development
Greg Spiegelberg Tom Kazimiers Postgres as a Graph Database
Introduction and Application Use Case

presented by Greg Spiegelberg and Tom Kazimiers

Graph databases can be an incredible tool for discovering relationships buried within the data but are they always the right choice? As technical people, we are inclined to learning something new. Graph database solutions are no exception and to many of us they appear as shiny things that must be explored and applied. They are not always the right fit and we forget the R in RDBMS. PostgreSQL ha...

more

14:00 - 14:50 Broadhurst/Belasco Data Science
15:00
 Erik Brandsberg Improve Postgres Transaction/Second with a Database Proxy, no code changes required
Get the most out of your Postgres DB without application changes

presented by Erik Brandsberg

In this session, we will demonstrate how a database proxy can be deployed in minutes to improve read/write transaction/sec (TPS). We will cover typical operations challenges and ways to solve to improve scale with: 1) Query caching, 2) Granular connection pooling, and 3) Read/write split with replication lag detection. No application changes required for deployment.

Technical hands-on demo a...

more

15:00 - 15:50 Imperial/Juliard Ops and Administration
John Hammink A breakdown of the PostgreSQL ecosystem
a 10,000 foot view.

presented by John Hammink

The way that PostgreSQL is used and supported nowadays is widely variable and so are the sorts of companies that have taken it into use, either as components of their own services or as providers of services to other PostgreSQL users.

In this talk, we take the 10,000 foot view of the PostgreSQL ecosystem, highlighting the subsets of the ecosystem and taking a brief survey at who's doing what.

15:00 - 15:20 Broadhurst/Belasco Other
Ahsan Hadi Better late then never - Stored Procedures in PG 11

presented by Ahsan Hadi

Finally we have stroed procedures in PostgreSQL 11, better late then never.

PostgreSQL versions prior to 11 only supported functions, the support for stored procedures was added in PostgreSQL 11. It is added as a new schema object which is similar to functions and but without a return value and with some subtle differneces and benefits.

The main benefits of Stored procedure are the follo...

more

15:00 - 15:20 Carnegie/Alvin Migration
15:30
Anson Abraham Omics data and PostgreSQL
Using PG to store and query Omics data

presented by Anson Abraham

Advances in the technologies and informatics used to generate and process large biological data sets (omics data) are promoting a critical shift in the study of biomedical sciences. While genomics, transcriptomics and proteinomics, coupled with bioinformatics and biostatistics, are gaining momentum, they generate tera- to peta-byte sized data files on a daily basis. These data file sizes, toge...

more

15:30 - 15:50 Broadhurst/Belasco Case Studies
Ajay Patel Monitor Postgres for performance
Monitor Postgres for performance

presented by Ajay Patel

Postgres is really easy and simple to install and use on various platforms cloud or on-premises. Monitoring is a key element of the Database eco-system and to the overall database performance. It also helps to ensure Database is healthy and contributes towards long term stability.

Monitoring sounds really easy but it becomes tricky on what to monitor and how to monitor?

On this session ...

more

15:30 - 15:50 Carnegie/Alvin Ops and Administration
16:10
Julian Fischer Turning PostgreSQL into a Cloud Native Data Service
Lessons learned from operating thousands of PostgreSQL servers.

presented by Julian Fischer

In this talk you will learn about the distilled experience of operating thousands of PostgreSQL database servers. The presentation covers feedback gathered from operating a full lifecycle automation for PostgreSQL across all major on-premise and public infrastructures. Find out how enterprise requirements can be met at scale using state of the art open source tools. A series of common pitfalls ...

more

16:10 - 16:30 Broadhurst/Belasco Ops and Administration
Jeremy Schneider Wait! What's going on inside my database? Understanding PostgreSQL wait events
A deep dive into wait events on PostgreSQL

presented by Jeremy Schneider

PostgreSQL 9.6 introduced wait events and PostgreSQL 10 progressed them, but what are they? What do they mean? How do I find them and how do I make them go away? Wait events are one of the most significant advancements in observability for PostgreSQL databases; their usefulness is unparalleled in troubleshooting performance. This talk will go into all that and more as we explore the world of Po...

more

16:10 - 17:00 Imperial/Juliard Ops and Administration
Stas Kelvich Uncage the elephant: distributed postgresql

presented by Stas Kelvich

Recently, we have seen a rise of databases that claim to provide high availability and scalability without sacrificing classical transactional guarantees. In most cases, such systems are developed from scratch and can choose internal architecture according to the deemed distributed algorithms. In turn, PostgreSQL is a 30-year-old database and some of its architectural decisions can be a good fo...

more

16:10 - 16:30 Carnegie/Alvin Distributed SQL
16:40
Tatiana Krupenya Sergei Rider How to access your cloud PostgreSQL servers from highly secured corporate environment.
Connect to your secured data easily.

presented by Tatiana Krupenya and Sergei Rider

Sometimes it maybe a very tricky just to establish a connection with your remote database. Especially if your working place is in highly secured corporate environment and your database server is in highly secured remote cloud.

We will talk about bypassing SSH, SSL, jump servers, proxy servers, VPNs and various SSO (Kerberos/SSPI/LDAP/Active Directory) authentication systems in order to acces...

more

16:40 - 17:00 Broadhurst/Belasco Ops and Administration
Bertrand Drouvot Active Session History In PostgreSQL: Why?, How? and use cases

presented by Bertrand Drouvot

PostgreSQL provides session activity. However, in order to gather activity behavior, users have to sample the pg_stat_activity view multiple times. Furthermore, it could be even more useful to link the session activity to the sql statistics (coming from pg_stat_statements): that would provide the ability to easily drill-down to the sqls activity from a general overview of the database activity....

more

16:40 - 17:00 Carnegie/Alvin Ops and Administration
2020-03-27
09:00
Hans-Jürgen Schönig PostgreSQL performance from the ground up
Because database speed matters

presented by Hans-Jürgen Schönig

Are you facing performance problems? Well, if you do this is the perfect training class for you. You will be guided through PostgreSQL performance from the ground up and learn how to optimize your PostgreSQL database to achieve maximum performance under all circumstances. We will also point you to the most common bottlenecks and provide easy solutions to those problems.

09:00 - 15:00 Gilbert Ops and Administration
Filip Rembiałkowski postgresql.conf A-Z

presented by Filip Rembiałkowski

We will take a full day trip to explore the postgresql.conf and explain all the interesting machinery behind those knobs - especially the additions in version 12.

09:00 - 15:00 Odets Ops and Administration
 Tom Kincaid Always-On with Postgres BDR (Deploying with Multi-Master)
The next generation of high availability.

presented by Tom Kincaid

This course will present the Postgres-BDR Always-On architecture. It will do an in depth review of logical replication and high availability concepts.

How to achieve maximum availability through a Postgres Multi-Master architecture will presented. Specific topics that will be covered:

1) How to achieve zero down time upgrades. 2) How to quickly (under a second) failover. 3) How to failo...

more

09:00 - 15:00 Wilder Ops and Administration
Gavin Fleming Working with spatial data in PostGIS

presented by Gavin Fleming

Kartoza is a South African company with a global footprint. We specialise in geospatial solutions with FOSS, which means we use PostGIS a lot. We also present courses on PostGIS. This workshop is a condensed version of what we normally do in two or three days. You will get hands-on experience with loading spatial data, understanding geometries and using spatial data in analysis. In the process ...

more

09:00 - 15:00 Oneill Case Studies
Henrietta Dombrovskaya Boris Novikov The Ultimate Postgres Optimization: Going Beyond Docs and Textbooks
Think like a database

presented by Henrietta Dombrovskaya and Boris Novikov

Abstract: Is it possible to teach somebody to optimize? Or is optimization a sort of magic, available only for the limited circle of wizards? We believe that it is entirely possible to become an optimization wizard, and we are going to prove it in the course of this training. We will provide the participants with the opportunity to look at the process of query writing “as a database engine”, a...

more

09:00 - 15:00 Ziegfeld Development
Unscheduled events
PostgresConf Organizers Friday Lunch

presented by PostgresConf Organizers

Friday Lunch