According to the StackOverflow survey, PostgreSQL is the most loved database in 2022. What makes it so special? I mean it is just another SQL database, right? let’s find out
Well first, let's clear out that PostgreSQL is ACID-compliant which is the primary requirement of relational databases and it supports foreign keys, stored procedures, joins, and views too just like MySQL
When we think of relational databases, MySQL is the first thing that comes to mind. When we google MySQL vs PostgreSQL, it spits out “MySQL is a purely relational database, whereas PostgreSQL is an object-relational database.”
Object Relational Database?
Objects, Classes and Inheritance are directly supported in database schemas and in the query language. In addition, just as with pure relational systems, it supports extension of the data model with custom data types and methods. This is what makes PostgreSQL so useful, it helps in solving several use cases that would have otherwise required us to learn new database systems like
OLTP (Online Transaction Processing):
Traditional CRUD (Create-Read-Update-Delete) operations.
OLAP (Online Analytical Processing):
OLAP is used by data engineers to analyze and get insights into data.
Since PostgreSQL is based on HTAP (Hybrid transactional/analytical processing) architecture, it can handle OLAP pretty well.
FDW (Foreign Data Wrapper):
We can create or use FDW to connect to different databases like Redis, MySQL, Neo4j and perform data flow operations.
Streaming:
PipelineDB extended from PostgreSQL is a time series database that can be used for real time reporting and analytics applications.
Geospatial:
PostGIS is a spatial database extender for PostgreSQL. It adds support for geographic objects, allowing location queries to be run in SQL.
Time Series:
Timescale extended from PostgreSQL for time series and analytics. It is used to combine relentless streams of financial and tick data with other business data to build new apps and uncover unique insights.
Distributed Tables:
Distributed Tables are tables whose rows are spanned across multiple tables to speed up read and write operations. This is done using a consistent hashing technique. CitusData extended from PostgreSQL gives this feature out of the box