Program for PGConf India, 2020

Unscheduled events
Amit Sharma

Understanding Backup
Types of Backups

presented by Amit Sharma from EnterpriseDB

In order to get the maximum reliability, backups play a vital role to ensure that you have all the data required to recover from any eventuality. There are multiple methods available with which you can take a backup and one needs to plan and understand which method suits them the best. We will be discussing various environments depending on size and recovery methods to give you a clear view of ...

more

Database Administration
Asim Rama Praveen

Fault injection facility for PostgreSQL developers

presented by Asim Rama Praveen from Pivotal Software Inc

How to write a isolation/TAP test to validate crash recovery such that the server crashes after writing a commit record but before updating CLOG? How to test whether a terminate signal that arrives exactly after writing a PREPARE record is handled correctly?

It is possible to come up with many more scenarios like these where existing test frameworks such as regress/TAP/isolation fall short....

more

Database Engine Developers
Ivan Panchenko

Practical JSON

presented by Ivan Panchenko from Postgres Professional

This talk describes some practical cases of JSON usage with the compendium of corresponding PostgreSQL JSON tools: SQL functions and operators, including the new ones introduced in PostgreSQL 12 according to SQL:2016 standard, JSQuery extension, aggregates, recursive CTE, special indexes for JSON search, JSON support in server-side procedural languages. The talk is illustrated by numerous examp...

more

Application Developer
Avinash Vallarapu Jobin Augustine

25 Interesting Features of PostgreSQL 12
PostgreSQL 12

presented by Avinash Vallarapu, Jobin Augustine from Percona

  • PostgreSQL continued to be the DBMS of the year for 2018. An interesting fact is that there is a new release every year. So we have another new release this year, PostgreSQL 12. It is planned to be released in the third quarter of the year 2019. There are a lot of improvements in partitioning, query planner, Indexing and some SQL features, etc. Among all of the interesting features for develo...
more

Database Administration
Jobin Augustine

Deeper Understanding of PostgreSQL Execution Plan : At plan time and run time

presented by Jobin Augustine from Percona

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

more

Application Developer
Oleg Bartunov

Full Text Search in PG12

presented by Oleg Bartunov from Moscow University, Postgres Professional

Full-Text Search ( FTS ) is a search for the documents, which satisfy query and, optionally, return them in some order. The most usual case is to find documents containing all query terms and return them in order of their similarity to the query. Notions of query and similarity are very flexible and depends on application. Document can live on a hard drive or to be completely virtual as a resu...

more

Application Developer
Anastasia Lubennikova

B-tree indexes - learn more about the heart of PostgreSQL

presented by Anastasia Lubennikova from Postgres Professional

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

more

Database Administration
Beena Emerson

The Truth about Partitions

presented by Beena Emerson from EnterpriseDB

A native partitioning system was added in PostgreSQL 10 and it has since evolved considerably. In PostgreSQL 13 the existing Postgres benchmarking tool pgbench was extended to allow testing for range and hash partitions.

Choosing a good partition strategy is not a child's play. Partition done wrong can lead to massive performance deterioration.

This talk will summarise all the features a...

more

Database Administration
Rushabh Lathia

Contributing PostgreSQL Open Source Community: Viewing PostgreSQL at different angles.

presented by Rushabh Lathia from EnterpriseDB India Pvt. Ltd.

PostgreSQL always encourages everyone to submit the patches for the features they would like to see in PostgreSQL and always encourages them to participate in the goal to make the PG as user-friendly. Any PostgreSQL user would like to get involved in the process or would like to contribute to back to the PG but the question is HOW?

One area where PG falls short is finding enough reviewers...

more

Application Developer
Avinash Vallarapu Jobin Augustine

PostgreSQL Tutorial For Oracle And Mysql Dbas And Beginners
PostgreSQL Tutorial for Oracle DBAs and Beginners

presented by Avinash Vallarapu, Jobin Augustine from Percona

  • This tutorial is focused on teaching PostgreSQL to professionals who are already experienced with Oracle or MySQL or SQL Server. Relational databases share a lot of similar concepts. You may find it easier when the concepts of PostgreSQL are related with Oracle/MySQL. Thus, I would start with PostgreSQL Architecture for Oracle/MySQL DBAs.

  • As we discuss from basics, even a beginner could ...

more

Training
Sachin Kotwal

Sharding in PostgreSQL

presented by Sachin Kotwal from 2ndQuadrant

Sharding in PostgreSQL

1. How to achieve Sharding in PostgreSQL

This section will give details of PostgreSQL Sharding prerequisite (required extensions and sample example).

  • Sharding with inheritance based partitioning : Demo example for Sharding with inheritance based partitioning.

  • Sharding with declarative partitioning : Demo example for Sharding with inheritance based...

more

Database Administration
Krishnaswamy Subramanian & Nithya Natarajan

Taming 100+ PostgreSQL instances on the Cloud

presented by Krishnaswamy Subramanian & Nithya Natarajan from ThoughtWorks

Kubernetes lets you deploy applications in ways that are highly available and resilient, and can utilize the underlying resources more efficiently. This can bring huge cost savings to the business, especially when you’re entrusted with running PostgreSQL clusters on Kubernetes, rather than over traditional VMs.

In this talk, we share our experiences of running and managing 100+ PostgreSQL i...

more

Case Study
Kaushik Iyer

Real time data streaming in PostgreSQL

presented by Kaushik Iyer from Endurance International Group, APAC

Real time data streaming of any Data Modification event on the database can prove to be instrumental as a form of derived event sourcing to update heterogenous and non slave data stores. PostgreSQL out of the box provides functionalities and settings which can enable the generation of such events. When combined with services like Debezium which can monitor and record the events we get a pip...

more

Case Study
Ashu Pachauri

PostgreSQL: Pushing the limits on a single machine

presented by Ashu Pachauri from Clarisights

With more than 2 billion writes and 400 billion records read per day on a single machine, this talk is the story of how we, at Clarisights, scaled a single PostgreSQL instance more than 50x in last one year.

I'll outline the approaches we took to scale our use case on PostgreSQL, including how we identified and implemented the avenues for scaling both reads and writes, sequen...

more

Case Study
Vaibhav Dalvi

Journey of the Query from SELECT to Result set.

presented by Vaibhav Dalvi from EnterpriseDB

Many of the users and ‘wanting to be postgres developers’ wonder what really happens when a SQL is issued over PSQL prompt and till the result is shown on prompt(specially SELECT queries). During this presentation I will like to take the audience through the different phases of query when one types “SELECT * FROM mytable WHERE b = 20”. The presentation will first list out the phases and la...

more

Database Engine Developers
Abhijit Menon-Sen

Locks in Postgres

presented by Abhijit Menon-Sen from 2ndQuadrant

Postgres uses locks at every level in order to support concurrent operations. These include spinlocks, lightweight locks, and regular (heavyweight) locks. Each of these lock types has different properties and applications, but they all have a direct impact on performance and scalability. Understanding how these locks are used and how they interact can help to understand how your server will per...

more

Database Administration
Nikhil Sontakke

Toward full ACID distributed transaction support with Foreign Data Wrapper
Toward full ACID distributed transaction support with Foreign Data Wrapper

presented by Nikhil Sontakke from 2ndQuadrant

PostgreSQL has Foreign Data Wrapper feature and it is the powerful feature to access the distributed data across heterogenous data stores. FDW became writable at PostgreSQL 9.3 therefore PostgreSQL with FDW has potential to become distributed database supporting reads and writes.

However one of the biggest missing piece is transaction management for distributed transactions. Currently atomic...

more

Database Engine Developers
Pavan Deolasee

Recent advances in addressing key challenges around MVCC

presented by Pavan Deolasee from 2ndQuadrant

PostgreSQL’s Multi-Version Concurrency Control provides many nice properties, including read and write scalability. But it has also been a constant source of irritation and problems. In the last few years, significant work has been done in this area and a lot of work is in pipeline. In this talk, we will discuss those improvements, how to make use of those and what’s likely to come in future re...

more

Database Administration
Jim Mlodgenski

Top 10 Mistakes When Migrating From Oracle to PostgreSQL

presented by Jim Mlodgenski from Amazon Web Services

As more and more people are moving to PostgreSQL from Oracle, a pattern of mistakes is emerging. They can be caused by the tools being used or just by not understanding how PostgreSQL is different than Oracle. In this talk, we discuss the top mistakes people generally make when moving to PostgreSQL from Oracle and what the correct course of action.

Application Developer
Jim Mlodgenski

Introduction to PL/pgSQL Development

presented by Jim Mlodgenski from Amazon Web Services

When developing against a PostgreSQL database, many times you will use SQL from your applications, but sometimes you need more. You might need to run some of your logic on the database server for performance or you may want to just create some server-side functions to simplify your application development. There is an infinite number of reasons why you would want to write some code that runs on...

more

Training
Apoorva Aggarwal

Why physical storage of your database tables might matter

presented by Apoorva Aggarwal from Grofers India Pvt Ltd

SQL Query on a database table running slow? Let's create an index on the table. The query is still running slow although the query planner says the index is being utilized. What do we do next?

This talk is about a time when we were building personalized recommendations for our customers and were faced with high latencies in our systems. We pre computed relevant item recommendations for ea...

more

Application Developer
Tarun Garg

Looking beyond B-Tree Index for performance optimizations
Indexing also has their limits and what do to do in those cases?

presented by Tarun Garg from Engineering Lead @ SquadVoice

When thinking about optimizations in SQL queries, we often only think about changing the structure of SQL queries, avoiding joins or implementing B-tree indexing, but the truth of the matter is they only work up to a certain extent.

e.g. when data to be fetched by SQL becomes huge, indexing like techniques(specially b-tree) also does not provide many benefits over no indexing because of pla...

more

Database Administration
Denis Mekhanikov

How to boost and scale Postgres - from sharding to in-memory data grids

presented by Denis Mekhanikov from GridGain

Businesses are accumulating data at enormous rates requiring huge amounts of storage. Due to this, many industry players tend to predict Postgres' extinction. Nevertheless, most businesses have Postgres deployments for decades and continue doing so regardless of the advancement in big data, mobile, IoT, and all other revolutions that contribute to data generation.

So, how does Postgres manag...

more

Database Administration
Hari Kiran

PostgreSQL Performance Optimization - A DBA's View
PostgreSQL Performance Optimization - A DBA's View

presented by Hari Kiran from 2ndQuadrant

Training focusses on a few Linux, PostgreSQL, and Application performance tips & techniques. All these are tested on low-mid-high read/write-intensive database shops. The takeaway from this Training would be an assessment of PG Instances from a Performance Optimization standpoint.

Training
Krishnaswamy Subramanian & Nithya Natarajan

The Kubernetes Spell for PostgreSQL on the Cloud
The Kubernetes Spell for PostgreSQL on the Cloud

presented by Krishnaswamy Subramanian & Nithya Natarajan from ThoughtWorks

Kubernetes lets you deploy applications in ways that are highly available and resilient, and can utilize the underlying resources more efficiently. This can bring huge cost savings to the business, especially when you’re entrusted with running PostgreSQL clusters on Kubernetes, rather than over traditional VMs.

In this training, we share our experiences of running and managing 100+ PostgreSQ...

more

Training
Mansur Shaikh

PostGIS - A DBA and Developer view
PostGIS - A DBA and Developer view

presented by Mansur Shaikh from EnterpriseDB

PostGIS is an open-source, freely available spatial database extension for the PostgreSQL Database Management System. In a nutshell, PostGIS adds spatial functions such as distance, area, union, intersection, and specialty geometry data types to PostgreSQL. So in a normal database, you store data of different types (numeric, text, timestamps, images…) which can help you answer questions like ‘h...

more

Training
Lakshmikant Gundavarapu (L.K)

PostGreS Hyper Scale (Citus)
PostGreS Hyper Scale (Citus)

presented by Lakshmikant Gundavarapu (L.K) from Microsoft

PostGreS HyperScale (Citus)

Training
Simon Riggs

Schema Change and Schemaless
Schema Change and Schemaless

presented by Simon Riggs from 2ndQuadrant

How does PostgreSQL cope with schema change on production databases? What functions are available and how do we use them? What other features are on the roadmap? Is a Schemaless approach to data management via JSON a valid alternative approach? What other issues are faced if you adopt that approach? What improvements might we expect in the future for schemaless?

Database Administration
Marc Linster

The future of Postgres in a multi-cloud world
The future of Postgres in a multi-cloud world

presented by Marc Linster from EnterpriseDB

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

Keynote
Robert Haas

Avoiding, Detecting, and Recovering From Data Corruption
Avoiding, Detecting, and Recovering From Data Corruption

presented by Robert Haas from EnterpriseDB

Hardware or software failure or user error can result in your PostgreSQL database becoming corrupted. In this talk, I'll discuss some things that you can do to minimize the likelihood that your database will become corrupted. In case that doesn't work, or you already have a corrupted database, I'll also discuss signs you may be able to spot that will tell you that your database has become ...

more

Database Administration