Fuzzy.cz

From time to time someone on the mailing list asks if there's a way to limit number of connections for a given IP address, database or a user. No, currently there's nothing like that built into the core,. although it'd be pretty useful feature, especially on clusters shared by multiple clients (applications, customers, ...). Sometimes such quotas can be reasonably implemented using a connection pooler like pgbouncer or things like netfilter connlimit, but both solutions have disadvantages.

But hey! We do have a little thingies called "extensions", "hooks" and "shared libraries." Last week I hacked up a simple "proof of concept" extension that uses "client auth hook" and allows you to limit number of connections by various criteria. It's not perfect (more on that later), but it works quite fine. It's available at github and once I fix those issues I'm aware of I'll publish it on pgxn.

PS: Thanks to Magnus for pointing out the existing "client auth hook" might be useful to implement this, and TL for pointing out how unbelievably ugly kluge the original version of the extension was.

We all know there are fundamental differences between relational and object model - called "object-relation impedance mismatch." If we are going to build relational object model on top of relational database (which is natural if using object-oriented language), we have to solve that difference somehow, and ORM tools are one of the options. But are the often mentioned benefits real?

If you have ever engaged in performance tuning in one of the newer Oracle versions, maybe you have met the legendary statspack (see Oracle Statspack Survival Guide), or its successor Automatic Workload Repository (AWR), whose main principle lies in creating "snapshots" of important internal values (number of queries, transactions, disk reads, cache reads, etc) and consequent analysis of differences between snapshots. It is not widely known that there is a similar tool for PostgreSQL called pgstatspack, which references to Statspack even by its name - it is based on the very same principle (analysis of snapshot differences), and although it is more simple, it may provide  invaluable services when tuning performance.

Are you using relational databases but the indexes are closed book for you? You absolutely don't know what the indexes are and how they work, or you are confused by various types of indexes (B-Tree, Hash, Bitmap) and you're not sure about their advantages or disadvantages? Or you just can't find out why a particular index is not used when evaluating a query? Maybe this article will help you to answer these questions ...

This article contains brief description of database structure used for the PGMon project - tables, their structure, etc.

Goal of this article is to describe the database structure used for OpenStats project in a nutshell (but with enough details). It contains description of individual tables and their columns, but an explanation of some seemingly illogical solutions.

Imagine you are working on a web application that requires storing of files (e.g. a photo gallery). And there comes a question "Should I store the files in a database, or directly on the filesystem?" Maybe you've asked this question on a discussion forum / mailing list and I guess the answer you've received was something like "It's much more efficient to store files outside the database." Well, I'll try to explain why I prefer storing files in a database. This article is based on a discussion I've had with James Lewis, after I posted a question to pg-php mailing list.