Postgres (PostgreSQL) is gaining in popularity. I see it from the customers I interact with, who last year voted Postgres as the number-one new database they wanted support for on SolarWinds’ THWACK user community, and in the DB-Engines rankings, where Postgres has been climbing steadily and is ranked number four in popularity. Most customers I talk with have added Postgres to the mix of databases they already have, but I’ve also seen organizations use Postgres as the primary database.
Now that SolarWinds has Postgres support across all our database performance management solutions, I’ve had to get familiar with this database management platform.
Postgres Platforms
While Postgresql.org has been around since 1996, its origins go back to the mid-1980s, when Michael Stonebraker, who led the Berkeley team that developed Ingres, started a new project called Postgres to build upon the ideas in Ingres.
While most PostgreSQL users rely upon the free and open source implementation, there are several variants. Here are some of the popular PostgreSQL implementations I’ve run across:
PostgreSQL
- Available at www.postgresql.org (PostgreSQL Global Development Group), which started in 1996, this open source distribution supports a broad range of operating systems, including all Linux variations, Windows, and numerous UNIX systems.
- While this is a free and open source distribution, many individuals and large companies contribute to it. The PostgreSQL Global Development Group currently supports four major versions (12.x, 11.x, 10.x, and 9.x) and also supports 160 of the 179 mandatory features for SQL:2016 Core Conformance.
EDB Postgres
- EnterpriseDB (EDB) provides an open source database management platform based on PostgreSQL and is a major contributor to open source PostgreSQL. Its solution includes tools (e.g., monitoring and integration) along with Oracle compatibility for moving Oracle applications to Postgres.
Azure Database for PostgreSQL
- Microsoft offers a PaaS or DBaaS based on the PostgreSQL engine. There are two options: single server or hyperscale cluster. This offering, similar to other DBaaSs, removes the need to manage the infrastructure supporting the database and comes with Azure benefits such as high availability, performance, and scalability.
Amazon RDS and Aurora for PostgreSQL
- Amazon RDS (Relational Database Service) is a web service that facilitates running an RDBMS in the AWS cloud. It supports PostgreSQL database engines, along with four other popular engines (Oracle, MySQL, SQL Server, and Aurora). Since Amazon RDS is a PaaS offering, it removes the tedium associated with managing database infrastructure and provides the flexibility of simply adding resources to support growth, a set of tools, and more to make it easier to implement PostgreSQL in the cloud.
- Aurora, a fully managed DBaaS offering, is PostgreSQL- (and MySQL-) compatible and offers replication flexibility over Amazon RDS. According to Amazon, Aurora can deliver up to 3x the throughput of stock PostgreSQL on similar hardware. Amazon offers a broad set of features ranging from security to auto scaling and database snapshots.
?Postgres Metrics
Generally, PostgreSQL isn’t much different from a typical DBMS where you monitor metrics such as memory, I/O, transaction information, and row activity, such as how many rows are updated or deleted, etc. Third-party monitoring products report on metrics such as waits, locks, and detailed data regarding the queries executed against the databases. There are, however, three interesting metrics I’ve found that Postgres users deem important to monitoring the health of a PostgreSQL instance:
Vacuum: When tuples (rows) are deleted or modified in PostgreSQL, vacuum goes in and removes obsolete tuples. The pg_stat_progress_vacuum view provides information about current vacuuming processes.
Checkpoints: PostgreSQL temporarily stores blocks in shared buffers and then writes to a WAL file (Write Ahead Log), which reduces the number of writes to disk. Monitoring this data is important because client back-end activity could be impacted, for instance, if the WAL size has reached the threshold max (max_wal_size).
Cache Evictions: The PostgreSQL shared buffer cache stores change information before it’s written to disk for performance. There are three ways the blocks in the buffer (referred to as dirty blocks) are finally written to disk: the user back-end process, the writer process, and the checkpointer process.
The Future for PostgreSQL
Since both Amazon and Microsoft have put their resources behind PostgreSQL, unsurprisingly, both technology companies and traditional organizations are implementing applications on Postgres. The number of tools supporting PostgreSQL will continue to grow, and those tools will fill in the gaps for the features not natively available in the database.