Gendai Apurikeshon no tame no Database Design Patan

Database design wa, apurikeshon ga seicho suru ni tsurete sono juuyousei ga masu sukunai sukiru no hitotsu desu. Purojekuto no shoki ni yoku sekkei sareta suki-ma wa, tasuu no kinou kaihatsu o taipu na risoraito nashi de sasaeru koto ga dekimasu. Gyaku ni, fusei na sekkei wa dono atarashii kinou mo kurushiku shi, dono kueri mo ossoku shi, subete no deploy wo kiken ni sarashimasu. Data reya ni tsuite no kettei wa, apurikeshon no subete no reya ni hansha sare, ato de sore o kaeru no wa kosuto ga kakari masu.

Kono kiji de wa, gendai apurikeshon ni totte juuyou na patan ni tsuite setsumei shimasu. Kore wa gakuijutsu teki na renshu dewa naku, team ga production de database wo hayaku, iji kanou ni, soshite anzen ni henko suru tame ni shiyo suru jissenteki na senryaku desu. Storage engine no sentaku, schema design no genri, query optimization, CQRS ya event sourcing to itta architectual patan, soshite team to data ga seicho suru ni tsurete database wo nameraka ni ugokasu operational na jissen o torimashimasu.

Database no Sentaku: Rereshonaru vs. NoSQL

Anata ga kudasu saisho de mottomo juuyou na kettei wa, dono category no database o tsukau ka desu. Ii nyusu wa, mukashi no shuzoku-teki na senso wa hotondo owatta koto desu. Kyo de wa, junsui ni rereshonaru dake, arui wa NoSQL dake no team wa sukunai desu. Genjitsuteki na apurochi wa, sorezore no workload ni tekishita tsuru o erabi, tokidoki tasuu no storage engine o heizon saseru koto desu.

PostgreSQL ya SQLite no youna rereshonaru database wa, data ni meikaku na kankei ga ari, referential integrity ga taisetsu de, entity-kan o fluent ni query suru hitsuyo ga aru baai ni kagayakimasu. Seikyuu shisutemu, zaiko kanri tsuru, aruiwa transaction ga kanzen ni kanryo suru ka zenzen dekinai ka no donchi ka ga hitsuyo na apurikeshon o tsukutteiru nara, ACID hosho ga hitsuyo desu. Rereshonaru database wa, nijunen ni wataru field test ni yotte shoomei sareta, sorera no hosho o ataemasu.

MongoDB no youna document database wa, data ga kaizo-teki de, access pattern ga mae mo tte wakatteori, consistency hosho o write throughput ka schema flexibility to torihikikau koto ga dekireba, yori tekishiteimasu. Content management system, event logging pipeline, soshite data no katachi ga hindo ni kawaru apurikeshon de kagayakimasu.

Koko ni, puraimari database o erabu tameno jissenteki na kettei furemuwaaku ga arimasu:

• Default de PostgreSQL o shiyo shinasai. 95% no use case o yoku atsukai, document-gata data no tameno JSON column o sapoto shi, subarashii indexing o mochi, seijuku shita ecosystem o yuushimasu. Koko kara hajimete, tokubetsu na riyuu ga atta toki dake hazushite kudasai.
• Apurikeshon ga device-jo, WASM wo toshita browser-chu, aruiwa single-server tsuru no tameno embedded database to shite doudo suru toki wa, SQLite ni te o nobashite kudasai. Zero-configuration de, yomi ni totemo hayaku, saikin no extension ni yori odorokubeki nouryoku o motsu.
• Fukaku nesuto sareta document o itsumo ichi-tai to shite yomi kaki shi, consistency yokyu ga saishu-teki na icchi-read o yokyo dekiru hodo yurui baai wa, MongoDB ka Firestore o kento shinasai.
• Production de no multi-database no wana o sake nasai. Futatsu no database o hashiru koto wa, un'yo no fukuzatsusa o nibaika shimasu. Puraimari database ga workload o atsukaenai koto o sokutei shite kara dake, dainino storage engine o tsuika shinasai.

Watashi ga production codebase de yoku miru saisho no koukai wa, rereshonaru data no tameni NoSQL database o erabu koto desu. Anata no entity ga tagai o sansho shi, join ga hitsuyo naraba, rereshonaru database ga hoshi desu. Apurikeshon object to rereshonaru table no aida no impedance mismatch wa jitsuzai shimasu ga, koyuu no rereshonaru model to, sono tameni kaisatsu sareteinai document store to no aida no impedance mismatch yori mo haruka ni chiisai desu.

Seikika, Hiseikika, soshite Shin no Chukan'ichi

Daremo ga gakkou de normalization o manabimasu. Daiichi keiki, daini keiki, daisan keiki -- kono shinten wa, kiyona, fuyo no nai schema o update anomaly nashi de yakusoku shimasu. Shikashi, production no genjitsu wa motto nyuansu ga arimasu. Kanzen ni seikika sareta schema wa, joju no table o tada hitotsu no page o render suru tameni join suru query o yoku umi, sore wa osoku fukuzatsu desu. Kanzen ni hiseikika sareta schema wa write wo hayaku shi read wo tanjun ni shimasu ga, consistency mondai o shokai shi, dono write mo yori error-gae ni shimasu.

Jissenteki na chukan'ichi wa, integrity no tameni seikika shi, performance no tameni hiseikika suru koto desu ga, sore o ishiki-teki ni okonai, riyuu o kiroku shinasai. Data no shin no kankei o torareru seikika sareta schema kara hajimemasai. Soshite, jissai no query pattern o sokutei shita ato de, performance benefit ga tsuika no fukuzatsusa o seito-ka suru basho de, hiseikika sareta field ka summary table o tsuika shinasai.

-- Start normalized
CREATE TABLE orders (
  id        UUID PRIMARY KEY,
  user_id   UUID NOT NULL REFERENCES users(id),
  status    TEXT NOT NULL DEFAULT 'pending',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  id         UUID PRIMARY KEY,
  order_id   UUID NOT NULL REFERENCES orders(id),
  product_id UUID NOT NULL REFERENCES products(id),
  quantity   INT NOT NULL,
  unit_price NUMERIC(10,2) NOT NULL
);

-- Denormalize only when measured: add total to orders
ALTER TABLE orders ADD COLUMN total NUMERIC(10,2);

-- Keep it consistent with a trigger or application-level logic
CREATE OR REPLACE FUNCTION update_order_total()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE orders SET total = (
    SELECT SUM(quantity * unit_price)
    FROM order_items WHERE order_id = NEW.order_id
  ) WHERE id = NEW.order_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER order_total_trigger
AFTER INSERT OR UPDATE OR DELETE ON order_items
FOR EACH ROW EXECUTE FUNCTION update_order_total();

Ruru wa kantan desu: juuyo na query o sokutei suru made, kesshite hiseikika shite wa ikemasen. Sokuteizen no hiseikika wa, jitsuzai no mondai o kaiketsu suru koto no shoomei nashi ni, fuyo data no fukuzatsusa o subete shokai shimasu. Hiseikika suru toki wa, kettei o kiroku shi, consistency o kaku-nin suru test o tsuika shi, drift o hakken shi teisei dekiru reconciliation job o kouchiku shinasai. Fuyo data wa itsuka drift shimasu. Drift kenshutsu o keikaku suru no wa, hianshugi dewa naku, engineering no seijuku-sa desu.

Hontouni Kikku Indexing Senryaku

Index wa, dono database user demo riyou dekiru saimo impakuto no aru performance sai-tekika desu. Hitotsu no yoku haichi sareta index ga, suuman no row no sequential scan o wazukana page read ni kaeru koto ga dekimasu. Shikashi, index wa muryo dewa arimasen. Dono index mo write ni overhead o tsuika shi, storage o shohi shi, aru query ni taishite osugiru candidate ga areba query planner o konran saseru koto ga arimasu.

Production de ichi-ziteki ni yoi kekka o umu senryaku wa, mitsu no genri ni shitagaimasu. Daiichi, gaibu kii o index shinasai. Betsu no table o sansho suru dono column mo default de index sareru beki desu. Join performance wa, join no ryogawa deno index lookup ni izon shiteori, gaibu kii no index-wasure wa, rereshonaru database de mottomo juuyona performance misu desu.

Daini, column deha naku, query pattern o index shinasai. Mottomo osoi query no WHERE clause to ORDER BY clause o mite, sorera no pattern ni seikaku ni icchi suru composite index o tsukuri nasai. (status, created_at) ni taisuru composite index wa, created_at dake de filter suru query ni wa muyou desu ga, (created_at, status) ni taisuru composite index wa, created_at ga juubun ni sentakuteki nara, ryo-ho no query ni yakudatsu koto ga dekimasu. Composite index ni okeru column no junjo wa, hijoni juuyo desu.

-- Instead of separate indexes
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created ON orders(created_at);

-- Create composite indexes that match real query patterns
-- Query: SELECT * FROM orders WHERE status = 'active' ORDER BY created_at DESC;
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);

-- Query: SELECT * FROM orders WHERE user_id = $1 AND status = 'active';
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

Daisan, zengo o sokutei shinasai. PostgreSQL no pg_stat_user_indexes view wa, dono index ga jissai ni tsukaware, dore ga shiiniku ka o shimeshimasu. Workload o hashiri, index shiyo tokei o check shi, kesshite scan sarenai index o sakujo shinasai. Miyo no index wa mugaideha arimasen: dono write sosa mo osoku shi, yuuyo na data page o hojiru kamo no cache memory o shohi shimasu.

Bubun index wa, amari tsukawareteinai tsuru desu. Yoku row no subset dake o query suru nara (active na order, mimiori no event, hi-sakujo no user), sorera no row dake o cover suru bubun index o tsukuri nasai. Zenmen index no ichibun no ichi no saizu de, scan mo zutto hayaku narimasu.

-- Partial index: only index active orders
CREATE INDEX idx_orders_active ON orders(created_at DESC)
WHERE status = 'active';

-- This index is tiny compared to a full index and serves the query perfectly

Repository Patan to Data Access Chusho

Repository patan wa, domain ronri to data access code no aida o chukai shimasu. Sore wa, korekushon rashii interface o teikyo shi, aggregate no hokyaku to hozon o okonai, kiso naru storage mechanism no shosai o kakushi masu. Jissai ni wa, anata no apurikeshon code wa, data ga PostgreSQL kara na no ka, caching layer kara na no ka, sotoshi API kara na no ka o shirazu ni, userRepository.findById(id) ya orderRepository.save(order) no youna method o yobimasu.

Kono patan no kachi wa, database o kaetari, caching layer o donyu shitari suru hitsuyo ga deta toki ni hakkari shimasu. Controller, service, utility kan ni query ga chirabatteiru team wa, MongoDB kara PostgreSQL ni kirikaeru toki ni hyaku-tanni no file no risoraito ni chokumen shimasu. Repository o tsukatteiru team wa, sukuna no implementation file o kaeru dake de, interface wa kawaranainode sugimasu.

Shikashi, repository patan ni wa, rereshonaru database to no ma no yoku shirareru kincho ga arimasu. Repository interface ga amari ni generic (findAll, findById, save, delete) da to, rereshonaru database ga teikyo suru yutakana query nouryoku o hyogen dekimasen. Team wa kekkyoku, repository ni semnon-teki na query method o tsuika shite shimai, sore ga dandan to abstraction o leaky ni shimasu. Kaiketsusaku wa, rereshonaru database no tameno repository ga, key-value store no tameno repository yori mo oku no method o motsu koto o ukeireru koto desu. findActiveByRole, searchByName, countByStatus method o motsu user repository wa, abstraction no shippai dewa naku, kiso engine no nouryoku ni taishite shojiki de aru koto desu.

Query nouryoku o kakusu repository wa, chusho dewa naku, handicap desu. Mokuhyowa domain o storage no shosai kara koritsu saseru koto de atte, subete no database wo saitei koubunbo ni teika suru koto deha arimasen.

CQRS to Event Sourcing: Susunda Patan ni Makeru Toki

CQRS (Command Query Responsibility Segregation) wa, data no henko roto to data no yomi roto o bunri shimasu. Saikan'na keitai dewa, CQRS architecture wa onaji database o tsukaimasuga, write to read de wa betsu no model o tsukaimasu. Write model wa invariant o kyokoo shi event o umimasu. Read model wa sorera no event o shohi shi, tokutei no query ni senteki-ka sareta hiseikika no view o kouchiku shimasu. Kono bunri ni yori, sorezore no gawa o dokuritsu shite suke~ru to sai-tekika dekimasu.

Event sourcing wa sara ni susunde imasu. Entity no genzai no state o hozon suru kawari ni, sore o kaeta subete no event o hozonshimasu. Genzai no state wa, sorera no event o saisei suru koto de motome raremasu. Kore niyori, kanzen na audit trail, dono jiten demo state o saikochiku suru nouryoku, soshite downstream consumer ni tai suru natural na event source ga teikyo sarenmasu. Torihikiko wa juudaina operational fukuzatsusa desu: event store infrastructure, projection kanri, write to read model kan no eterministic icchi, soshite state deha naku event de kangaeru ninchiteki futan desu.

Kore ga patan no shojiki na hyoka desu: hotondo no apurikeshon wa CQRS mo event sourcing mo hitsuyo to shimasen. Sorera wa, yori kan'na architecture ga mitsumerarenai tokutei no yoken ga aru toki dake seito-ka sareru fukuzatsusa o tsuika shimasu. CQRS o kento suru no wa, anata no read to write workload ga konponteki ni betsu no tokucho o motsu baai desu: fukuzatsuna read projection o motsu takai write throughput, ka, sosa goto ni betsu no consistency yokens. Event sourcing o kento suru no wa, ho aruiwa product yoken ni yori fuhenko no audit trail ga hitsuyona baai, ka, subete no state henko ga saikochiku kano de bunseki kano de arubeki baai desu.

Moshi korera no patan o saiyo surunara, CQRS dake de hajimete, audit trail yoken ga meikaku na baai dake event sourcing o tsuika shinasai. PostgreSQL de betsu no read model o iji suru koto de CQRS o jisso suru no wa kanri kano desu. Ue ni event store o tsuika suru no wa, juudaina fukuzatsusa no jump de ari, yotee to shikin no aru ishi-teki na kettei subeki desu.

Maigreeshon, Setsuzoku Pool, soshite Un'yo no Kenkosei

Database design no un'yo gawa wa, ii patan ga nameraka ni shinka suru apurikeshon to, column no gata o kaeru tameni production saba de jiko ga hitsuyo na apurikeshon o wakeru tokoro desu. Mitu no jissen ga, database henko o yoku atsukau team to, kurushimu team o ichi-ziteki ni kuben shimasu.

Daiichi, dono schema henko mo, version kanri ni hokan sareta gyaku kano na maigreeshon de arubeki desu. Flyway, Liquibase, Araiba Alembic no youna tsuru wa, maigreeshon o junjo ni tekiyo shi, dore ga tekiyo sareta ka o tsu~rakku shimasu. Maigreeshon file wa code desu: mitori, test, application code to onaji pipeline de deploy saremasu. Onoono no maigreeshon wa chiisaku shuchu subeki desu. Hitotsu no file de column tsuika, data backfill, table name henko o okonau maigreeshon wa risk desu. Dokuritsu shite rollback dekiru step ni bunkatsu shinasai.

Daini, setsuzoku pooling wa oputshonarudeha arimasen. Database setsuzoku o hiraku no wa kosuto ga kakarimasu: TCP handshake, SSL negotiation, nintei ga fukumaremasu. Setsuzoku pool wa, thread ga kari Dashite kaesu jizokuteki na setsuzoku no setto o iji shimasu. Pool no saizu wa juuyo desu: amari ni sukunai to request ga taimu shi, amari ni ooi to database ga kontekusuto soukini subete no jikan o tsukaimasu. PostgreSQL no ii starting point wa pool_size = 2 * CPU_cores de, query latency to setsuzoku matsu jikan ni motozuite monitor shi chosei shinasai.

Daisan, maigreeshon o CI/CD pipeline ni chui shite togo shinasai. Anzen na patan wa, atarashii apurikeshon code o deploy suru mae ni maigreeshon o tekiyo suru koto desu. Kore ni yori, atarashii code wa kitai shita schema o mi, furui code wa mada atarashii schema to no gojisei ga aru koto ni narimasu (sakini tekiyo sareta henko ga gojisei o motsukara desu). Tsumari, dono maigreeshon mo genzai no code to gojisei ga nakereba narimasen: genko no code ga sansho shiteiru column o saku jo shinai koto, tsuka no kikan nashi ni table o saimei shinai koto.

• Kakudai: furui code ga mada hashitteiru aida ni, atarashii column ka table o tsuika suru.
• Iko: data o backfill shi, write o atarashii kozo ni ikou suru.
• Shushuku: furui code ga mogen hashitte inai koto o kakunin shita ato de, furui column ka table o sakujo suru.

Downtime zero no schema henko no tameno kono expand-migrate-contract patan wa, jikkouchu no code ga atsukaenai jotai ni database o nokosanai kara kikimasu. Kore wa kiritsu o hitsuyo to shimasu (hitotsu no deploy cycle no aida, furui code path o ikashite oku hitsuyo ga arimasu) ga, database ni kanren suru deploy no shippai no mottomo yoku aru gen'in o sakujo shimasu.

SQL vs. ORM: Tadashii Baransu o Mitsukeru

Raw SQL o kaku koto to Object-Relational Mapper o tsukau koto no ronso wa, software kaihatsu no naka de mottomo jizoku-teki na giron no hitotsu desu. Ryo-ho ni mo seito na point ga ari, tadashii kaitou wa purojekuto no kontekusuto ni yorimasu.

Prisma, TypeORM, SQLAlchemy no youna ORM wa, application object to database table no aida no jido mapping, migration kanri, soshite anata no application gengo de no query kouchiku o teikyo shimasu. Sorera wa boirateito code no zentai category o shokyaku shi, hajimera suku ni shimasu. Daisho wa, ORM ga SQL wo chusho shite shimau koto de, nanika okoru to (osoi query, igai na join, lock escalation), ORM no kodo to kisoryo no SQL no ryo-ho o rikai shite debug shinakereba narimasen. ORM wa fukuzatsuna access pattern ni taishite saitekide nai query o seisei shi, N+1 query mondai wa ORM o tsukau dono team demo no tsukuji desu.

Raw SQL wa, database server de jikko sareru mono o kanzen ni control dekimasu. Anata no schema to database engine no nouryoku ni chosetta, suki na query o kaku koto ga dekimasu. Daisho wa, jido mapping o ushinai, migration o mizukara kanri shinakereba narazu, codebase no achikochi ni chirabatta SQL string ga test shi nikuku, risaikuringu ga sara ni muzukashiku naru koto desu.

Genjitsuteki na chukan wa, 80% no kantan'na CRUD sas no tameni ORM o tsukai, performance chosei ka fukuzatsuna hokoku ga hitsuyona 20% no tameni raw SQL ni ochirukaoto desu. Yoi ORM wa, raw query o jikko shi, sono kekka o typed object ni yarinaosu hoho o teikyo shiteimasu. Kantan'na sas ni wa ORM no query builder o tsukai, fukuzatsuna mono ni wa raw SQL o kaki, ryo-ho o jissai no database de genjitsuteki na data volume de test shinasai.

PostgreSQL wa, nouryoku, shinrai-sei, ecosystem no baransu ga dono database yori mo sugureteiru tame, gendai no apurikeshon ni totte default no sentaku desu. SQLite wa embedded to local-first no supesu de shihai shiteimasu. MySQL wa, legacy oyobi WordPress ecosystem de yoku nokotteimasu. Anata ga erabu database wa, sore o yoku tsukau tameni tekiyo suru patan yori mo juuyo dehaarimasen: schema design, indexing, migration kanri, operational jissen ga, tin no logo yori mo hodoeku seiko o kime masu.

Anata ga erabu database wa, sore o yoku tsukau tameni tekiyo suru patan yori mo juuyo dehaarimasen. Schema design, indexing, soshite operational jissen ga, tin no logo yori mo hodoeku seiko o kime masu.

Database design wa, ikkai kagiri no katsudo dewa arimasen. Sokutei shi, chosei shi, manabu koto no keizokuteki na jissen desu. Kono kiji no patan wa shuppatsu-ten to shite yakunitachi masu ga, hontouno senmonshika wa, database ga jissai no workload de donoyouni kodo suru ka o kansatsu shi, targeddo sita ishi-teki na henko o okonau koto kara kuru mono desu. Kantan ni hajime nasai. Subete o sokutei shinasai. Soshite, mayo wazu ni migration file ni te o nobasu koto o osore nai de kudasai.