DEV Community: Lucas Rivelles

How indexes power your database

Lucas Rivelles — Sun, 03 Nov 2024 17:28:17 +0000

When working with databases, it's fundamental that we understand how to optimize our queries. One of the most important aspects of scaling our use cases is being able to retrieve information as fast as possible. Before we dive in specifics, let's have an overview on how databases typically store information.

When we run an INSERT statement, a new row is created under a table. However, this is just a logical representation of data that is stored in disk. Imagine you are implementing a database and, for the sake of simplicity, the inserted data end up in a simple text file, separated by rows and commas.

1, Adam, EMPLOYEE
2, Anne, EMPLOYEE
3, John, CUSTOMER
4, Jane, CUSTOMER
5, Cris, EMPLOYEE
...

How would you implement the logic to fetch a user by ID? The most obvious answer would be to just traverse the rows and look for the searched ID, which has a linear time complexity. Maybe this works fine for our small file, but what if we have 20 million users? Every query would need to traverse potentially millions of rows to find a single user.

In a database management system, this would mean a sequential access in a full table scan. It would be much easier if the database already knows where to fetch the data so it doesn't need to scan the entire file, right? Fortunately, it exists and we will see how to get the most advantages of it. Let's see how indexes work.

Database Indexes

We already saw what we don't want to happen when looking for data: sequential access in a table scan. Indexes are used to avoid this scenario by providing an additional data structure that provides references to data in disk.

The typical implementation of indexes use variations of the B-Tree data structure to store references to pages where actual data lives. Since they are balanced, it's possible to use binary search, which has logarithmic time complexity and is generally faster than linear complexity.

In this example, let's say we are looking for user with ID 15. We start reading from the root node. As we perform a search inside of it, we fall into the reference between 7 and 16, which brings us to the next node, which is a leaf node. It contains the ID we are looking for and provides the address of the page that the database needs to load to access the data we need.

This ensures that we don't need to traverse all our dataset anymore to find a user by ID.

A practical example

This example uses PostgreSQL running in a Docker image. To follow, you can simply run:

docker run --name some-postgres -e POSTGRES_PASSWORD=mysecretpassword -d postgres

docker exec -it some-postgres /bin/sh

Let's first define our table without any index and insert some data.

CREATE TABLE users (
    id INTEGER,
    name VARCHAR(100),
    type VARCHAR(100)
);

INSERT INTO users (id, name, type) VALUES
(1, 'Alice', 'EMPLOYEE'),
(2, 'Bob', 'CUSTOMER'),
(3, 'Charlie', 'EMPLOYEE'),
(4, 'David', 'CUSTOMER'),
(5, 'Eve', 'EMPLOYEE'),
(6, 'Frank', 'CUSTOMER'),
(7, 'Grace', 'EMPLOYEE'),
(8, 'Hank', 'CUSTOMER'),
(9, 'Ivy', 'EMPLOYEE'),
(10, 'Jack', 'CUSTOMER');

Now, let's run a EXPLAIN ANALYZE statement to understand what happens when searching for a user with ID=7:

EXPLAIN ANALYZE SELECT * FROM users WHERE id=7;

It will return something like this:

                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Seq Scan on users  (cost=0.00..12.12 rows=1 width=440) (actual time=0.087..0.092 rows=1 loops=1)
   Filter: (id = 7)
   Rows Removed by Filter: 9
 Planning Time: 0.121 ms
 Execution Time: 0.119 ms
(5 rows)

Take a look at the first statement, it clearly says that the database will run a sequential scan on the users table (Seq Scan on users). As we saw, this is something we want to avoid.

Let's now create an index for our table:

ALTER TABLE users ADD PRIMARY KEY (id);

If we run the EXPLAIN ANALYZE statement again, we will see that the database still did the sequential scan. But why does that happened? Most likely, since our data is very small, reading the entire table might be less costly than using the index, since it would need to find the reference to the data and load the entire page anyway, there would be additional overhead, so it's just simpler to only scan the entire table. The database is smart enough to plan the query according to the context we currently have. Now, let's change our scenario and add 1 million users to our table:

DELETE FROM users WHERE 1=1;

INSERT INTO users (id, name, type)
SELECT
    i,
    'User_' || i,
    CASE WHEN i % 2 = 0 THEN 'EMPLOYEE' ELSE 'CUSTOMER' END
FROM
    generate_series(1, 1000000) AS i;

Let's see how the database plans our query this time:

EXPLAIN ANALYZE SELECT * FROM users WHERE id=20;

This will return:

                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Index Scan using users_pkey on users  (cost=0.42..8.44 rows=1 width=24) (actual time=0.050..0.052 rows=1 loops=1)
   Index Cond: (id = 20)
 Planning Time: 0.130 ms
 Execution Time: 0.219 ms
(4 rows)

Now, we can see that the database chose to use an index scan (Index Scan using users_pkey). This effectively means that, in order to find our user, the database performed a binary search in the index structure to locate the actual data.

Conclusion

In this article, we saw how indexes can power our databases and speed-up reading operations. We explored how indexes use variations of B-Trees to use the advantages of binary search algorithm over sequential search. Finally, we explored in practice how the database plans the query execution on tables with and without indexes.

Database Isolation Levels: A Practical Guide

Lucas Rivelles — Wed, 07 Feb 2024 19:43:22 +0000

Picture this: you're building a bridge to some cargo across a turbulent river. Each plank meticulously placed, each bolt securely fastened. But what happens when two teams of workers try to build different sections of the bridge simultaneously, unaware of each other's progress?

Welcome to the world of database isolation levels! It's almost a common sense that we should use relational databases if we want to achieve high consistency. However, is this assumption entirely true for all use cases? Can we assume we won't have consistency problems if we just decide to use a relational database such as MySQL or PostgreSQL in our applications?

We will see that, unfortunately, life is not that simple. We'll start by explaining some concepts and see, in practice, how high consistency can be achieved.

1. Environment Setup
2. ACID Properties
3. Isolation Levels
       3.1. Read Uncommitted - The Weakest One
       3.2. Read Committed - Preventing Dirty Reads
       3.3. Repeatable Read (Snapshot Isolation)
       3.4. Serializable - The Strongest One
4. Conclusion
5. References

Environment Setup

In the examples, we'll use a Docker environment with a MySQL container and a script to fill the database with some data.

If you want to reproduce it in your local environment, the docker-compose and the SQL scripts can be found here. Follow the next steps to make it ready:
1- Clone the repository

git clone git@github.com:rivelles/isolation-level-demo.git

2- Access the project and run the container:

docker-compose up

3- Open two new terminal tabs
4- On each of them, access the container and log into MySQL:

docker exec -it mysql /bin/bash
mysql -u root -p --database=bank

5- For the password, just hit Enter

All good!

Transactions and ACID Properties

We can define a transaction as a single unit of work that can access and modify data in a database. It can be composed by multiple queries. Usually, databases that implement transactions follow a set of conventions called ACID.

A database is considered to be ACID if it accomplishes a set of properties:

Atomicity: Each transaction is treated as a single unit. Either it succeeds entirely (commit) or it fails entirely (abort).
Consistency: Constraints are guaranteed to not be violated.
Isolation: One transaction will not affect other transactions' results.
Durability: After a transaction has been committed, its changes will be stored permanently.

When speaking specifically about isolation, databases implement different levels of guarantees to control what happens when two transactions handle objects concurrently. These levels come with trade-offs between their strength in terms of consistency and performance. In the next sections, we'll dive-in each of them with some simple real-world examples to illustrate what problems they try to solve.

Isolation Levels

Concurrent transactions happen when two or more of them run without knowing about each other, making them prone to access the same objects and potentially updating them.

Existing databases have mechanisms to prevent concurrency problems to happen. The main purpose of it is to remove the responsibility of the developers to worry about concurrency when dealing with the database. They allow us to set-up different levels of consistency based on the needs we have and which problems we want to avoid.

Let's see in practice how they work.

Read Uncommitted - The Weakest One

In our application, let's imagine we have two transactions updating the balance for an account. They will read the value, and update the row with the new balance calculated by the application (let's always increment it by 1 to keep it simple). Let's see how it goes in the following scenario:

Transaction 1	Transaction 2
`SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;`
`START TRANSACTION;`
`SELECT balance FROM accounts WHERE id = 1;`
`UPDATE accounts SET balance = 1001 WHERE id = 1;`
	`SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;`
	`START TRANSACTION;`
	`SELECT balance FROM accounts WHERE id = 1;`
`ROLLBACK;`
	`UPDATE accounts SET balance = 1002 WHERE id = 1;`
	`COMMIT;`

Transaction 1 execution

mysql> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.01 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT balance FROM accounts WHERE id = 1;
+---------+
| balance |
+---------+
|    1000 |
+---------+
1 row in set (0.01 sec)

mysql> UPDATE accounts SET balance = 1001 WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

Transaction 2 execution:

mysql> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT balance FROM accounts WHERE id = 1;
+---------+
| balance |
+---------+
|    1001 |
+---------+
1 row in set (0.00 sec)

mysql> UPDATE accounts SET balance = 1002 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

In the end, we have our balance with a total amount of 1002, although transaction 1 rolled back:

mysql> SELECT balance FROM accounts WHERE id = 1;
+---------+
| balance |
+---------+
|    1002 |
+---------+
1 row in set (0.00 sec)

This is because transaction 2 was able to see the uncommitted update made by transaction 1, this is considered a dirty read. Because of that, we end up with an inconsistent value. There is an easy way of fixing this without changing the isolation level, which is using atomic operations, so instead of changing the balance amount using a direct assignment, we can do the following:

UPDATE accounts SET balance = balance + 1 WHERE id = 1;

Notice that now we increment the balance by 1 instead of assigning a direct value on it. When we do it, the database will lock the row until the transaction finishes (either committing or rolling back). So transaction 2 wouldn't be able to update it.

Since our focus is on isolation levels, let's see the behavior of the same use case in a different setup to illustrate and understand how it works.

Read Committed - Preventing Dirty Reads

With READ COMMITTED isolation level, the database guarantees that we will only see data that was previously committed. So, if we execute the same scenario, when transaction 2 reads the balance, it would still see the initial value. Let's see how it would go:

Transaction 1 execution

mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT balance FROM accounts WHERE id = 2;
+---------+
| balance |
+---------+
|    1000 |
+---------+
1 row in set (0.00 sec)

mysql> UPDATE accounts SET balance = 1001 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

Transaction 2 execution

mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT balance FROM accounts WHERE id = 2;
+---------+
| balance |
+---------+
|    1000 |
+---------+
1 row in set (0.00 sec)

mysql> UPDATE accounts SET balance = 1001 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

Notice that, when transaction 2 reads the balance for account 1, it gets 1000, because transaction 1 hadn't committed it yet. Since it rolled back, we ended up with the correct value.

mysql> SELECT balance FROM accounts WHERE id = 2;
+---------+
| balance |
+---------+
|    1001 |
+---------+
1 row in set (0.00 sec)

Although this isolation level prevents dirty reads, there are other problems that could happen:

If transaction 1 had committed instead of rolled back, we'd have both transactions successfully committed with the final value at 1001, this phenomenon is called lost update, since the update made by transaction 1 would be overridden by transaction 2.
Imagine user 3 has two accounts and we want to display in the screen the sum of their values and also the individual value for each of them, while other transaction is handling a withdraw from one of the accounts. If the second transaction commits while the first one is getting the results, we might show an inconsistent value, let's see this in practice:

Transaction 1	Transaction 2
`SET TRANSACTION ISOLATION LEVEL READ COMMITTED;`
`START TRANSACTION;`
`SELECT id, balance FROM accounts WHERE customer_id = 30;`
	`SET TRANSACTION ISOLATION LEVEL READ COMMITTED;`
	`START TRANSACTION;`
	`UPDATE accounts SET balance = balance - 100 WHERE id = 3;`
	`COMMIT;`
`SELECT SUM(balance) FROM accounts WHERE customer_id = 30;`
`COMMIT;`

Transaction 1 execution

mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT id, balance FROM accounts WHERE customer_id = 30;
+----+---------+
| id | balance |
+----+---------+
|  3 |    1000 |
|  4 |    1000 |
+----+---------+
2 rows in set (0.00 sec)

mysql> SELECT SUM(balance) FROM accounts WHERE customer_id = 30;
+--------------+
| SUM(balance) |
+--------------+
|         1900 |
+--------------+
1 row in set (0.01 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

Transaction 2 execution

mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE accounts SET balance = balance - 100 WHERE id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

In the end, we have the individual values showing each account with a balance of $1000, but the sum showing $1900. This phenomenon is called non-repeatable read, which happens when one transaction queries the same objects more than once and gets different results. Let's see how the next isolation level avoids this scenario.

Repeatable Read (Snapshot Isolation)

With REPEATABLE READ, we get another level of consistency. Basically, this level guarantees us two things:

Reads won't affect writes
Writes won't affect reads

The database achieves that by making every transaction to run pointing to a consistent snapshot of the database. In the past example, we would have avoided the non-repeatable read phenomenon, since when transaction 1 started, no matter what transaction 2 changes, it wouldn't see any update, even after transaction 2 commits its changes. Now let's see it in practice:

Transaction 1	Transaction 2
`SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;`
`START TRANSACTION;`
`SELECT id, balance FROM accounts WHERE customer_id = 40;`
	`SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;`
	`START TRANSACTION;`
	`UPDATE accounts SET balance = balance - 100 WHERE id = 5;`
	`COMMIT;`
`SELECT SUM(balance) FROM accounts WHERE customer_id = 40;`
`COMMIT;`

Transaction 1 execution

mysql> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT id, balance FROM accounts WHERE customer_id = 40;
+----+---------+
| id | balance |
+----+---------+
|  5 |    1000 |
|  6 |    1000 |
+----+---------+
2 rows in set (0.00 sec)

mysql> SELECT SUM(balance) FROM accounts WHERE customer_id = 40;
+--------------+
| SUM(balance) |
+--------------+
|         2000 |
+--------------+
1 row in set (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

Transaction 2 execution

mysql> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE accounts SET balance = balance - 100 WHERE id = 5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

In the end, both reads made by transaction 1 returned consistent values. When transaction 1 started, all objects it will read were committed by transactions that finished before it started so, even though transaction 2 committed the withdraw on account 5, transaction 1 would never get this change.

Although this isolation level is stronger, we could still face some problems. We saw in READ COMMITTED that we can face a lost update if one transaction overrides a previously committed change. The SQL standard does not require that REPEATABLE READ isolation level prevent this phenomenon, however, some implementation does that; it's not the case on MySQL. If we try to update the balance for an account in two different transactions, the final value will be set by the one that finished last.

There is also another concurrency problem we can face. Imagine that we have a rule stating that a customer can't have a loan bigger than the current balance. So when creating a loan we need to first check the account's balance and when withdrawing money, we also need to check the total amount of loaned money:

Transaction 1	Transaction 2
`SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;`
`START TRANSACTION;`
`SELECT balance FROM accounts WHERE id = 6;`
	`SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;`
	`START TRANSACTION;`
	`SELECT COALESCE(0, SUM(amount)) as total_amount_loans FROM loans WHERE account_id = 6;`
	`UPDATE accounts SET balance = balance - 100 WHERE id = 6;`
	`COMMIT;`
`INSERT INTO loans VALUES (1, 6, 1000);`
`COMMIT;`

Transaction 1 execution

mysql> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT balance FROM accounts WHERE id = 6;
+---------+
| balance |
+---------+
|    1000 |
+---------+
1 row in set (0.00 sec)

mysql> INSERT INTO loans VALUES (1, 6, 1000);
Query OK, 1 row affected (0.01 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

Transaction 2 execution

mysql> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COALESCE(0, SUM(amount)) as total_amount_loans FROM loans WHERE account_id = 6;
+--------------------+
| total_amount_loans |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

mysql> UPDATE accounts SET balance = balance - 100 WHERE id = 6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

In the end, we will have violated our rule, since user was able to create a loan with a value bigger than what they had in their balance. Even if we wanted to enforce the rule by adding a second check in the balance after inserting the loan, this query wouldn't be able to see the withdraw made by transaction 2.

Serializable - The Strongest One

What is the safest way of avoiding concurrency problems? Exactly, by having no concurrency. This is what SERIALIZABLE consistency level tries to achieve. It executes the transactions in a way that it seems to be executed in a serial order.

There are many ways on how this can be implemented. One of the most popular is the Two-Phase Lock (2PL), used by MySQL. It basically uses locks when transactions are accessing DB objects in a way that:

If a transaction wants to read an object, it needs to acquire a shared lock, which can be acquired by many transactions at a time.
If a transaction wants to write to an object, it needs to acquire an exclusive lock. To proceed, there can't be any other locks in that object.

Let's see what would happen in the same example:

Transaction 1	Transaction 2
`SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;`
`START TRANSACTION;`
`SELECT balance FROM accounts WHERE id = 7;`
	`SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;`
	`START TRANSACTION;`
	`SELECT COALESCE(0, SUM(amount)) as total_amount_loans FROM loans WHERE account_id = 7;`
	`UPDATE accounts SET balance = balance - 100 WHERE id = 7;`
`INSERT INTO loans VALUES (1, 7, 1000);`

Transaction 1 execution

mysql> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT balance FROM accounts WHERE id = 7;
+---------+
| balance |
+---------+
|    1000 |
+---------+
1 row in set (0.00 sec)

mysql> INSERT INTO loans VALUES (1, 7, 1000);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Transaction 2 execution

mysql> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK, 0 rows affected (0.01 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COALESCE(0, SUM(amount)) as total_amount_loans FROM loans WHERE account_id = 7;
+--------------------------+
|    total_amount_loans    |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (0.00 sec)

mysql> UPDATE accounts SET balance = balance - 100 WHERE id = 7;
Query OK, 1 row affected (7.19 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

In this example, when transaction 1 read the balance for account 7, it created a shared lock on that object, meaning that another transaction could read it but needed to wait if it wants to change it; on the other side, transaction 2 read the sum of the loans and also created a shared lock on the loans table. When transaction 1 tried to insert the new loan, it needs to wait because transaction 2 is holding a shared lock on loans table, and when transaction 2 tried to withdraw from account 7's account, it also needs to wait for an exclusive lock on that object, which is being hold by transaction 1. This is called a deadlock, and happens when two transactions cannot proceed because each of them is waiting for the other one to release a lock. The database detected it and killed transaction 1 automatically, allowing transaction 2 to proceed.

With this, we are still in a consistent state, because only transaction 2 completed. Because of the atomicity property, no changes made by transaction 1 were applied.

Conclusion

In summary, database isolation levels are key to maintaining data consistency and transaction reliability. From the least restrictive 'Read Uncommitted' to the strong 'Serializable', each level offers unique benefits and considerations.

There is no "correct" transaction isolation level. Every application should consider carefully the trade-offs between them, balancing performance and data integrity. The purpose of this article was to enlighten us about this subject so we are more prepared to take decisions for our applications and use-cases.

References

This article is inspired in Martin Kleppman's amazing book, Designing Data-Intensive Applications, which you can find here

Elasticsearch: The keyword data type

Lucas Rivelles — Wed, 15 Jun 2022 17:43:08 +0000

We already saw how Elasticsearch stores text values using its standard analyser. However, what if we want to store entire phrases into our inverted index? For this, we need to use the data type keyword.

Fields mapped as keywords are analyzed using a different analyzer. Its analyzer is so called no-op analyzer, because it doesn't make any changes to our variable. Let's see an example:

If we analyze the text The 2 QUICK Brown-Foxes jumped over the lazy dog's bone. :) using the standard analyzer, we`ll see this result:

POST /_analyze { "text": "The 2 QUICK Brown-Foxes jumped over the lazy dog's bone. :)" }

{ "tokens" : [ { "token" : "the", "start_offset" : 0, "end_offset" : 3, "type" : "<ALPHANUM>", "position" : 0 }, { "token" : "2", "start_offset" : 4, "end_offset" : 5, "type" : "<NUM>", "position" : 1 }, { "token" : "quick", "start_offset" : 6, "end_offset" : 11, "type" : "<ALPHANUM>", "position" : 2 }, { "token" : "brown", "start_offset" : 16, "end_offset" : 21, "type" : "<ALPHANUM>", "position" : 3 }, { "token" : "foxes", "start_offset" : 22, "end_offset" : 27, "type" : "<ALPHANUM>", "position" : 4 }, { "token" : "jumped", "start_offset" : 28, "end_offset" : 34, "type" : "<ALPHANUM>", "position" : 5 }, { "token" : "over", "start_offset" : 35, "end_offset" : 39, "type" : "<ALPHANUM>", "position" : 6 }, { "token" : "the", "start_offset" : 40, "end_offset" : 43, "type" : "<ALPHANUM>", "position" : 7 }, { "token" : "lazy", "start_offset" : 44, "end_offset" : 48, "type" : "<ALPHANUM>", "position" : 8 }, { "token" : "dog's", "start_offset" : 49, "end_offset" : 54, "type" : "<ALPHANUM>", "position" : 9 }, { "token" : "bone", "start_offset" : 55, "end_offset" : 59, "type" : "<ALPHANUM>", "position" : 10 } ] }
The text was completely modified by Elasticsearch's standard analyzer and the result of this operation was stored in the inverted index with each term. Now, let's modify our request by specifying that we want to use the keyword analyzer.

POST /_analyze { "text": "The 2 QUICK Brown-Foxes jumped over the lazy dog's bone. :)", "analyzer": "keyword" }

{ "tokens" : [ { "token" : "The 2 QUICK Brown-Foxes jumped over the lazy dog's bone. :)", "start_offset" : 0, "end_offset" : 63, "type" : "word", "position" : 0 } ] }
Now, the generated token was the text itself.
What are the effects of storing a field as a keyword? If we store it as a text, we are able to use Elasticsearch's textual queries, whereas if we use keyword, it is not possible, since the term which is stored is exactly the String that we used on the request.
When using a keyword, the field can be also used for aggregations, which we'll see in the next articles.

Elasticsearch: Object and Nested Data Types

Lucas Rivelles — Wed, 15 Jun 2022 17:22:00 +0000

Mapping basically defines the structure of documents and it is also used to configure how values will be indexed within Elasticsearch.

Elasticsearch doesn't require us to create a mapping for our indices, because it works using dynamic mapping. So, it will infer our data types based on what we are inserting in our document.

Since mapping is quite flexible, we can also combine explicit mapping with dynamic mapping. So, we can create an index with explicit data types and, when we add documents, they may have new fields and Elasticsearch will store them according to their types.

Data Types

Elasticsearch provides some pretty regular data types, which we can find also in many programming languages, such as: short, integer, long, float, double, boolean, date

The object data type

The object data type represents how Elasticsearch stores its JSON values, basically every document is an object, and we can have nested objects, let's see an example:

PUT /users
{
  "mappings": {
    "properties": {
      "name": {"type": "text"},
      "birthday": {"type": "date"},
      "address": {
        "properties": {
          "country": {"type": "text"},
          "zipCode": {"type": "text"}
        }
      }
    }
  }
}

This will create an index called users with a text field, a date field and an object field, which represents an address and contains nested text fields.
If we want to index a document to this, we can make the following request:

POST /users/_doc/1
{
  "name": "Lucas",
  "birthday": "1990-09-28",
  "address": {
    "country": "Brazil",
    "zipCode": "04896060"
  }
}

Since Elasticsearch runs on top of Apache Lucene, we should be aware that these objects are not really stored as JSON inside it. When we index a nested object, like the address field from the last example, Elasticsearch flattens the object and make it like this:

{
  "name": "Lucas",
  "birthday": "1990-09-28",
  "address.country": "Brazil",
  "address.zipCode": "04896060"
}

What if we had an array of addresses instead? In this case, Elasticsearch would then store our fields like an array of countries and an array of zipCodes, so this:

POST /articles/_doc
{
  "name": "Elasticsearch article",
  "reviews": [
    {
      "name": "Lucas",
      "rating": 5
    },
    {
      "name": "Eduardo",
      "rating": 3
    }
  ]
}

Will be stored like this:

{
  "name": "Elasticsearch article",
  "reviews.name": ["Lucas", "Eduardo"],
  "reviews.rating": [5, 3]
}

The nested data type

In the previous example, we indexed a document representing an article that contains 2 reviews from 2 different users. Let's try to run the following query to try to get articles reviewed by Eduardo with rating greater than 4:

GET /articles/_search
{
  "query": {
    "bool": {
      "must": [
        {"match": { "reviews.name": "Eduardo" }},
        {"range": { "reviews.rating": {"gt": 4} }}
      ]
    }
  }
}

We'll get the following result:

...
"hits" : [
      {
        "_index" : "articles",
        "_id" : "sboYZIEBFSkh39rxJql6",
        "_score" : 1.287682,
        "_source" : {
          "name" : "Elasticsearch article",
          "reviews" : [
            {
              "name" : "Lucas",
              "rating" : 5
            },
            {
              "name" : "Eduardo",
              "rating" : 3
            }
          ]
        }
      }
    ]
...

This is not exactly what we are looking for, and why is that? Basically, since Elasticsearch flattened our document, it can't query based on these filter because they are not related. But what if we need to have this relationship? That's where the data type nested comes in.

This data type basically tells Elasticsearch that our nested object has a relationship with its parent. Let's see the same example, but defining the field reviews as nested.

PUT /articles_v2 
{
  "mappings": {
    "properties": {
      "name": {"type": "text"},
      "reviews": {"type": "nested"}
    }
  }
}

Let's index the same document as before:

POST /articles_v2/_doc
{
  "name": "Elasticsearch article",
  "reviews": [
    {
      "name": "Lucas",
      "rating": 5
    },
    {
      "name": "Eduardo",
      "rating": 3
    }
  ]
}

Let's now create a nested query to search for articles reviewd by Eduardo with rating greater than 4.

GET /articles_nested_v2/_search
{
  "query": {
    "nested": {
      "path": "reviews",
      "query": {
        "bool": {
          "must": [
            { "match": {"reviews.name": "Eduardo"} },
            { "range": {"reviews.rating": {"gt": 4}} }
          ]
        }
      }
    }
  }
}

And the return shows as that we have no articles matching these conditions, which is correct!

{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 0,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  }
}

Document Analysis in Elasticsearch

Lucas Rivelles — Sun, 12 Jun 2022 17:13:24 +0000

When we index a document in Elasticsearch, its text values pass through an analysis process. In this article we'll cover what happens in this process and how Elasticsearch's standard analyzer works.

Introduction to analysis

The main objective of the analysis is storing the documents in a way that makes them efficient for searching. This happens at the moment that we index some document in Elasticsearch and it uses three mechanisms to do so, which are:

Character filter
Tokenizer
Token filter

Character filters

The first step consists in receiving the full text and adding, removing or changing characters. For example, we can remove HTML tags, such as:

Input: <p>I <strong>REALLY</strong> love to go hiking!</p>
Result: I REALLY love to go hiking!

An analyzer may contain zero or more character filters, and the result of the operation is passed to the tokenizer.

Tokenizer

Differently from the character filters, an analyzer must contain exactly one tokenizer, and its responsibility is to split a String into tokens. In this process, some characters may be removed from the text, such as punctuation. An example of this would be:

Input: I REALLY love to go hiking!
Result: "I", "REALLY", "love", "to", "go", "hiking"

Token filters

The token filters will receive the tokens and operate on them. A simple example is the lowercase filter.
Input: "I", "REALLY", "love", "to", "go", "hiking"
Result: "i", "really", "love", "to", "go", "hiking"

An analyzer may also have zero or more token filters.

For more examples of built-in character filters, tokenizers and token filters, we can check the official documentation.

Elasticsearch's standard analyzer consists of:

No character filters
The standard tokenizer
The lowercase and an optional stop words token filter.

Using the analyze API

Elasticsearch provides us a way of visualizing how a String gets analyzed, which is the analyze API. To use it, we just need to send a POST request to the /_analyze endpoint with a "text" parameter. Let's try it out!

POST /_analyze
{
  "text": "The 2 QUICK     Brown-Foxes jumped over the lazy dog's bone. :)"
}

In the response, we can see the generated tokens:

{
  "tokens" : [
    {
      "token" : "the",
      "start_offset" : 0,
      "end_offset" : 3,
      "type" : "<ALPHANUM>",
      "position" : 0
    },
    {
      "token" : "2",
      "start_offset" : 4,
      "end_offset" : 5,
      "type" : "<NUM>",
      "position" : 1
    },
    {
      "token" : "quick",
      "start_offset" : 6,
      "end_offset" : 11,
      "type" : "<ALPHANUM>",
      "position" : 2
    },
    {
      "token" : "brown",
      "start_offset" : 12,
      "end_offset" : 17,
      "type" : "<ALPHANUM>",
      "position" : 3
    },
    {
      "token" : "foxes",
      "start_offset" : 18,
      "end_offset" : 23,
      "type" : "<ALPHANUM>",
      "position" : 4
    },
    {
      "token" : "jumped",
      "start_offset" : 24,
      "end_offset" : 30,
      "type" : "<ALPHANUM>",
      "position" : 5
    },
    {
      "token" : "over",
      "start_offset" : 31,
      "end_offset" : 35,
      "type" : "<ALPHANUM>",
      "position" : 6
    },
    {
      "token" : "the",
      "start_offset" : 36,
      "end_offset" : 39,
      "type" : "<ALPHANUM>",
      "position" : 7
    },
    {
      "token" : "lazy",
      "start_offset" : 40,
      "end_offset" : 44,
      "type" : "<ALPHANUM>",
      "position" : 8
    },
    {
      "token" : "dog's",
      "start_offset" : 45,
      "end_offset" : 50,
      "type" : "<ALPHANUM>",
      "position" : 9
    },
    {
      "token" : "bone",
      "start_offset" : 51,
      "end_offset" : 55,
      "type" : "<ALPHANUM>",
      "position" : 10
    }
  ]
}

In this API, we can specify the character filters, the tokenizer and the token filters that we want to use. The same result we got would've been returned if we had made the request like this:

POST /_analyze
{
  "text": "The 2 QUICK     Brown-Foxes jumped over the lazy dog's bone. :)",
  "char_filter": [],
  "tokenizer": "standard",
  "filter": ["lowercase"]
}

Managing indexes in Elasticsearch

Lucas Rivelles — Mon, 06 Jun 2022 18:55:10 +0000

An index is the equivalent of a relational database table in Elasticsearch. It stores related documents in JSON format. We`ll see how to create an index from scratch and perform some basic operations on documents in order to explore its features.

For this, we are going to use the trial version of Elastic Cloud and its Kibana interface. By default, it uses a cluster with two nodes.

Creating an index

In order to create a simple index, we just need to send a PUT request, for example:

PUT /users
It will create our index named users and return the following confirmation:

{ "acknowledged" : true, "shards_acknowledged" : true, "index" : "users" }
By default, Elasticsearch will create an index with 1 shard and 1 replica, we can see this by making the following request:

GET /_cat/shards/users?v
The return tells us the details: we have an index with one shard, in which the primary is running at 10.42.11.185 and the replica is running at 10.42.1.147

index shard prirep state docs store ip node users 0 r STARTED 0 225b 10.42.1.147 instance-0000000001 users 0 p STARTED 0 225b 10.42.11.185 instance-0000000000
If we want to specify the number of shards and replicas in the moment of the creation of our index, we can do so by passing the details in the request body:

PUT /products { "settings": { "number_of_shards": 2, "number_of_replicas": 2 } }
It will create an index called products with 2 shards, and each shard will have 2 replicas. Since we are running in a cluster with two nodes, it will result in this architecture:

Since each node must run either one primary shard or its replica, we end up with 2 unassigned nodes, we can see it with our own eyes by calling GET /_cat/shards/products?v.

index shard prirep state docs store ip node products 0 r STARTED 0 225b 10.42.1.147 instance-0000000001 products 0 p STARTED 0 225b 10.42.11.185 instance-0000000000 products 0 r UNASSIGNED products 1 p STARTED 0 225b 10.42.1.147 instance-0000000001 products 1 r STARTED 0 225b 10.42.11.185 instance-0000000000 products 1 r UNASSIGNED

Updating properties

As we saw in the previous example, we ended up with unused replicas at our index. Let's fix this by updating the number of replicas.

PUT /products/_settings { "settings": { "number_of_replicas": 1 } }
Now, let's see if it worked by calling GET /_cat/shards/products?v again.

index shard prirep state docs store ip node products 0 r STARTED 0 225b 10.42.1.147 instance-0000000001 products 0 p STARTED 0 225b 10.42.11.185 instance-0000000000 products 1 p STARTED 0 225b 10.42.1.147 instance-0000000001 products 1 r STARTED 0 225b 10.42.11.185 instance-0000000000
Yes! It worked! :)

Now, let's take a look in our first example. We created a 'users' index with default values, i.e., 1 shard and 1 replica. Let's try to increase its number of shards to match the 'products' index.

PUT /users/_settings { "settings": { "number_of_shards": 2 } }
Uh oh! It returns the following error message:
"Can't update non dynamic settings [[index.number_of_shards]] for open indices [[users/78H88glwR0WkN0Z-92naSA]]"
Updating the number of shards is a special operation. It wasn't possible to do so before version 5.0 (to decrease) and version 6.1 (to increase). We can do this using the split and the shrink operations.

The split operation

This operation is used specifically in order to increase the number of shards of an index. Before splitting an index, we need to:

Make sure that the index is read-only
Make sure that the cluster is in green status (healthy)

It's also important to remember that we must split the index into factors of the number of primary shards. So, if we have 2 primary shards, we might split it to 4, 6, 8 shards, and so on.

First, let's make the index read-only:

PUT /users/_settings { "settings": { "index.blocks.write": true } }
Then, we can split the number of shards:

POST /users/_split/users_v2 { "settings": { "index.number_of_shards": 2 } }
After this operation, Elasticsearch will create a new index called users_v2 with 2 shards. If we don't want our clients to point to the newly created index, we can use the aliases feature.

Deleting an index

Let's remove the old users index, it's as simple as it looks like:

DELETE /users

How sharding works in Elasticsearch

Lucas Rivelles — Mon, 06 Jun 2022 15:16:59 +0000

Elasticsearch is a cool distributed database that allows us to store, search and analyze structured and no-structured data. One of the most common use cases is the high performant full text search, which allows users to retrieve data based on a text input. To do so, it uses the inverted index mechanism of Apache Lucene.

Basic Architecture

Data in Elasticsearch is organized in indexes, comparable to tables on relational databases. An index is stored directly within a node or in a cluster, which is formed by more than one node, which can be physical or virtual machines. A single node architecture can be represented as follows.

This is pretty straight-forward as we have all of our data stored within a single node. That is usually the case when we want to test some features on a local environment or if we want a simple development environment. However, it's obviously not recommended for production if we want an acceptable level of availability.

Sharding

The previous architecture has several draw-backs. If we want to scale for data storage or throughput, we would need to vertically scale the node by adding more resources to it. Since that is not as simple as it looks like, usually this is not what we look for.

To achieve our scaling purposes, Elasticsearch provides a sharding mechanism. But how does it work?

Imagine that our previous node has a 500GB capacity, if we have 800GB of data to be stored it obviously will not be able to handle it. Sharding is a way used to divide our indices into smaller pieces, in which each piece of data is called a shard. Let us see how it looks like in our example.

Now we can have our index divided into 2 shards, increasing the amount of data that can be stored. We can also have more than one shard per node, in case we want to scale other nodes in the future.

Aside from improving storage, sharding also allows us to improve the performance of operations in our cluster. Since we have our data distributed in more than one node, they may be parallelized, making multiple machines work on the same query.

Trade-offs

What is the trade-off of sharding our index? If we have an improvement of both storage and performance, why not define an incredibly high number of shards? By having too many shards, our cluster needs to maintain metadata state of all of our shards in order to operate, this may lead us to poor performance of operations, so we need to put an effort on planning correctly the number of shards that we need. Although we don't have a silver bullet to make this planning, we can find insights here.

Considerations

Sharding is a mechanism that provides us ways to scale our cluster horizontally, both in storage and performance. When configuring it, it will split our index in smaller pieces of data.

We can define more than one shard per node if we plan to scale out in the future, however, we need to be aware of the effects of oversharding and avoid it.

We`ve seen that sharding our data makes us improve our storage and performance, but what happens if one of our node becomes unavailable? In our architecture, we may lose big parts of our data. Is there a way to avoid it? In the next article we will talk about replication and how it helps solving this problem.

DEV Community: Lucas Rivelles

How indexes power your database

Database Indexes

A practical example

Conclusion

Database Isolation Levels: A Practical Guide

Table of Contents

Environment Setup

Transactions and ACID Properties

Isolation Levels

Read Uncommitted - The Weakest One

Transaction 1 execution

Transaction 2 execution:

Read Committed - Preventing Dirty Reads

Transaction 1 execution

Transaction 2 execution

Transaction 1 execution

Transaction 2 execution

Repeatable Read (Snapshot Isolation)

Transaction 1 execution

Transaction 2 execution

Transaction 1 execution

Transaction 2 execution

Serializable - The Strongest One

Transaction 1 execution

Transaction 2 execution

Conclusion

References

Elasticsearch: The keyword data type

Elasticsearch: Object and Nested Data Types

Data Types

The object data type

The nested data type

Document Analysis in Elasticsearch

Introduction to analysis

Character filters

Tokenizer

Token filters

Using the analyze API

Managing indexes in Elasticsearch

Creating an index

Updating properties

The split operation

Deleting an index

How sharding works in Elasticsearch

Basic Architecture

Sharding

Trade-offs

Considerations