DBA / Database

How was the PostgreSQL cluster organized? Replication scheme and fault tolerance?

Provide an interesting problem you solved at your previous job.

Task 5: EXPLAIN shows type: ALL, rows: 200000, Using temporary; Using filesort. What does it mean and how to optimize?

Task 1: Find the top 5 routes (departure_station, arrival_station) by the number of tasks for January 2025 from the harvester_tasks_queue table.

Was the data schema normalized in your project? How did you approach normalization?

What monitoring tools have you used?

Task 3: Find duplicate tasks (by departure_station, arrival_station, departure_date, crawler_id) and propose a plan for safe removal from the harvester_tasks_queue table.

Task 4: Optimize the query WHERE DATE(tep.available_from_departure_date) = departureDate. Explain the problem and suggest a solution.

How to protect the target table from duplicates when inserting data from a temporary table? How to update existing records with more recent data (upsert)?

How did you handle deadlocks in a high-load system? What did you do when they were detected?

How do you verify the results of a stored procedure or query? Methods and approaches.

How to organize the architecture of a table that grows rapidly for fast operation only with fresh data? How to efficiently delete old data?

Задание 2: Найти маршруты с активными задачами (status = 'Ready to start'), но без обновлений 30+ дней. Схемы: harvester_tasks_queue и station__crawler_mapping_with_crawler_ids.

Have you ever had cases of upgrading the database product version? How did you prepare and what problems arose?

Tell about your current position: responsibilities, team, infrastructure, tools.