PG Phriday: JSON and JSONB Revisited

With Postgres 9.5 on the horizon, I figured it’s a good time to see how things have advanced since my [intlink id='pg-phriday-functions-and-addressing-json-data']last dive[/intlink] into that particular ocean. This is probably particularly relevant since even MongoDB, a JSON-driven NoSQL database, is now partially powered by Postgres. A lot of people found that particular revelation quite shocking, but maybe they shouldn’t, given the advancements embedded within the last couple of Postgres releases.

PG Phriday: Postgres-XL and Horizontal Scaling

With all of the upheaval in the Postgres world thanks to advancements in extensions, foreign data wrappers, and background workers, it’s getting pretty difficult to keep track of everything! One of these rapidly moving targets is Postgres-XL and its role in helping Postgres scale outward. Large warehouses have a critical need for horizontal scaling, as the very laws of physics make it effectively impossible to perform aggregate queries on tables consisting of several billion rows. Even furiously indexed, partitioned, and clustered tables, on over-provisioned servers loaded with the best NVRAM-based storage, 64 CPUS, and 1TB of RAM, become lumbering beasts when someone wants a cumulative summary of last year’s activity.

PG Phriday: Displaced Durability

A lot of DBAs are quite adamant regarding ACID compliance. I count myself among them. But unlike the other parts of the acronym, there are times when data durability isn’t actually a high priority. Data staging holding areas, temporary tables that need visibility across sessions, and other transient information do not require zealous protection. As a DBA it feels weird saying it, but there’s just some data we simply don’t care about losing.

PG Phriday: Cluster Control

It has occurred to me that I may have been spending a bit too much time being excited about new Postgres features and developments in the community. One of the intents of this weekly article was for educational purposes, so this week, let’s get back to basics. To that end, the topic for this week boils down to the tools available for managing Postgres instances, and how to use them. Surprisingly, it’s not as straight-forward as you might think.