DEV Community: Driptaroop Das

Explaining PostgreSQL Explain

Driptaroop Das — Fri, 17 Jan 2025 16:33:37 +0000

I publish my tech articles in my blog https://blog.dripto.xyz including multiple learnings on postgres. Check it out if you are interested. :)

Hello after a long hibernation and welcome to my Postgres series where I will cover different aspects of Postgres I learned as an application developer. In this section, we will be covering the Explain command in PostgreSQL.

In short, The EXPLAIN command is used to obtain information about how the query planner works. It shows the query plan that the planner generates for the query. The EXPLAIN command is very useful for optimizing queries and understanding how the query planner works.

Demo

Lets jump directly to a query.

`Explain`

Start the database with compose (a.k.a. docker compose). In this post, we will use the docker compose from the repo. This will populate a process table.

Please note that this post is also easy to follow even if you don't have a database in ready and don't want to start/follow my compose file.

Let's take this query:

SELECT * FROM process WHERE user_id='10cfd02f-21d3-4439-9983-5685ec9204c4';

and try to find the query plan for this:

Explain SELECT * FROM process WHERE user_id='10cfd02f-21d3-4439-9983-5685ec9204c4';

The output will be something like:

Gather  (cost=1000.00..53116.77 rows=1 width=133)
  Workers Planned: 2
  ->  Parallel Seq Scan on process  (cost=0.00..52116.67 rows=1 width=133)
        Filter: (user_id = '10cfd02f-21d3-4439-9983-5685ec9204c4'::text)

Explain Enhancement

Additional information can be derived by setting track_io_timing flag on. Enables timing of database I/O calls. This parameter is off by default, as it will repeatedly query the operating system for the current time, which may cause significant overhead on some platforms.

SET track_io_timing = on;

we can get even more information by using the options. The most important options are,

ANALYZE: Adding this option makes EXPLAIN not just display the query plan and PostgreSQL's estimates, but also actually run the query. It then shows the real execution time and the number of rows processed at each step. Be cautious with this for UPDATE and DELETE queries since they’ll actually modify data! This is a must-have for serious SQL performance debugging.
BUFFERS: This one works only with ANALYZE and provides details about how many pages are read, written, or dirtied at each step. If you’re analyzing performance, this is gold—you’ll definitely want this information.
VERBOSE: When enabled, it shows all the output expressions for every step in the execution plan. Most of the time, this just adds noise, but can be useful for hard to debug queries.
SETTINGS: Available since PostgreSQL v12, this option includes any performance-related parameters that have been changed from their defaults in the output. It's handy for understanding how tweaks to the configuration affect your query.
WAL: Introduced in v13, this option shows the Write-Ahead Logging (WAL) activity generated by queries that modify data. Like BUFFERS, it works only with ANALYZE and is super useful for understanding the impact of data changes.
FORMAT: This lets you choose the format of the output. For humans, the default TEXT format is by far the easiest to read and work with for performance tuning. Other formats like XML, JSON, or YAML are better suited for automated tools or scripts.

Let's try to enhance the Explain command with the ANALYZE and BUFFERS options.

Explain (analyze, buffers) SELECT * FROM process WHERE user_id='10cfd02f-21d3-4439-9983-5685ec9204c4';

The output of this will be,

Gather  (cost=1000.00..53116.77 rows=1 width=133) (actual time=446.306..449.906 rows=1 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=1179 read=40521
  I/O Timings: shared read=731.428
  ->  Parallel Seq Scan on process  (cost=0.00..52116.67 rows=1 width=133) (actual time=433.582..433.598 rows=0 loops=3)
        Filter: (user_id = '10cfd02f-21d3-4439-9983-5685ec9204c4'::text)
        Rows Removed by Filter: 666666
        Buffers: shared hit=1179 read=40521
        I/O Timings: shared read=731.428
Planning:
  Buffers: shared hit=8
Planning Time: 3.036 ms
Execution Time: 449.998 ms

Explanation

Let's go through the output of the enhanced Explain command line by line:

Gather (cost=1000.00..53116.77 rows=1 width=133) (actual time=446.306..449.906 rows=1 loops=1)
- Gather -> This is the topmost node in the plan tree. It is used to collect the results from all the workers.
- (cost=1000.00..53116.77 rows=1 width=133) -> This is the estimated cost of the plan. The cost is calculated by the planner based on the number of rows and the width of the rows.
  - cost=1000.00..53116.77 -> The estimated cost of the plan. The cost is unitless and is used to compare the cost of different plans. The lower the cost, the better the plan.
  - rows=1 -> The estimated number of rows that will be returned by the plan.
  - width=133 -> The estimated width of the rows that will be returned by the plan.
- (actual time=446.306..449.906 rows=1 loops=1) -> This is the actual time taken by the plan to execute.
- Workers Planned: 2 -> This is the number of workers that are planned to execute the plan.
- Workers Launched: 2 -> This is the number of workers that are actually launched to execute the plan. this can be different than the number of workers planned if the system is busy or have a restrictive configuration.
- Buffers: shared hit=1179 read=40521 -> This is the number of buffers that are used by the plan. The number of buffers that are hit in the shared buffer cache and the number of buffers that are read from the disk.
  - shared hit=1179 -> The number of buffers that are hit in the shared buffer cache. This means that the data is already in the shared buffer cache and does not need to be read from the disk.
  - read=40521 -> The number of buffers that are read from the disk. This means that the data is not in the shared buffer cache and needs to be read from the disk. This is an expensive operation compared to reading from the shared buffer cache.
- I/O Timings: shared read=731.428 -> This is the time taken to read the data from the disk. This is an important metric to consider when optimizing the query.
- -> Parallel Seq Scan on process (cost=0.00..52116.67 rows=1 width=133) (actual time=433.582..433.598 rows=0 loops=3) -> In postgres the query plans execute from bottom to top. This is the bottommost node in the plan tree. This is the actual operation that is performed by the plan.
  - Parallel Seq Scan -> This is the actual operation that is performed by the plan. In this case, it is a parallel sequential scan of the table.
  - loops=3 -> The number of times the operation is executed.
  - Filter: (user_id = '10cfd02f-21d3-4439-9983-5685ec9204c4'::text) -> perform filter on the rows that are returned by the scan.
  - Rows Removed by Filter: 666666 -> The number of rows that are removed by the filter. This is a per-loop average, rounded to the nearest integer.
    - The total number of rows removed is this value multiplied by the number of loops. so in this case, it is 3*666666=1999998.
    - So that means the total number of rows should be returned from the query is 2000000-1999998=2. But only 1 row is returned. where is the other row?
    - This is because this value is a per-loop average, rounded to the nearest integer.
    - What does this mean? When more than one loop is used for the plan node (e.g. loops=2 or greater), then “Rows Removed by Filter” is an average value of the “Rows Removed by Filter” per-loop.
    - For example, if one loop removed 10 rows, and another removed 30 rows, we’d expect to see a value of 20 as the average of the two.
    - When there’s one loop (loops=1), the figure is the actual number of rows processed and removed.
- Buffers: shared hit=8 -> The number of buffers that are used by the plan. The number of buffers that are hit in the shared buffer cache.
- Planning Time: 3.036 ms -> The time taken by the planner to generate the plan. The planner generates the plan by considering different strategies from statistics.
  - For simple queries, the planning time is usually very low. But for complex queries involving multiple tables and joins, the planning time can be significant.
- Execution Time: 449.998 ms -> The total time taken by the plan to execute. This is the sum of the actual time taken by the plan to execute. Generally this is what we try to optimize.

Whew, that was a lot of information specially for a simple select planning. But this information is very useful when optimizing queries. By looking at the output of the Explain command, we can identify the bottlenecks in the query and optimize it accordingly.

For more complex queries the output will be more complex and will have more information. But the basic structure of the output will be the same. Example for a more complex query with partitioned table, multiple join, subqueries and CTEs:

Query:

with Completed_orders AS
        (select o1.order_id, o1.order_date, o1.customer_id, o1.status from orders o1)
SELECT
    c.first_name,
    c.last_name,
    o.order_id,
    o.order_date,
    p.product_name,
    od.quantity,
    od.price,
    od.discount,
    (od.price * od.quantity) - (od.price * od.quantity * od.discount) AS final_price
FROM
    (select c1.first_name, c1.last_name, c1.customer_id from customers c1) c
        JOIN
    (select o2.order_id, o2.order_date, o2.customer_id, o2.status from Completed_orders o2 where o2.status='Completed') o ON c.customer_id = o.customer_id
        JOIN
    order_details od ON o.order_id = od.order_id
        JOIN
    products p ON od.product_id = p.product_id
ORDER BY
    o.order_date DESC;

Plan:

Sort  (cost=211.31..211.77 rows=184 width=530) (actual time=0.124..0.125 rows=0 loops=1)
  Sort Key: o1.order_date DESC
  Sort Method: quicksort  Memory: 25kB
  Buffers: shared hit=3
  ->  Hash Join  (cost=82.80..204.39 rows=184 width=530) (actual time=0.038..0.039 rows=0 loops=1)
        Hash Cond: (od.product_id = p.product_id)
        ->  Hash Join  (cost=68.30..186.64 rows=184 width=284) (never executed)
              Hash Cond: (od.order_id = o1.order_id)
              ->  Append  (cost=0.00..101.20 rows=4080 width=40) (never executed)
                    ->  Seq Scan on order_details_1 od_1  (cost=0.00..20.20 rows=1020 width=40) (never executed)
                    ->  Seq Scan on order_details_2 od_2  (cost=0.00..20.20 rows=1020 width=40) (never executed)
                    ->  Seq Scan on order_details_3 od_3  (cost=0.00..20.20 rows=1020 width=40) (never executed)
                    ->  Seq Scan on order_details_4 od_4  (cost=0.00..20.20 rows=1020 width=40) (never executed)
              ->  Hash  (cost=68.19..68.19 rows=9 width=248) (never executed)
                    ->  Hash Join  (cost=56.03..68.19 rows=9 width=248) (never executed)
                          Hash Cond: (c1.customer_id = o1.customer_id)
                          ->  Seq Scan on customers c1  (cost=0.00..11.50 rows=150 width=240) (never executed)
                          ->  Hash  (cost=55.92..55.92 rows=9 width=16) (never executed)
                                ->  Append  (cost=0.00..55.92 rows=9 width=16) (never executed)
                                      ->  Seq Scan on orders_2023 o1_1  (cost=0.00..18.62 rows=3 width=16) (never executed)
                                            Filter: ((status)::text = 'Completed'::text)
                                      ->  Seq Scan on orders_2024 o1_2  (cost=0.00..18.62 rows=3 width=16) (never executed)
                                            Filter: ((status)::text = 'Completed'::text)
                                      ->  Seq Scan on orders_2025 o1_3  (cost=0.00..18.62 rows=3 width=16) (never executed)
                                            Filter: ((status)::text = 'Completed'::text)
        ->  Hash  (cost=12.00..12.00 rows=200 width=222) (actual time=0.005..0.005 rows=0 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 8kB
              ->  Seq Scan on products p  (cost=0.00..12.00 rows=200 width=222) (actual time=0.005..0.005 rows=0 loops=1)
Settings: search_path = 'public'
Planning:
  Buffers: shared hit=646
Planning Time: 3.759 ms
Execution Time: 0.362 ms

Query:

with Completed_orders AS
        (select o1.order_id, o1.order_date, o1.customer_id, o1.status from orders o1)
SELECT
    c.first_name,
    c.last_name,
    o.order_id,
    o.order_date,
    p.product_name,
    od.quantity,
    od.price,
    od.discount,
    (od.price * od.quantity) - (od.price * od.quantity * od.discount) AS final_price
FROM
    (select c1.first_name, c1.last_name, c1.customer_id from customers c1) c
        JOIN
    (select o2.order_id, o2.order_date, o2.customer_id, o2.status from Completed_orders o2 where o2.status='Completed') o ON c.customer_id = o.customer_id
        JOIN
    order_details od ON o.order_id = od.order_id
        JOIN
    products p ON od.product_id = p.product_id
ORDER BY
    o.order_date DESC;

Plan:

Sort  (cost=211.31..211.77 rows=184 width=530) (actual time=0.124..0.125 rows=0 loops=1)
  Sort Key: o1.order_date DESC
  Sort Method: quicksort  Memory: 25kB
  Buffers: shared hit=3
  ->  Hash Join  (cost=82.80..204.39 rows=184 width=530) (actual time=0.038..0.039 rows=0 loops=1)
        Hash Cond: (od.product_id = p.product_id)
        ->  Hash Join  (cost=68.30..186.64 rows=184 width=284) (never executed)
              Hash Cond: (od.order_id = o1.order_id)
              ->  Append  (cost=0.00..101.20 rows=4080 width=40) (never executed)
                    ->  Seq Scan on order_details_1 od_1  (cost=0.00..20.20 rows=1020 width=40) (never executed)
                    ->  Seq Scan on order_details_2 od_2  (cost=0.00..20.20 rows=1020 width=40) (never executed)
                    ->  Seq Scan on order_details_3 od_3  (cost=0.00..20.20 rows=1020 width=40) (never executed)
                    ->  Seq Scan on order_details_4 od_4  (cost=0.00..20.20 rows=1020 width=40) (never executed)
              ->  Hash  (cost=68.19..68.19 rows=9 width=248) (never executed)
                    ->  Hash Join  (cost=56.03..68.19 rows=9 width=248) (never executed)
                          Hash Cond: (c1.customer_id = o1.customer_id)
                          ->  Seq Scan on customers c1  (cost=0.00..11.50 rows=150 width=240) (never executed)
                          ->  Hash  (cost=55.92..55.92 rows=9 width=16) (never executed)
                                ->  Append  (cost=0.00..55.92 rows=9 width=16) (never executed)
                                      ->  Seq Scan on orders_2023 o1_1  (cost=0.00..18.62 rows=3 width=16) (never executed)
                                            Filter: ((status)::text = 'Completed'::text)
                                      ->  Seq Scan on orders_2024 o1_2  (cost=0.00..18.62 rows=3 width=16) (never executed)
                                            Filter: ((status)::text = 'Completed'::text)
                                      ->  Seq Scan on orders_2025 o1_3  (cost=0.00..18.62 rows=3 width=16) (never executed)
                                            Filter: ((status)::text = 'Completed'::text)
        ->  Hash  (cost=12.00..12.00 rows=200 width=222) (actual time=0.005..0.005 rows=0 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 8kB
              ->  Seq Scan on products p  (cost=0.00..12.00 rows=200 width=222) (actual time=0.005..0.005 rows=0 loops=1)
Settings: search_path = 'public'
Planning:
  Buffers: shared hit=646
Planning Time: 3.759 ms
Execution Time: 0.362 ms

We will explore these unexplained planning methods (such as Hash Join, Append, Seq Scan, Sort, etc.) in the upcoming sections.

Visualization Tools

There are many tools available to visualize the query plan with explain analyze. Some of the popular ones are:

PGAdmin: PGAdmin has a pretty good graphical query plan visualization tool. You can see the query plan in a graphical format by clicking on the Explain button in the query editor.
Depesz' EXPLAIN ANALYZE visualizer
Dalibo's EXPLAIN ANALYZE visualizer. Offline version available at https://github.com/dalibo/pev2.

Summary

In this article, we explore the PostgreSQL EXPLAIN command, a crucial tool for understanding and optimizing query performance. We delve into the basic usage of EXPLAIN to reveal query plans, and discuss advanced options such as ANALYZE, BUFFERS, and more, which provide deeper insights into query execution and performance bottlenecks. Additionally, we analyze example output from EXPLAIN and highlight visualization tools that can further aid in interpreting complex query plans. This piece offers essential knowledge for developers looking to enhance their SQL performance debugging skills using PostgreSQL.

Essential Reads

My own blog: https://blog.dripto.xyz
PostgreSQL Documentation: EXPLAIN
Explaining the unexplainable: If you can read only one article about Explain, this is the one to read.
How to interpret PostgreSQL EXPLAIN ANALYZE output.
This article also available in github.

Distributed job runner/task scheduler with postgres and spring

Driptaroop Das — Wed, 15 Mar 2023 07:12:31 +0000

Imagine a scenario where there is a system that is inserting new rows occasionally in your database table. and your application is querying the database to see if there are any new rows inserted in the table, and if there are any new rows, it will take those rows up and process and update them.

In a non-distributed system, the process of querying the database to see if there are any new rows, and processing and updating them, works fine. However, in a distributed system, there's always the possibility of multiple instances updating a single row, leading to data race and deadlocking.

One of the solutions to this problem is using a distributed job runner/task scheduler. frameworks like shedlock(https://github.com/lukas-krecan/ShedLock) solve this by employing a distributed lock. Shedlock gives you the guarantee that even in the case of multiple instances/nodes/threads, a task lock can be acquired by at most 1 node. But this is not the most efficient and scalable solution in a distributed environment. If there are multiple nodes, only one node will do all the processing, and other nodes will be idle, effectively emulating a non-distributed environment.

A better solution is to leverage the Postgres pessimistic locking in combination with skip_locked to pick up equal amounts of rows to process. This way, we can scale up or down the number of nodes to handle the load. Let me show how in an example.

Example with Spring boot

Setup

Let's assume there is a very simple table consisting of only 2 columns, the primary key id and the data,

CREATE TABLE test(
    id BIGINT PRIMARY KEY, 
    data TEXT, 
    processed BOOLEAN
);

The equivalent JPA entity and repository would be,

@Entity
class Test(
    @Id val id: Long,
    val data: String,
    val processed: Boolean
)

interface TestRepository: JpaRepository<Test, Long>

Now imagine someplace where we need to check for new rows in the table and update them,

@Service
class TestService(private val testRepo: TestRepository) {

    @Transactional
    fun doSomething() {
        val t = testRepo.findFirstByProcessedIsFalse() // ----- (1)
        if (t != null) {                       
            process(t)                        
            testRepo.save(t)
        }
    }

    private fun process(t: Test) {
        TODO("do processing")
    }
}

(1): this method does not exist yet. We'll add them next

interface TestRepository: JpaRepository<Test, Long> {
    fun findFirstByProcessedIsFalse(): Test?
}

Problem

A single instance of this code will run just fine. But in a distributed system, there would be data race when multiple nodes will try to get a hold of the same row from the DB table. Since this operation is transactional, only one node will be able to acquire the locks. This will result in blocked threads in other nodes, which will either wait till the lock is released or the DB lock timeout is up.

Solution

Let's solve this using pessimistic locking in combination with skip_locked.

const val SKIP_LOCK = "-2"
interface TestRepository: JpaRepository<Test, Long> {
    @Lock(LockModeType.PESSIMISTIC_WRITE) // ------ (1)
    @QueryHints(QueryHint(name = AvailableSettings.JPA_LOCK_TIMEOUT, value = SKIP_LOCK)) // ------ (2)
    fun findFirstByProcessedIsFalse(): Test?
}

The method employs two tricks:

@Lock(LockModeType.PESSIMISTIC_WRITE) creates and holds a pessimistic lock on a row. As long as the lock is held, the rows cannot be edited by other threads.

@QueryHints(QueryHint(name = AvailableSettings.JPA_LOCK_TIMEOUT, value = SKIP_LOCK)) instructs other threads not to wait for acquiring the pessimistic locks and instead skip them and move on to the next free row. This is achieved by changing the JPA_LOCK_TIMEOUT mode. The SKIP_LOCK timeout mode value in JPA is -2.

And thats it. No need to change the rest of the code. This will ensure that the nodes in a distributed system will be able to get a hold of unique rows and do processing on them, effectively enabling a more scalable system.

Using layered docker images over fat-jar docker images in spring boot application

Driptaroop Das — Wed, 01 Feb 2023 17:24:22 +0000

TL;DR

To achieve more efficient docker image building and faster startup times, instead of doing this,

FROM eclipse-temurin:17-jdk
ARG ARG_VERSION
ARG APP_NAME=test-app

EXPOSE 8080

WORKDIR app
ARG JAR_FILE=build/libs/${APP_NAME}-${ARG_VERSION}.jar

ADD ${JAR_FILE} app.jar

EXPOSE 8080
ENTRYPOINT ["java", "-jar", "app.jar"]

Do this,

FROM eclipse-temurin:17-jdk as builder
ARG ARG_VERSION
ARG APP_NAME=test-app

ARG JAR_FILE=build/libs/${APP_NAME}-${ARG_VERSION}.jar
WORKDIR app

COPY ${JAR_FILE} app.jar
RUN java -Djarmode=layertools -jar app.jar extract

FROM eclipse-temurin:17-jdk
WORKDIR /app

COPY --from=builder app/dependencies/ ./
COPY --from=builder app/spring-boot-loader/ ./
COPY --from=builder app/snapshot-dependencies/ ./
COPY --from=builder app/application/ ./

EXPOSE 8080
ENTRYPOINT ["java", "org.springframework.boot.loader.JarLauncher"]

The problem

Even though considered archaic by some, using Dockerfile is still one of the more flexible ways to create a docker image (and contrary to popular belief, it doesn't always require a docker daemon to be present, i.e. check out kaniko). In our organization, it is very common to build docker images for Spring-boot applications via a Dockerfile for deployment purposes.

One of the main issues is that over time, the use of a traditional Dockerfiles can consume disk space exponentially.

To illustrate this, let's take an example of a typical build of a Spring Boot repository. This build creates what is known as a fat-jar, which is a JAR file that contains not only the Java program but also embeds its dependencies. Since it contains a lot of dependencies, it is not uncommon for a fat-jar of a fairly complex project to be around 100MB in size.

Now, consider an organization that has around 50 microservices. With each build of these microservices, the organization will be generating around 5GB of data (100MB x 50). If these services are built around 60 times a month (twice a day on average, though it should be much more if done in a CI pipeline) and each build creates and deploys its own docker image, this will take up around 300GB of storage at least per month. As you can see, this can quickly become an issue as the number of services and builds increases.

What can be done?

As we've seen, the use of fat jar images can lead to a significant increase in disk space usage over time. However, it's worth noting that even though the images are built multiple times, only a small fraction of it actually changes per build. The dependencies largely stay the same, and only the application code changes. In fact, the application code that changes is usually quite small in comparison to the entire image, often being less than 10MB. By keeping this information in mind, we can leverage Docker image layers to our advantage. By using layers, we can create more efficient and secure images that are faster to build and deploy.

Understanding Docker Images and Layers

A Docker image is a collection of layers. Each layer is an immutable TAR archive with a hash code generated from the file. When building a Docker image, each command that adds files will result in a layer being created. These layers can be cached and reused in future builds, making the process more efficient.

When using a fat-jar image, the fat-jar is added directly to the Dockerfile. This creates a Docker image with a single layer of application and dependencies. As a result, every single change to the fat jar (even for a single file change) will create a new layer and thus adding to the issue of disk space consumption as described previously. By leveraging layers, we can optimize the space usage as well as making the process of building and deploying the images faster.

Spring Boot and Layered Jars

Spring Boot version 2.3.0 and above offers two new features to improve the generation of Docker images:

Buildpack support : This feature provides the Java runtime for the application, allowing for the automatic building of the Docker image without the need for a Dockerfile. However, this feature is out of scope for this blog post.
Layered jars : This feature helps to optimize the Docker layer generation process. If the Spring Boot jar is created using the spring-boot-maven-plugin or spring-boot-gradle-plugin, the jar file comes pre-created with 4 layers. The BOOT-INF/layers.idx file records the different layers and can be checked by extracting the jar. A sample BOOT-INF/layers.idx file might look like this:

 - "dependencies":
   - "BOOT-INF/lib/"
 - "spring-boot-loader":
   - "org/"
 - "snapshot-dependencies":
 - "application":
   - "BOOT-INF/classes/"
   - "BOOT-INF/classpath.idx"
   - "BOOT-INF/layers.idx"
   - "META-INF/"

The layers can also be inspected by using the command:

 java -Djarmode=layertools -jar target/<jar_name>.jar list

This will provide a simplistic view of the content of the layers.idx file.

 dependencies
 spring-boot-loader
 snapshot-dependencies
 application

We can also extract the layers into directories:

 java -Djarmode=layertools -jar target/<jar_name>.jar extract

the application changes the most often. It hosts the actual code for the application. By storing each layer as an individual Docker image layer, we can optimize the space usage and speed up the build and deployment process. Only the application layer will be changed with each build, while the other layers can be cached and reused from previous builds.

So, how do we do this?

We use multistage Docker builds.

Multistage Dockerfiles

We use multistage dockerfiles to create layered docker images for our Spring Boot applications.

First, let's add the fat jar file to the base image:

 FROM eclipse-temurin:17-jdk as builder
 ARG ARG_VERSION
 ARG APP_NAME=test-app

 ARG JAR_FILE=build/libs/${APP_NAME}-${ARG_VERSION}.jar
 WORKDIR app

 COPY ${JAR_FILE} app.jar

Next, we extract the layers of the artifact using the following command:

 RUN java -Djarmode=layertools -jar app.jar extract

Then, we copy the layers from the builder image to the actual image:

 FROM eclipse-temurin:17-jdk
 WORKDIR /app

 COPY --from=builder app/dependencies/ ./
 COPY --from=builder app/spring-boot-loader/ ./
 COPY --from=builder app/snapshot-dependencies/ ./
 COPY --from=builder app/application/ ./

Finally, we start the image using org.springframework.boot.loader.JarLauncher and expose the necessary ports:

 EXPOSE 8080
 ENTRYPOINT ["java", "org.springframework.boot.loader.JarLauncher"]

Final Dockerfile:

FROM eclipse-temurin:17-jdk as builder
ARG ARG_VERSION
ARG APP_NAME=test-app

ARG JAR_FILE=build/libs/${APP_NAME}-${ARG_VERSION}.jar
WORKDIR app

COPY ${JAR_FILE} app.jar
RUN java -Djarmode=layertools -jar app.jar extract

FROM eclipse-temurin:17-jdk
WORKDIR /app

COPY --from=builder app/dependencies/ ./
COPY --from=builder app/spring-boot-loader/ ./
COPY --from=builder app/snapshot-dependencies/ ./
COPY --from=builder app/application/ ./

EXPOSE 8080
ENTRYPOINT ["java", "org.springframework.boot.loader.JarLauncher"]

Additional Advantage:

Since the jar is already extracted in the docker image, the startup time is improved. On average, I can see a 1 - 1.5 sec improvement in the startup time across all my spring boot application.

Conclusion:

In conclusion, using layered docker images can greatly reduce the storage space consumed by your Spring Boot applications and improve the startup time of your services. By using multistage Dockerfiles, we can extract the layers of our fat jar and store them in individual image layers. This way, only the application layer will be changed per build and the other layers can be cached and reused from the previous builds. This not only saves storage space, but also improves the startup time of your services, providing an additional advantage to your organization.

Further Read:

Declarative HTTP Clients in Spring Boot 3 with Kotlin

Driptaroop Das — Mon, 30 Jan 2023 03:27:39 +0000

Kotlin and Spring Boot 3 make a powerful combination for building modern, scalable applications. With the introduction of declarative HTTP clients in Spring Boot 3, developers can define their HTTP client configurations in a concise, readable manner.

In this blog post, we'll explore how to use declarative HTTP clients in Spring Boot 3 with Kotlin to streamline your development process and increase your productivity.

Setting up Spring Boot 3

To use declarative HTTP clients in Spring Boot 3 with Kotlin, you'll need to have a Spring Boot 3 project set up using Kotlin as the programming language. You can create a new Spring Boot 3 project using the Spring Initializer and selecting Kotlin as the programming language.

Enabling declarative HTTP clients

Remember to use the webflux dependency to enable declarative clients. If you already have your project set up, you'll need to enable the HTTP clients by adding the following dependency to your project:

implementation("org.springframework.boot:spring-boot-starter-webflux")

Defining your client

Next, you'll need to define your HTTP client by creating a new interface. This interface will define all of the endpoints that you need to make HTTP requests to.

 import org.springframework.web.bind.annotation.PathVariable
 import org.springframework.web.service.annotation.GetExchange
 import org.springframework.web.service.annotation.HttpExchange

 @HttpExchange("/shows")
 interface ImdbClient {
     @GetExchange("/{showId}")
     fun getShow(@PathVariable("showId") showId: String): ResponseEntity<Show>
 }

@HttpExchange: Used here in the class level to declare the base path for all the http calls. Also can be used to declare other parameters of the http call.
@GetExchange: a specialized version of @HttpExchange with GET method to use with individual methods.
@PathVariable: Annotation which indicates that a method parameter should be bound to a URI template variable. Note the templated path variable showId in the @GetExchange("/{showId}") annotation.

Autowiring your client

you can autowire your client into your application and use it to make HTTP requests.

 @Service
 class TestService(
     private val client: ImdbClient
 ) {
     fun getShows() {
         val show = client.getShow("1")
     }
 }

Configuration

Finally, You can customize and bootstrap your HTTP client by supplying a configuration to bind it with a webclient instance. This will allow you to configure the underlying WebClient.Builder used to create your client.

 @Configuration
 class Config {
     @Bean
     fun imdbClient(builder: WebClient.Builder): ImdbClient {
         val webClient = builder.baseUrl("https://api.imdb.com").build()
         return HttpServiceProxyFactory.builder(WebClientAdapter.forClient(webClient)).build()
             .createClient(ImdbClient::class.java)
     }
 }

In conclusion, declarative HTTP clients in Spring Boot 3 and Kotlin provide a simple and efficient way to handle HTTP requests in your applications. With its concise syntax and powerful features, you can streamline your development process and improve the overall quality of your code.

Lazily Initializing Implicit Logger in Every Class in Kotlin

Driptaroop Das — Mon, 30 Jan 2023 00:12:00 +0000

How Kotlin(JVM) logger declaration works

If you're familiar with the Kotlin programming language on the JVM platform, you may have encountered the need for logging in your projects. Typically, this requires declaring a logger for each class in your code, using a library such as slf4j. While this approach is functional, it can become tedious and repetitive, especially as the number of classes in your project increases. In this blog, we'll explore an alternative method for class-level logging in Kotlin JVM that leverages implicit loggers, streamlining the process and reducing the boilerplate code you need to write.

A typical logger declaration using slf4j in Kotlin JVM may look like the following code sample:

import org.slf4j.LoggerFactory

class Test {
    val log = LoggerFactory.getLogger(Test::class.java)

    fun doSomething() {
        log.info("doing things")
    }
}

It's worth noting that if you choose to use a different logging framework, such as log4j2 or kotlin-logging, the code for declaring a logger may look different. For instance, here's a code sample for declaring a logger using kotlin-logging:

private val logger = KotlinLogging.logger {}

The syntax may vary based on the specific logging framework you choose, but the overall process of declaring a logger for each class remains the same.

How Lombok does this with less boilerplate in Java

If you're familiar with Java, you may have encountered Lombok, a library that reduces boilerplate code in your Java projects. One aspect of Lombok that can be particularly useful for logging is its ability to simplify the declaration of class-level loggers. With Lombok, a logger declaration can be reduced to just a single annotation, as demonstrated in this code sample:

import lombok.extern.slf4j.Slf4j;

@Slf4j
public class ExampleClass {
  public static void main(String... args) {
    log.error("Something is wrong here");
  }
}

In this example, the @Slf4j annotation generates a logger for the class using the slf4j framework. This greatly reduces the amount of code you need to write for logging, making it a convenient and efficient solution for logging in Java projects. While Kotlin does not have a direct equivalent to Lombok, there is another approache to streamlining logging that we'll explore in this blog.

Can we do something better in Kotlin?

As it turned out, we can.

Fortunately, Kotlin provides features that allow us to improve upon the standard method of declaring loggers for each class. One such feature is reified type parameters, which enables the creation of inline extension variables with preserved type information. With this in mind, we can create an inline extension variable that acts as an implicit logger for a class. The reified generics allow us to fetch class details to pass to the logger creation, and by storing the created loggers in a hashmap, we can avoid redundant recreations and ensure constant lookup times.

import org.slf4j.Logger
import org.slf4j.LoggerFactory
import kotlin.reflect.KClass

val loggerMap = hashMapOf<KClass<*>, Logger>()
inline val <reified T> T.log: Logger
    get() = loggerMap.getOrPut(T::class) {
        LoggerFactory.getLogger(
            if (T::class.isCompanion) T::class.java.enclosingClass else T::class.java)
    }

Once this code is in classpath, declaring a logger for a class becomes incredibly simple. That is, you don't have to declare anything at all, just go ahead and use the log variable and Kotlin compiler will create one for you the first time it is used.

class Test {
    fun doSomething() {
        log.info("doing things")
    }
}

This reduces the amount of code required and makes the logging process more efficient and less prone to error. You can even put it in a library and use it for multiple applications.

Whether you're a seasoned developer or just starting out with Kotlin, implementing an implicit logger is a great way to streamline your logging process and save time in your development workflow. So, give it a try and see how it can simplify your projects.

Using user-defined types (UDT) with Apache Cassandra in Spring Boot 3.x

Driptaroop Das — Fri, 27 Jan 2023 11:57:22 +0000

Apache Cassandra is a highly scalable and distributed NoSQL database that is well-suited for handling large amounts of data. One of the powerful features of Cassandra is the ability to use user-defined types (UDT) to model complex data structures. In this blog post, we will learn how to use UDTs with Spring Boot 3.x to interact with a Cassandra database.

Creating the data model in cassandra

First, let's create a simple UDT in Cassandra. You can do this by running the following command in the Cassandra shell (imagining test being the cassandra keyspace):

CREATE TYPE test.address (
    street text,
    city text,
    zip text
);

This creates a UDT called "address" with three fields: street, city, and zip.

Next, we will create a table that uses this UDT. For example, we can create a table called "users" with the following command:

CREATE TABLE test.users (
    id int PRIMARY KEY,
    name text,
    address frozen<address>
);

This table has three columns: id, name, and address. The "address" column is the UDT type here. When defining a user-defined type column, it is best practice to utilize the frozen keyword. This ensures that the user-defined type value cannot be partially updated, but must be completely overwritten. By using the 'frozen' keyword, Cassandra treats the value of the user-defined type as a single, immutable block of data, similar to a binary large object (BLOB).

Setup spring boot 3.x application

Now that we have our UDT and table set up, we can start using them with Spring Boot. Head to start.spring.io to create a spring boot 3.x project with cassandra or add the following dependency to the existing spring boot projects.

Gradle:

  implementation("org.springframework.boot:spring-boot-starter-data-cassandra")

Maven:

<dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-cassandra</artifactId>
</dependency>

We also need to configure the Cassandra connection in our application.yml file:

spring:
  data:
    cassandra:
      contact-points: localhost
      port: 9042
      keyspace-name: test

Setup data model in Spring boot application

Once we have the dependency and configuration in place, We also need to create a POJO class that represents our "users" table:

import org.springframework.data.cassandra.core.mapping.PrimaryKey
import org.springframework.data.cassandra.core.mapping.Table

@Table
data class User (
    @PrimaryKey
    val id: Int,
    val name: String,
    /**
     * @CassandraType annotation is not required by default and is redundant. but can be added if requried.
     * @CassandraType(type = CassandraType.Name.UDT, userTypeName = "address")
     */
    val address: Address
)

And a POJO class that represents our address UDT:

import org.springframework.data.cassandra.core.mapping.UserDefinedType

@UserDefinedType("address")
data class Address (
    val street: String,
    val city: String,
    val zip: String
)

we also need to create a Cassandra repository to interact with our users table. For example:

import org.springframework.data.cassandra.repository.CassandraRepository

interface UserRepository : CassandraRepository<User, Int>

Use the repository to call Cassandra

With these classes in place, we can now use the UserRepository to perform CRUD operations on the users table. For example, we can save a new user with:

@Configuration
class App {
    @Bean
    fun runner(userRepository: UserRepository) = ApplicationRunner{
        val user = User(1, "John Doe", Address("Street name", "Berlin", "12345"))
        userRepository.save(user)
    }
}

Conclusion

We hope this tutorial gave you a good idea on how to use user-defined types (UDT) with Apache Cassandra in Spring Boot 3.x. We've shown you how to create a UDT and table in Cassandra, and how to use them with Spring Boot to perform CRUD operations on the table.

Using UDTs can be a powerful way to model complex data structures in Cassandra, and Spring Boot makes it easy to work with these structures in a Java application. With the right configuration and setup, you can easily start using UDTs in your projects to improve the scalability and performance of your data storage.

Be sure to follow best practices when working with UDTs, such as using frozen types and avoiding unnecessary nesting to minimize the complexity. Remember also to keep in mind that UDTs are not supported in some version of Cassandra and not all the drivers support it.

As always, feel free to leave a comment or reach out if you have any questions or need further help. Happy coding!

Execute startup scripts in cassandra docker

Driptaroop Das — Tue, 24 Jan 2023 23:57:34 +0000

Recently I started learning about Cassandra. As with any of my learning journeys, it started with me creating a small-scale application in my local machine to use as my inner feedback loop to tinker with. I chose to have a docker-compose file to start the Cassandra instance for me whenever I wanted.

One of the things I wanted to do was to auto-create and provision the keyspace in the Cassandra container as soon as it started. There were 2 ways I found to do that,

Using `docker-entrypoint-initdb.d` :

if using the bitnami/cassandra images, the script (sh, cql or cql.gz) files at the /docker-entrypoint-initdb.d directory are executed at the startup. Its fairly easy to accomplish that. Assuming the startup scripts are at ./init-scripts/cassandra location, the docker-compose file would look like this.

services:
  cassandra:
    image: bitnami/cassandra:4.0.7
    ports:
      - "7000:7000"
      - "9042:9042"
    environment:
      - CASSANDRA_CLUSTER_NAME=test
    volumes:
      - "./init-scripts/cassandra:/docker-entrypoint-initdb.d"

But somehow I couldn't get the bitnami docker image to work for me without issues. The other option was to use the official cassandra image. But that one didn't have the docker-entrypoint-initdb.d startup script like the bitnami image. The other solution was to use an init-container.

Using init-containers:

The idea of an init-container is fairly simple. In addition to the actual container, one additional container is needed to startup, execute startup scripts on actual container and then stop silently.

services:
  cassandra:
    image: cassandra:4.1.0
    ports:
      - "7000:7000"
      - "9042:9042"
    environment:
      - CASSANDRA_CLUSTER_NAME=test
  init-cassandra:
    image: cassandra:4.1.0
    depends_on:
      - cassandra # IMPORTANT: this init container can only start up after the original container is started
    restart: "no" # IMPORTANT: the restart should be set to "no" so that the init container is used only once after the original container is started
    entrypoint: ["/init.sh"] # executing the init script
    volumes:
      - ./cassandra-init-data.sh:/init.sh # the init script is added via volumes

The init script (./cassandra-init-data.sh in this case) looked like this,

#!/usr/bin/env bash

until printf "" 2>>/dev/null >>/dev/tcp/cassandra/9042; do
    sleep 5;
    echo "Waiting for cassandra...";
done

echo "Creating keyspace"
cqlsh cassandra -u cassandra -p cassandra -e "CREATE KEYSPACE IF NOT EXISTS spring_cassandra WITH replication = {'class': 'SimpleStrategy', 'replication_factor': '1'};"

This script waits for cassandra 9042 port to be available in the original image and then creates a keyspace.

Note: printf "" 2>>/dev/null >>/dev/tcp/cassandra/9042 checks whether any message can be sent to cassandra:9042 port or else it is failing silently. Check this link for more details.

And this is it. It is something new that I learned today and thought it is interesting enough to be shared in here.

Accessing the POST request body in Spring WebClient filters

Driptaroop Das — Mon, 16 Jan 2023 00:34:13 +0000

Accessing the POST request body in Spring WebClient filters can be a bit tricky, as the request body is typically passed as a stream. In this blog post, we'll explore two solutions to this problem.

The problem

In some cases, you may need to access the POST request body in a Spring WebClient filter, in order to perform an operation such as signing the request with JWS header. However, because the request body is passed as a stream, it can be difficult to access and manipulate all at once.

Solution 1

Extending the `ClientHttpRequestDecorator` class

One approach to solving this problem is to extend the ClientHttpRequestDecorator class and override the writeWith method. This allows you to gain access to the DataBuffer publisher, which represents the stream of the request body. You can then concatenate and retrieve the original request by joining the published data buffers using DataBufferUtils.

class BufferingRequestDecorator(delegate: ClientHttpRequest) : ClientHttpRequestDecorator(delegate) {

    override fun writeWith(body: Publisher<out DataBuffer>): Mono<Void> =
        DataBufferUtils.join(body)
            .flatMap { db -> setJwsHeader(extractBytes(db)).then(super.writeWith(Mono.just(db))) }

    private fun setJwsHeader(data: ByteArray = byteArrayOf()): Mono<Void> {
        headers.add("JWS-header", createJws(data)) // or do whatever you want with the data
        return Mono.empty()
    }

    private fun extractBytes(data: DataBuffer): ByteArray {
        val bytes = ByteArray(data.readableByteCount())
        data.read(bytes)
        data.readPosition(0)
        return bytes
    }
}

Once you have this class implemented, you can call it in the WebClient filter and deconstruct the old request and create a new one out of it.

class WebClientBufferingFilter : ExchangeFilterFunction {
    override fun filter(request: ClientRequest, next: ExchangeFunction): Mono<ClientResponse> =
        next.exchange(
            ClientRequest.from(request)
                .body { outputMessage, context ->
                    request.body().insert(
                        BufferingRequestDecorator(outputMessage),
                        context
                    )
                }.build()
        )
}

Create a webclient with this filter

WebClient.builder()
        .filter(WebClientBufferingFilter())
        .build()

Solution 2

Using a custom JSON Encoder

By default, WebClient uses Jackson to encode the data into JSON. When setting up the request, we use the utility BodyInserters.fromValue to create a body inserter for our data. The DefaultRequestBuilder for WebClient keeps this BodyInserter object at the time when the request will be sent, passing it all known message-writers and other relevant contextual information. However, we may need to access the request body before it is sent in order to perform additional actions such as signing or adding an authorization header.

Create a wrapper class around Jackson2JsonEncoder that allows us to intercept the encoded body. Specifically, we will be wrapping the encode method implementation from AbstractJackson2Encoder.

const val REQUEST_CONTEXT_KEY = "test"
class BodyProvidingJsonEncoder : Jackson2JsonEncoder() {
    override fun encode(
        inputStream: Publisher<out Any>,
        bufferFactory: DataBufferFactory,
        elementType: ResolvableType,
        mimeType: MimeType?,
        hints: MutableMap<String, Any>?
    ): Flux<DataBuffer> {
        return super.encode(inputStream, bufferFactory, elementType, mimeType, hints)
            .flatMap { db: DataBuffer ->
                Mono.deferContextual {
                    val clientHttpRequest = it.get<ClientHttpRequest>(REQUEST_CONTEXT_KEY)
                    db
                }
            }
    }

    private fun extractBytes(data: DataBuffer): ByteArray {
        val bytes = ByteArray(data.readableByteCount())
        data.read(bytes)
        data.readPosition(0)
        return bytes
    }
}

Build a custom ReactorClientHttpConnector to put the request in the context.

class MessageSigningHttpConnector : ReactorClientHttpConnector() {
    override fun connect(
        method: HttpMethod,
        uri: URI,
        requestCallback: Function<in ClientHttpRequest, Mono<Void>>
    ): Mono<ClientHttpResponse> {
        // execute the super-class method as usual, but insert an interception into the requestCallback that can
        // capture the request to be saved for this thread.
        return super.connect(
            method, uri
        ) { incomingRequest: ClientHttpRequest ->
            requestCallback.apply(incomingRequest).contextWrite {
                it.put(
                    REQUEST_CONTEXT_KEY,
                    incomingRequest
                )
            }
        }
    }
}

Define a custom ExchangeFunction using the utility ExchangeFunctions.create() which accepts a custom HttpConnector. This connector has access to the function that makes the request. It is at this point that we can get a handle on the ClientHttpRequest and wait for the body to be serialized so that the header can be added.

val httpConnector = MessageSigningHttpConnector()
    val bodyProvidingJsonEncoder = BodyProvidingJsonEncoder()

    val client = WebClient.builder()
        .exchangeFunction(ExchangeFunctions.create(
            httpConnector,
            ExchangeStrategies
                .builder()
                .codecs { clientDefaultCodecsConfigurer: ClientCodecConfigurer ->
                    clientDefaultCodecsConfigurer.defaultCodecs().jackson2JsonEncoder(bodyProvidingJsonEncoder)
                    clientDefaultCodecsConfigurer.defaultCodecs()
                        .jackson2JsonDecoder(Jackson2JsonDecoder(ObjectMapper(), MediaType.APPLICATION_JSON))
                }
                .build()
        ))
        .build()

As always, be sure to test your codes thoroughly.

Solution 2 is originally found in https://andrew-flower.com/blog/Custom-HMAC-Auth-with-Spring-WebClient. I tinkered with it a bit before finding solution 1 which is somehow inspired by https://github.com/spring-projects/spring-framework/issues/26489. Finally, I decided to go with solution 1 as I found it to be a bit more simple and elegant.

originally published at: https://blog.dripto.xyz/accessing-the-post-request-body-in-spring-webclient-filters

DEV Community: Driptaroop Das

Explaining PostgreSQL Explain

Demo

Explain

Explain Enhancement

Explanation

Visualization Tools

Summary

Essential Reads

Distributed job runner/task scheduler with postgres and spring

Example with Spring boot

Setup

Problem

Solution

Using layered docker images over fat-jar docker images in spring boot application

TL;DR

The problem

What can be done?

Understanding Docker Images and Layers

Spring Boot and Layered Jars

Multistage Dockerfiles

Additional Advantage:

Conclusion:

Further Read:

Declarative HTTP Clients in Spring Boot 3 with Kotlin

Lazily Initializing Implicit Logger in Every Class in Kotlin

How Kotlin(JVM) logger declaration works

How Lombok does this with less boilerplate in Java

Can we do something better in Kotlin?

Using user-defined types (UDT) with Apache Cassandra in Spring Boot 3.x

Creating the data model in cassandra

Setup spring boot 3.x application

Setup data model in Spring boot application

Use the repository to call Cassandra

Conclusion

Execute startup scripts in cassandra docker

Using docker-entrypoint-initdb.d :

Using init-containers:

Accessing the POST request body in Spring WebClient filters

The problem

Solution 1

Extending the ClientHttpRequestDecorator class

Solution 2

Using a custom JSON Encoder

`Explain`

Using `docker-entrypoint-initdb.d` :

Extending the `ClientHttpRequestDecorator` class