PgConf.Russia 2020

A brief story how MySQL → PG migration could increase company efficiency tenfold times:

1. Program code has been reduced 50 times, with optimization of backend team (from 15 to 3 engineers)
2. Software development of new features has become measuring in days, not in months
3. Infrastructure costs per 1M users have been reduced 20 times
4. Database structure and technical documentation were simplified significantly, from 100K high-dependent tables to just 20 simple tables
5. New security level because of total forbidden on external SQL commands to the database
6. Quick analytics aggregation on multiple parameters, without external analytics systems
7. The last, but not the least: the main business was keeping alive during migration

Many businesses which use Database management systems like Oracle, DB2 & MS SQL are unreliable these days. Moreover, the costs incurred in maintaining these systems and its product licenses keeps on increasing. As the competitors are migrating over to the new technologies and tools available in the market, it is necessary for these businesses to migrate to new environment which is efficient, consistent and reliable to stay in the market and the technologies used in the current environment have become obsolete or no longer serve the business purpose. PostgreSQL has emerged as a top open-source RDBMS software. Since there is no licensing cost associated with it most of the companies are planning to migrate the databases which are currently running on other RDBMS like Oracle, DB2, MS SQL server to PostgreSQL. This report summarizes the various methodologies, procedures and techniques involved in successfully migrating the data from Oracle to PostgreSQL & DB2 to PostgreSQL. Migration is not a simple effort there should be proper planning and testing involved in this right from database connectivity to performance analysis. In this paper we are going to cover most of the steps which we need to consider before the migration and after the migration like choosing the correct tools for implementing the migration, time taken to migrate ,data compatibility, code conversion, application connectivity to database, database configuration parameters, performance analysis, replication setups, database monitoring, patching and backup strategies.

Walk-through of extending PostgreSQL with a user-defined type. The journey begins from the basics, from creating simple domain types over existing types, and continues to implementing a full-blown datatype from scratch in C.

PostgreSQL's advanced index types, GiST, GIN, and SP-GiST, are covered in enough detail to give an understanding of what each of them is good for. Support functions for each of them are shown for the example 'color' datatype.

In my talk, I will tell how we built a geographically distributed system of personal data storage based on Open Source software and PostgreSQL. The concept of the inCountry business is to provide customers with a ready-to-use infrastructure for personal data storage. Our business customers are ensured that their customer’s personal data is securely stored within their country’s borders. We wrote an API and SDK and built a variety of services. Our system complies with generally accepted security standards (SOC Type 1, Type 2, PCI DSS, etc.). We built our infrastructure with Consul, Nomad, and Vault, used PostgreSQL, ElasticSearch as a storage system, Nginx, Jenkins, Artifactory, other tools to automate management and deployment. We have assembled our development and management teams - DevOps, Security, Monitoring, and DBA. We use both cloud providers and bare-metal servers located in different regions of the world. Development of the system architecture and ensuring the stability of the infrastructure, consistent and secure operation of all its components is the main task facing our teams.