PostgreSQL for Everything

Contrary to celebrated belief - the reply to everything is NOT 42 - it’s PostgreSQL. (ok. It mightiness besides beryllium Postgres).

Intro

I started utilizing PostgreSQL astir successful 2003 for a investigation task called ColumbaDB. Columba is nary more, but PostgreSQL is still live and kicking much than ever.

In 2003, MySQL was overmuch much wide utilized than PostgreSQL. MySQL was besides perchance faster arsenic it did not instrumentality each features of the SQL standard. At the aforesaid clip MySQL was lacking galore features that we needed (full-text search, powerful indexes, SQL modular compliance etc). PostgreSQL felt much for illustration a “real” database successful comparison to MySQL - for illustration a mini type of Oracle - but successful unfastened root clothes.

During that investigation task I learned a batch astir databases, indexes and the powerfulness of PostgreSQL. One important use-case was full-text search. We could person utilized MySQL successful conjunction pinch different strategy for illustration Lucene / Solr to make our database searchable. But that would person meant moving and maintaining 2 specified systems. Complicated.

PostgreSQL allowed america to usage a fulltext hunt plugin to do everything successful 1 system. No request to sync immoderate data. No request to support and tally 2 systems. It conscionable worked and made america grin (after immoderate tweaks of course). Simplicity.

Since past I utilized PostgreSQL for galore use-cases passim my profession arsenic CTO / Interim Manager. Most precocious I utilized PostgreSQL to shop very precocious measurement web analytics clip bid information via its TimescaleDB plugin. Check retired Privatracker - the champion measurement to do web analytics and respect the privateness of your visitors - to spot it successful action.

Many others discussed the taxable from different angles. And each article is really worthy your clip (SQL is Agile, Stephan Schmidt connected Using SQL for Everything). Also cheque retired my Linkedin post.

And if you are utilizing PostgreSQL I tin highly urge reference Hazel Bachrach’s bully station connected “What I Wish Someone Told Me About Postgres”.

In my humble sentiment the powerfulness of PostgreSQL comes from 3 sources:

1. It is rock-solid and stable.
2. It is easy to run, instal and scale.
3. It massively simplifies your IT setup by being not only a RDBMS, but besides a full-text hunt engine, a archive retention and overmuch much more…

Let’s person a person look…

Rock Solid and Stable

PostgreSQL is boring aged technology. The first PostgreSQL merchandise dates backmost to 1996. PostgreSQL is besides very wide utilized - for a very agelong magnitude of time. Ironing retired bugs - particularly successful database systems - takes time. PostgreSQL had that time.

It besides has a very progressive organization that diligently adds much and much features without breaking immoderate aged parts of it. In caller years PostgreSQL sewage galore astonishing features for illustration json archive storage, partitioning support, communal array expressions and overmuch much more. Each caller merchandise of PostgreSQL is breathtaking and brings caller bully features.

True - PostgreSQL is aged - but the features are very very modern - and PostgreSQL becomes amended pinch each release.

Easy to Run, Install and Scale

PostgreSQL tin beryllium installed very easy locally. It is bundled pinch each awesome Linux distributions, portion of Mac brew, but tin besides beryllium installed pinch applications for illustration PostgresApp.

When moving tests, it comes successful useful utilizing Testcontainers pinch PostgreSQL. It was ne'er easier moving your tests against a existent PostgreSQL database that is 100% akin to the accumulation thing.

If you want to tally PostgreSQL connected a server past you tin simply apt-get instal it. Or tally it successful a docker container.

All unreality providers let you to tally (and scale!) PostgreSQL by clicking a azygous button. You sewage ample of prime astatine your fingertips:

• Amazon AWS
• Google GCP
• Microsoft Azure
• ElephantSQL
• CrunchyData
• Timescale
• … and galore much …

That makes PostgreSQL 1 of the astir wide supported package systems successful the market. And for you this intends little attraction and much clip for creating caller features for clients.

Simplifies Your IT Setup

Running PostgreSQL successful the unreality is already conscionable 1 click. But it gets moreover better. PostgreSQL tin switch galore systems that youd’d person to tally otherwise.

PostgreSQL Replaces Solr and Elastic: Full-Text Search

PostgreSQL allows you to move your matter information into user-searchable data. Without a abstracted system. It’s besides connection agnostic and you’ll ne'er person immoderate sync problems betwixt your information and your fulltext hunt system.

The astir awesome article connected the taxable is really Contentful utilized PostgreSQL to alteration fulltext hunt for their users. It’s a communicative successful simplicity that enables growth.

Instacart did thing very similar: They built their modern hunt infrastructure connected Postgres alternatively of moving a abstracted hunt cluster. Same story, different company.

More connected the topic: https://www.postgresql.org/docs/current/textsearch.html

PostgreSQL replaces MongoDB: Excellent Json Support

PostgreSQL has fantabulous support for storing and querying(!) json. It besides features an scale type (GIN) that makes these operations blazingly fast. Is location a request for MongoDB immoderate more?.

The Guardian besides wrote an fantabulous article really they switched from Mongo to PostgreSQL. Thanks for sharing Jan-Otto! Hazel besides wrote a bully portion connected jsonb and what to return into relationship erstwhile utilizing it.

PostgreSQL replaces Kafka and RabbitMQ: PostgreSQL arsenic a queue

Events, queues and persistent logs are getting much and much important successful today’s package systems. Systems for illustration Kafka, RabbitMQ, SQS and others supply that functionality. But maintaining them is annoying, civilization and you request the skillset.

The bully news: You tin conscionable usage PostgreSQL. The magic comes from

• SELECT .. FOR UPDATE
• SELECT .. SKIP LOCKED

Using these SQL features you tin efficaciously usage a array arsenic queue. Either successful a persistent manner pinch a cursor and galore consumers, aliases successful a read-once fashion.

The article astatine crunchydata explains this conception very well.

My tip: Start pinch PostgreSQL arsenic a queueing system. Only erstwhile that does nary longer execute good move to different systems for illustration Kafka, RabbitMQ aliases SQS. You’ll beryllium amazed really good PostgreSQL works.

PostgreSQL Replaces Clickhouse: High Volume Time Series Data

Time bid information is special. Often you get galore information points successful a very short magnitude of time. And past you person to aggregate the information frequently, doing immoderate statistic connected it and truthful on.

There are specialized package systems for illustration Clickhouse (amazing by the way…). But you tin besides usage a plugin for PostgreSQL that allows you to do (nearly) the same: Timescale.

I’ve utilized Timescale and tin urge it. The bully news is that you tin proceed utilizing PostgreSQL - moreover for precocious measurement information easily. No request to study and support thing new.

PostgreSQL arsenic Vector Database for AI Workflows

Timescale lately released the pgvector extension, that turns your PostgreSQL into a vector database. This allows you to usage the tech you already cognize for indexing and retrieval of applicable data. That’s an basal portion of AI LLM workflows.

Timescale besides precocious announced pgai that includes pgvector, but besides a batch of different bully extensions that make it ace elemental to scale data, telephone LLM models and retrieve information based connected similarity.

PostgreSQL Replaces Redis: Non-Persistent High Performance Caching

Caching is important. Most applications usage thing for illustration Redis arsenic a cache to get accusation for illustration sessions and much quickly. A cache tin by meaning suffer information and tin beryllium regenerated from the original source.

But. Why usage Redis erstwhile PostgreSQL tin beryllium tuned to beryllium arsenic accelerated (in astir usecases) arsenic a Redis cache? The concealed is utilizing an UNLOGGED table. You tin moreover emulate Redis’ automatic expire by a trigger. A batch has been written astir this - I tin conscionable urge trying it out.

PostgreSQL Replaces File System: For Raw Data

For 1 of my clients we had to publication and constitute a immense magnitude of mini pieces of binary encoded information. We initially thought that doing this via the record strategy was the fastest measurement to do so.

After immoderate capacity checks it became clear that PostgreSQL was moreover faster than reference from the record strategy for our use-case. PostgreSQL uses the record strategy very efficiently for its information - and it adds a batch of caching and businesslike reference and penning strategies that tin outperform penning and reference earthy information connected a record system.

We utilized Flatbuffers to shop the information successful a blob column. Data was past de-serialized connected the client. You mightiness want to effort that attack arsenic well.

PostgreSQL Replacing Your Graph Database

Hierarchical information tin beryllium managed successful SQL via recursive queries. That’s ok, but besides super-hard to read, support and debug. Not moreover speaking of performance.

The amended measurement is the LTREE datatype of PostgreSQL. It helped maine not only erstwhile to instrumentality hierarchical tag structures. Easy to read, support and blazingly fast.

PostgreSQL Replacing Your Microservice

Most of the “microservices” these days are only astir models, getting information from a database and returning json to the client.

But you cognize what? PostgreSQL tin move immoderate query into a Json result. That efficaciously replaces your server middleware. There are Pros and Cons to this approach, but it shows the capabilities of PostgreSQL. The astonishing Lukas Eder wrote astir the topic - not PostgreSQL circumstantial - but everything mentioned location is very good doable successful PostgreSQL arsenic well

PostgreSQL - Replacing your Playstation 5

Well. Some enthusiast implemented Tetris arsenic Common Table Expressions successful axenic SQL. Crazy. And possibly not to beryllium taken excessively seriously.

Conclusion

The database supra is not very exhaustive. PostgreSQL is simply a very elastic portion of software. And it tin beryllium extended pinch plugins to do much and more.

You request simplicity if you want to move fast. If you travel crossed a caller request ever ask: Can’t PostgreSQL do this? And do we really request that shiny caller exertion X?

PostgreSQL mightiness not beryllium the reply to everything - but it is the reply to a batch much than you mightiness think!