What does a COMMIT do internally to PostgreSQL?

In PostgreSQL, a COMMIT statement is used to finalize a transaction and make its changes permanent in the database. When a COMMIT statement is executed, the following things happen internally in PostgreSQL:

  1. All locks held by the transaction are released, allowing other transactions to access the modified data.
  2. The transaction is marked as committed in the PostgreSQL transaction log.
  3. PostgreSQL writes a commit record to the transaction log. This record contains information about the changes made by the transaction, which can be used to recover the database in case of a system failure.
  4. The transaction log is flushed to disk to ensure that the commit record is durable.
  5. Any changes made by the transaction are written to the PostgreSQL data files on disk.
  6. The transaction is removed from the list of active transactions.
  7. Any temporary tables or indexes created during the transaction are dropped.

After a transaction is committed, its changes become visible to all other transactions. If a system failure occurs before the commit record is written to the transaction log or the changes are written to disk, PostgreSQL can use the transaction log to recover the database to a consistent state.

It’s important to note that a COMMIT statement in PostgreSQL is atomic, consistent, isolated, and durable (ACID), which ensures that the database remains in a consistent state even in the presence of system failures, crashes, or power outages.

Why COMMIT’s response time is fairly flat in PostgreSQL?

In PostgreSQL, the response time for COMMIT statements is generally flat because the transaction commit process is designed to be fast and efficient.

When a COMMIT statement is executed, the changes made by the transaction are written to the PostgreSQL data files on disk, and the transaction log is written to ensure durability. However, this process is designed to be fast, and the performance impact is generally minimal.

The primary reason for the flat response time is that PostgreSQL uses a write-ahead log (WAL) to record changes made by transactions. The WAL is a sequential log, and all changes are written sequentially to the log file. This design allows PostgreSQL to optimize disk I/O and reduce the number of disk seeks required to commit a transaction.

Additionally, PostgreSQL uses a multi-version concurrency control (MVCC) mechanism to manage transaction isolation. This means that concurrent transactions can access the database without interfering with each other, which can reduce contention and improve performance.

In general, the response time for COMMIT statements in PostgreSQL is fairly consistent, even as the size and complexity of the database increase. However, the response time can still be impacted by factors such as the size of the transaction, the number of indexes and constraints on the affected tables, and the number of concurrent transactions accessing the database.

Monitoring REDO generated in PostgreSQL? 

To monitor the amount of redo generated by queries in PostgreSQL, you can use the pg_stat_statements extension, which provides a view of executed SQL statements and related statistics.

Here is an example SQL query that retrieves the top 10 SQL statements by the amount of redo generated:

SELECT query, 
sum(blks_written) as redo_generated 
FROM pg_stat_statements 
WHERE query <> ‘<insufficient privilege>’ 
GROUP BY query 
ORDER BY redo_generated DESC 
LIMIT 10;

This query retrieves the total amount of redo generated by each SQL statement recorded in the pg_stat_statements view, and then sorts the results by the amount of redo generated in descending order. The LIMIT 10 clause limits the output to the top 10 queries.

About Shiv Iyer 422 Articles
Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.