PG Phriday: Constipated Connections

We’ve all had it happen. One day, we run a query or function and we wait for the result. And we wait. And we wait. Eventually, we realize something is wrong and find a DBA and yell at them.

“Hey Postgres dude!” we scream. “The database is slow!”

Or maybe we can’t even get a connection. Postgres just keeps saying something about too many clients. This application isn’t launching and there are ten managers breathing down our neck and we panic.

“Hey Postgres dude!” we cry. “The database is down or something!”

Is it, though?

PG Phriday: The Audacity of NoSQL

The pure, unadulterated, presumptuous impudence of NoSQL. Engines like MongoDB recklessly discard concepts like ACID in some futile quest to achieve “web scale”, and end up accomplishing neither. RDBMS systems have literally decades of history to draw upon, and have long since conquered the pitfalls NoSQL platforms are just now encountering. There may be something to a couple of them, but by and large, they’re nothing we really need.

At least, that’s something I might have said a couple of weeks ago.

PG Phriday: A Postgres Perspective on MongoDB

I’ve been almost exclusively a Postgres DBA for a seemingly interminable length of time. While this is good for specializing, nobody wants to be a One-Trick Pony. And aside from the occasional bit of Python to write more advanced tools when Bash isn’t up to the job, it’s All Postgres All The Time. While few things would make me happier, it pays to branch out occasionally.

PG Phriday: All in a Name

Naming objects in a database can sometimes be an exercise in frustration. What kind of guidelines should we follow for schemas and tables? What about columns or variables? Should the same rules apply to indexes, constraints, and sequences? Functions and triggers are much different than all of those elements, yet still exist within the same namespace. Then we have to contend with Postgres reserved words, many of which are probably only obvious to someone well versed in database lingo.

PG Phriday: EXCEPTIONal Performance

Like any programming language, the PL/pgSQL Postgres procedural language has plenty of handy control structures. Among those thankfully modern accoutrements is the humble EXCEPTION block. While not the more prevalent try/catch methodology, we can use BEGIN anywhere to start an embedded section for the same effect.

Knowing this is the case, what are the costs of actually using them? Postgres is fairly streamlined, and as such, can take several shortcuts when there are no exception blocks within a chunk of PL/pgSQL. Thus, it’s not uncommon for members of mailing lists to mutter about ‘performance costs’ and suggest that the worst performance hit is felt by even breathing the word ‘exception’ in the presence of a Postgres instance. The implication is that further leverage will be incremental by comparison.