Materialized View — Pano Her Açılışta Neden Bütün Günü Yeniden Topluyor?

1
CREATE MATERIALIZED VIEW branch_daily_totals AS
2
SELECT b.id                       AS branch_id,
3
       b.name                     AS branch_name,
4
       t.booked_at::date          AS day,
5
       SUM(t.amount)              AS total,
6
       COUNT(*)                   AS transaction_count,
7
       now()                      AS refreshed_at
8
FROM transactions t
9
JOIN accounts a ON a.id = t.account_id
10
JOIN branches b ON b.id = a.branch_id
11
GROUP BY b.id, b.name, t.booked_at::date;
12

13
-- Required for REFRESH ... CONCURRENTLY.
14
CREATE UNIQUE INDEX branch_daily_totals_key ON branch_daily_totals (branch_id, day);

1
@Component
2
class BranchTotalsRefresher {
3

4
    private final JdbcTemplate jdbc;
5

6
    BranchTotalsRefresher(JdbcTemplate jdbc) {
7
        this.jdbc = jdbc;
8
    }
9

10
    @Scheduled(cron = "0 0 * * * *")
11
    @SchedulerLock(name = "branch-totals-refresh", lockAtMostFor = "PT20M")
12
    void refresh() {
13
        // Readers keep seeing the previous result while this runs.
14
        jdbc.execute("REFRESH MATERIALIZED VIEW CONCURRENTLY branch_daily_totals");
15
    }
16
}

1
@Service
2
class BranchDashboard {
3

4
    private final JdbcClient jdbc;
5

6
    BranchDashboard(JdbcClient jdbc) {
7
        this.jdbc = jdbc;
8
    }
9

10
    record BranchTotal(String branch, BigDecimal total, long count, OffsetDateTime refreshedAt) {}
11

12
    List<BranchTotal> today() {
13
        return jdbc.sql("""
14
                SELECT branch_name, total, transaction_count, refreshed_at
15
                FROM branch_daily_totals
16
                WHERE day = current_date
17
                ORDER BY total DESC
18
                """)
19
            .query((rs, i) -> new BranchTotal(
20
                    rs.getString("branch_name"),
21
                    rs.getBigDecimal("total"),
22
                    rs.getLong("transaction_count"),
23
                    rs.getObject("refreshed_at", OffsetDateTime.class)))
24
            .list();
25
    }
26
}