PostgreSQL Overview
No matter what you do, the chance is you need to store the data you serve to consumers, as well as to keep the data you receive from them. There’s hardly a system design that doesn’t involve a storage solution. Despite that almost every storage boils down to a file, the way you interact with the file differs dramatically between applications. In this post, we’ll consider a database, specifically PostgreSQL, that is the second most popular RDBMS solution that implements SQL on the market. There are several other popular solutions, e.g. MySQL (+ MariaDB), SQLite, or MSSQL; most of them have a similar set of basic commands due to the strictly standardized SQL but differ in types, implementation, and extra features added on top. In my experience, this additional sugar is very different and influences the decision of what database one should use.
You can run Postgres on your machine using Docker. You can also install it from sources, but I personally wouldn’t recomment doing so.
docker run --name "postgresql" -p 25432:5432 -e POSTGRES_USER=some_username -e POSTGRES_PASSWORD=some_password -e POSTGRES_DB=some_db_name -t -d postgres
Postgres is quite old but has significantly changed over the years (SQL changed). Due to widespread enterprise adoption, PostgreSQL receives enough attention to get trendy updates and extensions earlier than competitors (pgvector is a good example, a popular extension used in AI and machine learning). In my opinion, it is also quite heavy and isn’t the most performant RDBMS. The killer feature of Postgres is durability and predictability. The Write Ahead Logging (WAL) Postgres uses is different than MySQL’s WAL implementation (redo log). One of the biggest changes in PostgreSQL was the implementation of ACID (for instance, MySQL still isn’t ACID compliant).
PostgreSQL Role in Software Architecture
When it comes to PostgreSQL, Software Architecture views it as a data storage workhorse, something that isn’t supposed to respond in microseconds (like Redis) but can reliably keep an unknown amount of structured and unstructured data.