DEV Community: Nir Tayeb

Creative database denormalization techniques

Nir Tayeb — Tue, 30 Jul 2024 19:18:50 +0000

In the early stages of application development, the data schema is typically designed with normalization as the guiding principle. However, as the application grows and tables expand, specific queries can become sluggish, negatively impacting the user experience. In such situations, it becomes essential to consider denormalizing the data.

What is denormalization?

Denormalization involves strategically duplicating a small amount of data across tables, reducing the need for joins that combine data from multiple sources. Another denormalization technique is maintaining precomputed data aggregations instead of calculating them in real time. While this approach introduces some data redundancy, it can significantly enhance query performance.
Some "textbook" examples:

The Classic Approach: Instead of joining the "orders" and "customers" tables, which might be large, to retrieve the customer name, we add the customer name as a column in the orders table.
The "Justin Bieber" Case: In a social media app, rather than maintaining the relationship of "users liked a post" and then aggregating the count of likes for a post, we keep the current likes_count in the posts table. This approach is named the "Justin Bieber" case because Instagram used it to manage likes for celebrity posts.
Short-Circuiting: In scenarios with three or more levels of joins, such as organization → users → categories → posts, to eliminate one or more join levels, we add organization_id and/or user_id directly to the posts table.

What are the drawbacks of denormalization?

After denormalizing the data, new problems arise, including the need for extra storage space due to data duplication. When a value changes, all its copies in other tables must be updated to avoid anomalies and discrepancies. Updating all the copies of the data can lead to longer write operations.

These are classic textbook examples of denormalization. Databases have evolved, and we have new tools to maintain our basic data model.

Materialized view

What is a materialized view?

A materialized view is a database object that includes a copy of a query's result. This way, we don't need to fetch, process, and combine the data from all the tables involved in the query or aggregate and group the data repeatedly.
Examples (postgres syntax):

CREATE MATERIALIZED VIEW MV_MY_VIEW
AS SELECT * FROM <table_name>;

The supported RDBMS are Oracle (>8i), PostgreSQL (>9.3, since 2013), SQL Server (> 2000), DynamoDB, and BigQuery.
MySQL doesn't support it out of the box.
Stream processing frameworks: Apache Kafka (since v0.10.2), Apache Spark (since v2.0), Apache Flink, Kinetica DB, Materialize, and RisingWave all support materialized views on data streams.

The benefits of using materialized views:

No need to change the DB schema
Faster data retrieval because it doesn't re-calculate but stores the results
It's possible to index the results.

The cons of using materialized views:

Usually, materialized views are not auto-refreshed, so data may not be fresh.
Maintainance outside of the code
Requires more storage

Offload as a Document

When I worked at Tapingo/GrubHub a few years ago, we aimed to decouple the ordering system from the central database. One reason, though not the primary one, was the frequent need to fetch related order data for various purposes, such as payments, POS display, ticket printing, Kitchen Display System, courier information, and order content.
We addressed this by creating a comprehensive document encapsulating all relevant data contexts and distributing it to various microservices. This document included duplicate data about shops and customers, which was accurate for the specific time. Each microservice had the autonomy to decide where and how long to store the document, allowing for fast query times by order-id, shop-id, and other indices.
The document could be stored in a cache like Redis or MongoDB or streamed/pushed to a message broker or stream processing system.

Use Array/JSON columns

At EasyTeam, we implemented a "take a break feature," allowing admins to define which breaks are available during a shift, which the worker then clocks in for.
A simple approach would be to create a many-to-many table between the scheduled shift and break types tables. However, since scheduled shifts are replicated frequently (new shifts every week), this would lead to an enormous many-to-many table. Instead, we used an array column on the scheduled shifts table to store the break types' IDs, with a GIN index on the column for faster searches.
While this method may compromise data integrity, it significantly reduces the stored data.

From my experience, joining a table with a JSON field and selecting the JSON field in the query (either with *or specifically) could hurt query performance.

In summary, denormalization helps fine-tune frequent heavy-read queries that lack performance. Use it wisely to avoid complex maintenance, extra storage costs, and data integrity.

This post was originally published on my newsletter, Percentile 99th. If you wish to learn how to improve application performance, I will write about it extensively there.

Latency Under the Lens

Nir Tayeb — Tue, 12 Mar 2024 15:59:21 +0000

In the past, I was usually trying to fine-tune the code of my application, either by coding better or using the most performant tools and techniques. But in several recent jobs, I've noticed that the performance issue was not actually in the code but was "around" the application.

For example:

Timeouts between the printer at the locations and my server - in the APM, the request processing time takes tens of milliseconds, so why a timeout?
Long query time to database - but the query was optimized and ran a few milliseconds on the query client when tested.

The issue was high latency between the client and the server because of a nonoptimal geolocation deployment of servers.

For example, the database deployed in the west region while the application server was in the east region. In another case I had, the app servers and the database were on two different continents (Europe and the U.S.)

Nowadays, this issue is too common because provisioning servers is so easy, trying to keep cloud costs cheap and the separation between developers and the cloud infrastructure maintainers.

Let's demonstrate the issue by exploring the latency difference between a few countries. We will use https://wondernetwork.com/pings, one of the greatest sites I found a couple of years ago, telling the story of the importance of latency.

The website runs about 30 pings and shows the average; a ping is the time for the round trip between the sender and receiver (Client ↔ Server).

So, to establish the first TCP connection between a client and a server (Browser and Application Server, App Server and DB, etc.) - it takes ping-time X 1.5 to pass the 3-Way handshake.

Then, each transferring of packet of data will take the ping time. A standard TCP packet is up to 1.5KB, so to pass 15KB of data, you wait for at least a ping-time X 10 packets if none of the packets dropped in the middle.

Now imagine how long it takes to pass 100 records of shop listings with their full catalog and item descriptions between an app server in Europe and a database server in the U.S. Or how long it takes the browser to download your single-page application from your servers.

There are many ways to decrease the latency of your app, which I will elaborate on in the following posts in the latency series. but to name a few:

Manually align the deployment of your servers in the same region on your cloud provider
Working with private networks inside your cloud provider
Using a CDN for static content
Keeping connections alive between sender and receiver

This post was originally published on my newsletter, Percentile 99th. If you wish to learn about the ways to decrease latency and more about application performance, I write about it extensively there.

Essential Database Optimizations Before Re-Architecting Your Backend

Nir Tayeb — Fri, 23 Feb 2024 09:23:56 +0000

In the last post, I covered six ways ORMS made writing poorly performant code easy. But it is just a part of the story.

Ensuring your applications run efficiently is essential. Before diving into complex solutions like caching, microservices, or rewriting code in different languages, it’s vital to solidify the fundamentals. By focusing on six key pillars, you can significantly boost your application’s performance, achieving up to an 80–90% improvement. Let’s explore these often-overlooked foundational techniques that can dramatically enhance the performance of your database-driven applications.

Side notes:

I may refer to RDBMS and SQL in the post, but these principles apply to NoSQL systems and beyond.
This post was also published in my newsletter, containing the 6 pillars with more depth.

Let's start,

Database version

New major versions are coming with new features and may include performance improvements. It is 200% right if we refer to PostgreSQL.

Benchmark made by EDB shows a 47% performance enhancement between PostgreSQL v9.6 and v15 and a 10.4% upgrade from v12 to v15. V16 has as well some minor improvements. You can read more in this post by "benchant.com".

Hardware

The database is the heart of your application. If it doesn't have enough computing resources, your whole application will be waiting for these resources to be available.

CPU

For small-medium size, you need a minimum of 4 cores for parallelism of your workloads, connection concurrencies, and background jobs done by the DB (i.e., journaling). Scale up with these considerations in mind:

Workload Characteristics: CPU-intensive workloads (e.g., complex calculations and heavy transactions) require more cores for better performance.
Concurrency: Higher concurrent connections and transactions can increase CPU requirements.
Database Size and Complexity: Larger databases or those with complex schemas and queries may benefit from additional CPU resources to handle the increased processing demands.

Memory

RAM impacts the database's ability to cache data, work with indexes, and process queries efficiently, significantly affecting overall performance. For small-medium sizes require at least 4GB of RAM and scale up by these considerations:

Database Working Set Size: Ideally, your server should have enough RAM to hold the frequently accessed ("hot") portion of your database (tables and indexes) in memory to reduce disk I/O.
Connection Overhead: PostgreSQL uses a multi-process architecture, where each connection has its dedicated process. More connections imply more RAM for connection overhead. As a rough estimate, plan for about 10 MB (or more) per connection.
Workload Type: Read-heavy workloads benefit from more RAM for caching, reducing read operations from disk. Write-heavy workloads may benefit less from caching but require sufficient RAM for sorting and transaction logs.

Storage

The storage size varies from one application to another, but what is important here is the type of storage and its IO performance:

Storage Type: SSDs should be your choice
Provisioned IOPS: Consider using "provisioned IOPS" storage for high-performance workloads to ensure consistent and fast disk I/O.
Storage Auto-scaling: Enable auto-scaling for storage to adjust as your database grows automatically.

Scaling up your system usually costs more money. Monitor your current resources' use closely and consider ways to decrease the use of the resources using query/structure optimizations (RAM/CPU/storage) before adding more resources.

Network proximity / Geographics:

It's best to keep your database and application server as close to each other as possible, network-wise so that they can communicate more efficiently. One way to do this is by provisioning them in the same region on your cloud service and putting them within the same private virtual network. Also, it's a good idea to use private IP addresses for communication between them rather than public IPs, which will help reduce the number of network hops and increase the speed of data transfer.

Connections Management

Opening a connection to the database is an expensive operation. It includes the DNS resolving, TCP handshake, TLS Handshake, authentication, and setting up internal resources such as session objects and watchdogs. So, you should be conscious about opening a connection to the DB and reusing them as much as possible.

The way to achieve it is by using connection pooling. It maintains a set number of connections and opens new ones until reaching a maximum value of connections. So, your application code should always have an open connection.

Amount of queries in a single request/transaction

Whether you use connection pooling or not, your database/pool has a limit on the maximum number of open connections. You should also verify the number of queries and their processing time so you won't get to the point where your code is waiting for a connection to be available instead of running your business logic.

What should you look for?

The N+1 problem
Querying too much unnecessary data:
- Filtering fetched data in code instead of in your queries.
- Aggregate fetched data in code instead of in a query (count/sum/averages/min/max/etc)
- Fetch fields without using them (look up big text fields or tables with a lot of columns)
Use joins instead of multiple separate queries.

Optimize query processing time.

Fine-tune condition evaluation with indexes

Imagine you have a table of users containing ID, name, and birth date. Your app has a search function for all the users who have a Birthday today. Usually, the database will scan the table data row by row and filter all the records with this birthday. Imagine this table has 1M records.

Indexes are like a sidecar to your tables; they contain a "table of contents" in various data structures, so when you query data, it will be possible to access the records without scanning all table content. The index contains the data of one or more of your columns, but instead of using a list to hold the data, it uses other data structures like trees and a hash table and divides the column value into different parts so the search will be faster. full table scan is O(n), while index scan usually is O(Log(n)) or O(1), depends on the index.

So now, instead of scanning the entire table to find users with a birthday, it checks the index, gets pointers for the records in the table (or, more accurately, to the exact pages in the filesystem), and then retrieves the data.

To understand why indexing is crucial, what to index, and what kind of indexes are available, you can read here.

Fine-tune data fetching with Partitioning

If you deal with big data and need more than indexing, partitioning the data will let you only query the relevant portion. For example, if partitioning the data by "Year," you will query only data from 2024, and all 2023 won't be retrieved from the storage if you haven't explicitly asked for it (partition is like a different storage unit). If most queries are in filtering by 2024, it will dramatically enhance your query performance.

Partitioning has overhead in maintenance, and it can lead to a spike in lock manager waits and negatively impact user query performance. Increasing smaller tables and multiple indexes and partitions can generate many locks per query, causing contention problems.

Based on my experience, focusing on these 6 pillars can significantly enhance the performance of the application by 80-90%, even before implementing any additional solutions such as caching, rewriting specific parts of the application in more performant languages, dividing into microservices, or using NoSQL.

The Six Pillars of Database-Driven Application Performance

Nir Tayeb — Thu, 22 Feb 2024 23:19:01 +0000

In the last post, I covered six ways ORMS made writing poorly performant code easy. But it is just a part of the story.

In this post, I will cover the bigger picture of working against a database and six significant topics that will most impact your application performance that you need to be conscious of and can decrease the need to add complexity to your system (like caching, multiple different DB's, etc.)

Side notes:

I may refer to RDBMS and SQL in the post, but these principles apply to NoSQL systems and beyond.
This post was also published in my newsletter, containing the 6 pillars with more depth.

Let's start,

Database version

New major versions are coming with new features and may include performance improvements. It is 200% right if we refer to PostgreSQL.

Hardware

The database is the heart of your application. If it doesn't have enough computing resources, your whole application will be waiting for these resources to be available.

CPU

Workload Characteristics: CPU-intensive workloads (e.g., complex calculations and heavy transactions) require more cores for better performance.
Concurrency: Higher concurrent connections and transactions can increase CPU requirements.
Database Size and Complexity: Larger databases or those with complex schemas and queries may benefit from additional CPU resources to handle the increased processing demands.

Memory

Database Working Set Size: Ideally, your server should have enough RAM to hold the frequently accessed ("hot") portion of your database (tables and indexes) in memory to reduce disk I/O.
Connection Overhead: PostgreSQL uses a multi-process architecture, where each connection has its dedicated process. More connections imply more RAM for connection overhead. As a rough estimate, plan for about 10 MB (or more) per connection.
Workload Type: Read-heavy workloads benefit from more RAM for caching, reducing read operations from disk. Write-heavy workloads may benefit less from caching but require sufficient RAM for sorting and transaction logs.

Storage

The storage size varies from one application to another, but what is important here is the type of storage and its IO performance:

Storage Type: SSDs should be your choice
Provisioned IOPS: Consider using "provisioned IOPS" storage for high-performance workloads to ensure consistent and fast disk I/O.
Storage Auto-scaling: Enable auto-scaling for storage to adjust as your database grows automatically.

Scaling up your system usually costs more money. Monitor your current resources' use closely and consider ways to decrease the use of the resources using query/structure optimizations (RAM/CPU/storage) before adding more resources.

Network proximity / Geographics:

Connections Management

Amount of queries in a single request/transaction

What should you look for?

The N+1 problem
Querying too much unnecessary data:
- Filtering fetched data in code instead of in your queries.
- Aggregate fetched data in code instead of in a query (count/sum/averages/min/max/etc)
- Fetch fields without using them (look up big text fields or tables with a lot of columns)
Use joins instead of multiple separate queries.

Optimize query processing time.

Fine-tune condition evaluation with indexes

To understand why indexing is crucial, what to index, and what kind of indexes are available, you can read here.

Fine-tune data fetching with Partitioning

The Six Pillars of Database-Driven Application Performance

Nir Tayeb — Thu, 22 Feb 2024 23:19:00 +0000

In the last post, I covered six ways ORMS made writing poorly performant code easy. But it is just a part of the story.

Side notes:

I may refer to RDBMS and SQL in the post, but these principles apply to NoSQL systems and beyond.
This post was also published in my newsletter, containing the 6 pillars with more depth.

Let's start,

Database version

New major versions are coming with new features and may include performance improvements. It is 200% right if we refer to PostgreSQL.

Hardware

The database is the heart of your application. If it doesn't have enough computing resources, your whole application will be waiting for these resources to be available.

CPU

Workload Characteristics: CPU-intensive workloads (e.g., complex calculations and heavy transactions) require more cores for better performance.
Concurrency: Higher concurrent connections and transactions can increase CPU requirements.
Database Size and Complexity: Larger databases or those with complex schemas and queries may benefit from additional CPU resources to handle the increased processing demands.

Memory

Database Working Set Size: Ideally, your server should have enough RAM to hold the frequently accessed ("hot") portion of your database (tables and indexes) in memory to reduce disk I/O.
Connection Overhead: PostgreSQL uses a multi-process architecture, where each connection has its dedicated process. More connections imply more RAM for connection overhead. As a rough estimate, plan for about 10 MB (or more) per connection.
Workload Type: Read-heavy workloads benefit from more RAM for caching, reducing read operations from disk. Write-heavy workloads may benefit less from caching but require sufficient RAM for sorting and transaction logs.

Storage

The storage size varies from one application to another, but what is important here is the type of storage and its IO performance:

Storage Type: SSDs should be your choice
Provisioned IOPS: Consider using "provisioned IOPS" storage for high-performance workloads to ensure consistent and fast disk I/O.
Storage Auto-scaling: Enable auto-scaling for storage to adjust as your database grows automatically.

Scaling up your system usually costs more money. Monitor your current resources' use closely and consider ways to decrease the use of the resources using query/structure optimizations (RAM/CPU/storage) before adding more resources.

Network proximity / Geographics:

Connections Management

Amount of queries in a single request/transaction

What should you look for?

The N+1 problem
Querying too much unnecessary data:
- Filtering fetched data in code instead of in your queries.
- Aggregate fetched data in code instead of in a query (count/sum/averages/min/max/etc)
- Fetch fields without using them (look up big text fields or tables with a lot of columns)
Use joins instead of multiple separate queries.

Optimize query processing time.

Fine-tune condition evaluation with indexes

To understand why indexing is crucial, what to index, and what kind of indexes are available, you can read here.

Fine-tune data fetching with Partitioning

6 Ways You're Using ORMs Wrong and How To Fix

Nir Tayeb — Sun, 11 Feb 2024 22:18:00 +0000

We love ORMs because they simplify and streamline the working process with relational databases.

ORMs provide a higher level of abstraction, allowing us to work with objects and classes instead of raw SQL queries. They automate everyday database tasks such as CRUD operations, reducing the boilerplate code we need to write. ORMs handle database connections and transactions, making managing and scaling our applications easier. With ORMs, it's possible to write database-agnostic code, quickly switch between different database systems, and focus more on application logic.

But with all the benefits and abstractions, it is easy to write poorly performant code.

For the examples, I'll use the scheme from the Prisma ORM documentation, which describes a blogging platform with users, profile details, posts, and categories.

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model User {
  id      Int      @id @default(autoincrement())
  email   String   @unique
  name    String?
  role    Role     @default(USER)
  posts   Post[]
  profile Profile?
}

model Profile {
  id     Int    @id @default(autoincrement())
  bio    String
  user   User   @relation(fields: [userId], references: [id])
  userId Int    @unique
}

model Post {
  id         Int        @id @default(autoincrement())
  createdAt  DateTime   @default(now())
  updatedAt  DateTime   @updatedAt
  title      String
  published  Boolean    @default(false)
  author     User       @relation(fields: [authorId], references: [id])
  authorId   Int
  categories Category[]
}

model Category {
  id    Int    @id @default(autoincrement())
  name  String
  posts Post[]
}

enum Role {
  USER
  ADMIN
}

The n+1 problem

Fetching of data without using eager loading

Assuming we want to list all user posts with a User role. A naive approach to writing the code is:

async getUserPosts() {
    const users = await prisma.user.findMany({where: {role: prisma.Role.User}});
  return users.flatMap(user => user.posts);
}

Behind the scenes, what is going to happen:

Query the database for the user's table for all the users with role admin:

SELECT * FROM users WHERE role='User'

Then, For each record, query the database (again) for the user posts:

SELECT * FROM posts WHERE userid=<X>

You won't feel the performance issue when developing the application with a few test users. But once you get to hundreds or thousands of users in the production environment, this function will take "forever" from a user experience perspective. Every round trip to your DB takes at least 10ms, not including the query that needs to run and the data transfer between the databases and your server.

Using eager-loading, you will reach the database only once!

async getUserPosts() {
    const users = await prisma.user.findMany({
        where: {role: prisma.Role.User}, 
        include: {posts: true }
    });
    return users.flatMap(user => user.posts);
}

By using include here, the ORM (prisma in my example) uses the SQL Join statement when constructing the SQL to query the data.

SELECT * 
FROM users
INNER JOIN posts ON users.id = posts.userid 
WHERE role='User'

Another variation of the n+1 problem

Assuming we want to list all posts with their writer's short bio, a naive approach will look like this:

const posts = await prisma.posts.findMany();
const postsWithBio = posts.map((post: prisma.Post) => {
    return {
        title: post.title,
    publishedAt: post.publishedAt,
    authorBio: post.author.profile?.bio
});

Again, the issue is that the code will make a round trip to the database for each post to query the author and the profile. The solution in this specific case is to use include twice, but if we have a more complex schema that uses more relations between the entities. We can implement another solution.

First, map all the unique author IDs, then fetch only the profiles (or bios) of these author IDs and map them

async function getPostWithBio(){
    const posts = await prisma.posts.findMany();
    const authorIds = posts.map((post: prisma.Post) => post.authorid);
    const profiles = await prisma.profiles.findMany({authorId: {in: authorIds}});
    const authorToBio = new Map(profiles.map(p => [p.userId, p.bio]));

    const postsWithBio = posts.map((post: prisma.Post) => {
        return {
            title: post.title,
        publishedAt: post.publishedAt,
        authorBio: authorToBio.get(post.authorId)
    });
}

In this way, only two queries are going to the database.

SELECT * FROM posts;
SELECT * FROM profiles where userid in (1,2,3,4,...);

Remember that this is a straightforward use case; this issue is widespread in more complex systems and looks slightly different. Your code will iterate over one entity, then go into other classes and functions, which will fetch more data from the DB, creating the N+1 problem without you notice.

How to identify the N+1

The best way to identify the N+1 issue in your system is by monitoring your app in three different ways:

Turn on your debug/info log and configure your ORM to log the SQL queries it generates.
Watch the database query log and look for frequent and similar queries fetching by one ID.
Using an Application Performance Monitoring (APM) system such as NewRelic, Sentry, and Azure Application Insight - they visualize all the calls to the DB on each request/transaction and show you how long it took and how long each request spent communicating with the DB.

Querying without using indexes

No defining Index

Assuming we need to search for posts by title, a naive implementation will look like this:

const posts = await prisma.posts.findMany({title: query});

If we look in the schema, we won't see an index definition on the title field. We won't see any index explicitly defined in our schema (except primary keys/foreign keys relations/unique). Why? Because it's not intuitive for object-oriented/functional programmers to think about data indexing.

Running the code above will result in a full table scan to look up the query. As the name suggests, a full table scan means an O(N) lookup for the data. Running the query will take a lot of time and resources if we have thousands of posts and more properties on each post.

If we set an index (of the default "b-tree" type) on the field, the lookup time will usually decrease to O(Log-N).

model Post {
  id         Int        @id @default(autoincrement())
  createdAt  DateTime   @default(now())
  updatedAt  DateTime   @updatedAt
  title      String
  published  Boolean    @default(false)
  author     User       @relation(fields: [authorId], references: [id])
  authorId   Int
  categories Category[]

  @@index([title])
}

💡 Suppose you don't use the query frequently. Instead of defining an index on the title field, you can add conditions to the query using other indexes. Then, the search will be on a subset of the data instead of having a full table scan.

💡 Remember that the cost of indexing the data is a lower write performance. So, depending on your use case, decide which index is necessary and which is nice if writing performance matters to you.

The query cannot use the index (wrong index type)

Assuming we want to query posts by a string found in the title.

const posts = await prisma.posts.findMany({title: {contains:"query"}});

This code results in a query including the "LIKE" operator:

SELECT * FROM posts WHERE title LIKE '%query%';

The default index algorithm is B-Tree, and leading '%' cannot be queried using the index.

The solution is to define another type of index to query the data efficiently; in this case, it's GIN or GiST (on PostgreSQL)

@@index([title], type: GIN)

For further reading, I suggest reading this fantastic blog post explaining how trigram-based operations and indexes improve query performance by several orders of magnitude.

Using model objects as output

Assuming we need to implement an endpoint to fetch all users, a naive approach will look like this.

@Controller('users')
export class UsersController{
  @Get()
  findAll(): prisma.User {
    return await prisma.users.findMany({});
  }
}

When nest.js and other frameworks evaluate objects, they transform the object entirely to JSON, iterating all over the properties and sub-properties. Using the model as the output will result in lazy loading of each user's relations, meaning more queries to the DB and more unnecessary data returned with the requests. It can also lead to a security risk of exposing private or confidential information.

Instead, you should clearly define the interface to use. With typescript, it's possible to use Pick to define a partial of another type to pick only a subset of the properties or Omit to exclude some properties.

Query unnecessary data

Assuming we want to query all posts and print only their dates and titles:

const posts = await prisma.posts.findMany({});
for (const post of posts) {
   console.log(post.title, post.publishedAt);
}

This code ends up with this SQL query:

SELECT * FROM posts;

It looks simple, but if we have even 1000 posts and their content each is 10KB of words, we transfer at least 10MB of data without even using it. Instead, we should select only the fields we need.

const posts = await prisma.posts.findMany({select: ['title', 'publishedAt']});
for (const post of posts) {
   console.log(post.title, post.publishedAt);
}

Which will end up with a query like:

SELECT title, publishedAt FROM posts;

In summary, using ORM is excellent for productivity but not always suitable for performance. Understanding what is happening behind the scenes is crucial to ensure optimal code performance.

If you enjoyed the article, please forward and share it with your teammates/acquaintances and help them avoid these mistakes.
This post was originally published in my newsletter "Percentile 99th".
My subscribers get all the information first; in the future, exclusive content will be shared only over the newsletter.

Why you should avoid Percentile 99th for your users

Nir Tayeb — Mon, 05 Feb 2024 20:29:17 +0000

The 99th percentile is the highest percentile you can get. It means you are among the top scorers since you scored higher than 99% of the group who took the test. Only 1 in 100 the group scores in this range, so it places you at the very top of the pool.

Most of us want to be in the 1% percent that scored the highest. We want to be the top earners, performers, the fastest, the fittest, and generally the best in our crafts.

However, in application performance metrics, the users in the 99th percentile have the highest latency score (or response time, first contentfull paint, etc.…). Therefore, they had the worst experience in the measured period.

Why you should care?

You probably think, okay, it's only 1% who experience some shitty performance. Why should I care about 1%?

The 1% percent could happen where your system doesn't scale, for example, when working on too much data - which usually occurs for the top clients.
The 1% can be the edge cases when one user's action might affect other users' experience when the system runs on shared resources.
The 1% percent could lead your way through capacity planning. By analyzing this metric, you can determine the level of infrastructure, compute power, or database capacity needed to handle the occasional high-latency requests without sacrificing overall performance.
Outliers can skew the mean score. Removing them can give a more accurate representation of the average user experience.

Monitoring this metric is only the first step; the next step is finding the root cause of the score and fixing it for good. But it is only sometimes straightforward.

In my newsletter "Percentile 99th", I'll cover, every other week, the principles, how-to's, and deep dive analysis into different technologies, including real-life examples from my experience and top companies, to help you decrease the percentile 99th metric and help you become the top 1% percent of engineers who knows to improve the system to work like magic (and design new systems better), a real performance speedster.

See you there, at Percentile 99th