Why PostgreSQL is my favourite database

Hello from me

I’ve been working with database systems for over 20 years, including Oracle, Sybase and MySQL – but for me, PostgreSQL is the best of them all.

So what makes PostgreSQL such a great database system? In all honesty, having used it for over a decade, I could fill many pages with my answer. But I’ll try to run you through the key points that really stand out for me.

PostgreSQL is very well crafted

PostgreSQL started out as a project from University of California Berkeley, and I think this is why it’s always been given such attention to detail and quality. It strives to be the best it can be with a solid core. It’s also very slim and trim. What I find so remarkable is that after many years of expanding on their features, PostgreSQL still feels very nimble and light.

PostgreSQL is really easy to use

Once PostgreSQL is installed – you’re ready to go. There’s no need to configure storage systems or define extents. Just plug in storage and start creating your database. There’s also a lot less maintenance overheads than other database systems – which makes the whole thing more efficient and nice to use.

For me, the command line tool is very powerful. One of my favourite features here is the built-in syntax help. Do you need to know how to add an index or alter a table? There’s no need to run to a browser to check the documentation, it’s all right there at your fingertips. Easy.

PostgreSQL is always growing

PostgreSQL releases a new version every year with quarterly updates. Each major release has new optimizations and additions, which means that PostgreSQL is always moving forward, always evolving, adapting and improving.  And because there’s no commercial entity producing PostgreSQL, all the features are available in the database system, with no hidden costs or no fear of a feature audit.

PostgreSQL is easily expandable

PostgreSQL has its core system that’s always evolving – this is the PostgreSQL base. Beyond that it allows additional features to be added to the core system using the extension interface. The extension system allows these features to be seamlessly added into the database server, enhancing PostgreSQL while keeping them separate. This pluggable design allows functionality to be added as needed so you can easily, and quickly, adapt it to fit your needs.

PostgreSQL has advanced features

PostgreSQL supports databases in the Terabyte and Petabyte range. But it doesn’t stop there. It has declarative partitioning, it supports parallel operations and it also allows streaming replication both synchronously and asynchronously with hot or warm standbys. And let’s not forget that it also provides logical replication.

But there’s more. PostgreSQL features Foreign Data Wrappers (FDW) – something I really like. A FDW allows access to data that’s outside of the database. It means you can connect to external sources such as files, other database systems and even systems like Docker or Twitter, the list goes on and on. All this data can be accessed from within PostgreSQL using a standard table interface. Amazing, right?

Other little things

There are some other great little gems that hide within PostgreSQL, like the cast operator :: I really like this one. This can be used to easily change data types in queries.  Let’s say you have a timestamp and you just want to use the date portion as a string, well it’s as easy as:

timestamp_column::date::text

You can read this as, the timestamp is cast to a date (the time portion is removed) which is then cast to a string in one simple operation. Of course, there’s so much more besides this little gem, but this isn’t the place to dive into them all.

So to sum it up

PostgreSQL is a sleek, powerful, elegant, expandable database system, always growing but never losing that feeling of being light on its feet. It manages many tasks itself, tasks that would require monitoring in any other database system. Of course, you still need knowledge of the system, but with many configuration options available, you now have time to explore them as you don’t have to worry about if you have enough extents for your table.

What are other people reading?