PostgreSQL – Why You’d Want to Use It
If you’ve been in this industry for a few years, then you probably know what SQL is. It’s the golden standard for working with databases and nearly every modern coding language interfaces with it. Though simple it’s very flexible and different variants of it allow for some more robust functionality like Oracle SQL and MySQL. PGSQL, or PostgreSQL, is another one of these variants, but it takes things one step further and even one step after that.
Let’s start with what exactly PGSQL is and is not. PGSQL is a variant of SQL and uses most of the same syntax. It’s open source and has been in development for decades now, if you’ve been around the block then you may even know it as Ingres. It can do all the same things that traditional SQL databases can do, following the same rules and structure. It is not a reinvention of the wheel, more so a different take on it. It is not specifically NoSQL, though it can be used for it, and it doesn’t require a brand-new set of fundamental knowledge if you’re already familiar with SQL. Like all programming languages PGSQL is a tool and it has advantages that lend itself to particular applications.
This first advantage will be weighed more heavily based on your personal views, but PGSQL is entirely open source. You’ll never have to worry about over-deployment and the software is totally free with no license required. If you’re in need of wide-scale development then this becomes a much bigger deal, as no power is sacrificed in sparing costs. This has also led to PGSQL developing a wide and active following in recent years. If at any point you were to look for guidance or some sort of extension for a specific task, chances are there will be several people out there ready to give you their solution.
The ability to freely add extensions makes PGSQL highly scalable. As the source code is completely open for you to change you can mold PGSQL to work however you need it to. Thanks to the wide community of PGSQL users it’s also cross-platform, available on Windows and nearly every brand of Unix. If you make a PGSQL database on one system, you’ll be able to transfer it to another with no worries of incompatibility. Like PL/SQL you also have access to stored procedures, allowing you to make your own custom functions. Inheritance is another neat feature, allowing you to copy columns from one table to another but have that column be treated as its own address. Any data changed for that column in one table will automatically change the data in the same way for all inherited copies of that column in all other tables. Managing facts and dimensions becomes a whole lot easier.
An example of some of the available Extensions; https://help.compose.com/docs/postgresql-extensions
PGSQL is remarkably stable, even under constant high usage. It’s specifically designed to work in these environments thanks to its MVCC (multi-version concurrency control) and extensive indexing features. Not only do you have access to the standard B+ Tree indexing, you also get GiST, SP-Gist, KNN Gist, GIN, BRIN, Covering indexes, and Bloom filters. These indexes can be used with any data type in PGSQL, of which it supports many. Perhaps the more interesting ones are JSON, geometric shapes like Circles and Polygons, XML, UUIDs, and even an option to make your own Composite and Custom datatypes. Combine this with full search capabilities and you can wade through extremely complex databases with relative ease.
For all these great upsides though there are of course downsides. First and foremost is speed. PGSQL is not the fastest horse in the SQL race, considerably so when compared to MySQL. It is slower at handling massed queries than Oracle SQL. Then there’s the negative aspect of being open source; reliability. If a new version of PGSQL comes out and you’d like to switch to it, there’s no guarantee your customizations will survive the update.
The most pressing downside to many, and what will likely make or break it for you, is memory performance. PGSQL is extremely resource intensive, especially when allocating for client connections. Every time a client connects PGSQL forks a new process and allocates 10MB of memory. Add this onto the maintenance of some very advanced indexing and a PGSQL database can become an absolute hog quickly. If you’re going to use PGSQL for a large enterprise scale database be prepared to dedicate a machine or two just to be a server for it. Because of these memory demands the cost for PGSQL can go from zero to expensive, whether you use your company’s own machine or are allocated one through a service like Amazon. Finally the wide community that PGSQL possesses is still rather small, minuscule compared to Oracle and MySQL. It’s simply not all that popular, though it has been steadily on the rise for years. While it’s not so barren that you won’t find a swath of active communities for it, it is uncommon enough that finding a PLSQL database administrator may prove difficult. Likewise, the UX options for PLSQL are very limited and have stability issues presenting a learning curve to anyone that you’d be looking to train up.
If you have the power for it though PGSQL can be extremely useful when making a database for something that has a lot of unique pieces moving with it. The ability to store and index JSON, XML, or any object you want cannot be overlooked. While it may not be lightweight or especially fast, few other services can offer the scalability and freedom offered by PostgreSQL. Just don’t be too surprised if you start maxing out your server’s processor after a few months of growth; when this happens try partitioning or add more power if possible.
