New UUID versions

Primary key generation is a known issue quite efficiently though not ideally solved by sequences. There are still problems with distributed generation, real uniqueness and predictability. Initially, UUID was not intended for use as a DBMS key, but its new versions (6, 7 and 8) make it quite convenient to use them as both synthetic and natural keys.

How to transfer 10TB from Oracle to Postgres in 24 hours?

We offer to your attention our experience in data migration and the Ora2PgCopy program written in Java for high-speed data transfer from Oracle to Postgres, which is used after creating tables and transferring the program code of application systems. High data transfer speed is provided by using the Postgres command “copy”, using multithreaded Java technology for file processing, managing the nologged/logged table option, and supporting LOB and CLOB data types. According to the test results, Ora2PgCopy works noticeably faster than such analogues as: Ispirer (convertum), oracle_fdw, ora2pg, Pentaho kettle. Ora2PgCopy can function as a module as part of the LUI4ORA2PG migration automation system or independently of it. The history of the growth of the Live Universal Interface (LUI) web application development tool and the LUI4ORA2PG migration tool can be found in previous presentations at PGConf conferences: https://pgconf.ru/2019/118109 , https://pgconf.ru/201911/264095 , https://pgconf.ru/2020/262456, https://pgconf.ru/2021/288310, https://pgconf.ru/2022/316022.

Pgpool-II in the "Master-Master" mode or Network engineer’s overview of the PostgreSQL load balances solution.

One of the main needs of high-load database projects is a right configured load balancer within the database cluster. Most of the solutions I worked with, including Pgpool-II, could not fully suit business requirements. In this article me as a network engineer with broad experience will cover tricky configurations of the Pgpool-II operating in the “Master-Master” mode, as well as consider cases in which similar solutions are indispensable.

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.