/amazing-postgresql

A collection of info, queries, resources and ideas on PostgreSQL

Primary LanguagePLpgSQLMIT LicenseMIT

Amazing PostgreSQL

This repository is a personal documentation project of my jurney with PostgreSQL.

As I progress in this jurney, I will do my best to provide well defined use cases where PostgreSQL can make a difference in your engineering experience.

Tag along in this journey and you will learn how a single tool can offer advanced functionalities and almost replace the need for advanced systems like Redis, RabbitMQ or Kafka.

Table of Contents

👉 In this page:

👉 Projects and Tutorials:

Why is PostgreSQL so cool?

Modern apps require a variety of data pattenrs. Even among small sized projects, it is common to witness normalized and denormalized data structures, cache layers, event emitters and reactive programming styles.

Surely, many different tools has been created over the yers as "the best option for X". Resulting in a multi-technology hell in which engineers are demanded to learn way too many things, mastering none of them.

PostgreSQL is like McGyver's Swiss knife.
It can do many different things, and it does them pretty well too.

My personal points are:

  • PostgreSQL combines SQL and NoSQL into one single tool. You can handle tables AND documents.
  • PostgreSQL comes with tons of extensions. GIS, UUID, and even Cron jobs!
  • SELECT ... FOR UPDATE ... SKIP LOCKED� is a magic spell that makes it possible to implement highly concurrent tasks on a single db instance. I made Fetchq thanks to this feature.
  • PostgreSQL embeds a pub/sub broker, making stuff like Redis or RabbitMQ slightly redundant.
  • PostgreSQL offers table routing rules to store different data into different disks. Note that a single EBS disk can go up to 16Tb of data, with multiple disks you can potentially store in PostgreSQL more than 30Tb without clustering it.
  • PostgreSQL offers table partitioning rules. This makes it possible to achive linear data ingestion performances even as data grows huge.

Here are a few articles that I find inspiring:

One Tool to Handle 'em All

PostgreSQL is a safe bet for any new project.
With it you can replace:

  • Redis: because it embeds a pub/sub broker.
  • RabbitMQ: because you can handle queues of billions of tasks that are given to a great number of parallel clients.
  • Kafka: because you can store massive amount of events into partitioned tables, handling each client's cursor in it.
  • ElasticSearch: because you can run full-text searches and index any field into a schema-less JSON data structure.

You can focus in learning PostgreSQL deeply, instead of spreading your attention span over multiple tools!

PostgreSQL is also free as in "free of speech" and "free beer". No commercial licencing. You use it.

PostgreSQL is also embarassingly cheap to run. I've been running a massiva data scraping project on a ridiculous budget of ~~160 dollars per month by running PostgreSQL on an AWS EC2 machine via Docker.

A small-to-medium project will easily run Postgres + the application services on a single EC2 using stuff like CapRover for under 30 dollars / month!

Prerequisites for Running the Examples

I run all my stuff on a MacOS while actively developing on it. I will possibly test a few things on Ubuntu20+ on AWS but there is no guarantee for any of the examples/projects to really work outside my machine 😅.

I will make a serious effort to avoid any local dependency by wrapping almost everything with Docker. It works well most of the time.

Along this journey, I'll be using the following technologies:

  • PostgreSQL: the database
  • PSql: Postgres' procedural language
  • PgTap: A Unit Test framework for Postgres
  • Docker: I will run almost everything as a container
  • Make: I will document every CLI command into Makefiles
  • NodeJS: A few demo applications to play with Postgres
  • Typescript: The emerging typed language for Javascript

Licence

You can freely use the resources in this repo.

I would seriously appreciate if you mention me, my work and my website in case you find it useful and/or plan to use and/or redistribute my work.