Scala Akka HTTP+PostgreSQL：如何高效插入Case Class数据到多表并优化查询？

当前方案的核心问题 #

你现在的实现有两个关键缺陷：

1. 缺乏原子性：四次独立的transact对应四个独立事务，若中间某一步插入失败，前面已提交的操作会导致数据库数据不一致（比如widths表有数据，但heights表插入失败，tables表无关联数据）。
2. 性能低效：每次transact都会建立数据库连接、发起网络请求，四次操作的开销远大于单次事务的开销。

优化方案 #

方案一：合并到同一事务（代码清晰，保证原子性）

将四个插入操作组合到同一个ConnectionIO中，仅执行一次transact，确保所有操作要么全部成功要么全部回滚，同时减少连接和网络开销。

先修正原代码的类型不一致问题（Table的width类型与Width类的width类型不匹配），实现如下：

import doobie._
import doobie.implicits._
import cats.effect.IO

case class Width(id: String, width: Int)
case class Height(id: String, height: Long)
case class Area(id: String, area: Long)
// 修正width类型为Int，与Width类字段类型一致
case class Table(id: String, width: Int, height: Height, area: Area)

def dbCreate(xa: Transactor[IO], table: Table): IO[Unit] = {
  val dbActions = for {
    widthId <- sql"INSERT INTO widths (id, width) VALUES (${table.id}, ${table.width})"
                 .update
                 .withUniqueGeneratedKeys[Int]("id")
    heightId <- sql"INSERT INTO heights (id, height) VALUES (${table.id}, ${table.height.height})"
                  .update
                  .withUniqueGeneratedKeys[Int]("id")
    areaId <- sql"INSERT INTO areas (id, area) VALUES (${table.id}, ${table.area.area})"
                .update
                .withUniqueGeneratedKeys[Int]("id")
    _ <- sql"INSERT INTO tables (id, width_id, height_id, area_id) VALUES (${table.id}, $widthId, $heightId, $areaId)"
           .update
           .run
  } yield ()

  dbActions.transact(xa)
}

方案二：用PostgreSQL CTE合并为单个SQL查询（性能最优）

PostgreSQL支持CTE（公共表表达式），可将四次插入合并为一个SQL语句，一次性发送到数据库，进一步降低网络开销，同时保持原子性。

实现如下：

import doobie._
import doobie.implicits._
import cats.effect.IO

case class Width(id: String, width: Int)
case class Height(id: String, height: Long)
case class Area(id: String, area: Long)
case class Table(id: String, width: Int, height: Height, area: Area)

def dbCreateSingleQuery(xa: Transactor[IO], table: Table): IO[Unit] = {
  sql"""
    WITH inserted_width AS (
      INSERT INTO widths (id, width) VALUES (${table.id}, ${table.width}) RETURNING id
    ), inserted_height AS (
      INSERT INTO heights (id, height) VALUES (${table.id}, ${table.height.height}) RETURNING id
    ), inserted_area AS (
      INSERT INTO areas (id, area) VALUES (${table.id}, ${table.area.area}) RETURNING id
    )
    INSERT INTO tables (id, width_id, height_id, area_id)
    SELECT ${table.id}, iw.id, ih.id, ia.id
    FROM inserted_width iw, inserted_height ih, inserted_area ia;
  """.update.run.transact(xa).void
}

方案选择 #

• 若优先考虑代码可读性和维护性，选方案一：每个插入操作独立，逻辑清晰，便于后续修改。
• 若追求极致性能（如高并发场景），选方案二：仅发起一次数据库请求，减少网络延迟。

内容的提问来源于stack exchange，提问作者Vakindu