{"id":2196,"date":"2026-10-07T15:52:14","date_gmt":"2026-10-07T13:52:14","guid":{"rendered":"https:\/\/www.loicmathieu.fr\/wordpress\/fr\/?p=2196"},"modified":"2026-10-08T16:43:31","modified_gmt":"2026-10-08T14:43:31","slug":"database-performance-postgres-vs-mysql","status":"publish","type":"post","link":"https:\/\/www.loicmathieu.fr\/wordpress\/informatique\/database-performance-postgres-vs-mysql\/","title":{"rendered":"Database performance: Postgres vs. MySQL"},"content":{"rendered":"

Kestra<\/a> supports Postgres and MySQL, among other databases; our internal benchmarks showed that Postgres performed much better than MySQL when running Kestra. We therefore generally recommend running Kestra in production with Postgres.<\/p>\n

During our benchmarks<\/a>, Postgres sustains 4,000 executions\/min with a latency of 370 ms for our typical flows, and a latency of around 130 ms for a single launch. MySQL, for its part, cannot even sustain 1,000 executions\/min, and has a latency of around 160 ms for a single launch.<\/p>\n

But even so, some of our users were using MySQL without any issues, which prompted me to look into the issue.<\/p>\n

Kestra uses its database for three things:<\/p>\n

    \n
  1. Storing workflow-related resources (flows, metadata, users, etc.)<\/li>\n
  2. Storing workflow execution data<\/li>\n
  3. An internal queue for asynchronous message passing between components<\/li>\n<\/ol>\n

    The internal queue is the most critical component; it lies at the heart of Kestra, and its Executor, which is responsible for the orchestration logic, relies heavily on it.\nThe Executor processes an execution in a loop; at each step, it advances the workflow, then sends tasks to be executed to the Worker and an execution update event to trigger itself again.\nSo it reads a message from the queue, processes the message, and sends another one.\nThe JDBC queue operates via a SELECT ... FOR UPDATE<\/code> statement; once the message has been processed, it is deleted within the same transaction.\nTo optimize performance, the queue reads in batches (pollSize<\/code>, 100 by default), and the Executor processes them in parallel using a number of threads configured by default to the number of available CPU cores.<\/p>\n

    Each execution can generate dozens or even hundreds of messages<\/strong> and thus DB transactions, which I suspect is the cause of the performance differences.<\/p>\n

    After verifying that the indexes were identical between the two databases and were being used correctly, I tested several things:<\/p>\n

      \n
    1. Configured the datasource, including enabling the prepared statement cache, which is disabled by default in MySQL but enabled in Postgres (see here<\/a> for reference). No change<\/strong>.<\/li>\n
    2. Increased the pollSize<\/code> to create larger batches (250). No change<\/strong>.<\/li>\n
    3. Decreased the pollSize<\/code> to create smaller commits (10). Worse!<\/strong>.<\/li>\n<\/ol>\n

      I then asked Claude what it thought, and it replied:<\/p>\n

      \n MySQL’s default setting of sync_binlog=1<\/code> enforces a synchronous fsync<\/code> of the binlog on every commit, and Kestra\u2019s JDBC queue design performs extremely frequent commits (approximately 52 commits per execution, or about 2,600 commits per second during a 6-minute performance test with 18,000 executions), because message processing occurs within the same transaction as each query cycle of the queue. By default, Postgres performs one fsync on the WAL per commit and does not have a second log; MySQL performs two (redo log + bin log).\n<\/blockquote>\n

      In summary, MySQL is configured by default to perform two fsync operations (system calls to synchronize file writes to disk) per commit, whereas Postgres performs only one. The second point relates to the binlog used for point-in-time recovery and replication between replicas: whereas the redo log is specific to MySQL\u2019s storage engine (InnoDB in this case), the binlog is identical regardless of the storage engine.<\/p>\n

      When you disable the binlog (--skip-log-bin<\/code>), performance is on par between MySQL and Postgres! :tada:<\/p>\n

      However, in a production environment, completely disabling the binlog is not a good idea; instead, you can choose what should be stored in the binlog (using binlog_row_image<\/code>) or how often it is written (using sync_binlog<\/code>).<\/p>\n

      Setting binlog_row_image<\/code> to MINIMAL<\/code> will create smaller events in the binlog, thereby reducing the number of bytes written per second. The tests were inconclusive; it is actually the number of writes that is the problem, not their size, which is consistent with the worste performance when pollSize<\/code> is reduced.<\/p>\n

      Setting sync_binlog<\/code> to 25 will write the binlog to disk every 25 commits instead of after every commit. The InnoDB redo log remains synchronized with every commit: a MySQL crash will not result in any data loss. In the event of an OS or server crash, up to 24 commits may be missing from the binlog (but not from the database), which only affects replication and point-in-time recovery.<\/p>\n

      Performance is then close to that of Postgres. This is a trade-off between performance and binlog durability: it’s up to you to choose the value based on your tolerance, but binlog configuration is a classic part of MySQL tuning.<\/p>\n

      To wrap it up, I had a negative preconception about MySQL\u2019s performance, but knowing that some heavy users of Kestra (thousands of flows, millions of executions per month) were using it, I thoight I needed to look into it. And since I\u2019m not an expert, Claude\u2019s little help pointed me in the right direction and allowed me to learn a little more about how MySQL works under the hood.<\/p>","protected":false},"excerpt":{"rendered":"

      Kestra supports Postgres and MySQL, among other databases; our internal benchmarks showed that Postgres performed much better than MySQL when running Kestra. We therefore generally recommend running Kestra in production with Postgres. During our benchmarks, Postgres sustains 4,000 executions\/min with a latency of 370 ms for our typical flows, and a latency of around 130 ms for a single launch. MySQL, for its part, cannot even sustain 1,000 executions\/min, and has a latency of around 160 ms for a single…

      Read More Read More<\/span><\/a><\/p><\/p>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"activitypub_content_warning":"","activitypub_content_visibility":"","activitypub_max_image_attachments":4,"activitypub_interaction_policy_quote":"anyone","activitypub_status":"federated","footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false,"_members_access_role":[],"_members_access_error":""},"categories":[9],"tags":[203,211,236,159],"class_list":["post-2196","post","type-post","status-publish","format-standard","hentry","category-informatique","tag-database","tag-kestra","tag-mysql","tag-performance"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t