4 Articles tagged “UPDATE”

This article fits in the PostgreSQL Concurrency series, where we installed a tweeter like application schema and had all the characters from Shakespeare’s A Midsummer Night’s Dream tweet their own lines in our database in PostgreSQL Concurrency: Data Modification Language.

A previous article in the series covered how to manage concurrent retweets in an efficient way: Computing and Caching, where we learn how to maintain a cache right in your PostgreSQL database, thanks for materialized views. We even went as far as maintaining an external cache in another application layer using PostgreSQL LISTEN and NOTIFY features and a Golang application.

Today’s article is going to address concurrency in the context of updating data in a batch. This activity is quite common, as soon as your system is connected to other systems either internally or with external providers. While it’s pretty easy to ingest new data, and easy enough to update data from an external source when nothing happens in your database, doing the operation safely with concurrent activity is more complex. Once more though, PostgreSQL comes with all the tooling you need to handle that situation.

Let’s continue to dive in PostgreSQL Concurrency. Last week’s article PostgreSQL Concurrency: Isolation and Locking was a primer on PostgreSQL isolation and locking properties and behaviors.

Today’s article takes us a step further and builds on what we did last week, in particular the database modeling for a tweet like application. After having had all the characters from Shakespeare’s A Midsummer Night’s Dream tweet their own lines in our database in PostgreSQL Concurrency: Data Modification Language, it’s time for them to do some actions on the tweets: likes and retweet.

Of course, we’re going to put concurrency to the test, so we’re going to have to handle very very popular tweets from the play!

PostgreSQL is a relational database management system. It’s even the world’s most advanced open source one of them. As such, as its core, Postgres solves concurrent access to a set of data and maintains consistency while allowing concurrent operations. Postgres exposes its concurrency APIs in the SQL language, in particular in the DML parts of it: you can read the Data Manipulation Language chapter of the PostgreSQL docs for all the details.

Performance consulting involves some tricks that you have to teach over and over again. One of them is that SQL tends to be so much better at dealing with plenty of rows in a single statement when compared to running as many statements, each one against a single row.