PGConf.Russia 2025

This talk explores the challenges we encountered — and solved — while migrating massive databases from Oracle to PostgreSQL.

One of the most complex aspects of this process was rebuilding foreign keys (FKs). To overcome these challenges, we had to dive deep into the internal workings of FK creation and validation.

Key topics include:

Does ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY have an execution plan?

Can an ordinary user influence this process?

What locks are applied during FK creation and validation?

Also, we’ll introduce a first-principles method for investigating performance issues, applying it to a real-world FK creation bottleneck.

Two years ago, I spoke about data retrieval queries, where the LINQ to DB library offers almost limitless possibilities.

This talk is about data modification operations. LINQ to DB allows you to execute UPDATE + JOIN queries, INSERT FROM SELECT, supports the MERGE operator, row-level locking with SELECT FOR UPDATE, temporary table creation, and features a fast data insertion mechanism.

I will also discuss the linq2db. EntityFrameworkCore project — a solution for those who aren’t ready to leave EF Core but still want the capabilities of LINQ to DB. I’ll share our experience implementing LINQ to DB in a large-scale project.

Databases are a core component of any information system, and query performance directly impacts overall efficiency.

Last year, we explored suboptimal queries and optimization techniques, and the topic proved highly relevant. In this second installment, we’ll cover:

Using extended statistics for computed columns

Pagination techniques for improved query handling

Real-world optimization examples from 1C

The visibility map in PostgreSQL is an important mechanism for optimizing database performance, accelerating read operations, data cleanup, and indexing. Despite some limitations, such as memory consumption and fragmentation, its use can significantly enhance performance in high-load systems. Effective utilization of the visibility map requires proper configuration and monitoring, which is especially crucial in systems with large data volumes and high transaction concurrency.