The last line of defense for those OOPS moments — so even DBAs can sleep peacefully. Have you ever accidentally run a DELETE without a WHERE clause? Or typed DROP TABLE thinking you were in staging, only to realize a half-second too late that you were in prod? If yes — keep reading, this is for you. If no — keep reading anyway. Nobody who uses Postgres long enough stays immune.

The analytics databases optimized for queries. The real leverage was always where data gets written. Your application writes to Postgres. So does almost everyone else’s. Postgres is the most widely used database in the world, topping Stack Overflow’s developer survey two years running. It didn’t win as a data warehouse. It won as the place applications write. For years, the data infrastructure companies competed on what happens after — faster queries, better compression, smarter optimizers.

I was writing an article for Infoq on the topic less spoken costs of managed databases and one question that the editor asked is how much margin RDS makes compared to running a Postgres instance on EC2? That intrigued me and honestly, I never did the math so far. I have been using AWS-managed databases since 2016 and I thought what’s a better time to do an analysis of Cloud cost than today ?

I was trying to play with Phil’s pgtam and the first step is that to install Postgres version 16. Sounds fairly innocous. When I ran configure, I was getting the below error: checking for icu-uc icu-i18n... no configure: error: ICU library not found If you have ICU already installed, see config.log for details on the failure. It is possible the compiler isn't looking in the proper directory. Use --without-icu to disable ICU support.

Abstract On a high level, the talk addressed following questions: What is Edge and why are the benefits of edge? What are the challenges of implementing Postgres at the edge? How we implemented it at Cloudflare? Where Postgres at the edge is heading? Slides from the presentation Link from Developerweek Cloudx Link from Developerweek Cloudx virtual

Abstract We talked about the importance of having a good toolset, of practicing incidents and of internally advocating database best practices to a large engineering organization. Recordings of the presentation

Abstract With the advent of cloud/managed offerings, running Postgres on their own hardware is becoming rare. It’s great that a lot of the Ops work is taken care of by the provider, however, understanding a layer or two beneath these abstractions will be useful for anyone in strengthening their knowledge. For eg. When you can’t connect to an instance how do you find where the problem is? a. Are you able to reach the server i.

Abstract This talk explores how Cloudflare uses HAProxy for health checks, load balancing and reading traffic among nodes set up with Postgres streaming replication in hot standby mode. Cloudflare operates multiple Postgres Clusters across four data centers, and all of these clusters are made up of six nodes. During a primary failure, Cloudflare’s high availability system promotes a replica to become a primary, and HAProxy makes sure there is no write traffic between two primaries to avoid a split-brain scenario.

I kept hearing about the term wire protocol especially Postgres wire protocol in the recent days (Looking at you cockroachdb, yugabytedb - in a good way) but never really quite understood it. Decided to implement something simple in Go to understand it better. As always, if you find anything wrong or I misunderstood please correct me. In simple terms, “wire” - something over network generally (but PG also supports over domain sockets)

I use pg_rewind for rewind a Postgres cluster which is ahead of the primary. Yesterday, after the rewind Postgres didn’t start. It failed with the below error. This is using 9.6 version pg_rewind client. Sep 2 20:06:14.371503 productiondb[1]: [1-1] time=2022-09-02 20:06:14.338 GMT,pid=78197,user=admin,db=postgres,client=[local],appname=[unknown],vid=,xid=0 FATAL: the database system is starting up Sep 2 20:06:14.371611 productiondb[1]: [1-1] time=2022-09-02 20:06:14.370 GMT,pid=78184,user=,db=,client=,appname=,vid=,xid=0 LOG: entering standby mode Sep 2 20:06:14.371991 productiondb[1]: [1-1] time=2022-09-02 20:06:14.371 GMT,pid=78184,user=,db=,client=,appname=,vid=,xid=0 PANIC: could not open file "pg_replslot/ae0750b1/state": No such file or directory Sep 2 20:06:14.

Citus: Distributed PostgreSQL for Data-Intensive Applications paper can be downloaded here. Recently, our team got a request to provide a solution to shard Postgres. One of the solutions that we discussed was Citus. I have heard about the product and seen their blogs related to Postgres in the past but never used it. I thought it would be fun to read about its internal workings. If you find something wrong on the notes, please send a pull request.

Capturing thoughts from https://twitter.com/viggy28/status/1530800893842444289 When you are doing major DML changes, other than locks one more thing to keep in mind is replication lag. Especially if you use your replicas in hot standby mode. When you need to delete most of the records in a massive table, its better to create a new table and just copy the records that you need to preserve. When you need to delete all the records in a massive table, just truncate it instead of deleting them.

Before I forget let me write this down here. $$ log_stament - none - all log_min_duration_statment - millisecond value When you set log_statement=none and log_min_duration_statement=1 then any statement which takes longer than 1 millisecond will be logged. When you set log_statement=all and log_min_duration_statement=1 then all statements are logged; however it only shows duration on statements longer than 1 millisecond.

From the wiki, In computing, data storage, and data transmission, character encoding is used to represent a repertoire of characters by some kind of encoding system that assigns a number to each character for digital representation. Okay, they mean converting character to some number for storing and transmitting data. There are two popular character sets 1. ASCII 1. American Standard Code for Information Interchange 2. Pretty much all the characters and symbols in modern keyboard comes under ASCII 3.

I have know about the postgres wire protocol, but first I ran into streaming replication protocol. I was looking at this code in stolon replConnParams["replication"] = "1" db, err := sql.Open("postgres", replConnParams.ConnString()) if err != nil { return nil, err } defer db.Close() rows, err := query(ctx, db, "IDENTIFY_SYSTEM") if err != nil { return nil, err } I was wondering what is this IDENTIFY_SYSTEM. A G-search pointed me to this

Libpq-Postgres-Go After I started working on Postgres, I have heard this term libpq enough times, but never had a good grasp of it. After digging around this topic for a couple of days, here is my understanding. From the Postgres doc https://www.postgresql.org/docs/9.5/libpq.html, libpq is the C application programmer’s interface to PostgreSQL. libpq is a set of library functions that allow client programs to pass queries to the PostgreSQL backend server and to receive the results of these queries

Understanding context in Golang through Postgres I was trying to learn Go package context especially with respect to Postgres. On a very high level context provides context to the operation. Yeah, I agree, the previous statement doesn’t really add much value, but hold on I don’t really know how to explain it, rather let’s go over some code. Sometimes its easier to understand by seeing it in action :)

Step 1: Setting up HAProxy I hope you gone through Part1 of this series. Perhaps, one thing you might have noticed is that I’ve to connect to the specific master database. In our case, since both the databases are running on docker, only the localhost port is different. (In a production environment, most likely you going to run the databases on a different host). The main reason for active-active replication is high availability.

Postgres doesn’t support active-active replication natively. As of this writing, we have to rely on 3rd party tools. I decided to go with BDR. I didn’t want to spin up multiple VMs. So, the obvious choice is docker. Make sure you have docker on mac & docker compose. Step1: Running 2 Postgres instances using docker container Thanks to jgiannuzzi, who created a docker image with Postgres and BDR. docker-compose.yml file content