PostgreSQL triggers and isolation levels

Introduction In this article, we are going to see how the PostgreSQL isolation levels guarantee read and write consistency when executing database triggers. While relational database systems provide strong data integrity guarantees, it’s very important to understand how the underlying transactional engine works in order to choose the right design for your data access layer.

SQL EXISTS and NOT EXISTS

Introduction In this article, we are going to see how the SQL EXISTS operator works and when you should use it. Although the EXISTS operator has been available since SQL:86, the very first edition of the SQL Standard, I found that there are still many application developers who don’t realize how powerful SQL subquery expressions really are when it comes to filtering a given table based on a condition evaluated on a different table.

How does the 2PL (Two-Phase Locking) algorithm work

Introduction The 2PL (Two-Phase Locking) algorithm is one of the oldest concurrency control mechanisms used by relational database systems to guarantee data integrity. In this article, I’m going to explain how the 2PL algorithm works and how you can implement it in any programming language.

PostgreSQL trigger consistency check

Introduction In this article, we are going to see how to implement a non-trivial consistency check using a PostgreSQL INSERT and UPDATE trigger. By using a database trigger after executing an INSERT or UPDATE, we can ensure that the sum of salaries in a given department does not exceed the maximum budget allocated for the given department.

A beginner’s guide to database deadlock

Introduction In this article, we are going to see how a deadlock can occur in a relational database system, and how Oracle, SQL Server, PostgreSQL, or MySQL recover from a deadlock situation.

JDBC Driver Maven dependency list

Introduction Ever wanted to connect to a relational database using Java and didn’t know which JDBC Driver Maven dependency to use? If so, this article is surely going to help you from now on.

Pagination best practices

Introduction In this article, we are going to discuss several data pagination best and worst practices. Data pagination is omnipresent in enterprise applications. Yet, most solutions, not only they offer a bad user experience, but they are also inefficient.

A beginner’s guide to database table relationships

Introduction In a relational database, a relationship is formed by correlating rows belonging to different tables. A table relationship is established when a child table defines a Foreign Key column that references the Primary Key column of its parent table. Every database table relationship is, therefore, built on top of Foreign Key columns, and there can be three table relationship types: one-to-many is the most common relationship, and it associates a row from a parent table to multiple rows in a child table. one-to-one requires the child table Primary Key to be… Read More

How to implement a database job queue using SKIP LOCKED

Introduction In this article, we are going to see how we can implement a database job queue using SKIP LOCKED. I decided to write this article while answering this Stack Overflow question asked by Rafael Winterhalter. Since SKIP LOCKED is a lesser-known SQL feature, it’s a good opportunity to show you how to use it and why you should employ it, especially when implementing a job queue task.

How does a relational database execute SQL statements and prepared statements

Introduction In this article, we are going to see how a relational database executes SQL statements and prepared statements.