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: Alien Incursion

Foreign tables have been a headline feature of Postgres ever since the release of version 9.2. Combined with extensions, they’re the secret sauce that allows Postgres to pull data from other database engines, flat files, REST interfaces, and possibly every gas station, residence, warehouse, farmhouse, hen house, outhouse, and doghouse in the area.

Postgres all the things

PG Phriday: Trusty Table Tiers

I always advocate breaking up large Postgres tables for a few reasons. Beyond query performance concerns, maintaining one monolithic structure is always more time consuming and consequentially more dangerous. The time required to create a dozen small indexes may be slightly longer than a single larger one, but we can treat the smaller indexes as incremental. If we want to rebuild, add more indexes, or fix any corruption, why advocate an all-or-nothing proposition? Deleting from one large table will be positively glacial compared to simply dropping an entire expired partition. The list just goes on and on.

PG Phriday: COPY and Alternative Import Methods

I recently noted that the COPY command in Postgres doesn’t have syntax to skip columns in source data when importing it into a table. This necessitates using one or more junk columns to capture data we’ll just be throwing away. During that, I completely forgot that friendly devs had contributed alternative file handling methods as Foreign Data Wrappers. Most people think of foreign wrappers as a method for interacting with remote databases. Perusing the full list however, reveals some surprising data sources. Twitter? Hive? Video cards?!