PGConf.Russia 2023

The following topics will be covered as part of the presentation:

• Architecture of logical replication
• Publisher introduction
• Subscriber introduction
• Data syncronization introduction
• Logical decoding
• Replication slot
• output plugin

Every DBA has in one way or another experienced the situation when PostgreSQL tables and indexes grow significantly in size. While looking for the reason for such behavior, we often conclude that database objects have "bloated". In this talk, we'll discuss the reasons behind bloating, create a testing environment to define the best method to reduce bloating. We'll also compare several anti-bloating utilities and get familiar with one more tool that helps us to efficiently struggle against bloating. We expect this presentation to become helpful for PostgreSQL DBAs of any experience level.

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.