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. The traditional model relied on storage gravity: move your data into our walls, pay us to store and query it. Once data lived inside a warehouse, the switching cost kept it there.

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.

Obviously, one solution is to disable ICU support as recommended by the error message. However, that’s an anti-feature as per the Postgres documentation.

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.e. Is ICMP even working?

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.371991 productiondb[1]: [1-1] time=2022-09-02 20:06:14.371 GMT,pid=78182,user=,db=,client=,appname=,vid=,xid=0 LOG:  startup process (PID 78184) was terminated by signal 6: Aborted

Of course, the /state file didn’t exist inside the replication slot sub-directory ae0750b1.

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. Before digging their white paper, let’s take a step back and ask what is sharding and why do we need sharding?

Capturing thoughts from https://twitter.com/viggy28/status/1530800893842444289

1. 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.

2. 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. Total of 128 characters
   4. Decimal value between 65 and 90 represents captial alphabets and 97 to 122 represents small alphabets
2. UTF-8
   1. ASCII doesn’t cover characters from other languages.
   2. UTF-8 is capable of encoding all 1,112,064 valid character code points in Unicode using one to four one-byte (8-bit) code units.
   3. It has backward compatible with ASCII

I run into character set in postgres. There is client_encoding and server_encoding setting.

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

visi@viggy28-MacBook-Pro ~ % psql "dbname=postgres user=postgres replication=database password=replaceme" -c "IDENTIFY_SYSTEM;"
      systemid       | timeline |  xlogpos  |  dbname
---------------------+----------+-----------+----------
 6852440194718025990 |        1 | 0/1542DB8 | postgres

visi@visis-MacBook-Pro ~ % psql "dbname=postgres user=postgres replication=database password=replaceme" -c "TIMELINE_HISTORY 1;"
ERROR:  could not open file "pg_xlog/00000001.history": No such file or directory
(1 row)

I learnt you can directly postgres using other than SQL by sending messages to the backend (server) through the wire protocol !!

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 :)

The complete program is here

Step 1: Setting up HAProxy

I hope you gone through [Part1] (https://viggy28.dev/article/postgres-active-active-replication/) 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. If one of the nodes goes down, you still want your application to behave normally. You don’t want to hard-code your DSN on the application or keep checking the health of the database every time before you make a connection. Fair enough. [HAProxy] (<www.haproxy.org>), an open source project solves this particular change.

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] (https://www.2ndquadrant.com/en/resources/postgres-bdr-2ndquadrant/).

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] (https://github.com/jgiannuzzi), who created a docker [image] (https://hub.docker.com/r/jgiannuzzi/postgres-bdr) with Postgres and BDR.