PostgreSQL for Everything - Dr. Raphael A. Bauer, MBA | Fractional & Interim CTO | PE/VC Tech Strategy & Due Diligence | Berlin | London | Austin, TexasHome<br>Interim CTO<br>Services<br>Blog<br>About<br>🇩🇪
Home<br>Interim CTO<br>Services<br>Blog<br>About<br>🇩🇪
Menu
PostgreSQL for Everything.
Contrary to popular belief - the answer to everything is NOT 42 - it’s PostgreSQL. (ok. It might also be Postgres).<br>Table Of Contents
Intro<br>I started using PostgreSQL roughly in 2003 for a research project called ColumbaDB. Columba is no more, but PostgreSQL is still alive and kicking more than ever.<br>In 2003, MySQL was much more widely used than PostgreSQL. MySQL was also potentially faster as it did not implement all features of the SQL standard. At the same time MySQL was lacking many features that we needed (full-text search, powerful indexes, SQL standard compliance etc). PostgreSQL felt more like a “real” database in comparison to MySQL - like a tiny version of Oracle - but in open source clothes.<br>During that research project I learned a lot about databases, indexes and the power of PostgreSQL.<br>One important use-case was full-text search. We could have used MySQL in conjunction with another system like Lucene / Solr to make our database searchable. But that would have meant running and maintaining two such systems. Complicated.<br>PostgreSQL allowed us to use a fulltext search plugin to do everything in one system. No need to sync any data. No need to maintain and run two systems. It just worked and made us smile (after some tweaks of course). Simplicity.<br>Since then I used PostgreSQL for many use-cases throughout my career as CTO / Interim Manager. Most recently I used PostgreSQL to store very high volume web analytics time series data via its TimescaleDB plugin. Check out Privatracker - the best way to do web analytics and respect the privacy of your visitors - to see it in action.<br>Many others discussed the topic from different angles. And each article is really worth your time (SQL is Agile, Stephan Schmidt on Using SQL for Everything). Also check out my Linkedin post.<br>And if you are using PostgreSQL I can highly recommend reading Hazel Bachrach’s nice post on “What I Wish Someone Told Me About Postgres”.<br>In my humble opinion the power of PostgreSQL comes from three sources:<br>It is rock-solid and stable.<br>It is easy to run, install and scale.<br>It massively simplifies your IT setup by being not only a RDBMS, but also a full-text search engine, a document storage and much much more…<br>Let’s have a closer look…<br>Rock Solid and Stable<br>PostgreSQL is boring old technology. The first PostgreSQL release dates back to 1996. PostgreSQL is also very widely used - for a very long amount of time. Ironing out bugs - especially in database systems - takes time. PostgreSQL had that time.<br>It also has a very active community that diligently adds more and more features without breaking any old parts of it. In recent years PostgreSQL got many amazing features like json document storage, partitioning support, common table expressions and much much more. Each new release of PostgreSQL is exciting and brings new nice features.<br>True - PostgreSQL is old - but the features are very very modern - and PostgreSQL becomes better with every release.<br>Easy to Run, Install and Scale<br>PostgreSQL can be installed very easily locally. It is bundled with all major Linux distributions, part of Mac brew, but can also be installed with applications like PostgresApp.<br>When running tests, it comes in handy using Testcontainers with PostgreSQL. It was never easier running your tests against a real PostgreSQL database that is 100% similar to the production thing.<br>If you want to run PostgreSQL on a server then you can simply apt-get install it. Or run it in a docker container.<br>All cloud providers allow you to run (and scale!) PostgreSQL by clicking a single button. You got ample of choice at your fingertips:<br>Amazon AWS<br>Google GCP<br>Microsoft Azure<br>ElephantSQL<br>CrunchyData<br>Timescale<br>… and many more …<br>That makes PostgreSQL one of the most widely supported software systems in the market. And for you this means less maintenance and more time for creating new features for clients.<br>Simplifies Your IT Setup<br>Running PostgreSQL in the cloud is already just one click. But it gets even better. PostgreSQL can replace many systems that youd’d have to run otherwise.<br>PostgreSQL Replaces Solr and Elastic: Full-Text Search<br>PostgreSQL allows you to turn your text data into user-searchable data. Without a separate system. It’s also language agnostic and you’ll never have any sync problems between your data and your fulltext search system.<br>The most impressive article on the topic is how Contentful used PostgreSQL to enable fulltext search for their users. It’s a tale in simplicity that enables growth.<br>Instacart did something very similar: They built their modern search infrastructure on Postgres instead of running a separate...