1. Transaction sizes and PostgreSQL protections
Now, let us dig into some more details on transaction sizes and how PostgreSQL tries to protect you during normal usage.
2. Keep transactions small
When making transactions, focus on grouping only statements that need to be protected inside of them helps ensure your code is easier to reason about.
While computers may be great at remembering their spot in an extended transaction, humans rarely are. Database performance can come to a crawl due to keeping all the affected records of the transaction in memory before they are committed. Finally, the more statements you have in a transaction, the most potential places you have for something to error and cause the whole block to fail.
3. Dividing operations
In this example, we've sold some chocolate chip cookies. We're inserting the sale into the sales table, then adding the cookies into our baking_list table and reducing the inventory by the amount we sold. The critical things that must happen together are the recording of the sale and the reduction of inventory. The reminder to bake some more could be a separate transaction since it's an independent need. It is essential to ensure that you understand which operations need to be protected to ensure data integrity, and this comes with an in-depth of understanding of your data, and what you're trying to achieve with it.
4. Is this a transaction?
Now let's look at this statement. This example is just an ordinary SQL statement without the transaction wrapper. However, by default, PostgreSQL treats every single SQL statement as a transaction unless it's a BEGIN or COMMIT. It wraps a BEGIN and a COMMIT around each one! Additionally, while we're working in SQL here, if you were using R or Python, many database libraries also automatically emit BEGIN and COMMIT statements around queries.
5. What about selects?
Now, what about select queries? They also get wrapped. PostgreSQL tries to do everything it can to help provide safety and sanity to your queries. These are called single statement transactions.
6. Let's practice!
Let's go apply what we just learned here.