PostgreSQL for Everything

By Bauer, MBA | Fractional & Interim CTO | PE/VC Tech Strategy & Due Diligence 📍 Berlin | London | Austin, Texas

---

Contrary to the famous claim in The Hitchhiker's Guide to the Galaxy, the answer to the ultimate question of life, the universe, and everything is 42 PostgreSQL.

Introduction

My journey with PostgreSQL began around 2003 during a research initiative known as ColumbaDB. While ColumbaDB is no longer active, PostgreSQL has not only survived but has thrived, becoming more relevant than ever.

Back then, MySQL was often perceived as the faster option because it bypassed several complexities of the SQL standard. However, MySQL lacked critical capabilities we required, such as:

• Robust full-text search
• Advanced indexing options
• Strict adherence to SQL standards

In contrast, PostgreSQL felt like a professional-grade database—essentially a compact, open-source version of Oracle. Through that project, I gained a deep appreciation for the architecture of indexes and the sheer versatility of the platform.

The "Single System" Advantage

We could have paired MySQL with a separate system like Solr or Lucene to handle search functionality. However, that would have introduced the overhead of managing two distinct environments. By choosing PostgreSQL, we utilized a full-text search plugin to consolidate everything into one engine. After a few configuration tweaks, it worked seamlessly.

"The goal is to reduce architectural complexity. Why run five systems when one can do the job of all five?"

Since then, in my roles as an Interim Manager and CTO, I have applied PostgreSQL to countless scenarios. Most recently, I leveraged the TimescaleDB plugin to manage massive volumes of time-series data for web analytics. You can see this in action at Privatracker, a privacy-first analytics tool.

For those looking to dive deeper, I recommend:

1. SQL is Agile
2. Stephan Schmidt’s insights on Using SQL for Everything
3. Hazel Bachrach’s guide: What I Wish Someone Told Me About Postgres

---

The Three Pillars of PostgreSQL's Power

In my professional opinion, the strength of PostgreSQL stems from three primary areas:

1. Rock-Solid Stability

PostgreSQL is "boring" technology in the best way possible. Because it has been widely adopted for decades, the majority of critical bugs have been identified and resolved.

The community is exceptionally disciplined, adding modern features without compromising backward compatibility. Recent additions include:

• JSON document storage
• Table partitioning
• Common Table Expressions (CTEs)

While the core is old, the feature set is cutting-edge.

2. Seamless Deployment and Scaling

Getting PostgreSQL up and running is trivial. Whether you are a developer or a sysadmin, the options are endless:

This ubiquity means less time spent on "plumbing" and more time building features for clients.

3. Drastic Simplification of the IT Stack

PostgreSQL isn't just a Relational Database Management System (RDBMS); it is a multi-tool that can replace several specialized pieces of software.

🛠️ Replacing Solr and Elasticsearch (Full-Text Search)

PostgreSQL transforms raw text into searchable data. Because the search is integrated, you eliminate the "sync lag" between your primary database and your search index.

• Case Study: Contentful used Postgres to enable full-text search.
• Case Study: Instacart built their search infrastructure on Postgres rather than maintaining a separate cluster.
• Documentation: PostgreSQL Text Search

🛠️ Replacing MongoDB (JSON Support)

With its superior support for jsonb and GIN (Generalized Inverted Index), PostgreSQL handles document storage with incredible speed.

• Example: The Guardian successfully migrated from MongoDB to PostgreSQL.

🛠️ Replacing Kafka and RabbitMQ (Queues)

Modern systems rely heavily on event logs and queues. While Kafka and SQS are powerful, they require specialized skillsets to maintain. You can use a standard SQL table as a queue by utilizing the SKIP LOCKED feature.

-- Example of consuming a task from a queue
BEGIN;
SELECT * FROM task_queue
WHERE status = 'pending'
ORDER BY created_at
FOR UPDATE SKIP LOCKED
LIMIT 1;
-- Process task and then COMMIT;

🛠️ Replacing Clickhouse (Time Series)

Time series data often involves high-velocity ingestion and frequent aggregation. While Clickhouse is excellent, the Timescale plugin allows PostgreSQL to handle these workloads efficiently.

🛠️ AI and Vector Workflows

The ecosystem continues to expand, with pgvector now allowing PostgreSQL to function as a vector database for AI applications.

---

Summary Architecture

The following diagram illustrates how PostgreSQL centralizes the data layer:

Final Considerations

When calculating the cost of infrastructure, consider the "Complexity Tax" ($\text{CT}$): $\text{CT} = \sum (\text{Maintenance} + \text{Sync Latency} + \text{Specialized Hiring})$

By consolidating your stack into PostgreSQL, you minimize $\text{CT}$ and maximize velocity.

Migration Checklist:

• Evaluate if current NoSQL needs can be met by jsonb.
• Test SKIP LOCKED for low-to-medium volume queuing.
• Explore pgvector for AI integration.
• Assess if a separate search cluster is truly necessary.