2023年7月新登場！Google Cloud SQLの最上位エディション「Enterprise Plus」を触ってみた

#!/usr/bin/env python3.9

import pathlib
import statistics
import subprocess
import sys
import time


def main(ip: str, pw: str):
    here = pathlib.Path(__file__).parent.resolve()

    psql = ["psql"]
    tasks = ["insert", "select", "update", "delete"]
    results = {"insert": [], "select": [], "update": [], "delete": []}
    env = {"PGHOST": ip, "PGUSER": "postgres", "PGPASSWORD": pw, "PGDATABASE": "pp_all"}

    for i in range(1, 8):
        script = (here / "create_table.sql").resolve().read_bytes()
        print(f"{ip=} executing: {i}")
        r = subprocess.run(psql, shell=True, env=env, input=script)
        print(f"{r.returncode}, {r.stdout=}, {r.stderr=}")
        for s in tasks:
            script = (here / f"{s}.sql").resolve().read_bytes()
            print(f"{ip=} Running task {s}")
            start = time.time()
            r = subprocess.run(
                psql, shell=True, env=env, input=script, stdout=subprocess.DEVNULL
            )
            print(f"{r.returncode=}")
            if r.returncode != 0:
                print(f"{r.stdout=}, {r.stderr=}")
            end = time.time()
            duration = end - start
            results[s].append(duration)
            print(f"{ip=} Command {psql} {duration=} seconds")
        else:
            print(f"{ip=} Dropping table")
            script = (here / "drop_table.sql").resolve().read_bytes()
            r = subprocess.run(psql, shell=True, env=env, input=script)
            print(f"{r.returncode}, {r.stdout=}, {r.stderr=}")
            time.sleep(5)
    else:
        print(f"Executed to {ip=}")
        for task, values in results.items():
            median = statistics.median(values)
            mean = statistics.mean(values)
            print(f"{task=} {median=} {mean=}")


if __name__ == "__main__":
    main(sys.argv[1], sys.argv[2])

\copy land_registry_price_paid_uk FROM './pp-2022.csv' with (format csv, encoding 'win1252', header false, null '', quote '"', force_null (postcode, saon, paon, street, locality, city, district));

\copy land_registry_price_paid_uk FROM './pp-2021.csv' with (format csv, encoding 'win1252', header false, null '', quote '"', force_null (postcode, saon, paon, street, locality, city, district));

\copy land_registry_price_paid_uk FROM './pp-2020.csv' with (format csv, encoding 'win1252', header false, null '', quote '"', force_null (postcode, saon, paon, street, locality, city, district));

SELECT
    *
FROM
    land_registry_price_paid_uk
WHERE
    transfer_date::date >= '2022-01-01';

UPDATE
    land_registry_price_paid_uk
SET
    price = 100000
WHERE
    transfer_date::date >= '2021-01-01'
    AND transfer_date::date <= '2021-12-31';

DELETE FROM
    land_registry_price_paid_uk
WHERE
    transfer_date::date >= '2020-01-01'
    AND transfer_date::date <= '2020-12-31';

CREATE TABLE land_registry_price_paid_uk (
    TRANSACTION uuid,
    price numeric,
    transfer_date date,
    postcode text,
    property_type char(1),
    newly_built boolean,
    duration char(1),
    paon text,
    saon text,
    street text,
    locality text,
    city text,
    district text,
    county text,
    ppd_category_type char(1),
    record_status char(1)
);

DROP TABLE land_registry_price_paid_uk;