Skip to main content

Why you should use postgres as a primary database?

· 3 min read
Poonai

image of an elephant

As a university student, I was more familiar with NodeJS andmongoose than SQL, so I went with MongoDB, instead of SQL. When I started working in the real world, I became familiar with postgres. Postgres has always been my go-to database for my projects since then ❤️.

here is my mental model, why I choose Postgres for all my projects?

  • tested and proven
  • third party extension
  • JSON for other needs
  • tooling around around postgres
  • community

tested and proven

For more than two decades, Postgres has been developed and is now being used by many large corporations. It's a basic human instinct to stick with tried-and-true methods. Take a look at the screenshot below to see how an instagram engineer flexing his postgres usage.

postgres at instagram

extension ecosystem

Postgres allows developers to extends it's capabilites by writing an extensions, since some unique usecase can't be solved by general purpose database.

I used pg_cron extenstion to solve my unique usecase myself

I wanted to do historic aggregation of a numeric column. Usual scenario would be building a ETL pipeline, but I found a solution using pg_cron. You can check this link to know the entire story.

Fellow OSS engineers have opensourced their extensions for the community to use. Here are some of my favourite extensions:

  • zomboDB integrates elastic search with Postgres for full text search.
  • pg_cron cron jobs inside postgres
  • pg_storm accelerate analytics query performance by offloading analytics computation to GPU

you can always write your own extension, if you don't find extenstion for your usecase. Now you can write extension in rust as well using pgx

JSON for other needs

Usual question that comes while choosing Postgres is that, can we store complex relationship?. But unknow fact to most of the developers is that, postgres let developers to store and query JSON data.

postgres json tweet

Tooling around Postgres

Having a good database alone won't solve the problem, there are other scenarios that we need to consider. For eg: backup, runnnig an HA setup. Postgres have all sort of tooling to run a production database.

  • patroni - running a HA postgres on k8s
  • kubesdb - running postgres on k8s
  • dexter - automatic indexer to optimize db query performance
  • timescale - turn your postgres into timeseries database
  • supabase - instant graphql api from postgres databases

Community

postgres community is very welcoming and have precense in all the popular social communities:

Ofc, you can join our community as well to talk about postgres :P

Not only does the community have a presence on various social media platforms, but it is also friendly and helps you instantly if you come across any issue.

Closing Notes

Postgres isn't just a database; it's an entire ecosystem of development, research, and innovation that's impossible to fathom. I want to end the essay by saying

Postgres doing its justification for its elephant mascot