How PostgreSQL is transforming the database landscape for good

How PostgreSQL is transforming the database landscape for good

With the fast-paced evolution in the technology, there has been an increase in the urge of going online among the enterprises. This strong urge of having online operations have also led to the need for a strong database, so as to keep the operations seamless without any unwanted disruptions.

Enterprises have been always looking for a robust database which could help them in managing their data efficiently. One such database that has been transforming the evolving database landscape for good is PostgreSQL.

Among the big names in the SQL databases, PostgreSQL is emerging with a promising future scope. It’s one of the most Oracle compatible databases. More and more enterprise are moving from Oracle to PostgreSQL.

And not just Oracle, PostgreSQL is replacing many paid databases in the cloud. With each new version, it’s adding more and more features.

Before we discuss how PostgreSQL has been emerging to transform the database landscape, and why, let us first get into the basic details of what PostgreSQL is and what makes it different from others in the database league.

What is PostgreSQL?

PostgreSQL is an open source object-based relational database management system (ORDBMS), which has been developed at the University of California at Berkeley. It has been actively developed since 1986 and is compatible with all major operating systems.

It provides a reliable and robust architecture along with modern features, data integrity, and extensibility. PostgreSQL is developed by the community for the community as an open source software where new innovations are constantly implemented over the period of time.

It not only conforms with the latest SQL standard, covering most of the SQL mandatory features but also offers other additional features, which makes it more sought after as a database among enterprises.

However, being open source doesn’t mean that it’s not well supported. EnterpriseDB offers excellent support on both cloud and on-premise installations. Other cloud vendors like AWS, GCP, Azure etc also support PostgreSQL dedicatedly on their respective cloud platforms.

How is PostgreSQL different from other SQL databases?

One of the foremost things which makes PostgreSQL different from other SQL databases is that it is an open source database. Apart from that, there are many additional features which makes PostgreSQL much sought after as a database.

Here are some of the features of PostgreSQL:

Runs on all major Operating Systems

PostgreSQL runs on all major Operating Systems such as Linux, UNIX, AIX, BSD, HP-UX, Mac OS X, Solaris, and Windows. With compatibility with almost all the major operating systems, it is easy for both developers and enterprise to make quick decisions as per their needs and budget.

Supports different types of Data

From standard text types to range and to JSON types, PostgreSQL supports many different data types. Lots of software vendors are actually moving away from MongoDB to PostgreSQL because of it’s excellent document storage capabilities like indexing, fast aggregates etc.

PostgreSQL supports key-value storage in the form of hstore data type. All in all, it can be used in many use-cases and not just as a relational database

Low maintenance cost

Due to the high stability of PostgreSQL, it not only requires lesser maintenance but also the cost of maintenance decreases over with time. DBAs do not have to baby-sit their database systems anymore.

PostgreSQL supports replication and write-ahead-logging, which means that the data integrity is ensured at all times.

Moreover, it is free to use (being an open source database) which also saves up the licence costs as compared to other databases, helping the enterprise in the Total Cost of Ownership (TCO) of their overall solution.

Supports Stored Procedure

PostgreSQL 11 now supports stored procedures, making it more compatible with Oracle database systems. It makes it very easy in Oracle to PostgreSQL migration, as the new procedure functionality can be a significant time saver. It also enables Transaction control- allowing the developers to COMMIT and ROLLBACK inside procedures.

Implements MVCC

PostgreSQL implemented MVCC (Multi Version Concurrency Control) even before other databases such as Oracle adopted it. With MVCC, it ensures that the data could be accessed in different versions concurrently especially when the read and write take place simultaneously.

It also ensures that until the entire changes are not made by the writer the readers see a previous version, and only once the entire changes are made (transactions are committed) they are able to see the updated data.

It is extensible

PostgreSQL provides extensibility, i.e. the user has the flexibility to define their own data types, index types, functional languages when they find any part of the system not as per their requirements. This extensibility offers the freedom to develop a custom plugin to make enhances and customize the database as per their requirements.

Not just PL/PgSQL, PostgreSQL supports PL/V8JS which means that you can write functions in JavaScript! It also supports PL/Python and PL/Perl among other languages.

Active Support

As PostgreSQL is developed by the community and is an open source platform, active extensive support from the community is readily available to solve the queries. Even the best of professional database experts offer their consultation on PostgreSQL. This makes it even more likeable as a database for an enterprise.

As mentioned earlier, EnterpriseDB offers excellent paid support and they are also very active in maintaining the database source code too. Apart from all these additional features PostgreSQL also offers the advanced features such as user-defined object types, table inheritance, sophisticated locking mechanism, roles, row leve security, materialized views, nested transactions, asynchronous replication, tablespaces, point-in-time recovery etc. which are there in the paid versions of other databases. We’ve also covered some of the most awesome PostgreSQL features previously.

How is PostgreSQL transforming the database landscape?

PostgreSQL is among the top 5 databases in the world as per DB-Engines. DB-Engines is the go-to authority when it comes to database rankings. They also awarded PostgreSQL as the Database of the year for 2018.

We also believe that because of features like JSON storage, PostgreSQL is not just going to be the best RDBMS but also a great Document database and a great key-value storage too.

Among the plethora of notable users of PostgreSQL are Reddit, Skype, Instagram, Tripadvisor, Met Office, Sony Online, BASF, Grofers and many more. These users are using PostgreSQL at an unprecedented scale.

Not just these, many more are moving to PostgreSQL for their database requirements because their existing database systems were either not scalable or not feature-proof enough.

Moreover, you can connect other database systems like Redis, MongoDB, SQLite etc using the excellent Foreign Data Wrapper functionality present in PostgreSQL. This will allow you to query other data source right from Postgres just like they are a table in it!

Some examples of companies moving to PostgreSQL-

  1. The UK weather service, the Met Service migrated to PostgreSQL from Oracle few years back. This helped them in reducing the TCO and maintenance costs in the long run. Because of its compatibility with the Oracle database, the transition was not very tough to complete.
  2. The Guardian, UK’s top news website, moved away from MongoDB to PostgreSQL few months back and they haven’t looked back since. They also covered the migration in an in-depth blog post.
  3. Shippable also moved their core database from MongoDB to PostgreSQL. They were facing multiple issues with MongoDB including a four-hour restart cycle. They were also facing trivial issues like DB lock-down when making indexes. All these issues prompted them to switch to PostgreSQL as it makes for an excellent document database too. Read more about it here.

Closing Notes

PostgreSQL, being an open source database platform offers the best of the features and an active continuous development with extensive support from the community. After building almost all of our projects (>40) on PostgreSQL in the last 4 years, we have realized that projects like PostgreSQL is a blessing for everyone including developers, vendors and the customers.

In the past, we tried other systems like MySQL but after finding that there are some issues open since last 13 years (we didn’t believe it too), we decided to move back to PostgreSQL. We’ve also migrated 15 of our customers from MongoDB/MySQL to PostgreSQL and they’ve never been so happy.

After introducing so many features in PostgreSQL 11, the community is nowhere close to done. They are already working to build more features and making PostgreSQL more robust and scalable database.

Looking to get an awesome PostgreSQL team? Look no further. We’ve been working with PostgreSQL since last 4 years and have built highly scalable apps with it. Get in touch at hello@citrusleaf.in or click here.