DEV Community: NYXa

Using HTTP Headers for faster Responses

NYXa — Tue, 07 Nov 2023 21:48:35 +0000

Intro

There is a history: You got a ticket because a specific API route is slow, and it's your job to fix that. Sounds familiar, right? Your first thought is to make whatever operation there fast, but it's not possible in this case. The second option is to give more resources to the service, but that does not sound like a real solution. The third option is to cache with Redis/memcached to avoid paying the penalty for every request. But what if I told you about a "free" solution that is as simple as adding some headers to the response?

The solution itself is tied to where the server is hosted, or at least where the domain exists. Each platform has its own ways to do so. For our example, we will use Cloudflare for the sake of simplicity.

Summary

Intro
Setup
Explanation
Closing thoughts
References

Setup

For our example we need:

Cloudflare account
Hono project 🔗
Three routes
- / to render a html page
- /no-cache to return a data without cache
- /cached to return cached data

After cloning the project, run npm run deploy to deploy the project to cloudflare workers.

Opening the page will you see a page with two dates.

Waiting some few seconds and reloading the page.

Without looking at the code, you may think that is a complex setup that is very specific. Not really. Here is the required code for caching the API response.

return new Response(new Date().toISOString(), {
  headers: {
    'Cache-Control': 'public, max-age=10' // keep the cache for 10 seconds
  }
})

Just like that, we cached using http and without redis.

Explanation

Cloudflare has cache headers so it can cache fetch requests made to their own domain, in my case: https://[project].stneto.dev. These headers aren't tied to a specific provider, so each of them can have their own version, such as: Netlify, Vercel, AWS Api Gateway and the list can go on.

The code example did show some JavaScript code, but the caching itself isn't tied to a language or a framework, so it's possible to do something like this as well:

Go (native http handler)

func handler(w http.ResponseWriter, r *http.Request) {
    w.Header().Set("Cache-Control", "max-age=3600") // Cache for 1 hour (in seconds)
    w.Header().Set("Expires", "Tue, 01 Jan 2024 00:00:00 GMT") // Cache expiration date and time
    w.Header().Set("ETag", "123456") // ETag for conditional requests


    w.Write([]byte("Hello 🤠"))
}

Rust (Axum)

async fn handler(_req: Request<Body>) -> Response<Body> {
    // Set Cache-Control header to enable caching for 1 hour
    Response::builder()
        .header("Cache-Control", "max-age=3600")
        .body(Body::from("Hello 🤠"))
        .unwrap()
}

Closing thoughts

The know-how to better use the platform can do wonders for you, as the solution is there "for free," and you just need to know how to use it. There is a difference between using what the platform offers to you and a specific platform feature. Using response headers between platforms is quite simple, as you can just replace them, and you are good to go. But using some very specific behavior tied to a platform can be quite dangerous if you ever need to move away from there. It's about the diminishing returns; staying "platform-free" can be nice because you have more flexibility on where the service runs, but at the same time, you won't be using 100% of what the platform offers to you.

References

Testable Go Code

NYXa — Thu, 02 Nov 2023 23:17:57 +0000

Maybe you are new to testing, maybe you are new to Golang, or maybe you are curious about how to test your code in golang. In this article i'm going to take some "bad" go code and add tests to it, in the process we will need to also improve the code itself to make it testable, so even if you don't write tests, you can make adjustments to your own code.

Summary

What are tests and it's objectives?
What are the different types of tests?
Now into a real world code and testing it
Closing thoughts
References

What are tests and it's objectives?

Code testing exists with the objective to make sure that your code behaves the same after you add and remove code, with the passing of time, or even modify the code itself. They don't guarantee that your code is bug-free, neither that you don't have unknown behavior, they just make sure that given inputs it will do what you expect. Don't think that just because you wrote tests that your code is perfect and there is no error within, they don't replace QA, logging and tracing. Use them as guides, but don't blindly follow them as rule books on how your code behaves

What are the different types of tests?

If you are an experienced developer you can guess some times, but in this article i'm going to show only three of them for the sake of time and explanations.

Type testing
Unit testing
Integration testing

Type testing

This kind of test is the most common type of them. They are the red line bellow the code or a compilation error. Any language with a decent type system is capable of doing this kind of test for you. For free. Really.

// src/main.rs
fn main() {
    let a = 1 + "a";
}

// cargo build --release
   Compiling something v0.1.0 (/private/tmp/something)
error[E0277]: cannot add `&str` to `{integer}`
 --> src/main.rs:2:15

I did not write any kind of test, but there was a implicit test there.

Unit testing

A very common and prolific kind of test. The focus of unit testing is to check pure functions, not creating any side effects. If you run the same function 100 times, the same expected result should happen 100 times.

// sum.go
package math

// Sum takes a slice of integers and returns their sum.
func Sum(numbers []int) int {
    sum := 0
    for _, num := range numbers {
        sum += num
    }
    return sum
}

// sum_test.go
package math_test

import "testing"

func TestSum(t *testing.T) {
    input := int[]{1, 2, 3}

    result := Sum(input)
    expected := 6

    if result != expected {
        t.Errorf("Expected %d, but got %d for input %v", expected, result, input)
    }
}

Integration testing

A more specific kind of test. The object of the test is to integrate different parts of the system, parts that can fail and you can't control. Be like accessing a external source like a database, or using some system call like reading a file or writing to one. It is common the creation of mocks, stubs and/or spies to avoid the flaky nature of the execution.

// file_writer.go
package file

import (
    "io/ioutil"
    "os"
)

// WriteToFile writes content to a file with the given filename.
func WriteToFile(filename, content string) error {
    return ioutil.WriteFile(filename, []byte(content), 0644)
}


// file_writer_test.go

package file

import (
    "io/ioutil"
    "os"
    "testing"
)

func TestWriteToFile(t *testing.T) {
    // Define a test filename and content.
    filename := "testfile.txt"
    content := "This is a test file."

    // Clean up the file after the test.
    defer func() {
        err := os.Remove(filename)
        if err != nil {
            t.Errorf("Error deleting test file: %v", err)
        }
    }()

    // Call the function to write to the file.
    err := WriteToFile(filename, content)
    if err != nil {
        t.Fatalf("Error writing to file: %v", err)
    }

    // Read the file to verify its content.
    fileContent, err := ioutil.ReadFile(filename)
    if err != nil {
        t.Fatalf("Error reading from file: %v", err)
    }

    // Check if the content matches the expected content.
    if string(fileContent) != content {
        t.Errorf("File content doesn't match. Expected: %s, Got: %s", content, string(fileContent))
    }
}

Now into a real world code and testing it

For our execution code let's use the code below

// pkg/database.go
package pkg

import (
    "log"

    "github.com/jmoiron/sqlx"
    _ "github.com/lib/pq"
)

var connection *sqlx.DB

func InitConnection() {
    db, err := sqlx.Connect("postgres", "user=postgres password=postgres dbname=lab sslmode=disable")
    if err != nil {
        log.Fatalln("failed to connect", err)
    }

    if _, err := db.Exec(getSql()); err != nil {
        log.Fatalln("failed to execute sql", err)
    }

    connection = db
}

// not the best way to do this, but it works for this context
func getSql() string {
    return `
        create table if not exists posts
        (
            id         serial primary key,
            title      varchar(255) not null,
            body       text         not null,
            created_at timestamp default current_timestamp,
            updated_at timestamp default current_timestamp
        );

        create table if not exists comments
        (
            id         serial primary key,
            post_id    int  not null references posts (id) on delete cascade,
            body       text not null,
            created_at timestamp default current_timestamp
        );
    `
}

// pkg/service.go
package pkg

type Post struct {
    ID        int       `db:"id" json:"id"`
    Title     string    `db:"title" json:"title"`
    Body      string    `db:"body" json:"body"`
    CreatedAt string    `db:"created_at" json:"created_at"`
    UpdatedAt string    `db:"updated_at" json:"updated_at"`
    Comments  []Comment `json:"comments"`
}

type Comment struct {
    ID        int    `db:"id" json:"id"`
    Body      string `db:"body" json:"body"`
    PostID    int    `db:"post_id" json:"-"`
    CreatedAt string `db:"created_at" json:"created_at"`
}

func GetPostsWithComments() ([]Post, error) {
    var posts []Post

    if err := connection.Select(&posts, "select * from posts"); err != nil {
        return nil, err
    }

    for i := range posts {
        if err := connection.Select(&posts[i].Comments, "select * from comments where post_id = $1", posts[i].ID); err != nil {
            return nil, err
        }
    }

    return posts, nil
}

// main.go
package main

import (
    "encoding/json"
    "fmt"
    "log"

    "github.com/stneto1/better-go-article/pkg"
)

func main() {
    pkg.InitConnection()

    posts, err := pkg.GetPostsWithComments()
    if err != nil {
        log.Fatalln("failed to get posts", err)
    }

    jsonData, err := json.MarshalIndent(posts, "", "  ")
    if err != nil {
        log.Fatalln("failed to marshal json", err)
    }

    fmt.Println(string(jsonData))
}

The code above does:

Initializes the global connection
Fetch all posts with their comments
Print as pretty json for the terminal

Simples and straightforward. Now let's add tests to the main code, GetPostsWithComments.

// pkg/service_test.go
package pkg_test

import (
    "testing"

    "github.com/go-playground/assert/v2"
    "github.com/stneto1/better-go-article/pkg"
)

func TestGetPostsWithComments(t *testing.T) {
    posts, err := pkg.GetPostsWithComments()

    assert.Equal(t, err, nil)
    assert.Equal(t, len(posts), 0)
}

After running the code an error should appear, as the test was ran but the global connection was not initialized.

package pkg_test

import (
    "testing"

    "github.com/go-playground/assert/v2"
    "github.com/stneto1/better-go-article/pkg"
)

func TestGetPostsWithComments(t *testing.T) {
    pkg.InitConnection() // remember to call before each test

    posts, err := pkg.GetPostsWithComments()

    assert.Equal(t, err, nil)
    assert.Equal(t, len(posts), 0)
}

Now you run the tests and they all pass, nice job. But if you modify the database, the test may fail in the future, as there is no guarantee the state of the database when the tests run.

What are the issues with the current code:

Real DB connection
Global connection
N+1 queries
Connection was not closed
Leaky abstraction → Output model = DB model

Let's fix some of those issues

// pkg/database.go
func InitConnection() *sqlx.DB {
    db, err := sqlx.Connect("postgres", "user=postgres password=postgres dbname=lab sslmode=disable")
    if err != nil {
        log.Fatalln("failed to connect", err)
    }

    if _, err := db.Exec(getSql()); err != nil {
        log.Fatalln("failed to execute sql", err)
    }

    return db
}

//pkg/service.go
func GetPostsWithComments(conn *sqlx.DB) ([]Post, error) {
    // redacted
}


// main.go
func main() {
    conn := pkg.InitConnection()
    defer conn.Close()

    posts, err := pkg.GetPostsWithComments(conn)
    // redacted
}

// pkg/service_test.go

package pkg_test

import (
    "testing"

    "github.com/go-playground/assert/v2"
    "github.com/stneto1/better-go-article/pkg"
)

func TestGetPostsWithComments(t *testing.T) {
    conn := pkg.InitConnection()
    defer conn.Close()

    posts, err := pkg.GetPostsWithComments(conn)

    // redacted
}

Now the function GetPostsWithComments gets its connection as a parameter, so we can test a more controlled connection. We can also close connection after running the tests.

Real DB connection
~~Global connection~~
N+1 queries
~~Connection was not closed~~
Leaky abstraction → Output model = DB model

For the next issue, a real database connection.

We need a postgres connection for our current tests, if you run in an environment that does not have said connection, we can't run our tests. Let's improve it then. Look at the database connection type *sqlx.DB, not a postgres specific connection, so as long as we provide a valid sqlx connection, we can be sure that our tests can execute easily. And for that we can use sqlite, both in memory or writing to a single file, as SQL for the most part is a spec language, we can swap sqlite and postgres depending on where we are running.

// pkg/database.go

// redacted

func InitTempDB() *sqlx.DB {
    // This commented line is to create a temporary database in /tmp,
    // in case you want to access the file itself
    // db, err := sqlx.Connect("sqlite3", fmt.Sprintf("file:/tmp/%s.db", ulid.MustNew(ulid.Now(), nil).String()))
    db, err := sqlx.Connect("sqlite3", ":memory:")
    if err != nil {
        log.Fatalln("failed to connect", err)
    }

    if _, err := db.Exec(getSql()); err != nil {
        log.Fatalln("failed to execute sql", err)
    }

    return db
}

// pkg/service_test.go
package pkg_test

import (
    "testing"

    "github.com/go-playground/assert/v2"
    "github.com/stneto1/better-go-article/pkg"
)

func TestGetPostsWithComments(t *testing.T) {
    conn := pkg.InitTempDB()
    defer conn.Close()

    posts, err := pkg.GetPostsWithComments(conn)

    assert.Equal(t, err, nil)
    assert.Equal(t, len(posts), 0)
}

Our main code does not change, as we need an actual connection to execute our app in production, but for tests we can use in memory sqlite to have a "clean" database for each test.

~~Real DB connection~~
N+1 queries
Leaky abstraction → Output model = DB model

Now let's fix out last issues

We still make n+1 queries to our database, one query for the posts, and n queries for the comments. For that let's create a business struct.

// pkg/service.go

// redacted

type postsWithCommentsRow struct {
    PostID           int    `db:"posts_id"`
    PostTitle        string `db:"posts_title"`
    PostBody         string `db:"posts_body"`
    PostCreatedAt    string `db:"posts_created_at"`
    CommentID        int    `db:"comments_id"`
    CommentBody      string `db:"comments_body"`
    CommentCreatedAt string `db:"comments_created_at"`
}

func GetPostsWithComments(conn *sqlx.DB) ([]Post, error) {
    var rawPosts []postsWithCommentsRow

    if err := conn.Select(&rawPosts, `
        select posts.id       as posts_id,
            posts.title         as posts_title,
            posts.body          as posts_body,
            posts.created_at    as posts_created_at,
            comments.id         as comments_id,
            comments.body       as comments_body,
            comments.created_at as comments_created_at
        from posts
                left join comments on posts.id = comments.post_id
        order by posts.id;
    `); err != nil {
        return nil, err
    }

    posts := make([]Post, 0)

OuterLoop:
    for _, rawPost := range rawPosts {
        post := Post{
            ID:        rawPost.PostID,
            Title:     rawPost.PostTitle,
            Body:      rawPost.PostBody,
            CreatedAt: rawPost.PostCreatedAt,
        }

        for _, post := range posts {
            if post.ID == rawPost.PostID {
                continue OuterLoop
            }
        }

        posts = append(posts, post)
    }

    for _, rawPost := range rawPosts {
        for i, post := range posts {
            if post.ID == rawPost.PostID {
                comment := Comment{
                    ID:        rawPost.CommentID,
                    Body:      rawPost.CommentBody,
                    CreatedAt: rawPost.CommentCreatedAt,
                }

                posts[i].Comments = append(posts[i].Comments, comment)
            }
        }
    }

    return posts, nil
}

Our code increased quite a bit. But what did it changed? First of all, now we make only one query to the database. We also manually map query result into a business rule structure in the Post and Comments structs. We also separated the database struct postsWithCommentsRow from our external structs.

~~N+1 queries~~
~~Leaky abstraction → Output model = DB model~~

And just like that, we fixed out last two issues.

Closing thoughts

After understanding a little more about tests, we took some go code, wrote some tests and improved the code. So what now? Even if you don't like go, you can still understand the concepts from what we discussed here and use in your own code. Testing is one of the skills that you learn with passing the time and acquiring more experience.

References

https://stock.adobe.com/search?k=testing&asset_id=95004747

Cost of Serialization

NYXa — Wed, 18 Oct 2023 15:06:21 +0000

In the last article we covered about checking the performance of any given query, but that is not the only reason that will make a “slow query”, moving the data from the database to whoever requested it can be expensive as well, sometimes more than the query itself. In this article i will try to show you why that matters and some information about it.

For this article we will focus into some parts of it, mostly from sending data from the database to the backend, and some others aspects:

Summary

What usually happens
Data being transported
Double serialization
Closing thoughts

What usually happens

When you have a decloupled client from the backend, you usually have something like:

Client makes request
Backend receives request
Authenticate the user
- Maybe with a db call to fetch the user
Make one or more db calls
- Having a single query when you can is the best way to avoid eating the tax to request data to the database
Send the response back to the client
- Maybe you transform the data before sending it back

What you don’t see in the list are the multiple serialization and deserialization costs associated with operations. Fetching from the database or any external source of information, will make you pay the deserialization cost, and when you serialize back to the client you pay for it again.

“Why i pay for it?” you ask, every point of interaction of data: database, server, client; each one of them has its own domain for handling data, even if its the same domain (typescript server/client) you still need to send data between them, and for that you need a common language to be able to send and for the receiver to understand and parse it.

Data being transported

How does data being transported look? Postgres has two data formats: text and binary.

# Packet: t=1695825669.456395, session=213070643341250
PGSQL: type=Query, F -> B
QUERY query=select * from users limit 200;

# Packet: t=1695825669.458287, session=213070643341250
PGSQL: type=RowDescription, B -> F
ROW DESCRIPTION: num_fields=3
  ---[Field 01]---
  name='id'
  type=1043
  type_len=65535
  type_mod=4294967295
  relid=57371
  attnum=1
  format=0
  ---[Field 02]---
  name='username'
  type=25
  type_len=65535
  type_mod=4294967295
  relid=57371
  attnum=2
  format=0
  ---[Field 03]---
  name='created_at'
  type=1114
  type_len=8
  type_mod=4294967295
  relid=57371
  attnum=3
  format=0

# Packet: t=1695825669.458287, session=213070643341250
PGSQL: type=DataRow, B -> F
DATA ROW num_values=3
  ---[Value 0001]---
  length=26
  value='01H7GXCFN7K4ZQEDP59G31SY4D'
  ---[Value 0002]---
  length=10
  value='username-1'
  ---[Value 0003]---
  length=26
  value='2023-08-10 23:44:21.689678'

# Packet: t=1695825669.458287, session=213070643341250
PGSQL: type=DataRow, B -> F
DATA ROW num_values=3
  ---[Value 0001]---
  length=26
  value='01H7GXCFN7865YD7Q16BXEAB8N'
  ---[Value 0002]---
  length=10
  value='username-2'
  ---[Value 0003]---
  length=26
  value='2023-08-10 23:44:21.689678'

...

# Packet: t=1695825677.943246, session=213070643341250
PGSQL: type=BackendKeyData, B -> F
BACKEND KEY DATA pid=1463956811, key=1412773965

# Packet: t=1695825677.943246, session=213070643341250
PGSQL: type=NotificationResponse, B -> F
NOTIFICATION RESPONSE pid=1970496882, channel='name-8785', payload=''

...

This is part of the data being sent from the backend (postgres) to a client (psql);

PGSQL: type=RowDescription, B -> F: This part of the message indicates that it is a "RowDescription" message. This message is being sent from the backend (B) to the frontend (F), typically as part of the result set description when a query is executed.
ROW DESCRIPTION: num_fields=3: This section tells you that the RowDescription message is describing a row with three fields (columns).
[Field 01]: This part provides information about the first field (column) in the row.

name='id': This field is named "id," indicating that it corresponds to a column named "id" in the result set.
type=1043: The "type" field specifies the data type of the column. In this case, the type is represented as an integer, and "1043" corresponds to a specific data type in PostgreSQL.
type_len=65535: The "type_len" field indicates the length of the data type. In this case, it's set to a very large value, 65535, which might be used to represent a variable-length character string (e.g., varchar) with an undefined maximum length.
type_mod=4294967295: The "type_mod" field typically represents the data type's modifier. In this case, the large value "4294967295" might signify that there is no modifier for the data type.
relid=57371: The "relid" field often refers to the OID (Object ID) of the table to which the column belongs. In this case, "57371" would be the OID of the related table.
attnum=1: The "attnum" field represents the attribute number of the column in the table. It's set to "1," indicating that this is the first column in the table.
format=0: The "format" field specifies the format of the data. A value of "0" indicates that the data is in text format. In binary mode, this value would be set to "1."

If you have a deeper knowledge you may think: “why not use something like protobuf? you can make the process faster!”

Yeah, true. But with protobuf you need to know beforehand the shape of the data being sent from both server and client, and that defeat the proposit of fetching less data or using sql aggregations.

Double Serialization

Serialization in the article references taking the data, transforming into a shared common language (json, yaml, xml, etc…), and sending it off to whoever requested it to be deserialized into a known data.

But what is double serialization? Double serialization is when data is being serialized/deserialized twice for the same request. Wonder how? Database → Api → Client. Each arrow means that data was serialized, shipped, then deserialized. If you aren’t careful with the data being passed around, you can end up with a lot of garbage to handle and performance issues as it can be a heavy operation. The more data needs to be transported, the more garbage is created.

async function someFunction(_: Request) {
  // Data is first serialized in the database, shipped,
  // then deserialized here to have a valid data object
  const data = await fetchFromDb()

  // Maybe you need to transform the data to aggregate or remove fields
  // const transformedData = someMapFunction(data);

  // You serialize data again here,
  // shipped for the client deserialize it,
  // then transform into something useful for them
  return new Response(JSON.stringify(data), {
    status: 200,
    headers: {
      'Content-Type': 'application/json'
    }
  })
}

Closing thoughts

Perhaps you've never had to think about how much information you search for and send. It's so much simpler to gather everything without being too careful, but it's these little details that start creating problems in the form of a snowball.

Thinking pessimistically:

You fetched more information than you needed.
The server had to do more work to process and send the information.
There are more data packets to be sent over the network, increasing the chance of problems and packet resending, which raises latency.
The application had more work to do in processing more information than necessary.
- If it's an application with a more "relaxed" approach to memory, the application itself has become heavier (garbage collection).
With more information to be sent, the response has also become heavier. Both in transforming the information from one domain to a common format and in sending all that information over the network again.
More information was transmitted, and now you also start paying for data transmission over the network.
The client receiving the information will need to put in more effort to process the information.

The simple decision to fetch more information than necessary created a huge snowball effect. Perhaps, when everything needs to move quickly for the delivery of something, it makes sense to reconsider later how much information is being transmitted. But thinking that this problem doesn't exist is a considerable risk and can turn into a massive snowball.

References

https://www.wireshark.org/ for checking the packages

Understanding database queries

NYXa — Mon, 09 Oct 2023 22:13:00 +0000

Preface: This article is not a tutorial on how to debug database performance, nor is it about how to fix it. Instead, it is a guide on how to do a basic lookup into the query operation and an attempt to explain some related concepts.

In the world of speed and performance, every single point of traction can make a big difference in the delivery of anything. Every main service probably has a data source where data is stored and served, so being able to retrieve data faster can lead to better performance and an overall reduction in costs.

But how we can measure performance? Most database offer some level of “performance debug” tools for that, in this article im using postgres and explain analyze, but the idea is the same for other sql services.

Summary

First Query and Index
Explaining the index
More complex queries
Bitmaps
Mixing indexed and non-indexed columns
What to take from searches and indexes?
KV Stores
What are some examples of KV stores?
What is the main difference between a KV store and a SQL database?
When to use one or another
References

First Query and Index

The first case is a very basic one, just a table with a id and a username column with the given sql.

CREATE TABLE IF NOT EXISTS "users" (
        "id" varchar PRIMARY KEY NOT NULL,
        "username" text NOT NULL
);

Our first query is to retrieve a record with a given id

explain analyze
select *
from users
where id = '01H7BZ4XC9HTG7H4DTH7B3AAY5';

Index Scan using users_pkey on users  (cost=0.28..8.29 rows=1 width=47) (actual time=0.033..0.035 rows=1 loops=1)
  Index Cond: ((id)::text = '01H7BWSR1GJCJBKGGPV5NS3YNY'::text)
Planning Time: 0.070 ms
Execution Time: 0.056 ms

Index Scan using users_pkey on users: This part of the output indicates that the database engine is performing an index scan on the "users" table using the "users_pkey" index. An index scan is a method of retrieving rows from a table using an index to look up the rows that match the specified condition.
(cost=0.28..8.29 rows=1 width=47): This section provides information about the estimated cost of executing the query. The cost is an arbitrary unit used by the database optimizer to compare query execution strategies. In this case, the estimated cost of the query is between 0.28 and 8.29. It also estimates that there will be 1 row returned, and the width of each row is 47 bytes.
(actual time=0.033..0.035 rows=1 loops=1): This part of the output shows the actual execution time of the query. It took between 0.033 and 0.035 milliseconds to execute the query. It also indicates that only 1 row was returned, and the query executed in a single loop.
Index Cond: ((id)::text = '01H7BWSR1GJCJBKGGPV5NS3YNY'::text): This line specifies the condition used in the index scan. It's comparing the "id" column of the "users" table, cast to text, to the value '01H7BWSR1GJCJBKGGPV5NS3YNY' also cast to text. This condition is used to identify the row(s) that match the WHERE clause of your query.
Planning Time: 0.070 ms: This line provides the time it took to plan the query, which is the process of determining the most efficient way to execute the query. In this case, it took 0.070 milliseconds to plan the query.
Execution Time: 0.056 ms: This line indicates the total execution time of the query, which is the time it took to execute the query after planning. In this case, it took 0.056 milliseconds to execute the query.

But what if we only wanted the indexed value itself? We can skip the lookup in the table and return the index value itself.

explain analyze
select id
from users
where id = '01H7BZ4XC9HTG7H4DTH7B3AAY5';

Index Only Scan using users_pkey on users  (cost=0.29..4.30 rows=1 width=27) (actual time=0.024..0.025 rows=1 loops=1)
  Index Cond: (id = '01H7BZ4XC9HTG7H4DTH7B3AAY5'::text)
  Heap Fetches: 0
Planning Time: 0.091 ms
Execution Time: 0.041 ms

Scanning index and maybe looking into row

For our next query, now we make a more realistic one: find the user with a given username

explain analyze
select *
from users
where username = 'username-999';

Seq Scan on users  (cost=0.00..22.50 rows=1 width=47) (actual time=0.179..0.180 rows=1 loops=1)
  Filter: (username = 'username-999'::text)
  Rows Removed by Filter: 999
Planning Time: 0.056 ms
Execution Time: 0.208 ms

We are faced with a different scan, now with Seq Scan and as the name implies, we sequentially scanned through the table looking rows with matching conditions. At the time of the query, the are 1000 rows in the table, only one with ‘username-999’ as a column value, but we had to scan the entire table as there was no way to guarantee that only that row had the value.

But lets say that there is a new business rule about unique usernames.

ALTER TABLE "users" ADD CONSTRAINT "users_unique_username" UNIQUE("username");

Making the same query again will result in a different scan.

explain analyze
select *
from users
where username = 'username-999';

Index Scan using users_unique_username on users  (cost=0.28..8.29 rows=1 width=47) (actual time=0.023..0.024 rows=1 loops=1)
  Index Cond: (username = 'username-999'::text)
Planning Time: 0.084 ms
Execution Time: 0.038 ms

Explaining the index

Why did it change from a Seq to an Index scan? Previously, we had no way to find which record had the username without scanning the entire table for the matching row(s). However, now we can have better assurance that the value is unique when modifying the data, and a faster lookup as we can scan the index more efficiently.

Scanning before indexing

Scanning after indexing

Indexes are amazing when you think about them, but why don't we index everything? There are costs associated with indexes, both in terms of storage and logic. When you mutate the table, the indexes also need to update their values, which adds costs to the overall database.

More complex queries

Now lets expend more into the possible search aspect of a table with a more complex one: Just a simple posts table with a relationship with the users table.

CREATE TABLE IF NOT EXISTS "posts" (
        "id" varchar PRIMARY KEY NOT NULL,
        "title" text NOT NULL,
        "body" text NOT NULL,
        "published" boolean DEFAULT false NOT NULL,
        "created_at" timestamp DEFAULT now(),
        "user_id" varchar NOT NULL
);
--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "posts" ADD CONSTRAINT "posts_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

For our next example query: search all published posts.

explain analyze
select *
from posts
where published is true;

Seq Scan on posts  (cost=0.00..2538.00 rows=50410 width=84) (actual time=0.012..15.176 rows=49868 loops=1)
  Filter: (published IS TRUE)
  Rows Removed by Filter: 50132
Planning Time: 0.074 ms
Execution Time: 17.384 ms

Now we are faced with a situation similar to before: a Seq Scan for a "simple" comparison. The first thought is to simply add an index. Yes, but not completely right. We only care about the published posts, so we can index them only based on that aspect, as we don't care about the other ones.

CREATE INDEX IF NOT EXISTS "posts_published_index" ON "posts" ("published") WHERE published IS true;

explain analyze
select *
from posts
where published is true;

Bitmap Heap Scan on posts  (cost=438.23..2480.33 rows=50410 width=84) (actual time=1.931..11.999 rows=49868 loops=1)
  Recheck Cond: (published IS TRUE)
  Heap Blocks: exact=1538
  ->  Bitmap Index Scan on posts_published_index  (cost=0.00..425.63 rows=50410 width=0) (actual time=1.658..1.659 rows=49868 loops=1)
Planning Time: 0.066 ms
Execution Time: 14.315 ms

Running the same query now give us a new scan method: Bitmap.

Bitmaps

Bitmap Index uses bits (0s and 1s) to represent whether a particular value exists or not in the column. Each bit corresponds to a row in the table, and if a value is present for a row, its corresponding bit is set to 1; otherwise, it's set to 0. The Bitmap Heap is where the database collects all the relevant rows from the table based on these conditions.

https://www.cybertec-postgresql.com/en/postgresql-indexing-index-scan-vs-bitmap-scan-vs-sequential-scan-basics/

Another case to access the data is from the users side, as follows:

explain analyze
select *
from posts
where user_id = '01H7BZ4XC9HTG7H4DTH7B3AAY5'

Seq Scan on posts  (cost=0.00..2788.00 rows=10 width=84) (actual time=1.265..15.436 rows=11 loops=1)
  Filter: ((user_id)::text = '01H7BZ4XC9HTG7H4DTH7B3AAY5'::text)
  Rows Removed by Filter: 99989
Planning Time: 0.066 ms
Execution Time: 15.452 ms

You can start to see the pattern around accessing the data. When you have a case where you have a clear path on how to access the data, you can check if the index is worth the case.

CREATE INDEX IF NOT EXISTS "posts_user_id_index" ON "posts" ("user_id");

explain analyze
select *
from posts
where user_id = '01H7BZ4XC9HTG7H4DTH7B3AAY5';

Bitmap Heap Scan on posts  (cost=4.50..42.20 rows=10 width=84) (actual time=0.054..0.073 rows=11 loops=1)
  Recheck Cond: ((user_id)::text = '01H7BZ4XC9HTG7H4DTH7B3AAY5'::text)
  Heap Blocks: exact=10
  ->  Bitmap Index Scan on posts_user_id_index  (cost=0.00..4.49 rows=10 width=0) (actual time=0.047..0.047 rows=11 loops=1)
        Index Cond: ((user_id)::text = '01H7BZ4XC9HTG7H4DTH7B3AAY5'::text)
Planning Time: 0.372 ms
Execution Time: 0.096 ms

Mixing indexed and non-indexed columns

We indexed both published posts and user posts, but what if we wanted both of them?

explain analyze
select *
from posts
where user_id = '01H7BZ4XC9HTG7H4DTH7B3AAY5'
  and published is true;

Bitmap Heap Scan on posts  (cost=4.49..42.20 rows=5 width=84) (actual time=0.100..0.117 rows=3 loops=1)
  Recheck Cond: ((user_id)::text = '01H7BZ4XC9HTG7H4DTH7B3AAY5'::text)
  Filter: (published IS TRUE)
  Rows Removed by Filter: 8
  Heap Blocks: exact=10
  ->  Bitmap Index Scan on posts_user_id_index  (cost=0.00..4.49 rows=10 width=0) (actual time=0.072..0.073 rows=11 loops=1)
        Index Cond: ((user_id)::text = '01H7BZ4XC9HTG7H4DTH7B3AAY5'::text)
Planning Time: 0.157 ms
Execution Time: 0.151 ms

We previously indexed both fields separately, but now we have both of them in the same query.

In this case, we can delve further into the query to better understand its planning aspect. By first index scanning the user_id, and then the published value, we can eliminate a larger portion of unwanted rows by examining the user_id. This is because there are more possibilities of having different values that we don't want. We can then check a smaller subset of the published filter.

We can also create a compounded index for the case above: user_id and published.

CREATE INDEX IF NOT EXISTS "posts_user_id_published_index" ON "posts" ("user_id", "published") WHERE "published" IS TRUE;


explain analyze
select *
from posts
where user_id = '01H7BZ4XC9HTG7H4DTH7B3AAY5'
  and published is true;

Bitmap Heap Scan on posts  (cost=4.33..23.54 rows=5 width=84) (actual time=0.045..0.051 rows=3 loops=1)
  Recheck Cond: (((user_id)::text = '01H7BZ4XC9HTG7H4DTH7B3AAY5'::text) AND (published IS TRUE))
  Heap Blocks: exact=3
  ->  Bitmap Index Scan on posts_user_id_published_index  (cost=0.00..4.33 rows=5 width=0) (actual time=0.037..0.038 rows=3 loops=1)
        Index Cond: ((user_id)::text = '01H7BZ4XC9HTG7H4DTH7B3AAY5'::text)
Planning Time: 0.342 ms
Execution Time: 0.076 ms

But what happens if we need to add a non-indexed column to the query? Will an index not be used?

explain analyze
select *
from posts
where user_id = '01H7BZ4XC9HTG7H4DTH7B3AAY5'
  and published is true
and created_at > '2023-01-01';

Bitmap Heap Scan on posts  (cost=4.33..23.55 rows=5 width=84) (actual time=0.038..0.044 rows=3 loops=1)
  Recheck Cond: (((user_id)::text = '01H7BZ4XC9HTG7H4DTH7B3AAY5'::text) AND (published IS TRUE))
  Filter: (created_at > '2023-01-01 00:00:00'::timestamp without time zone)
  Heap Blocks: exact=3
  ->  Bitmap Index Scan on posts_user_id_published_index  (cost=0.00..4.33 rows=5 width=0) (actual time=0.028..0.028 rows=3 loops=1)
        Index Cond: ((user_id)::text = '01H7BZ4XC9HTG7H4DTH7B3AAY5'::text)
Planning Time: 0.115 ms
Execution Time: 0.071 ms

The query planner is smart enough to understand that a subset of the query conditions are indexed, so it can perform that faster subset first and then the "slower" conditions later.

What to take from searches and indexes?

Indexes can speed up data access, but they come at a cost. The key takeaway from the examples above is to learn the patterns of how you access your data. Once you've identified these patterns, you can create indexes or explore alternative approaches to accessing the data.

KV Stores

But what if the SQL query isn't fast enough? Are there any alternatives? Yes, we can use key-value (KV) stores for that.

Here's a simple analogy: a SQL database is like a giant list that you filter and merge with queries, while KV stores are like map/hashmap data structures that allow direct access to the data you're looking for.

Not a real metric value, just an example to understand

What are some examples of KV stores?

Redis: A classic one, slow query performance? Just cache it on redis =D
DynamoDB: A modern and dangerous one, the aws service to have O(1) performance (supposedly) when acessing the data
Cassandra (Scylla): The dark horse, you hear about it on giant scales like the discord trillion messages and such.

What is the main difference between a KV store and a SQL database?

In "classic" databases, there are many points of access to the data. You can jump from table1 to table2 to table3 to table n, as long as there is a logical connection between them. To take advantage of this, you have to plan what should happen in the query. Database engines are great at this. However, one drawback is that you must plan the entire process and execute it afterward. In Postgres, this is shown as Planning Time: N ms - Execution Time: N ms.

With KV stores, you can achieve almost linear data access at the expense of a rigid access pattern. You must access data using the key → value pattern every time. There is a base cost to using this pattern, as you must compute the key to locate the data. For small amounts of data, this cost may not be worth paying. However, you can tip the scale in favor of the initial cost for larger amounts of data.

When to use one or another

In general, SQL databases are more flexible and offer decent performance when you don't know the data access pattern. When using a KV store, you trade flexibility for faster read speeds. For some use cases, this tradeoff is acceptable, but not for all. However, SQL databases and KV stores are not mutually exclusive. You can have both at the same time.