DEV Community: Fredson Gisa Kaze

Reconsider Using UUID in Your Database

Fredson Gisa Kaze — Wed, 03 Jul 2024 08:12:16 +0000

Using UUIDs (Universally Unique Identifiers) as primary keys in databases is a common practice for us as developer, but this approach can have significant performance drawbacks. I am going to explore with you two major performance issues associated with using UUIDs as keys in your database tables.

What are UUIDs?

A UUID (Universal Unique Identifier) is a 128-bit value used to uniquely identify an object or entity on the internet. Among various versions, UUIDv4 is the most popular.

Here’s an example of a UUIDv4:

123e4567-e89b-12d3-a456-426614174000

UUIDs are like the star players in a football team – they stand out and are unique, but not always the best choice for every play.

Problem 1: Insert Performance – The Fumble

When a new record is inserted into a table, the primary key index must be updated to maintain optimal query performance. Indexes are constructed using the B+ Tree data structure, which requires rebalancing with each insertion to stay efficient.

With UUIDs, the inherent randomness complicates this process, leading to significant inefficiencies. As your database scales, millions of nodes need rebalancing, drastically reducing insert performance when using UUID keys.

Example:

CREATE TABLE players (
    id UUID PRIMARY KEY,
    name VARCHAR(255)
);

INSERT INTO players (id, name) VALUES ('123e4567-e89b-12d3-a456-426614174000', 'Kevin Lebron');

Tip: Consider using UUIDv7 instead, as it has inherent ordering that simplifies indexing, like having a well-coordinated offensive line 😊!

Problem 2: Higher Storage Requirements

UUIDs consume much more storage compared to auto-incrementing integer keys. Auto-incrementing integers use 32 bits per value, whereas UUIDs use 128 bits – four times more per row. When stored in human-readable form, a UUID can consume up to 688 bits, approximately 20 times more per row.

This is like getting a penalty flag for excessive celebration. It’s unnecessary and costly.

Example:

CREATE TABLE players (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
);

INSERT INTO players (name) VALUES ('Kevin Lebron');

Let’s simulate the impact with two tables:

Table 1: Contains 1 million rows with UUIDs.
Table 2: Contains 1 million rows with auto-incrementing integers.

Results:

Total table size: The UUID table is about 2.3 times larger than the integer table.
ID field size: An individual UUID field requires 9.3 times more storage space than an integer field.
ID column size: Excluding other attributes, the UUID column is 3.5 times larger than the integer column.

Using UUIDs is like getting hit with repeated penalty flags – your storage requirements balloon unnecessarily, impacting performance.

Conclusion

While UUIDs are excellent for ensuring uniqueness, they present significant scalability challenges. The performance issues discussed are more noticeable at scale, so for smaller applications, the impact might be minimal. However, it is crucial to understand these implications and design your database accordingly.

Remember: In both football and databases, it's all about making the right plays at the right time!

If you found this article helpful, please share, and connect with me!

References
What is a UUID, and what is it used for?
The Problem with UUID
B Trees and B+ Trees. How they are useful in Databases

How to get started with PostgreSQL

Fredson Gisa Kaze — Tue, 26 Apr 2022 12:31:33 +0000

PostgreSQL is an open source Relational Database Management System (RDBMS). In this article, I’ll provide an introduction to getting started with PostgreSQL. The mother site for PostgreSQL is http://www.postgresql.org you can read more.

Installing PostgreSQL

You can run the command below on your terminal to quickly install PostgreSQL in Ubuntu.

Ubuntu

// Refresh the apt-get repository
$ sudo apt-get update
// Install PostgreSQL
$ sudo apt-get install postgresql postgresql-contrib

To verify you’ve got PostgreSQL installed, run the following command to check your PostgreSQL version:

postgres --version

Others can download the latest version of PostgreSQL here and follow the installation steps.

Getting Started

1. Login to the PostgreSQL Server

 $ sudo -u postgres psql
   -- Run command "psql" as UNIX USER "postgres".
   -- Enter the CURRENT SUPERUSER password for sudo.
psql (14.2 (Ubuntu 14.2-1.pgdg21.10+1))
Type "help" for help

postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

-- Display version
postgres=# SELECT version();
                                                           version                                                           
-----------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.2 (Ubuntu 14.2-1.pgdg21.10+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.2.0-7ubuntu2) 11.2.0, 64-bit

press q to quit display

-- Quit
postgres=# \q

2. Create Database, Create Table, CURD (Create-Update-Read-Delete) Records

-- Login to server
$ sudo -u postgres psql
......

-- List all databases via \l (or \list), or \l+ for more details
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | python=CTc/postgres
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres

-- Create a new database called testdb
postgres=# CREATE DATABASE testdb;
CREATE DATABASE

-- Connect to testdb database via \c (or \connect)
-- Take note of the change of database name in the command prompt.
postgres=# \c testdb 
You are now connected to database "testdb" as user "postgres".

-- Display all tables (aka relations) via \dt or \dt+ for more details
testdb=# \dt
Did not find any relations.


testdb=# CREATE TABLE IF NOT EXISTS accounts (
    user_id serial PRIMARY KEY, -- AUTO_INCREMENT integer, as primary key
    username VARCHAR ( 50 ) UNIQUE NOT NULL,
    password VARCHAR ( 50 ) NOT NULL,
    email VARCHAR ( 255 ) UNIQUE NOT NULL,
    created_on TIMESTAMP NOT NULL,
        last_login TIMESTAMP 
);

-- Display all tables in the current database, via \dt or \dt+ for more details
testdb=# \dt+
                                     List of relations
 Schema |   Name   | Type  |  Owner   | Persistence | Access method |  Size   | Description 
--------+----------+-------+----------+-------------+---------------+---------+-------------
 public | accounts | table | postgres | permanent   | heap          | 0 bytes | 


-- Quit
testdb=# \q

You need to end your SQL command with a semi-colon (;) every-time or \g. If you forget to enter a semi-colon (;), the command-prompt changes to "dbname-#" to indicate continuation. You can enter the semi-colon on the new line.

More on commands

\?: show all psql commands.
\h sql-command: show syntax on SQL command.
\c dbname [username]: Connect to database, with an optional username (or \connect).

Display Commands: You can append + to show more details.

       - \l: List all database (or \list).
       - \d: Display all tables, indexes, views, and sequences.
       - \dt: Display all tables.
       - \dv: Display all views.
       - \di: Display all indexes.
       - \ds: Display all sequences.
       - \dS: Display all system tables.
       - \dT: Display all types.
       - \du: Display all users.

See more commands

3. Commonly-used SQL Data Types
The commonly-used SQL data types in PostgreSQL are:

INT, SMALLINT: whole number.
DATE, TIME, TIMESTAMP: date and time.
NULL: Represent known value of 0 and empty string).
SERIAL: auto-increment integer (AUTO_INCREMENT in MySQL).
DOUBLE: single and double precision floating-point number.
NUMERIC(m,n): decimal number with m total digits and n decimal places (DECIMAL(m,n) in MySQL).
CHAR(n) and VARCHAR(n): fixed-length string of n characters and variable-length string of up to n characters. String are enclosed by single quotes, e.g., 'Fredson', 'Python, django'.
User-defined types: The ones that don't come defautly.

What is a stack and where can it be used?

Fredson Gisa Kaze — Tue, 18 May 2021 08:57:27 +0000

Stack is a linear data structure that follows a particular order in which the various operations are performed. The order may be LIFO(Last In First Out) or FILO(First In Last Out). There are many real-life examples of a stack.
Consider a box containing books, the book which was inserted in lastly will be easily first removed

10 Tips to quickly improve your UI designs

Fredson Gisa Kaze — Fri, 19 Feb 2021 10:58:53 +0000

You should know your users
Above all else, you have to know who your users are—inside and out. Don't stop at knowing what your users want. Dig deeper and find out what they need.
Define how people use your interface
Before you design your interface, you need to define how people will use it.
Set expectations
Make sure users have an idea of what will happen before they do it, for example, what will happen after clicking a certain button.
Predict mistakes
People make mistakes, but they shouldn’t always have to suffer the consequences. There are two ways to help lessen the impact of human error:
```
    -Prevent mistakes before they happen
    -Provide ways to fix them after they happen
```
Give feedback—fast
Just make sure your things work quick or in others, word respond quickly
Think carefully about element placement and size
This is a crucial thing in design, you must choose what shows users clearly like if it's the menu button, it should well aligned and seenable.
Don’t ignore standards
Being highly creative types, designers tend to love to reinvent things—but it’s not always the best idea. You must follow the standards of design.
Make your interfaces easy to learn
Simplicity in design is the best practice, so you have to make your design easy to understand.
Make decision-making simple
Listen to the data

Difference between Linked List and Array in CPP

Fredson Gisa Kaze — Tue, 16 Feb 2021 19:59:02 +0000

Basically, an array is a set of similar data objects stored in sequential memory locations under a common heading or a variable name.

While a linked list is a data structure that contains a sequence of the elements where each element is linked to its next element. There are two fields in an element of the linked list. One is the Data field, and the other is the link field, Data field contains the actual value to be stored and processed. Furthermore, the link field holds the address of the next data item in the linked list. The address used to access a particular node is known as a pointer.

Another significant difference between an array and a linked list is that Array has a fixed size and required to be declared prior, but Linked List is not restricted to size and expand and contract during execution. Below is a summarized table showing differences clearly