tl;dr for various ressource I read, heard or saw
Architectures Postgresql by Dimitri Fontaine
A quick reminder of how and why PostgreSQL follow ACID definition (Atomic, Consistent, Isolated and Durable), Atomic because everything can be done inside a transaction and rollback, Consistent because relations follow a strict schema with constraint, isolation can be provided at diverse levels and durable by ack after storing on disk
If you are not able to automatically restore backup they become useless (here is an article about how they handle it at Leboncoin
Don’t forget that when replicating operations you also replicate the DROP TABLE and TRUNCATE queries so hot swapping Standy for Primary when it crashes is not always possible.
There are lots of really great examples comparing client-side code in python VS advanced SQL queries most of which are from the book “Mastering PostgreSQL”
You have the possibility to alter individual transaction property in PostgreSQL, for example, you may want to add harsher constraints on financial transactions that are over a certain sum using a triggers
One abstruse feature of PostgreSQL is LATERAL JOIN
When doing an alter on a table column you can modify the actual content with the command USING that allow to apply a function (like replace, substring, etc), also you can combine multiple alters in the same query
Never forgot that PostgreSQL comes with powerful calendar operations to compare and manipulate dates like cal.itermonthdates and you can use generate_series with them
PostgreSQL also implements windows functions that you can see in action here
Next, you have some tooling for dev with python AnoSQL to define SQL function in .sql files, RegreSQL for testing regression in SQL queries, Geolocation with ip4r
Really great insight of the responsibility of a DBA “we are responsible to provide a service AND ensure data integrity” and concrete use case for some lesser known features in PostgreSQL