Sep 12, 2020

On the Topic of Postgres

I wanted to read up a bit more on Postgres as well, and this is what I found out.

There are a couple of important concepts, that one should be familiar with I think regarding Postgres.


ACID is not unique to Postgres, but it is this concept that makes building a database sometimes complicated.


That a database is atomic means that you expect transactions you do to be performed as one unit of work. Either the update is done in full, or it is not done at all.


When you define your database, you define a set of rules. For example you have Foreign Key Constraints, NULL constraints etc. That defines how the data must look. Consistency mean that the database can never be in a state that doesn't fulfill all these rules.


This means that when you run a series of concurrent transaction, the result must be the same as if you had run them in a sequence.


This means that if the DB says that it has persisted something, this data is now living in durable memory, like a harddrive, and not only in volatile memory like the RAM, that could die during a power failure.


This is what postgres was first with, but it has now been adopted by other databases as well. It is the technique that is used to guarantee the Isolation, while at the same time being performent as a database. In the old days, `Isolation was guaranteed by locking rows in the DB when they were read. Now it is fixed by storing multiple versions of the same row in the DB. Old rows are then removed during a vacuum.


Free space management is the technique that Postgres uses to know where it should write new data. It keeps track of which pages where there are the most available space, and uses these when writing new data.