What can be said about the query plan's optimality? MVCC There is a table CREATE TABLE orders ( id BIGSERIAL PRIMARY KEY, status TEXT, updated_at TIMESTAMP ); and a query running every second UPDATE orders SET status = 'processed', updated_at = now() WHERE id = 1; After a few days, you notice: • the table size has grown significantly • the query SELECT * FROM orders WHERE id = 1 runs slower Questions for the candidate: 1. Why does the table grow even though the row is always the same? 2. What role does MVCC play in this situation? 3. What should solve this problem?

The table grows because in PostgreSQL (and other MVCC databases) when a row is updated, the existing record is not overwritten but a new version of the row is created. The old version remains in the table until it is removed by the vacuum process. Therefore, even when updating the same row, old versions accumulate physically in the table, increasing its size.

MVCC (Multi-Version Concurrency Control) provides transaction isolation by allowing transactions to see the database in the state it was at the start of the transaction. For this, when a row is updated, a new version is created, and the old one remains accessible to other transactions that may read it. This leads to the accumulation of "dead" row versions.

The problem is solved by regular vacuuming (especially VACUUM FULL or autovacuum), which removes outdated row versions and frees space. You can also configure autovacuum to run more frequently to prevent old versions from accumulating. In some cases, reorganizing the table or using more suitable data update strategies helps.