Postgres has been moving up the popular database chats and taking center stage for an enterprise storage solution. It has all the bells and whistles with some pretty cool datatypes that developers have fallen in love with. We are going to dive in and look at Postgres, compare it to another popular database SQL Server, and go over some of the features that stand out. By the end of this, you should have a better understanding of the capabilities Postgres has to offer.
A little history
Postgres was developed at the University of California (Berkeley) and has been around for over two decades. This object-relational database system was first implemented in 1986 who was led by Professor Michael Stonebraker and sponsored by the Defense Advanced Research Projects Agency (DARPA).
Postges95 was renamed in 1994 and claimed to be 30-40% faster and included a new front-end library. Another note on this release introduced the GROUP BY query clause was added as well as the replacement of PostQUEL by SQL.
PostgreSQL found its new long-lasting name in 1996. The name was chosen to reflect the relationship between the original and newer version that has SQL capability. Also said to be the worst decision the company has ever made. The named caused confusion on how to pronounce it. We will refer to it as just Postgres.
What is Postgres?
Postgres is an advanced open source object-relational database management system that was designed to run on Unix. However, over the years other platforms have been introduced like Solaris, Mac OS, and Windows. Postgres also has availability in Docker.
Like any other piece of software, it has many different versions. At the time of this article, Postgres 11 is the current version and the one we are basing this article off of.
Why use it now?
This open-source database has many options for the developer and has come a long way with performance. You should always want to pick the right tool for the job and not pick something because your friend or team member told you it was cool. I would say some of the traction with Postgres is just that, It’s cool.
However, if you are looking for a hybrid database that supports document style like mongo and a relational-like SQL Server or Oracle, then this may be a great option for you. If you want a system that is scalable, performance-centric, cloud-ready (AWS) and has table inheritance, Postgres is for you. Maybe you are just tired of figuring out the licensing model of SQL Server and looking for something that is comparable and fits the budget.
Postgres community has helped shape a lot of features and continues to drive innovation with community support and discussions from all over the world.
Now don’t get me wrong, SQL Server has many features that have helped enterprise organizations drive their business for many years with interesting disaster recovery options. Let’s take a look at the default versions of Postgres and SQL Server.
Postgres has Multi-Version Concurrency Control (MVCC) which is one of the top selling points. The reads will never block writes and writes will never block reads. MVCC is not just in Postgres, this technology is found in other databases like Oracle, CouchDB, Berkeley DB, and many others. This highly concurrent system is a simple solution to a very difficult problem.
SQL Server has pessimistic locking that has different types of locks for each type of action. For the most part, this type of locking works well for most objectives.
Concurrent indexing, compression and partitioning all ship out of the box with Postgres. SQL Server has these features as well, but they come at an upgrade cost on top of the price of the standard version.
Full-Text Search also comes out of the box with Postgres. Simple to set up and works well. SQL Server has this ability as well just not in Azure. So those of you using this feature in SQL Server and looking to go to the cloud will have to run a managed instance or VM.
Some of the datatypes that developers are used to working with will find Postgres to be a pleasing transition. One of the main datatypes that stands out from the rest is the JSON support. SQL Server 2019 now has the ability to work with JSON. Longtime coming for this one. The following illustration will display a few Postgres datatypes to SQL Server. These datatypes are familiar with developers.
Are you sure Postgres is free?
Yes, Postgres is free. No tricky license agreements, graphs for versions by core or seat like SQL Server. Just plain free.
How do I get Postgres?
You can go to https://www.postgresql.org/download/ and get the version that fits your needs. They have all different flavors to select from.
You also have the option of using Docker and pulling an image of Postgres. If you chose to do this, no installation of Postgres, just mounting the image in Docker.
If you wanted something easy, you could go the AWS route. AWS has full support for Postgres. In fact, you can standup Postgres in a few minutes, with just a few clicks. (This does require an AWS subscription and you will pay for what you use.)
Tools for Postgres
Pgadmin4 (current at the time of this article) ships with Postgres and has support for the interface. Coming from SQL Server, you may find it to be a little overwhelming, not very intuitive and just an odd layout. Once you get in there and start learning why features are laid out how they are, it makes sense.
Other paid tools
Navicat ($$) – One of the best looking, I think. The layout is intuitive and well thought out.
DataGrip ($$) – Has that VS/VSCode feel to it for those.Net developers.
First off, I would like to say that my “go to” database is SQL Server and has been for many years. I have based my current career around this technology and to this day still, love what it can do. With that said, I find Postgres a great option for many different objectives and performs well under load. Postgres has all the need DR objectives built in and will meet most compliance regulations. Maybe the UI is not as clean as SQL Server, but the power for the price makes up this little annoyance.
The more I use Postgres, the more I enjoy the features. I have not purchased any of the third-party tools above but can see how they would enhance the experience of the first-time and experienced user.
I am hooked and can’t wait to see what other features will ship in future releases. Thumbs up for this one.