<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Ramin Orujov on Medium]]></title>
        <description><![CDATA[Stories by Ramin Orujov on Medium]]></description>
        <link>https://medium.com/@raminorujov?source=rss-65f49efe3c3d------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*cs1bD_CeUWOcBBt7HplB_A@2x.jpeg</url>
            <title>Stories by Ramin Orujov on Medium</title>
            <link>https://medium.com/@raminorujov?source=rss-65f49efe3c3d------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 18:16:04 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@raminorujov/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[A Small Win: My First Contribution to PostgreSQL JDBC Driver]]></title>
            <link>https://medium.com/@raminorujov/a-small-win-my-first-contribution-to-postgresql-jdbc-driver-8c8cf00f209d?source=rss-65f49efe3c3d------2</link>
            <guid isPermaLink="false">https://medium.com/p/8c8cf00f209d</guid>
            <category><![CDATA[java]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[jdbc-driver]]></category>
            <dc:creator><![CDATA[Ramin Orujov]]></dc:creator>
            <pubDate>Sat, 12 Jul 2025 19:24:48 GMT</pubDate>
            <atom:updated>2025-07-12T19:24:48.248Z</atom:updated>
            <content:encoded><![CDATA[<p>🚀 Excited to share my small contribution to the <strong>PostgreSQL JDBC Driver</strong>!</p><p>As of version <strong>v42.7.6</strong>, DatabaseMetaData.getIndexInfo() now includes <strong>index comments</strong> — a small but helpful improvement for those working with database introspection tools.</p><p>Check out the release notes here:<br>🔗 <a href="https://github.com/pgjdbc/pgjdbc/releases/tag/REL42.7.6">https://github.com/pgjdbc/pgjdbc/releases/tag/REL42.7.6</a></p><p>And here’s the original PR:<br>🔧 <a href="https://github.com/pgjdbc/pgjdbc/pull/3513/files">https://github.com/pgjdbc/pgjdbc/pull/3513/files</a></p><p>Glad to be part of the open source community! 🙌</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*aFOqKWsrqwhWdZ0eAkv6sw.png" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8c8cf00f209d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Postgres Story: How to efficiently implement a unique constraint with minimum locks]]></title>
            <link>https://medium.com/@raminorujov/postgres-story-how-to-efficiently-implement-a-unique-constraint-with-minimum-locks-aa3f72b8cf1b?source=rss-65f49efe3c3d------2</link>
            <guid isPermaLink="false">https://medium.com/p/aa3f72b8cf1b</guid>
            <category><![CDATA[locking]]></category>
            <category><![CDATA[constraints]]></category>
            <category><![CDATA[performance]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[index]]></category>
            <dc:creator><![CDATA[Ramin Orujov]]></dc:creator>
            <pubDate>Wed, 19 Feb 2025 06:01:42 GMT</pubDate>
            <atom:updated>2025-02-19T06:01:42.210Z</atom:updated>
            <content:encoded><![CDATA[<p>In this blog post I’ll explain alternative way of implementing unique database constraints efficiently on big non-partitioned tables. Adding a constraint using an existing index can be helpful in situations where a new constraint needs to be added without blocking table updates for a long time.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/639/1*rMFCDXsgmECbnn3anwXyjQ.png" /></figure><p>Adding unique constraint requires the most restrictive lock<a href="https://postgres-locks.husseinnasser.com/?pglock=AccessExclusiveLock"> <strong>ACCESS EXCLUSIVE (AccessExclusiveLock)</strong> </a>which blocks all queries and DML operations. Postgres has to build unique index to enforce the constraint and index build locks out writes (but not reads) on the table until it’s done.</p><p>Fortunately Postgres supports adding unique constraint based on existing index.</p><pre>ALTER TABLE blog.workflow<br>ADD CONSTRAINT series_id_revision_unique_idx UNIQUE (series_id, revision);</pre><p><strong>Step 1. Create new unique index concurrently</strong></p><pre>CREATE UNIQUE INDEX CONCURRENTLY <br>IF NOT EXISTS series_id_revision_unique_idx<br>    ON blog.workflow(series_id, revision);</pre><p>Building new index concurrently acquires <a href="https://postgres-locks.husseinnasser.com/?pglock=ShareUpdateExclusiveLock"><strong>ShareUpdateExclusiveLock</strong></a> which doesn’t block transactions with select, select for update, insert, update, delete, merge operations.</p><p><strong>Step 2. Add unique constraint using index</strong></p><pre>ALTER TABLE IF EXISTS blog.workflow<br>ADD CONSTRAINT series_id_revision_unique_idx UNIQUE USING INDEX series_id_revision_unique_idx;</pre><p>This operation requires <a href="https://postgres-locks.husseinnasser.com/?pglock=AccessExclusiveLock"><strong>AccessExclusiveLock</strong></a> table lock. Since it will use the existing unique index, lock time will be minimum just to update system catalog metadata.</p><p><strong>Restrictions</strong></p><ol><li>It is not supported on partitioned tables</li><li>The index cannot have expression columns nor be a partial index.</li><li>It must be a b-tree index with default sort ordering.</li><li>If a constraint name is provided then the index will be renamed to match the constraint name. Otherwise the constraint will be named the same as the index.</li><li>The index will be“owned” by the constraint, in the same way as if the index had been built by a regular ADD UNIQUE command. In particular, dropping the constraint will make the index disappear too.</li></ol><p><strong>References</strong></p><ol><li><a href="https://www.postgresql.org/docs/17/explicit-locking.html">https://www.postgresql.org/docs/17/explicit-locking.html</a></li><li><a href="https://www.postgresql.org/docs/17/sql-altertable.html#SQL-ALTERTABLE-DESC-ADD-TABLE-CONSTRAINT-USING-INDEX">https://www.postgresql.org/docs/17/sql-altertable.html#SQL-ALTERTABLE-DESC-ADD-TABLE-CONSTRAINT-USING-INDEX</a></li><li><a href="https://medium.com/@hnasr/postgres-locks-a-deep-dive-9fc158a5641c">https://medium.com/@hnasr/postgres-locks-a-deep-dive-9fc158a5641c</a></li><li><a href="https://postgres-locks.husseinnasser.com/">https://postgres-locks.husseinnasser.com/</a></li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=aa3f72b8cf1b" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Spring Boot Story: A simple and effective way to manage cron job]]></title>
            <link>https://medium.com/@raminorujov/spring-boot-story-a-simple-and-effective-way-to-manage-cron-job-798d47a6e850?source=rss-65f49efe3c3d------2</link>
            <guid isPermaLink="false">https://medium.com/p/798d47a6e850</guid>
            <category><![CDATA[scheduled-tasks]]></category>
            <category><![CDATA[cronjob]]></category>
            <category><![CDATA[configuration]]></category>
            <category><![CDATA[spring-boot]]></category>
            <category><![CDATA[spring-framework]]></category>
            <dc:creator><![CDATA[Ramin Orujov]]></dc:creator>
            <pubDate>Tue, 18 Feb 2025 07:09:40 GMT</pubDate>
            <atom:updated>2025-02-18T07:13:59.453Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>Summary</strong></p><p>In this post I’ll explain a simple way to manage Spring scheduled job execution and configuration via overriding environment variables.</p><p>Sample project: <a href="https://github.com/raminorujov/spring-boot-cron-job-management">https://github.com/raminorujov/spring-boot-cron-job-management</a></p><p>Almost all services need some kind of scheduled jobs for different purposes:</p><ol><li>Simple email notification job</li><li>Kafka sync job following <a href="https://microservices.io/patterns/data/transactional-outbox.html">outbox pattern</a></li><li>Data back-filling job</li></ol><p>Cron job typically has the following basic configuration:</p><ol><li>Job state: enable boolean flag</li><li>Run schedule — from every 10 minutes to 1 hour</li><li>Job specific parameters like batch size for data back-filling job.</li></ol><p>Spring framework provides rich support to externalized and override configuration</p><p>Spring Framework 5.3 enabled <a href="https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/scheduling/support/CronExpression.html">cron expressions</a> in scheduled job configuration.</p><p>Cron expression has six single space-separated time and date fields:</p><pre>┌───────────── second (0-59)<br> │ ┌───────────── minute (0 - 59)<br> │ │ ┌───────────── hour (0 - 23)<br> │ │ │ ┌───────────── day of the month (1 - 31)<br> │ │ │ │ ┌───────────── month (1 - 12) (or JAN-DEC)<br> │ │ │ │ │ ┌───────────── day of the week (0 - 7)<br> │ │ │ │ │ │          (0 or 7 is Sunday, or MON-SUN)<br> │ │ │ │ │ │<br> * * * * * *</pre><p>Sample cron expressions and macros</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4_rDamOB41Pu4fx8tQqVhA.png" /><figcaption>Example cron expression and macros</figcaption></figure><p>Here is a simple DataBackFill Cron Job with externalized configuration</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/4ca6e1b83012945aefc3878985dcd084/href">https://medium.com/media/4ca6e1b83012945aefc3878985dcd084/href</a></iframe><p>Default job configuration is enabled to run every 2 min with batch size 100</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/99c571bf19b2668577acb1fd8b518181/href">https://medium.com/media/99c571bf19b2668577acb1fd8b518181/href</a></iframe><p>Logs from job run with default configuration</p><pre>2025-02-18T00:28:10.554+04:00  INFO 17879 --- [cron-demo] [           main] c.raminorujov.cron.CronDemoApplication   : Starting CronDemoApplication using Java 21.0.4 with PID 17879 (/Users/ramin/Desktop/blog/spring-boot-cron-job-management/cron-demo/build/classes/java/main started by ramin in /Users/ramin/Desktop/blog/spring-boot-cron-job-management/cron-demo)<br>2025-02-18T00:28:10.556+04:00  INFO 17879 --- [cron-demo] [           main] c.raminorujov.cron.CronDemoApplication   : No active profile set, falling back to 1 default profile: &quot;default&quot;<br>2025-02-18T00:28:10.874+04:00  INFO 17879 --- [cron-demo] [           main] com.raminorujov.cron.DataBackfillJob     : DataBackfillJob enabled: true<br>2025-02-18T00:28:10.875+04:00  INFO 17879 --- [cron-demo] [           main] com.raminorujov.cron.DataBackfillJob     : DataBackfillJob batchSize: 100<br>2025-02-18T00:28:10.947+04:00  INFO 17879 --- [cron-demo] [           main] c.raminorujov.cron.CronDemoApplication   : Started CronDemoApplication in 0.63 seconds (process running for 1.002)<br><br>2025-02-18T00:30:00.032+04:00  INFO 17879 --- [cron-demo] [   scheduling-1] com.raminorujov.cron.DataBackfillJob     : Starting data back-fill job at 2025-02-17T21:30:00.032292Z<br>2025-02-18T00:30:00.033+04:00  INFO 17879 --- [cron-demo] [   scheduling-1] com.raminorujov.cron.DataBackfillJob     : Fetching and updating data with batch size: 100<br>2025-02-18T00:30:00.033+04:00  INFO 17879 --- [cron-demo] [   scheduling-1] com.raminorujov.cron.DataBackfillJob     : Finished data back-fill job at 2025-02-17T21:30:00.033157Z<br><br>2025-02-18T00:31:59.954+04:00  INFO 17879 --- [cron-demo] [   scheduling-1] com.raminorujov.cron.DataBackfillJob     : Starting data back-fill job at 2025-02-17T21:31:59.953674Z<br>2025-02-18T00:31:59.957+04:00  INFO 17879 --- [cron-demo] [   scheduling-1] com.raminorujov.cron.DataBackfillJob     : Fetching and updating data with batch size: 100<br>2025-02-18T00:31:59.957+04:00  INFO 17879 --- [cron-demo] [   scheduling-1] com.raminorujov.cron.DataBackfillJob     : Finished data back-fill job at 2025-02-17T21:31:59.957521Z</pre><p><strong>Overriding job configuration via ENV vars</strong></p><p>Rules for binding ENV vars <a href="https://docs.spring.io/spring-boot/reference/features/external-config.html#features.external-config.typesafe-configuration-properties.relaxed-binding.environment-variables">https://docs.spring.io/spring-boot/reference/features/external-config.html#features.external-config.typesafe-configuration-properties.relaxed-binding.environment-variables</a></p><pre>Spring Boot’s relaxed binding rules are, as much as possible, designed to be compatible with these naming restrictions.<br>To convert a property name in the canonical-form to an environment variable name you can follow these rules:<br>  Replace dots (.) with underscores (_).<br>  Remove any dashes (-).<br>  Convert to uppercase.<br><br>For example, the configuration property spring.main.log-startup-info would be an environment variable named SPRING_MAIN_LOGSTARTUPINFO.</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bSyobPs9An9a4cxRL1oWdg.png" /><figcaption>Spring external configuration relaxed binding rules</figcaption></figure><p>Considering these binding rules we need the following ENV vars to override the cron job configuration, all Continuous Deployment tools support overriding environment variables</p><pre>JOB_DATABACKFILL_ENABLED=true<br>JOB_DATABACKFILL_BATCHSIZE=1000<br>JOB_DATABACKFILL_CRONEXPRESSION=0 0/5 * * * *</pre><blockquote><strong>WARNING — Updated environment variables will only be effective in new instances of spring boot application. When running spring application in kubernetes environment, updating ENV vars will cause termination of current running pods and will start rolling new pods with updated configuration.</strong></blockquote><p>Logs from job overridden configuration batch-size=1000, cron=every 5 min</p><pre>2025-02-18T00:33:37.690+04:00  INFO 18496 --- [cron-demo] [           main] c.raminorujov.cron.CronDemoApplication   : Starting CronDemoApplication using Java 21.0.4 with PID 18496 (/Users/ramin/Desktop/blog/spring-boot-cron-job-management/cron-demo/build/classes/java/main started by ramin in /Users/ramin/Desktop/blog/spring-boot-cron-job-management/cron-demo)<br>2025-02-18T00:33:37.691+04:00  INFO 18496 --- [cron-demo] [           main] c.raminorujov.cron.CronDemoApplication   : No active profile set, falling back to 1 default profile: &quot;default&quot;<br>2025-02-18T00:33:37.871+04:00  INFO 18496 --- [cron-demo] [           main] com.raminorujov.cron.DataBackfillJob     : DataBackfillJob enabled: true<br>2025-02-18T00:33:37.872+04:00  INFO 18496 --- [cron-demo] [           main] com.raminorujov.cron.DataBackfillJob     : DataBackfillJob batchSize: 1000<br>2025-02-18T00:33:37.928+04:00  INFO 18496 --- [cron-demo] [           main] c.raminorujov.cron.CronDemoApplication   : Started CronDemoApplication in 0.405 seconds (process running for 0.73)<br><br>2025-02-18T00:35:00.029+04:00  INFO 18496 --- [cron-demo] [   scheduling-1] com.raminorujov.cron.DataBackfillJob     : Starting data back-fill job at 2025-02-17T21:35:00.028791Z<br>2025-02-18T00:35:00.029+04:00  INFO 18496 --- [cron-demo] [   scheduling-1] com.raminorujov.cron.DataBackfillJob     : Fetching and updating data with batch size: 1000<br>2025-02-18T00:35:00.029+04:00  INFO 18496 --- [cron-demo] [   scheduling-1] com.raminorujov.cron.DataBackfillJob     : Finished data back-fill job at 2025-02-17T21:35:00.029573Z<br><br>2025-02-18T00:40:00.018+04:00  INFO 18496 --- [cron-demo] [   scheduling-1] com.raminorujov.cron.DataBackfillJob     : Starting data back-fill job at 2025-02-17T21:40:00.017877Z<br>2025-02-18T00:40:00.020+04:00  INFO 18496 --- [cron-demo] [   scheduling-1] com.raminorujov.cron.DataBackfillJob     : Fetching and updating data with batch size: 1000<br>2025-02-18T00:40:00.020+04:00  INFO 18496 --- [cron-demo] [   scheduling-1] com.raminorujov.cron.DataBackfillJob     : Finished data back-fill job at 2025-02-17T21:40:00.020470Z</pre><p><strong>Disabling cron job execution</strong></p><p>Overriding job configuration actually will not stop cron jobs. Spring Framework will continue trigger jobs based on cron schedule (every 2 min in my example)</p><pre>-- JOB_DATABACKFILL_ENABLED=false<br><br>2025-02-18T00:41:40.665+04:00  INFO 19454 --- [cron-demo] [           main] c.raminorujov.cron.CronDemoApplication   : Starting CronDemoApplication using Java 21.0.4 with PID 19454 (/Users/ramin/Desktop/blog/spring-boot-cron-job-management/cron-demo/build/classes/java/main started by ramin in /Users/ramin/Desktop/blog/spring-boot-cron-job-management/cron-demo)<br>2025-02-18T00:41:40.666+04:00  INFO 19454 --- [cron-demo] [           main] c.raminorujov.cron.CronDemoApplication   : No active profile set, falling back to 1 default profile: &quot;default&quot;<br>2025-02-18T00:41:40.977+04:00  INFO 19454 --- [cron-demo] [           main] com.raminorujov.cron.DataBackfillJob     : DataBackfillJob enabled: false<br>2025-02-18T00:41:40.978+04:00  INFO 19454 --- [cron-demo] [           main] com.raminorujov.cron.DataBackfillJob     : DataBackfillJob batchSize: 100<br>2025-02-18T00:41:41.038+04:00  INFO 19454 --- [cron-demo] [           main] c.raminorujov.cron.CronDemoApplication   : Started CronDemoApplication in 0.592 seconds (process running for 1.118)<br>2025-02-18T00:42:00.017+04:00  INFO 19454 --- [cron-demo] [   scheduling-1] com.raminorujov.cron.DataBackfillJob     : Data back-fill job is disabled<br><br>2025-02-18T00:44:00.014+04:00  INFO 19454 --- [cron-demo] [   scheduling-1] com.raminorujov.cron.DataBackfillJob     : Data back-fill job is disabled<br><br>2025-02-18T00:46:00.023+04:00  INFO 19454 --- [cron-demo] [   scheduling-1] com.raminorujov.cron.DataBackfillJob     : Data back-fill job is disabled<br><br>2025-02-18T00:48:00.013+04:00  INFO 19454 --- [cron-demo] [   scheduling-1] com.raminorujov.cron.DataBackfillJob     : Data back-fill job is disabled</pre><p>Spring Framework has special constant <strong>Scheduled.<em>CRON_DISABLED</em></strong><em>=”-” </em>to disable scheduled jobs.</p><p><em>I use this to disable execution of scheduled jobs in integration tests and validate job logic with it’s own integration test case.</em></p><p>Logs with correctly disabled job</p><pre>-- JOB_DATABACKFILL_CRONEXPRESSION=-<br>2025-02-18T00:49:40.498+04:00  INFO 20350 --- [cron-demo] [           main] c.raminorujov.cron.CronDemoApplication   : Starting CronDemoApplication using Java 21.0.4 with PID 20350 (/Users/ramin/Desktop/blog/spring-boot-cron-job-management/cron-demo/build/classes/java/main started by ramin in /Users/ramin/Desktop/blog/spring-boot-cron-job-management/cron-demo)<br>2025-02-18T00:49:40.500+04:00  INFO 20350 --- [cron-demo] [           main] c.raminorujov.cron.CronDemoApplication   : No active profile set, falling back to 1 default profile: &quot;default&quot;<br>2025-02-18T00:49:40.697+04:00  INFO 20350 --- [cron-demo] [           main] com.raminorujov.cron.DataBackfillJob     : DataBackfillJob enabled: true<br>2025-02-18T00:49:40.698+04:00  INFO 20350 --- [cron-demo] [           main] com.raminorujov.cron.DataBackfillJob     : DataBackfillJob batchSize: 100<br>2025-02-18T00:49:40.757+04:00  INFO 20350 --- [cron-demo] [           main] c.raminorujov.cron.CronDemoApplication   : Started CronDemoApplication in 0.434 seconds (process running for 0.839)<br><br>Process finished with exit code 0</pre><p><strong>References</strong></p><ol><li><a href="https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/scheduling/support/CronExpression.html">https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/scheduling/support/CronExpression.html</a></li><li><a href="https://spring.io/blog/2020/11/10/new-in-spring-5-3-improved-cron-expressions">https://spring.io/blog/2020/11/10/new-in-spring-5-3-improved-cron-expressions</a></li><li><a href="https://docs.spring.io/spring-boot/reference/features/external-config.html">https://docs.spring.io/spring-boot/reference/features/external-config.html</a></li><li>Binding rules for ENV vars <a href="https://docs.spring.io/spring-boot/reference/features/external-config.html#features.external-config.typesafe-configuration-properties.relaxed-binding.environment-variables">https://docs.spring.io/spring-boot/reference/features/external-config.html#features.external-config.typesafe-configuration-properties.relaxed-binding.environment-variables</a></li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=798d47a6e850" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[PostgreSQL Story: How to optimize the query by refactoring data model]]></title>
            <link>https://medium.com/@raminorujov/postgresql-story-how-to-optimize-the-query-by-refactoring-data-model-ad88f32e13fd?source=rss-65f49efe3c3d------2</link>
            <guid isPermaLink="false">https://medium.com/p/ad88f32e13fd</guid>
            <category><![CDATA[data-modeling]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[optimization]]></category>
            <dc:creator><![CDATA[Ramin Orujov]]></dc:creator>
            <pubDate>Mon, 17 Feb 2025 15:46:32 GMT</pubDate>
            <atom:updated>2025-02-17T17:22:45.392Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>Summary</strong></p><p>Adding new index is not the only way of optimizing query. In this story I explain the practical way of optimizing query by enhancing data model and re-writing query. Knowing data model, query access patterns and thinking out the box can give you an interesting alternative ways of potential optimizations.</p><p><strong>Sample data model with 1 million rows</strong></p><p><a href="https://github.com/raminorujov/blog-optimize-query-by-refactoring-data-model">https://github.com/raminorujov/blog-optimize-query-by-refactoring-data-model</a></p><p>Data model is workflow table from multi-tenant application. Workflow represents important entity in the business domain. Workflow status is enum value (DRAFT, PUBLISHED, ARCHIVED). Series_id and revision pair is unique.</p><pre>CREATE TABLE if not exists blog.workflow (<br> id uuid NOT NULL,<br> name text NOT NULL,<br> description text NULL,<br> status text not null, -- DRAFT, PUBLISHED, ARCHIVED<br> series_id uuid NOT NULL,<br> revision int NOT NULL,<br> created_by text not null,<br> created_at timestamp not null,<br> tenant_id text not null,<br> CONSTRAINT workflow_pk PRIMARY KEY (id)<br>);</pre><p><strong>Workflow status transition diagram</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/446/1*wGLBnmharkENd_SKS274vw.png" /></figure><p><strong>Workflow status transition rules</strong></p><ol><li>Initial workflow is created as DRAFT with revision 1.</li><li>Publishing draft workflow will update status to PUBLISHED, keep current revision and automatically archive previous revision of any.</li><li>Published workflow can be revised. Revise operation will create a DRAFT version with new revision in the same seriesId.</li><li>Published workflow can be archived. Archive operation will update status to ARCHIVED and keep current revision.</li><li>Archived is the terminal state. No operation is allowed except duplicating workflow.</li></ol><p>Here is workflow data distribution across tenants</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/a9947d7c36fdbb05570d3ffd8d7e2eec/href">https://medium.com/media/a9947d7c36fdbb05570d3ffd8d7e2eec/href</a></iframe><p><strong>Workflow data observation</strong></p><ol><li>90% of the workflows have only 1 revision. 40% of them are DRAFT, 60% are PUBLISHED.</li><li>6% of the workflows have 2 revisions of ARCHIVED and DRAFT. It means user created new draft workflow(rev1), published(rev1) and then revised it(rev2).</li><li>3% of the workflows have 2 revisions of ARCHIVED and PUBLISHED. It means user created new draft workflow(rev1), published(rev1), revised it(rev2) and published it(rev2).</li><li>1% of the workflows have 3 or more revisions. For the simplicity I’ll assume revision 1 and 2 are ARCHIVED, revision 3 is PUBLISHED and revision 4 is DRAFT.</li></ol><p>For this demo I’ll generate total 1M workflow according to observation.</p><p><strong>Script to generate data for observation 1 – 900K workflows</strong></p><pre>insert into blog.workflow<br>select gen_random_uuid() as id, blog.random_string(10) as name, blog.random_string(10) as description, &#39;PUBLISHED&#39; as status, gen_random_uuid() as series_id, 1 as revision, <br> &#39;system&#39; as created_by, now() as create_at, td.tenant_id <br>from blog.tenant_dist td cross join lateral generate_series(1, round(1000000 * td.dist * 0.9 * 0.6))<br>union all<br>select gen_random_uuid() as id, blog.random_string(10) as name, blog.random_string(10) as description, &#39;DRAFT&#39; as status, gen_random_uuid() as series_id, 1 as revision, <br> &#39;system&#39;, now(), td.tenant_id<br>from blog.tenant_dist td cross join lateral generate_series(1, round(1000000 * td.dist * 0.9 * 0.4));</pre><p><strong>Script to generate data for observation 2 – 60K workflows</strong></p><pre>-- 6% revision 2 ARCHIVED + DRAFT<br>insert into blog.workflow<br>with archived as (<br> select gen_random_uuid() as id, blog.random_string(10) as name, blog.random_string(10) as description, &#39;ARCHIVED&#39; as status, gen_random_uuid() as series_id, 1 as revision, <br>  &#39;system&#39; as created_by, now() as created_at, td.tenant_id<br> from blog.tenant_dist td cross join lateral generate_series(1, round(1000000 * td.dist * 0.03))<br>)<br>,draft as (<br> select gen_random_uuid() as id, concat(name, &#39; 2&#39;) as name, description, &#39;DRAFT&#39; as status, series_id, 2 as revision, <br>  created_by, now(), tenant_id<br> from archived<br>)<br>select *<br>from archived<br>union all<br>select *<br>from draft;</pre><p><strong>Script to generate data for observation 3 – 30K workflows</strong></p><pre>-- 3% revision 2 ARCHIVED + PUBLISHED<br>insert into blog.workflow<br>with archived as (<br> select gen_random_uuid() as id, blog.random_string(10) as name, blog.random_string(10) as description, &#39;ARCHIVED&#39; as status, gen_random_uuid() as series_id, 1 as revision, <br>  &#39;system&#39; as created_by, now() as created_at, td.tenant_id<br> from blog.tenant_dist td cross join lateral generate_series(1, round(1000000 * td.dist * 0.015))<br>)<br>,published as (<br> select gen_random_uuid() as id, concat(name, &#39; 2&#39;) as name, description, &#39;PUBLISHED&#39; as status, series_id, 2 as revision, <br>  created_by, now(), tenant_id<br> from archived<br>)<br>select *<br>from archived<br>union all<br>select *<br>from published;</pre><p><strong>Script to generate data for observation 4 — 10K workflows</strong></p><pre>-- 1% 4 revision 2x ARCHIVED + PUBLISHED + DRAFT<br>insert into blog.workflow<br>with archived1 as (<br> select gen_random_uuid() as id, blog.random_string(10) as name, blog.random_string(10) as description, &#39;ARCHIVED&#39; as status, gen_random_uuid() as series_id, 1 as revision, <br>  &#39;system&#39; as created_by, now() as created_at, td.tenant_id<br> from blog.tenant_dist td cross join lateral generate_series(1, round(1000000 * td.dist * 0.01/4))<br>)<br>,archived2 as (<br> select gen_random_uuid() as id, concat(name, &#39; 2&#39;) as name, description, status, series_id, 2 as revision, created_by, now(), tenant_id<br> from archived1<br>)<br>,published as (<br> select gen_random_uuid() as id, concat(name, &#39; 3&#39;) as name, description, &#39;PUBLISHED&#39; as status, series_id, 3 as revision, created_by, now(), tenant_id<br> from archived2<br>)<br>,draft as (<br> select gen_random_uuid() as id, concat(name, &#39; 4&#39;) as name, description, &#39;DRAFT&#39; as status, series_id, 4 as revision, created_by, now(), tenant_id<br> from published<br>)<br>select *<br>from archived1<br>union all<br>select *<br>from archived2<br>union all<br>select *<br>from published<br>union all<br>select *<br>from draft;</pre><p><strong>Workflow search patterns</strong></p><ol><li>Search query always includes tenant_id and limited to first 100 matching workflows</li><li>Workflows can be searched by case insensitive name</li><li>Workflows can be filtered by multiple statuses</li><li>Workflows can be filtered to fetch only latest revision</li></ol><p>This is the current query</p><pre>SELECT *<br>FROM (<br>    SELECT w.*, max(w.revision) OVER (PARTITION BY w.tenant_id, w.series_id) as max_rev<br>    FROM blog.workflow w<br>    WHERE tenant_id = :tenantId<br>        AND (:statuses IS NULL or status = any(cast(:statuses as text[])))<br>        AND name ILIKE CONCAT(&#39;%&#39;, :name, &#39;%&#39;)<br>) AS t<br>WHERE (:latest is null or revision = t.max_rev)<br>ORDER BY created_at desc<br>limit 100;</pre><p>Existing indices on the workflow table</p><pre>create unique index concurrently if not exists workflow_series_id_revision_unique_idx on<br> blog.workflow(series_id, revision);<br><br>create index concurrently if not exists workflow_created_date_idx<br> ON blog.workflow(tenant_id, created_at desc);<br><br>analyze blog.workflow; </pre><p>Execution plan for the largest customer tenant_id=’tenant1&#39; (900K workflows) and other params are null, fetching first 100 records</p><pre>Limit  (cost=140120.05..140120.30 rows=100 width=93) (actual time=2164.058..2164.064 rows=100 loops=1)<br>  Buffers: shared hit=986092 read=20252 written=10753<br>  -&gt;  Sort  (cost=140120.05..141619.32 rows=599707 width=93) (actual time=2164.057..2164.059 rows=100 loops=1)<br>        Sort Key: w.created_at DESC<br>        Sort Method: top-N heapsort  Memory: 39kB<br>        Buffers: shared hit=986092 read=20252 written=10753<br>        -&gt;  WindowAgg  (cost=0.42..117199.68 rows=599707 width=93) (actual time=0.125..2066.521 rows=600000 loops=1)<br>              Buffers: shared hit=986092 read=20252 written=10753<br>              -&gt;  Index Scan using workflow_series_id_revision_unique_idx on workflow w  (cost=0.42..108204.08 rows=599707 width=89) (actual time=0.051..1833.409 rows=600000 loops=1)<br>                    Filter: ((tenant_id = &#39;tenant1&#39;::text) AND (name ~~* concat(&#39;%&#39;, NULL::unknown, &#39;%&#39;)))<br>                    Rows Removed by Filter: 400000<br>                    Buffers: shared hit=986092 read=20252 written=10753<br>Planning:<br>  Buffers: shared hit=41 read=3<br>Planning Time: 1.051 ms<br>Execution Time: 2164.106 ms</pre><p><strong>Observations</strong></p><ol><li>Sort is supported by <strong>workflow_series_id_revision_unique_idx</strong> index.</li><li>60% of total execution plan is spent on to calculate max revision</li><li>Max revision is calculated for all rows</li><li>Max revision aggregation has high IO reads <strong>Buffers: shared hit=986092 read=20252 written=10753</strong></li></ol><pre>SELECT pg_size_pretty(1017097 * current_setting(&#39;block_size&#39;)::bigint);<br>7946 MB</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/915/1*n5qfMU2LECWLhtXmzRS0rA.png" /></figure><p><strong>Common search queries and execution time for the largest tenant</strong></p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/2446d8e04a5406aa78954aa360810adb/href">https://medium.com/media/2446d8e04a5406aa78954aa360810adb/href</a></iframe><p><strong>Observations</strong></p><ol><li>Search queries by name and/or status switches to execution plan with <strong>Parallel Seq Scan on workflow</strong>, since existing index doesn’t have name and status columns.</li><li>Search queries by multiple statuses switches to execution plan with <strong>Index Scan using workflow_series_id_revision_unique_idx</strong></li></ol><p><strong>Data model refactoring v1 — Adding is_latest_revision boolean flag</strong></p><p>Since calculating max/latest revision is expensive, it’s better to add boolean <strong>is_latest_revision</strong> column to workflow table. Though it requires additional update when revising workflow, previous revision’s is_latest_revision must be updated to false for consistency.</p><pre>CREATE TABLE if not exists blog.workflow_v1 (<br>    id uuid NOT NULL,<br>    name text NOT NULL,<br>    description text NULL,<br>    status text not null,<br>    series_id uuid NOT NULL,<br>    revision int NOT NULL,<br>    is_latest_revision boolean not null,<br>    created_by text not null,<br>    created_at timestamp not null,<br>    tenant_id text not null,<br>    CONSTRAINT workflow_v1_pk PRIMARY KEY (id)<br>);</pre><p><strong>Populating is_latest_revision from existing data</strong></p><pre>insert into blog.workflow_v1<br>select w.id, w.name, w.description, w.status, w.series_id, w.revision,<br>       case<br>           when w.revision = (<br>               select max(v.revision)<br>               from blog.workflow v<br>               where v.tenant_id = w.tenant_id<br>                 and v.series_id = w.series_id<br>           ) then true<br>           else false<br>           end as is_latest_revision,<br>       w.created_by, w.created_at, w.tenant_id<br>from blog.workflow w;</pre><pre>create unique index concurrently if not exists workflow_v1_series_id_revision_unique_idx on<br>    blog.workflow_v1(series_id, revision);<br>create index concurrently if not exists workflow_v1_created_date_idx<br>    ON blog.workflow_v1 (tenant_id, created_at desc);<br>analyze blog.workflow_v1;</pre><p>New version of search query and execution plan for the largest customer tenant_id=’tenant1&#39; (900K workflows) and other params are null, fetching first 100 records</p><pre>explain (analyze, buffers)<br>SELECT w.*<br>FROM blog.workflow_v1 w<br>WHERE tenant_id = :tenantId<br>    AND (:statuses IS NULL or status = any(cast(:statuses as text[])))<br>    AND name ILIKE CONCAT(&#39;%&#39;, :name, &#39;%&#39;)<br>    and (:latest is null or w.is_latest_revision = :latest)<br>ORDER BY created_at desc<br>limit 100;<br><br>Limit  (cost=0.42..10.55 rows=100 width=90) (actual time=0.017..0.080 rows=100 loops=1)<br>  Buffers: shared hit=5<br>  -&gt;  Index Scan using workflow_v1_created_date_idx on workflow_v1 w  (cost=0.42..60802.03 rows=600573 width=90) (actual time=0.016..0.069 rows=100 loops=1)<br>        Index Cond: (tenant_id = &#39;tenant1&#39;::text)<br>        Filter: (name ~~* concat(&#39;%&#39;, NULL::unknown, &#39;%&#39;))<br>        Buffers: shared hit=5<br>Planning:<br>  Buffers: shared hit=25<br>Planning Time: 0.206 ms<br>Execution Time: 0.100 ms</pre><p><strong>Observations</strong></p><ol><li>Cost is only <strong>10.55 ms</strong>, compared to original query cost <strong>140120.30 ms</strong>, improvement is more <strong>1.3 million times.</strong></li><li>IO cost is <strong>Buffers: shared hit=5</strong>, compared to original query total IO cost 1017097 (<strong>Buffers: shared hit=986092 read=20252 written=10753)</strong>, improvement is more than 203K times.</li><li>Time is 0.100 ms, compared to original query time <strong>2164.106 ms</strong>, improvement is more than 21 million times.</li></ol><p><strong>V1 Common search queries and execution time for the largest tenant</strong></p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/513f4e0fa3938f4b4dc9ad8fe2f38daf/href">https://medium.com/media/513f4e0fa3938f4b4dc9ad8fe2f38daf/href</a></iframe><p><strong>Data model refactoring v2 — Adding next_revision_id pointer</strong></p><p>Since calculating max/latest revision is expensive, another option is adding <strong>next_revision_id</strong> column to workflow table, which will point to the next revision workflow id. Though it requires additional update when revising workflow, previous revision’s next_revision_id must be updated to new revision’s id for consistency.</p><pre>CREATE TABLE if not exists blog.workflow_v2 (<br>    id uuid NOT NULL,<br>    name text NOT NULL,<br>    description text NULL,<br>    status text not null,<br>    series_id uuid NOT NULL,<br>    revision int NOT NULL,<br>    next_revision_id uuid,<br>    created_by text not null,<br>    created_at timestamp not null,<br>    tenant_id text not null,<br>    CONSTRAINT workflow_v1_pk PRIMARY KEY (id)<br>);</pre><p><strong>Populating next_revision_id from existing data</strong></p><pre>insert into blog.workflow_v2<br>select w.id, w.name, w.description, w.status, w.series_id, w.revision,<br>       (<br>           select v.id<br>           from blog.workflow v<br>           where v.tenant_id = w.tenant_id<br>             and v.series_id = w.series_id<br>             and v.revision = w.revision + 1<br>       ) as next_revision_id,<br>       w.created_by, w.created_at, w.tenant_id<br>from blog.workflow w;</pre><pre>create unique index concurrently if not exists workflow_v2_series_id_revision_unique_idx on<br>    blog.workflow_v2(series_id, revision);<br>create index concurrently if not exists workflow_v2_created_date_idx<br>    ON blog.workflow_v2 (tenant_id, created_at desc);<br>analyze blog.workflow_v2;</pre><p>New version of search query and execution plan for the largest customer tenant_id=’tenant1&#39; (900K workflows) and other params are null, fetching first 100 records</p><pre>explain (analyze, buffers)<br>SELECT w.*<br>FROM blog.workflow_v2 w<br>WHERE tenant_id = :tenantId<br>    AND (:statuses IS NULL or status = any(cast(:statuses as text[])))<br>    AND name ILIKE CONCAT(&#39;%&#39;, :name, &#39;%&#39;)<br>    and (:latest is null or w.next_revision_id is null)<br>ORDER BY created_at desc<br>limit 100;</pre><pre>Limit  (cost=0.42..11.01 rows=100 width=105) (actual time=0.036..0.115 rows=100 loops=1)<br>  Buffers: shared hit=6<br>  -&gt;  Index Scan using workflow_v2_created_date_idx on workflow_v2 w  (cost=0.42..63750.84 rows=602406 width=105) (actual time=0.035..0.102 rows=100 loops=1)<br>        Index Cond: (tenant_id = &#39;tenant1&#39;::text)<br>        Filter: (name ~~* concat(&#39;%&#39;, NULL::unknown, &#39;%&#39;))<br>        Buffers: shared hit=6<br>Planning Time: 0.163 ms<br>Execution Time: 0.140 ms</pre><p><strong>Observations</strong></p><ol><li>Cost and execution time wise it is almost same as v1 model</li><li>Efficient way to find workflow’s next revision without filtering by series_id and revision</li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ad88f32e13fd" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Flyway 9 and PostgreSQL 15 upgrade troubleshooting story]]></title>
            <link>https://medium.com/@raminorujov/flyway-9-and-postgresql-15-upgrade-troubleshooting-story-00f503acaff2?source=rss-65f49efe3c3d------2</link>
            <guid isPermaLink="false">https://medium.com/p/00f503acaff2</guid>
            <category><![CDATA[java]]></category>
            <category><![CDATA[spring-boot]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[flyway]]></category>
            <dc:creator><![CDATA[Ramin Orujov]]></dc:creator>
            <pubDate>Thu, 29 Feb 2024 15:19:49 GMT</pubDate>
            <atom:updated>2024-02-29T15:19:49.648Z</atom:updated>
            <content:encoded><![CDATA[<p>Before the important product release we decided to upgrade Postgres RDS instances from version 14.6 to 15.4. Platforms engineering team successfully upgraded all RDS instances in dev and stage environment. After upgrade we started to see new flyway warnings:</p><p><strong>Flyway upgrade recommended: PostgreSQL 15.4 is newer than this version of Flyway and support has not been tested. The latest supported version of PostgreSQL is 14.</strong></p><p><strong>Flyway 9.5.1 (2022–10–20)</strong> added Postgres 15 support, it was decided to migrate to the latest 9.22.3 version in all microservices.</p><p>As usual I created a new branch in one of our most used microservices running on Spring Boot 2.7.x with Flyway 8.5.13. I changed the Flyway version in build.gradle and ran integration tests in my local dev machine. Application hung during startup and stayed in a blocked state for a few minutes. I stopped application, killed docker containers, started tests and observed the same behavior. For some reason the application was blocked when running the flyway SQL migration with <strong>CREATE INDEX CONCURRENTLY</strong> statement. I figured out that it was related to the locking mechanism used by Flyway when building concurrent indexes.</p><p>As a best practice we always use CREATE/DROP INDEX CONCURRENTLY, when this option is used, PostgreSQL will build the index without taking any locks that prevent concurrent inserts, updates, or deletes on the table; whereas a standard index build locks out writes (but not reads) on the table until it’s done.</p><p><strong>Quote from </strong><a href="https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY"><strong>PostgreSQL documentation</strong></a></p><p><em>Creating an index can interfere with regular operation of a database. Normally PostgreSQL locks the table to be indexed against writes and performs the entire index build with a single scan of the table. Other transactions can still read the table, but if they try to insert, update, or delete rows in the table they will block until the index build is finished. This could have a severe effect if the system is a live production database. Very large tables can take many hours to be indexed, and even for smaller tables, an index build can lock out writers for periods that are unacceptably long for a production system.</em></p><p><em>PostgreSQL supports building indexes without locking out writes. This method is invoked by specifying the CONCURRENTLY option of CREATE INDEX. When this option is used, PostgreSQL must perform two scans of the table, and in addition it must wait for all existing transactions that could potentially modify or use the index to terminate. Thus this method requires more total work than a standard index build and takes significantly longer to complete. However, since it allows normal operations to continue while the index is built, this method is useful for adding new indexes in a production environment. Of course, the extra CPU and I/O load imposed by the index creation might slow other operations.</em></p><p><strong><em>In a concurrent index build, the index is actually entered as an “invalid” index into the system catalogs in one transaction, then two table scans occur in two more transactions. Before each table scan, the index build must wait for existing transactions that have modified the table to terminate. After the second scan, the index build must wait for any transactions that have a snapshot (see </em></strong><a href="https://www.postgresql.org/docs/current/mvcc.html"><strong><em>Chapter 13</em></strong></a><strong><em>) predating the second scan to terminate, including transactions used by any phase of concurrent index builds on other tables, if the indexes involved are partial or have columns that are not simple column references.</em></strong><em> Then finally the index can be marked “valid” and ready for use, and the CREATE INDEX command terminates. Even then, however, the index may not be immediately usable for queries: in the worst case, it cannot be used as long as transactions exist that predate the start of the index build.</em></p><p>I added breakpoint, run integration test in debug mode, logged in to the docker container used by testcontainer and ran <strong>select * from pg_stat_activity;</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*jQMbKraXdds11EWs" /></figure><p>I saw 3 client backend processes:</p><ul><li>PID 55 is an idle Flyway process running SELECT COUNT(*) FROM pg_namespace WHERE nspname=? query to check if schema exists — See <a href="https://github.com/flyway/flyway/blob/a7c071afa67d7e3fd88e042bae69d888d0c8e3c3/flyway-database/flyway-database-postgresql/src/main/java/org/flywaydb/database/postgresql/PostgreSQLSchema.java#L47">link</a></li><li>PID 57 is my locked Flyway SQL migration process running CREATE INDEX CONCURRENTLY statement with <strong>wait_event_type = Lock</strong></li><li>PID 74 is my psql terminal session running select * from pg_stat_activity query.</li></ul><p>I ran <strong>select * from pg_stat_progress_create_index;</strong> query in a separate session and saw that PID 57 SQL migration process building concurrent index was locked by PID 55 Flyway schema check and waiting for old snapshots.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*es6GTAedlVYLtDqK" /></figure><p>Do you remember how PostgreSQL is building concurrent indexes from the highlighted documentation quote above?</p><p><strong><em>After the second scan, the index build must wait for any transactions that have a snapshot (see </em></strong><a href="https://www.postgresql.org/docs/current/mvcc.html"><strong><em>Chapter 13</em></strong></a><strong><em>) predating the second scan to terminate, including transactions used by any phase of concurrent index builds on other tables, if the indexes involved are partial or have columns that are not simple column references.</em></strong></p><p>So here PID 57 must wait predating PID 55 to terminate and free transaction snapshots.</p><p>I found that <a href="https://documentation.red-gate.com/fd/release-notes-for-flyway-engine-179732572.html">Flyway 9.1.2 (2022–08–03)</a> introduced new feature called transactional lock</p><p><a href="https://github.com/flyway/flyway/issues/2895">https://github.com/flyway/flyway/issues/2895</a></p><p><a href="https://github.com/flyway/flyway/issues/2895"><strong>Issue 2895</strong></a><strong> Use transactional locks in PostgreSQL by default, which enables support for PgBouncer (session locks can be enabled again by setting ‘flyway.postgresql.transactional.lock=false’)</strong></p><p>It means all transactions by default are running with global application level Postgres advisory lock using <strong>pg_try_advisory_xact_lock</strong> function and<strong> </strong>other sessions cannot acquire it until this lock is released(unlocked) by the owning process. This change explains why building concurrent index fails and we need to enable session level lock via <strong>flyway.postgresql.transactional.lock=false.</strong></p><p>After config change all flyway migrations successfully ran and application started normally.</p><p>References:</p><ol><li>Flyway release notes <a href="https://documentation.red-gate.com/fd/release-notes-for-flyway-engine-179732572.html">https://documentation.red-gate.com/fd/release-notes-for-flyway-engine-179732572.html</a></li><li>Flyway issue 2895 transactional lock implementation to support PgBouncer transaction pooling <a href="https://github.com/flyway/flyway/issues/2895">https://github.com/flyway/flyway/issues/2895</a></li><li>Implementation of PostgreSQLAdvisoryLockTemplate in Flyway 9.x. <a href="https://github.com/flyway/flyway/blob/flyway-9.22.3/flyway-core/src/main/java/org/flywaydb/core/internal/database/postgresql/PostgreSQLAdvisoryLockTemplate.java#L56">https://github.com/flyway/flyway/blob/flyway-9.22.3/flyway-core/src/main/java/org/flywaydb/core/internal/database/postgresql/PostgreSQLAdvisoryLockTemplate.java#L56</a></li><li>PostgreSQL documentation about building indexes concurrently <a href="https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY">https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY</a></li><li>PostgreSQL documentation about CREATE INDEX Progress Reporting <a href="https://www.postgresql.org/docs/current/progress-reporting.html#CREATE-INDEX-PROGRESS-REPORTING">https://www.postgresql.org/docs/current/progress-reporting.html#CREATE-INDEX-PROGRESS-REPORTING</a></li><li>PostgreSQL documentation about pg_stat_activity view <a href="https://www.postgresql.org/docs/15/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW">https://www.postgresql.org/docs/15/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW</a></li><li>PostgreSQL documentation about Advisory locks <a href="https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS">https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS</a></li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=00f503acaff2" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>