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:
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?
There is sometimes confusion when it comes to tools for migrating from Oracle to PostgreSQL. There are tools for migrating business data from an Oracle database to a PostgreSQL database, and there are also tools for technically migrating / translating the definitions...
September 14, 2023, time flies and for the third time this year we can announce we have the new minor releases available of PostgresPURE based on the new minor releases of PostgreSQL 11 through 15 made available by the PostgreSQL community. What have we done for you?...
Introducing the New Release of Cortex and Future Developments The previous version of Cortex (2023.1) took longer than originally planned, but we had good reasons for that. This new version (2023.2) is released as planned, although our clients again gave us a lot of…