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 TABLEandTRUNCATEqueries 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
USINGthat 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.itermonthdatesand you can usegenerate_serieswith 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