Overview of PostgreSQL architecture and ACID

Today I learned about PostgreSQL architecture in a more detailed way by understanding each component.

• The process starts from the client. A user or application sends a query to the PostgreSQL server through an API or connection layer. Once the request reaches the server, the query processing begins. The system first checks the query, then plans how to execute it, and finally runs it to get the result.
• Inside the server, one important part is the shared buffer. This is a memory area where frequently used data is stored. Instead of going to the disk every time, PostgreSQL first checks the shared buffer. This makes the system faster.
• Another important concept is write ahead logging. Before updating the actual data in storage, PostgreSQL writes the changes into a WAL log. This ensures that even if the system crashes, the data can be recovered.
• There are different background processes related to WAL. The WAL writer is responsible for writing log records from memory to the WAL files. The WAL sender sends these log records to another server in case of replication. On the other side, the WAL receiver receives the logs and applies them to keep the standby server updated.
• The storage layer contains the actual data like tables and indexes. Data is read from and written to the disk when needed.
• Another important background process is autovacuum. In PostgreSQL, when data is updated or deleted, the old data is not immediately removed. It remains as unused space. The autovacuum process automatically cleans this unused data and frees up space. It also helps maintain database performance by preventing the database from becoming slow over time.
• Along with this, other background processes keep running to manage tasks like saving data to disk and maintaining stability.
• Finally, after processing everything, the result is sent back to the client.

• Overall, learning these components like shared buffer, WAL writer, WAL sender, WAL receiver, and autovacuum helped me understand how PostgreSQL manages performance, data safety, and maintenance in a real system.
  ACID

• ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties are very important because they make sure that database transactions are reliable and the data remains correct.

• Atomicity means all operations in a transaction should happen completely or not happen at all. There is no partial execution. For example, in a bank transfer, if money is deducted from one account but not added to another account, the transaction will be cancelled. So either both actions happen or none happens.

• Consistency means the database should always follow rules and constraints. Before and after a transaction, the data must remain valid. For example, if a rule says account balance cannot be negative, the system will not allow a transaction that breaks this rule.

• Isolation means multiple transactions happening at the same time should not affect each other. Each transaction should work as if it is running alone. For example, if two users are accessing the same account, one user should not see incomplete changes made by another user.

• Durability means once a transaction is successfully completed, the data is permanently stored. Even if there is a system crash or power failure, the data will not be lost because it is saved properly in the database.

• A simple example to understand all these properties is a bank transfer system. When a person transfers money, the system ensures that the amount is deducted and added correctly, rules are followed, other users do not see intermediate changes, and once the transaction is completed, it is saved permanently.