PgConf.Russia 2020

Porting applications from Oracle to Postgres is common work today. Unfortunately it is not without problems. In presentation I'll try to show the basic performance problems related to differences between Oracle and Postgres and PL/SQL and PL/pgSQL.

PostgreSQL built-in full text search gives unique possibilities inaccessible for external search engines, such as virtual or generated document search and search with access restrictions. I will talk about these and other features, full text search configuration, indexes, and highlight the latest advances and future expectations

I like stored procedures - it is great technology. But like any other technologies it allows to write not well optimized code. It is not easy to write optimized code, sql statements in complex large applications. On second hand, there are some tools, that can be used very easily, that can help. Postgres has built-in tracking functions possibility. There are PLProfiler and plpgsql_check. With these tools is easy work to detect slow part of applications.With this knowledge, the fix of performance issue is less magic.

In the report, we want to talk about the experience of migrating one large system from Oracle to PostgreSQL. The system itself was built on the PHP + Oracle stack; its distinguishing feature was that all business logic was implemented in PL / SQL code. In a DBMS, there are more than 3000 packages with 4-10 functions (procedures) in each. In PHP - more than 10,000 forms with inserts of anonymous blocks used to receive data, process and save results in Oracle.

To solve this extremely voluminous work, we took ANTLR4 (a powerful parser generator for reading, processing, executing, or translating structured text), PL/SQL grammar, and created an automatic converter that allows you to convert all objects in the schema and our system from Oracle to working code for PostgreSQL.