Postgres and Open Source Experts

24x7x365 since 1997

Blog

Performance Analysis of PostgreSQL Data Checksums

Recently I have been working on PostgreSQL benchmarks for its data checksums feature. This incredibly valuable option to initdb -- introduced in version 9.3 in 2013 -- allows quick detection of corrupted disk data pages. It provides the glorious opportunity to simply failover to a standby before your data becomes corrupted, rather than endure the horror of discovering the corruption afterward and attempting to recover.

But people care as much …

Keeping the Fire Alive: Maintaining Client Relationships From a DBA Perspective

In my seven years at Command Prompt I've helped many clients, often several of them at the same time. After a project is completed, I know that it was a job well done when that client calls on Command Prompt months and maybe years later for additional help. Often, they call for me by name, creating long lasting and mutually beneficial relationships, which are key to Command Prompt's success. How …

Connection Initiation Overhead is Killing Your Web App - Use a Connection Pool

Customers often ask us what is the correct setting for max_connections on their PostgreSQL cluster?  There is a short answer to this question, and there is a very, very long answer. The short answer is: accept the default if that works for you, or try 10 times the number of CPU cores and see if that works ok.

There is another short answer which is: you may be asking the …

Copy Files Through an SSH Wormhole!

Here is a fun and useful Linux console hack you might like.

If you use ssh A LOT, maybe you encounter situations where you have a shell that is three or more hops removed your local workstation. And maybe you've been in the situation where there is some file that you need to get from "there" to "here" or vice versa. And you really are annoyed by the thought of …

PostgreSQL Non-exclusive Base Backups in Bash

Here I'm posting a bash script that implements PostgreSQL's new (since 9.6) "non-exclusive" base backup.

I often find that new customers are shy about binary PostgreSQL backups and only schedule logical pg_dump backups. PostgreSQL provides a brilliantly simple binary backup solution that enables easy point in time recovery, unlike pg_dump backups. So I always try to steer people towards these binary "base backups", as they are called in the PostgreSQL …

Postgres Load Testing with pgreplay

Overview

Usually we want to test before deploying changes to a production Postgresql cluster. Commonly the test itself is executed in a context that is not very similar to the production environment. How can you run a test that is realistic so there are no horrible surprises when you deploy on the production system?  Read on! This document describes a procedure where changes can be tested on a system that …

Simpycity 2.0.0 released (An ORM in Python)

What Simpycity Is

Simpycity is an object-relational mapper. It seamlessly maps PostgreSQL query
and function result sets to Python classes and class attributes.

It allows for the easy and rapid development of query- and
stored procedure-based data representations. Simpycity leverages PostgreSQL's
powerful composite type system, and the advanced type handling of the psycopg2
database access library.

What Simpycity is Not

Simpycity is not a SQL generator and does not attempt …