« Older Posts

PG Phriday: Broken Parts

October 21st, 2016 | Published in Database, Tech Talk | No Comments

Partitioning tables in Postgres can be an extremely risky endeavor. Unfortunately on many larger systems, it’s also essentially a requirement; the maximum size of a Postgres table is 32TB. This isn’t just because converting an existing table to a series of partitions is expensive or time consuming. We must consider how the query planner will react to the partitioned version of a table. There’s also the very real risk we will (or already have) implement flaws in the trigger or constraint logic.

Tools like pg_partman or pg_pathman are great for new systems, but existing deployments require retrofitting. So what happens if we have a large warehouse that’s already using a slightly flawed partition engine? And what other concerns remain even after achieving a pristine setup?

Tags: , , , , , ,

PG Phriday: Perfectly Logical

October 14th, 2016 | Published in Database, Tech Talk | 5 Comments

One of the things Postgres has been “missing” for a while is logical replication based on activity replay. Until fairly recently, in order to replicate single tables from one database to another, we had to encumber the table with performance-robbing triggers coupled to a third party daemon to manage transport. Those days might finally be behind us thanks to pglogical.

Tags: , , , , , ,

PG Phriday: Pesky Partition Plans

October 7th, 2016 | Published in Database, Tech Talk | No Comments

For all of those warehouse queries that never seem to complete before the heat death of the universe, there’s often a faster version. Sometimes this is due to a fundamental misunderstanding of how queries work, or how Postgres specifically functions. The trick is knowing when to back away slowly from an ugly but efficient query, and when to inject a flurry of predicates to fully illustrate the original intent of the query so the planner makes better decisions. When partitions are involved, this can be quite an adventure.

Tags: , , ,

PG Phriday: Postgres 9.6 Pluses

September 30th, 2016 | Published in Database, Tech Talk | 1 Comment

Timing can often be extremely fortuitous. Yesterday marked the official release of Postgres 9.6!

[caption id="attachment_1286" align="aligncenter" width="600"]Yaaaaayy... Yaaaaayy…[/caption]
Tags: , , ,

PG Phriday: Bodacious Benchmarks

September 23rd, 2016 | Published in Database, Tech Talk | 4 Comments

When it comes to putting Postgres through its paces, we often turn to benchmarks to absolutely bury it under a torrent of oppressive activity. It’s a great way to obtain maximum performance metrics and also observe how Postgres reacts and breaks down under such pressure. But these kinds of tests aren’t really practical, are they? After all, many such simulated workloads are nothing but bragging rights measured against previous Postgres releases, or for hardware comparisons. But while functionality beyond defaults is often overlooked, tools like pgbench are actually critical to the development process.

Tags: , , , ,

« Older Posts