PostgreSQL is a Hot Database Choice yet Again

It seems the venerable PostgreSQL database is garnering a new wave of buzz across the IT industry. Maybe our series of articles on Postgres earlier this year helped contribute to this newfound hipness? Probably not, but modern tech organizations hoping for a cheaper alternative to Oracle while still getting support for NoSQL consider it to be a worthy option.

Let’s look more closely at some of the reasons why PostgreSQL remains one of the hottest databases – relational or not – on the market. Perhaps it makes sense for your team’s next data-centric web or desktop application?

Postgres is actually growing in Popularity!?

PostgreSQL’s increase in popularity caught the attention of InfoWorld magazine, who recently talked about the database’s hot factor earlier this month. In fact, Postgres now ranks as the 4th most popular database in the industry, according to a study by DB-Engines. Not surprisingly, the only three DBs ranked higher are Oracle, MySQL, and SQL Server.

The reasons for the growing popularity of Postgres – especially with younger developers – are numerous. InfoWorld’s writer, Matt Asay notes the improved performance brought by the support for JSON included in PostgreSQL 9.2 and boosted in version 9.4. Another important reason involves programmers growing tired of trying to fit even hipper NoSQL options like MongoDB into solution where a relational database makes the most sense.

Ultimately, in a situation when an old-school DB works best, PostgreSQL’s open source nature is simply more cost effective than Oracle or SQL Server. In fact, Postgres first earned its mojo as a cheaper alternative to Oracle. Still, could this old school database scale fast enough for use in modern web applications?

PostgreSQL and its newfound Scalability

The ubiquitous nature of social networks like Facebook and Twitter puts the onus on modern web applications to be extremely scalable. Most RDBMS options generally provide poor scalability, as did Postgres for most of its existence. Asay notes the introduction of Citus, an extension for PostgreSQL, provides a level of scalability rivaling many of the popular NoSQL databases.

Citus supports Postgres instances across multiple nodes, while providing a distributed model for transactions and SQL queries. These features give this veteran relational database the parallelism required for a massively scalable application able to compete in today’s market place. Take that, Cassandra.

While Citus is available as an open source extension, the company that developed it also offers a commercial version with full support. This is a similar model that EnterpriseDB followed with PostgreSQL itself. Citus provides a great option for shops working with Postgres for development and them implementing Citus for extra scalability before going live.

It also lets companies take advantage of their in-house talent’s database skills without spending on training in the latest NoSQL database options. These bonuses are arguably behind the still growing popularity of PostgreSQL.  Elijah Zupancic‏, the Director of Solutions Engineering for Joyent comments on some of the other core reasons.

“From a developer perspective, it is a pleasure to use. The documentation is wonderful, the data types reflect the types developers work with, and there is little surprising.”

Keep coming back to the Betica Blog for additional insights on software development, testing, and occasionally, databases.

PgBouncer and Pgpool – Essential for Scaling PostgreSQL

Companies looking at PostgreSQL as a production database alternative to Oracle typically want their web applications to scale quickly as those expensive commercial alternatives. The competitive modern business landscape – most notably the fickle online customer — demands high performance, and will look elsewhere when encountering a slow eCommerce site. Ensuring high scalability when using Postgres is a must!

Thankfully, the robust open source community around PostgreSQL developed two tools aimed to boosting the database’s performance on high traffic websites. Called PgBouncer and Pgpool, they need to be considered as part of any Postgres implementation. Let’s take a closer look.

A Lightweight Connection Pooler for PostgreSQL

PgBouncer serves as a connection pooler for Postgres. It is a lightweight tool known for a small footprint with minimal overhead. The program simply caches connections to different database servers. Its ability to manage a pool of database connections limits the number of actual connections to each Postgres instance; boosting the overall performance during a high traffic scenario.

Focusing specifically on connection pooling is one of the reasons PgBouncer is able to keep its small footprint. As such, it is not usable for other vital web database functionality, such as load balancing or replication. Pgpool is an option for handling those functions in addition to connection pooling.

Postgres Load Balancing, Replication, and more

Pgpool – now in its second major iteration, so Pgpool-II – is more of a Swiss Army knife of Postgres middleware functionality. In addition to connection pooling, it also performs load balancing, parallel query processing, replication, and failover handling – vital features for any scalable web application database. All client access to a PostgreSQL instance (or multiple instances) is managed through the Pgpool middleware.

Replication and load balancing are essential for any web application receiving high volumes of traffic. Load balancing lets Pgpool-II seamlessly distribute SELECT statements across multiple replicated database servers; improving overall system throughput as a result. As middleware, it mimics the API of Postgres, so a client application connects with Pgpool-II in a similar manner as any PostgreSQL server.

PgBouncer or Pgpool – what Option works Best?

Many system architects and/or database administrators probably wonder whether PgBouncer or Pgpool makes the most sense in their database application. Maybe using both is the best solution? The following database administration blog looks at this question while providing an overview of both tools.

PgBouncer by itself is the wisest choice if connection pooling is the only major need. Maybe a separate load balancer is already in place? This rule also applies to applications hosted in resource-constrained environments where quick database calls with low throughput happen frequently.

Pgpool naturally makes sense if you need its additional features, like replication and load balancing, combined with connection pooling functionality. Both tools work well together in applications where many database connections are expected and PgBouncer’s lower overhead in regards to pooling helps the overall performance.

Whether choosing Pgpool, PgBouncer, or a combination of the two, there’s no denying both tools remain valuable options for any high traffic PostgreSQL application.

Keep returning to the Betica Blog for additional information, advice, and insights from the rich world of software development. Thanks for reading!

Barman and repmgr – Essential Tools for PostgreSQL

If your company’s software engineers are veterans with PostgreSQL, chances are pretty good they are also familiar with the utilities, Barman and repmgr. Barman handles the management of the backup and recovery process for a Postgres instance. While repmgr, as hinted at by its name, performs a similar role with replication – in fact repmgr also offers a measure of integration with Barman.

Let’s take a closer look at both tools to see if they make sense as part of your PostgreSQL implementation. If your team is considering Postgres as a cheaper alternative to Oracle, perhaps this additional information helps make your decision easier. Good luck!

Barman – the PostgreSQL Choice for Disaster Recovery

Developed by the well-known purveyor of Postgres support, training, and development, 2ndQuadrant, Barman is a worthy open source option for organizations needing a tool to handle backups and restores for PostgreSQL. It also plays an important role in any company’s disaster recovery process. Barman helps ensure databases are back online as quickly as possible – a vital factor in achieving business continuity.

In fact, Barman focuses its functionality on disaster recovery scenarios. It supports the remote and hot backups of multiple database servers, while helping DBAs or other network personnel get everything up and running again. The tool also provides remote management capabilities for multiple servers, including ssh support.

Other features include the local storage of metadata, PITR (Point-In-Time-Recovery), file compression, retention policies, incremental backups, tar integration, and more. In short, Barman is a fully functional backup and recovery solution for Postgres. Since it is written in Python, companies with developers skilled in that language can make modifications as needed.

Version 2.1 of Barman was released earlier this year. 2ndQuadrant also provides documentation as well as commercial support and consulting options. As an open source software product, a robust online community is available for advice on usage. Any company using PostgreSQL needs to explore Barman as an option for database backup and disaster recovery.

Manage PostgreSQL Replication with repmgr

Another open source Postgres utility developed by 2ndQuadrant, repmgr handles database replication across multiple PostgreSQL servers. The latest version of repmgr – 3.3.1 – was released in March of 2017, supporting Postgres versions 9.3 and later. It leverages streaming replication and the PostgreSQL 9 Hot Standby feature to ensure superior performance in high scalability and availability environments as well as ease of administration.

One of the unsurprising features of repmgr, considering the developer, is its seamless integration with Barman. You are able to make clones from a Barman archive, instead of accessing a live server. This helps prevent a performance hit on a production server.  If live streaming replication gets interrupted, an archive can be easily used in a pinch.

As with Barman, 2ndQuadrant also provides commercial-level support and consulting options with repmgr. When used together, both tools make it easier for companies to build an industry-leading relational database solution at a fraction of the cost of going with Oracle. It is yet another example of the benefits of open source software.

Stay tuned to the Betica Blog for additional news and insights from the wide world of software development. As always, thanks for reading!