Category: Learn Postgres

Backup & Restoration – Postgres supports two types of backups – logical and physical. Logical backup copies table by table as if any client performing series of SELECT statements. It is a reliable and simple method that works across major versions. However it takes more time and puts additional cpu and memory strain on the…

I completed the chapter about logging and auditing. Main takeaways are: – Postgres logs information about it’s actions. Quantity, frequency, destinaiton and content of these logs is highly configurable. – There are two types of logging: syslog for logging to third party systems. This method can drop logs under heavy traffic. logging collector it’s the…

I finished the query tuning chapter. Toady I read about query optimization and auto explain . Main takeaways are:– Usually query optimization boils down to leveraging an appropriate index.– Avoid subqueries since they will likely cause redundant looping.– Keep an eye on unused indexes (pg_stat_user_indexes) since they take up space and effort when inserting new…

I completed the section about indexes and started reading about EXPLAIN and ANALYZE statements. Several recent chapter were theory heavy and required a lot of note taking – this slowed down my progress. I will probably skip notes taking in next chapters – Audit Logging and Configuration … or at least restrain notes to crucial…

Toady finished the part about parallel workers. I could not spend the usual amount of time on the book because of the trip to the office. I hope to cover more tomorrow when I will start the section about indexes.

The “query tuning” chapter is great. I already learned a lot about Postgres planner and optimizer, execution stages (parsing, rewriting, optimization and execution), cost-based optimization and sequential nodes – sequential scan, index nodes and nested loops. Tomorrow I will start with parallel nodes!

I finished my notes about VACUUM and therefore the “transactions” chapter itself. Also, I browsed over the “extensions” chapter. I was interesting to learn about the pgxn and pgxs tools for extensions management. However the rest of this chapter won’t be very useful to me. But I am happy to move to the next one…

I wrote down notes about WAL and checkpoints. Tomorrow I hope to complete notes about VACUUM and move on to next chapters – “Extensions”.

I am still writing down notes on “Transactions” chapter.

Wrapped up sections about WAL and checkpoints. Tomorrow I will finish VACUUM section and I will be ready to write a proper summary about this chapter.