Recently on the pgsql-performance mailing list, a question popped up regarding Postgres RAM usage. In this instance Pietro wondered why Postgres wasn’t using more RAM, and why his process was taking so long. There were a few insightful replies, and they’re each interesting for reasons that aren’t immediately obvious. Let’s see what is really going on here, and perhaps answer a question while we’re at it.
PG Phriday: CONFLICT of Interests
MySQL has had a REPLACE INTO syntax to perform “UPSERT” logic since practically the very beginning. For the longest time, users who wanted to switch to Postgres, but for whatever reason relied on this functionality, were essentially trapped. Postgres 9.5 changed all that, but why did it take so long? As with much of Postgres history, it’s a long story.
PG Phriday: RESTing in the Corn
Last week we explored using Postgres as a [intlink id='pg-phriday-stuck-in-the-middle-with-postgres']central communication nexus[/intlink] between several data sources. At the time, I made a fairly hand-wavy allusion to REST interfaces. Since I hadn’t really explored further, I had assumed PLV8 could use core node.js or other similar libraries to invoke HTTP APIs. Of course as an untrusted language, PLV8 isn’t allowed to do that. It’s more of a language for easily manipulating JSON and JSONB objects within Postgres.
[caption id="attachment_1407" align="aligncenter" width="600"]
Only slightly less violent than JSON[/caption]
PG Phriday: Stuck in the Middle with Postgres
Earlier this year, I implied Postgres was some kind of [intlink id='pg-phriday-alien-incursion']super middleware[/intlink] for dragging data out of every external resource it could locate. But that example only used the Postgres foreign data wrapper to contact another Postgres server. Why be so unimaginative? The future is as unlimited as it is terrifying.
[caption id="attachment_1401" align="aligncenter" width="600"]
Meet the new Postgres mascot[/caption]
PG Phriday: Through the Window
Now that we know how Postgres [intlink id='pg-phriday-in-the-window']window functions work[/intlink], why not play with them a bit to get a better understanding of their capabilities? So long as we understand window functions are applied after data gathering and aggregation steps, much of their mystery and complexity is defanged. Let’s start actually using them for stuff!
[caption id="attachment_1396" align="aligncenter" width="480"]
Captain Murphy is tired of your nonsense[/caption]