Putting Concurrency to the Test

Perform a few tests to learn more about how concurrency works in SQL.

We'll cover the following

Fixing the table
Comparing the concurrency scaling
Performing Test
Try it yourself

When we benchmark the concurrency properties of the two statements, we quickly realize that the activity table is badly designed. The unique constraint includes a timestamptz field, which in PostgreSQL is only precise down to the microsecond.

Fixing the table

This kind of made-up unique constraint means we now have these errors to deal with:

Error: Database error 23505: duplicate key value violates unique constraint "activity_messageid_datetime_action_key"
DETAIL: Key (messageid, datetime, action)=(1, 2022-06-01 10:17:39.079724+00, rt) already exists.

The best course of action here is to do this:

Get hands-on with 1400+ tech skills courses.

Part I: Preface

Introduction to PostgreSQL

Part II: Introduction to Structured Query Language

Part III: Writing SQL Queries

Business Logic

A Small Application

The SQL REPL: An Interactive Setup

Indexing in PostgreSQL

Part IV: SQL Toolbox

SQL Tools

Managing Output in SQL

Understanding Nulls

Window Functions

Relations and Joins

Part V: Data Types

Serialization and Deserialization

PostgreSQL Data Types 101

Denormalized Data Types

Part VI: Data Modeling

Object Relational Mapping and Tooling for Database Modeling

Normalization

Practical Use Case: Geonames

Modelization Anti-Patterns

Denormalization

Not Only SQL

Part VII: Data Manipulation and Concurrency Control

Another Small Application

Isolation and Locking

Computing and Caching in SQL

Triggers

Listen and Notify

Batch Update, MoMA Collection

Part VIII: PostgreSQL Extensions

What’s a PostgreSQL Extension?

Auditing Changes with hstore

Million Song Dataset

Using Trigrams for Typos

Denormalizing Tags with intarray

The Most Popular Pub Names

Geolocation with PostgreSQL

Counting Distinct Users with HyperLogLog

Appendix

Putting Concurrency to the Test

Fixing the table