PGConf.Russia 2018

• Spatial and advanced spatial analysis with pure PostGIS (including cutting edge PostGIS functions available)
• How could we mix and tied efficiently PostgreSQL and Python data types (as NumPy ndarray, and Pandas DataFrames)
• Tools to improve our data manipulation environment (Jupyter tricks, easy dataviz...)
• How to go further throught GeoDataScience, with Python libs and framework tied with PostgreSQL/PostGIS (including Machine and DeepLearning)

Last year we announced Skala-SR / Postgres Pro database machine with a hardware and software support for remote direct memory access (RDMA) as a key feature. The first appliances have been already installed at customer sites, and even with the first version customers got some constructions, that were impossible without RDMA and CPU offload (got with Mellanox networking). However, capabilities of this equipment are much wider, and this talk is dedicated to current works and prospective developments for this topic.

Transition tables, a new feature in PostgreSQL 10, offer broad new capabilities including new ways to maintain materialized views. At the end of this talk, you will have seen new ways to use this feature and have it in your tool chest for the future.

For the majority of System Administrators and DBAs performing an upgrade for RDBMS, let alone a major one, is a pain. That’s because one of the key factors that plays a role in a decision if and when to perform an upgrade is the downtime that it might come to during the process. This is true for any databases but especially important for those that are in production or under a high load.

Often, a major upgrade get’s cancelled and a DBA needs to go back to an older version due to the lack of experience or some basic errors that could have been easily avoided at the planning stage.

In our consultancy, we perform upgrades for our clients regularly and it allowed us to streamline the process and take some preventative measures that help us to perform it quickly, efficiently and with minimal or no downtime.

In this talk, I will share some key steps and tools that will help any DBA to become better at major upgrade performance. I will answer the following questions:

How to prepare for an upgrade of PostgreSQL? What one needs to do at the planning stage? How to plan your actions during the actual upgrade process? How to perform an upgrade successfully without going back to the older version? What actions one must perform following an upgrade?

I will also go through the two most popular processes of an upgrade: pg_upgrade и pg_dump/pg_restore, will compare some of the benefits and downfalls using each of these. I will also discuss some of the main issues one might face throughout the process and ways to avoid them.

This talk would be of interest to those who are new to PostgreSQL, as well as experienced DBAs who would like to learn more about upgrades or those who, in general, would like to understand why major upgrades should NOT be avoided like the plague.