Tablo Bölümleme — Eski Yılı Silmek Neden Bütün Geceyi Aldı?

1
CREATE TABLE transactions (
2
    id          BIGINT GENERATED ALWAYS AS IDENTITY,
3
    account_id  UUID           NOT NULL,
4
    booked_at   TIMESTAMPTZ    NOT NULL,
5
    amount      NUMERIC(19, 2) NOT NULL,
6
    description TEXT           NOT NULL,
7
    PRIMARY KEY (id, booked_at)            -- must include the partition column
8
) PARTITION BY RANGE (booked_at);
9

10
CREATE INDEX ON transactions (account_id, booked_at);

1
CREATE TABLE transactions_2026_10 PARTITION OF transactions
2
    FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
3

4
CREATE TABLE transactions_2026_11 PARTITION OF transactions
5
    FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

1
@Component
2
class PartitionMaintenance {
3

4
    private static final DateTimeFormatter SUFFIX = DateTimeFormatter.ofPattern("yyyy_MM");
5

6
    private final JdbcTemplate jdbc;
7
    private final Clock clock;
8

9
    PartitionMaintenance(JdbcTemplate jdbc, Clock clock) {
10
        this.jdbc = jdbc;
11
        this.clock = clock;
12
    }
13

14
    @Scheduled(cron = "0 0 3 1 * *")
15
    @SchedulerLock(name = "partition-maintenance", lockAtMostFor = "PT30M")
16
    void monthly() {
17
        YearMonth now = YearMonth.now(clock);
18
        // Open the next three months ahead of time.
19
        for (int i = 1; i <= 3; i++) {
20
            YearMonth month = now.plusMonths(i);
21
            jdbc.execute("CREATE TABLE IF NOT EXISTS transactions_%s PARTITION OF transactions FOR VALUES FROM ('%s') TO ('%s')"
22
                    .formatted(month.format(SUFFIX), month.atDay(1), month.plusMonths(1).atDay(1)));
23
        }
24
        // Retention: detach the month that just turned too old, archive it, then drop it.
25
        YearMonth expired = now.minusMonths(25);
26
        String name = "transactions_" + expired.format(SUFFIX);
27
        jdbc.execute("ALTER TABLE transactions DETACH PARTITION " + name);
28
        archive(name);
29
        jdbc.execute("DROP TABLE " + name);
30
    }
31

32
    private void archive(String table) {
33
        // Copies the detached table to cold storage before it is dropped.
34
    }
35
}

1
-- The date range lets the planner read only October's partition.
2
SELECT account_id, SUM(amount) AS total
3
FROM transactions
4
WHERE booked_at >= '2026-10-01' AND booked_at < '2026-11-01'
5
GROUP BY account_id;