PGCon2014 - 20140704

PGCon 2014
The PostgreSQL Conference

Jan Urbański
Day Talks - Day 2 - Fri May 23 - 2014-05-23
Room Monpetit 202
Start time 13:00
Duration 00:45
ID 673
Event type Lecture
Track Hacking
Language used for presentation English

Postgres on the wire

A look at the PostgreSQL wire protocol

While it usually Just Works, sometimes it's useful to know exactly what happens between hitting return in psql and seeing results appear on the screen.

This talk will explain how the PostgreSQL frontend/backend protocol works. We will look at the low-level blocks from which the protocol is built and try to give the audience a solid understanding of how bytes are transferred back and forth between their applications and the Postgres server.

Even if you never directly deal with the PostgreSQL protocol, every application you're writing or maintaining uses it and it's still useful to know the basics of how it works. Sometimes, knowing the fundamentals can also help you understand some behaviour that otherwise would seems mysterious or quirky.

The talk is aimed at users, DBAs and system administrators interested in learning a bit about how the sausage gets sent.

We'll go through:

  • protocol versions and how they differ
  • differences between simple and extended protocol
  • text and binary query formats
  • authentication and encryption
  • asynchronous features: async queries and NOTICE
  • the COPY protocol
  • query cancellation and how does it work
  • future development of the protocol