How to put the whole world into a regular laptop: PostgreSQL and OpenStreetMap

I'll show in PostGIS how everyone can analyze the geodata of the entire Earth and get answers to their global questions in minutes and seconds.

When you use a taxi in small towns, calling a car by phone, then with a high probability your trip is charged by the program based on OSM data. For billing, one of the routing packages is used. Through this use case, the taxi company employees put the house number and street on the buildings and contribute not only to their business, but also to OpenStreetMap.

The data analytics scenario also includes the tasks of where it is better to place an outlet so that buyers come to it. Again, data on walking distance and population of the surroundings can be extracted from geodata. You can calculate the value of real estate based on many factors related to the location of the object and its surroundings.

Scientists can build predictive models to predict epidemics, urban evolution, plan recreational areas and development of existing territories based on open geodata.

Well, you can answer any geography question that comes to your mind: calculate the area of cities and buildings, the length of roads and extract the names of cities, regions and islands. You can, for example, become a champion in the game of "Cities" or establish a new service for renting electric scooters. Everything is limited only by your imagination.

I published https://github.com/igor-suhorukov/openstreetmap_h3 - my project of high performance data loader, which allows you to perform geoanalytics on data from OpenStreetMap in PostGIS. It transform OpenStreetMap World/Region PBF dump into partitioned by H3 regions schema. Columnar storage option activate CitusDB extension in PostgreSQL to speedup aggregation queries.

Crazy things you can do with PostgreSQL Indexes

Of the relational databases, PostgreSQL is fairly unique in the indexing capabilities it offers. While most of us are familiar with the use of indexes to speed performance when filtering on columns, PostgreSQL indexes can do far more than this.

This talk will focus on cases where difficult problems were solved through the creative use of indexes. Each of these cases is from an episode in my career.

In this presentation, you will learn:

• The general index access methods PostgreSQL supports out of the box
• How the planner uses indexes (only on a high level)
• Functional indexes
• Creative use of Index Only scans.

This is a talk for all audiences. The talk is primarily for beginner to intermediate users, but should have enough information for more advanced users to get some insight or inspiration from the talk as well.

Corosync-Pacemaker HA cluster. Work on mistakes

I will talk about the common mistakes when setting up a Corosync-Pacemaker failover cluster. Often these mistakes lead to fatal consequences, and, as a result, to the rejection of the chosen solution in favor of others. Would you like to get a recipe for the "right" cluster?

Migrating Oracle to PostgreSQL in a high-load system with microservice architecture

A report on our experience of implementing PostgreSQL instead of Oralce in a highly loaded system with a microservice architecture that processes several terabytes of data. Let's talk about how we went from piloting Greenplum to moving to several interacting PosgreSQL databases with different load profiles, and about the problems we encountered in the process.