Mitä ovat tallennetut proseduurit ja milloin niitä tulisi käyttää?

Question

Accepted Answer

**Tallennettu prosedyyri** on joukko SQL-lauseita (ja proseduurilogiikkaa) tietokantaan tallennettuja ja nimellä suoritettavia käskyjä. Se suorittaa logiikan *tietokannassa* sovelluskoodin sijaan. Niillä on oikeat hyödyt, mutta myös merkittävät kompromissit, joten niiden käyttöaika on aito suunnittelupäätös.

## Tallennettu prosedyyri

```sql
-- define a procedure that encapsulates logic in the database
CREATE PROCEDURE transfer_funds(from_id INT, to_id INT, amount DECIMAL)
LANGUAGE plpgsql AS $$
BEGIN
  UPDATE accounts SET balance = balance - amount WHERE id = from_id;
  UPDATE accounts SET balance = balance + amount WHERE id = to_id;
  -- can include logic: conditionals, loops, error handling, transactions
END;
$$;

CALL transfer_funds(1, 2, 100);   -- execute by name
```

Tallennettu prosedyyri niputtaa SQL:n ja proseduurilogiikan (muuttujat, ehdollisuudet, silmukat, virheiden käsittely) nimettyyn, uudelleenkäytettävään tietokantaroutiiniksi. Funktiot ovat samanlaisia, mutta palauttavat arvon.

## Mahdolliset hyödyt

```text
✓ Performance — runs IN the database (no round-trips for multi-step logic); precompiled
✓ Reduced network traffic — one call vs many queries from the app
✓ Reusability — shared logic callable from multiple applications/clients
✓ Security — grant EXECUTE on a procedure without granting table access; reduces
  injection surface (parameterized)
✓ Consistency — centralize critical logic so all clients use the same implementation
```

## Merkittävät kompromissit (miksi monet välttävät niitä nykyään)

```text
✗ Business logic in the DATABASE → harder to version-control, test, and maintain
  (logic split between app code and the database)
✗ Database-specific (PL/pgSQL vs T-SQL vs PL/SQL) → less portable, vendor lock-in
✗ Harder to debug than application code; weaker tooling
✗ Scaling — the database is often the hardest tier to scale; pushing logic there
  can increase its load
✗ Modern apps often prefer logic in the application layer (testable, portable, versioned)
```

## Milloin niitä käytetään

```text
✓ Performance-critical data-intensive operations (heavy multi-step DB logic, batch jobs)
✓ Logic that MUST be enforced at the DB level across multiple clients
✓ Reducing round-trips for operations involving many queries
✗ AVOID putting general business logic in procedures by default — modern practice
  usually favors the application layer (better testing, versioning, portability)
```

## Miksi tämä on tärkeää

Tallennettujen proseduurien ymmärtäminen — mukaan lukien niiden kompromissit — on arvokasta tietoa, koska ne edustavat aitoa arkkitehtuurivalintaa, jolla on oikeat hyödyt ja oikeat haitat, ja *milloin* niitä käytetään (ja milloin ei) heijastaa kypsää harkintaa.

Tallennetut proseduurit voivat tarjota **suorituskykyetuja** (monivaiheisen logiikan suorittaminen tietokannassa välttää verkon välityskierroksia), vähentynyttä verkkoliikennettä, uudelleenkäytettävyyttä asiakkaiden välillä, turvallisuutta (suoritusoikeuksien myöntäminen ilman taulukkokäyttöä) ja keskitettyä johdonmukaisuutta — joten ne sopivat suorituskykylle kriittisiin tietointensiivisiin operaatioihin, eräkäsittelyyn ja logiikkaan, joka on pakotettava tietokantatasolla useiden sovellusten välillä.

Kuitenkin **merkittävien kompromissien** ymmärtäminen on yhtä tärkeää ja heijastaa nykyaikaista käytäntöä: liiketoimintalogiikan sijoittaminen tietokantaan tekee siitä vaikeammin versionhallita, testata ja ylläpitää (logiikan jakautuminen sovelluksen ja tietokannan välille), sitoo sinut tietyn tietokannan proseduurikieleen (PL/pgSQL, T-SQL, PL/SQL — vähentää siirrettävyyttä), on vaikeampi debugata ja lisää kuormitusta tietokantatalolle (usein vaikeiten skaalata).

Näistä syistä **nykyaikainen sovellustenkehitys usein suosii liiketoimintalogiikan pitämistä sovelluskerroksessa** (testattavissa, siirrettävissä, versionhallinnassa) kuin tallennetuissa proseduureissa.

Avainsisältö on **harkinta milloin niitä käytetään** — sopivat erityisille suorituskykylle kriittisille tai DB-tason pakotuksille, mutta yleensä vältetään oletusarvona liiketoimintalogiikkalle.

Koska tallennetut proseduurit ovat yleisiä monissa järjestelmissä (erityisesti yritys-/perintö-), ja koska sekä niiden hyötyjen että niiden kompromissien (ja nykyaikaisen suuntauksen sovelluskerrokslogiikkaan) ymmärtäminen mahdollistaa järkevät arkkitehtuuripäätökset, tallennettujen proseduurien ymmärtäminen on arvokas senioritason tieto, joka osoittaa kyvyn punnita tietokantataso vs sovelluskerros logiikka — merkityksellinen suunnittelunäkökohta ja aihe, joka heijastaa arkkitehtuurin kypsyyttä.