{"id":83649,"date":"2020-06-02T07:42:21","date_gmt":"2020-06-02T05:42:21","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/database-as-sode-experience"},"modified":"2020-06-02T07:42:21","modified_gmt":"2020-06-02T05:42:21","slug":"database-as-sode-experience","status":"publish","type":"post","link":"https:\/\/prohoster.info\/hr\/blog\/administrirovanie\/database-as-sode-experience","title":{"rendered":"Iskustvo \u2018Database as Code\u2019","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p><img decoding=\"async\" alt=\"Iskustvo \u2018Database as Code\u2019\" src=\"\/wp-content\/uploads\/2020\/06\/a1ac23deeded97559fbe787ed09184a5.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>SQL, \u0161to mo\u017ee biti jednostavnije? Svaki od nas mo\u017ee napisati jednostavni upit \u2014 jednostavno unesemo <strong><em>select<\/em><\/strong>, navodimo potrebne kolone, a zatim <strong><em>from<\/em><\/strong>, naziv tablice, malo uvjeta u <strong><em>where<\/em><\/strong> i to je to \u2014 korisni podaci su nam u d\u017eepu, uz to (skoro) neovisno o tome koja je DBMS u pozadini (a mo\u017eda i <noindex><a rel=\"nofollow\" href=\"https:\/\/osquery.io\/\">nije DBMS uop\u0107e<\/a><\/noindex>). Kao rezultat toga, rad s gotovo bilo kojim izvorom podataka (relacijskim i sli\u010dnim) mo\u017ee se promatrati iz perspektive obi\u010dnog koda (sa svim posljedicama \u2014 kontrola verzija, pregled koda, stati\u010dka analiza, automatski testovi i sve to). To se odnosi ne samo na same podatke, sheme i migracije, ve\u0107 i na cjelokupno funkcioniranje skladi\u0161ta. U ovom \u010dlanku razgovarat \u0107emo o svakodnevnim zadacima i problemima s kojima se suo\u010davamo rade\u0107i s razli\u010ditim DB-ovima kroz prizmu &quot;database as code&quot;.<\/p>\n<p><\/p>\n<p>I zapo\u010dinjemo odmah s <noindex><a rel=\"nofollow\" href=\"https:\/\/www.yegor256.com\/2014\/12\/01\/orm-offensive-anti-pattern.html\">ORM<\/a><\/noindex>. Prvi sukobi tipa &quot;SQL vs ORM&quot; primije\u0107eni su jo\u0161 u <noindex><a rel=\"nofollow\" href=\"https:\/\/www.sql.ru\/forum\/904343\/orm-vs-sql\">pre- Petrinjskoj Rusiji<\/a><\/noindex>.<\/p>\n<p><noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><\/p>\n<h5 id=\"obektno-relyacionnyy-maping\">Objektno-relacijsko mapiranje<\/h5>\n<p><\/p>\n<p>Prista\u0161e ORM-a tradicionalno cijene brzinu i jednostavnost razvoja, neovisnost o DBMS-u i \u010disto\u0107u koda. Za mnoge od nas kod rada s bazom podataka (a \u010desto i sama baza podataka)<\/p>\n<p><\/p>\n<p>                        <b class=\"spoiler_title\">obi\u010dno izgleda otprilike ovako\u2026<\/b><\/p>\n<pre><code class=\"java\">@Entity\n@Table(name = &quot;stock&quot;, catalog = &quot;maindb&quot;, uniqueConstraints = {\n        @UniqueConstraint(columnNames = &quot;STOCK_NAME&quot;),\n        @UniqueConstraint(columnNames = &quot;STOCK_CODE&quot;) })\npublic class Stock implements java.io.Serializable {\n\n    @Id\n    @GeneratedValue(strategy = IDENTITY)\n    @Column(name = &quot;STOCK_ID&quot;, unique = true, nullable = false)\n    public Integer getStockId() {\n        return this.stockId;\n    }\n  ...<\/code><\/pre>\n<p><\/p>\n<p>Model je oboga\u0107en pametnim anotacijama, a negdje u pozadini hrabri ORM generira i izvr\u0161ava tone nekog SQL koda. Usput, programeri se svim snagama trude odvojiti se od svoje baze podataka kilometrima apstrakcija, \u0161to ukazuje na odre\u0111eni <noindex><a rel=\"nofollow\" href=\"https:\/\/www.sql.ru\/forum\/1303231\/prichiny-nenavisti-k-yazyku-sql\">&quot;SQL mr\u017enje&quot;<\/a><\/noindex>.<\/p>\n<p><\/p>\n<p>S druge strane barikada, pristalice \u010distog &quot;handmade&quot;-SQL-a isti\u010du mogu\u0107nost da izvuku maksimum iz svog DBMS-a bez dodatnih slojeva i apstrakcija. Kao rezultat toga, pojavljuju se &quot;data-centric&quot; projekti, gdje se bazama bave posebno obu\u010deni ljudi (oni su tako\u0111er &quot;bazisti&quot;, &quot;bazoviki&quot;, &quot;bazdend\u017eije&quot; itd.), a programerima ostaje samo &quot;zvati&quot; gotove poglede i pohranjene procedure, ne ulaze\u0107i u detalje.<\/p>\n<p><\/p>\n<p>A \u0161to ako uzmemo najbolje iz oba svijeta? Kako je to u\u010dinjeno u prekrasnom alatu s optimisti\u010dnim imenom <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/krisajenkins\/yesql\">Yesql<\/a><\/noindex>. Dati \u0107u nekoliko redaka iz op\u0107e koncepcije u mom slobodnom prijevodu, a detaljnije se s njom mo\u017eete upoznati <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/krisajenkins\/yesql#rationale\">ovdje<\/a><\/noindex>.<\/p>\n<p><\/p>\n<blockquote><p>Clojure je super jezik za stvaranje DSL-ova, ali SQL sam po sebi je ve\u0107 odli\u010dan DSL, i ne trebamo jo\u0161 jedan. S-izrazi su prekrasni, ali ovdje ne dodaju ni\u0161ta novo. Na kraju dobijamo zagrade radi zagrada. Ne sla\u017eete se? Tada \u010dekajte trenutak kada apstrakcija nad DB-om po\u010dne propu\u0161tati, i zapo\u010det \u0107ete borbu s funkcijom <em>(raw-sql)<\/em><\/p>\n<p>I \u0161to u\u010diniti? Ostavimo SQL obi\u010dnim SQL-om \u2014 jedna datoteka za jedan upit:<\/p><\/blockquote>\n<p><\/p>\n<pre><code class=\"sql\">-- ime: korisnici-po-dr\u017eavama\nselect *\n  from users\n where country_code = :country_code<\/code><\/pre>\n<p><\/p>\n<blockquote><p>\u2026 a zatim pro\u010ditajte ovu datoteku, pretvaraju\u0107i je u obi\u010dnu Clojure funkciju:<\/p><\/blockquote>\n<p><\/p>\n<pre><code class=\"lisp\">(defqueries &quot;some\/where\/users_by_country.sql&quot;\n   {:connection db-spec})\n\n;;; Funkcija s imenom `users-by-country` je stvorena.\n;;; Upotrijebimo je:\n(users-by-country {:country_code &quot;GB&quot;})\n;=&gt; ({:name &quot;Kris&quot; :country_code &quot;GB&quot; ...} ...)<\/code><\/pre>\n<p><\/p>\n<blockquote><p>Pridr\u017eavanjem na\u010dela &quot;SQL odvojeno, Clojure odvojeno&quot;, dobivate:<\/p>\n<ul>\n<li>Nema sintakti\u010dkih iznena\u0111enja. Va\u0161a baza podataka (kao i bilo koja druga) ne odgovara SQL standardu 100% \u2014 ali za Yesql to nije va\u017eno. Nikada ne\u0107ete tro\u0161iti vrijeme na lov za funkcijama \u010dija je sintaksa ekvivalentna SQL-u. Nikada se ne\u0107ete morati vra\u0107ati na funkciju <em>(raw-sql &quot;some (&#8216;funky&#8217; :: SYNTAX)&quot;))<\/em>.<\/li>\n<li>Najbolja podr\u0161ka za urednike. Va\u0161 urednik ve\u0107 ima odli\u010dnu podr\u0161ku za SQL. \u010cuvaju\u0107i SQL kao SQL, jednostavno ga mo\u017eete koristiti.<\/li>\n<li>Kompatibilnost s timom. Va\u0161i DBA mogu \u010ditati i pisati SQL koji koristite u svom Clojure projektu.<\/li>\n<li>Jednostavnija pode\u0161avanja performansi. Trebate izraditi plan za problemati\u010dni upit? Nema problema kada je va\u0161 upit obi\u010dan SQL.<\/li>\n<li>Ponovno kori\u0161tenje upita. Prevucite te iste SQL-datoteke u druge projekte, jer to je samo stari dobri SQL \u2014 jednostavno ga dijelite.<\/li>\n<\/ul>\n<p>\n<\/p><\/blockquote>\n<p>Po mom mi\u0161ljenju, ideja je vrlo cool i istovremeno jednostavna, zbog \u010dega je projekt dobio mnogo <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/krisajenkins\/yesql#other-languages\">sljedbenika.<\/a><\/noindex> na raznim jezicima. A mi \u0107emo dalje poku\u0161ati primijeniti sli\u010dnu filozofiju odvajanja SQL koda od svega ostalog daleko izvan ORM-a.<\/p>\n<p><\/p>\n<h5 id=\"ide--db-menedzhery\">IDE &amp; DB-upravitelji<\/h5>\n<p><\/p>\n<p>Zapo\u010dnimo s jednostavnom svakodnevnom zada\u0107om. \u010cesto moramo tra\u017eiti razne objekte u bazi podataka, na primjer, prona\u0107i tablicu u shemi i prou\u010diti njezinu strukturu (koje se kolone, klju\u010devi, indeksi, ograni\u010denja i sli\u010dno koriste). Od svake grafi\u010dke IDE ili bilo kojeg DB-managera, prvenstveno o\u010dekujemo upravo te mogu\u0107nosti. Trebalo bi biti brzo i da ne \u010dekamo pola sata dok se ne otvori prozor s potrebnim informacijama (osobito pri sporoj vezi s udaljenom bazom podataka), a dobivene informacije trebaju biti svje\u017ee i aktualne, a ne ke\u0161irane stare vijesti. \u0160to je baza podataka slo\u017eenija i ve\u0107a, to je te\u017ee to posti\u0107i.<\/p>\n<p><\/p>\n<p>No obi\u010dno odla\u017eem mi\u0161 i jednostavno pi\u0161em kod. Recimo, potrebno je saznati koje tablice (i s kojim svojstvima) se nalaze u shemi \"HR\". U ve\u0107ini DBMS-a \u017eeljenog rezultata mo\u017eemo posti\u0107i ovim jednostavnim upitom iz information_schema:<\/p>\n<p><\/p>\n<pre><code class=\"sql\">select table_name\n     , ...\n  from information_schema.tables\n where schema = 'HR'<\/code><\/pre>\n<p><\/p>\n<p>Od baze do baze sadr\u017eaj takvih referentnih tablica varira ovisno o sposobnostima svake SGBD. Na primjer, za MySQL iz te referentne tablice mo\u017eemo dobiti specifi\u010dne za tu SGBD parametre tablice:<\/p>\n<p><\/p>\n<pre><code class=\"sql\">select table_name\n     , storage_engine -- Koristi se \"motor\" (\n     \"MyISAM\", \"InnoDB\" itd)\n     , row_format     -- Format reda (\n     \"Fixed\", \"Dynamic\" itd)\n     , ...\n  from information_schema.tables\n where schema = 'HR'<\/code><\/pre>\n<p><\/p>\n<p>Oracle ne podr\u017eava information_schema, ali ima <noindex><a rel=\"nofollow\" href=\"https:\/\/en.wikipedia.org\/wiki\/Oracle_metadata\">Oracle metadata<\/a><\/noindex>, i ne javljaju se veliki problemi:<\/p>\n<p><\/p>\n<pre><code class=\"sql\">select table_name\n     , pct_free       -- Minimalni slobodni prostor u podatkovnom bloku (%)\n     , pct_used       -- Minimalno kori\u0161teno mjesto u podatkovnom bloku (%)\n     , last_analyzed  -- Datum posljednjeg prikupljanja statistike\n     , ...\n  from all_tables\n where owner = 'HR'<\/code><\/pre>\n<p><\/p>\n<p>Ni ClickHouse nije iznimka:<\/p>\n<p><\/p>\n<pre><code class=\"sql\">select name\n     , engine -- Koristi se \"motor\" (\n     \"MergeTree\", \"Dictionary\" itd)\n     , ...\n  from system.tables\n where database = 'HR'<\/code><\/pre>\n<p><\/p>\n<p>Ne\u0161to sli\u010dno se mo\u017ee posti\u0107i i u Cassandri (gdje postoje columnfamilies umjesto tablica i keyspace-ovi umjesto shema):<\/p>\n<p><\/p>\n<pre><code class=\"sql\">select columnfamily_name\n     , compaction_strategy_class  -- Strategija kompresije\n     , gc_grace_seconds           -- Vrijeme \u017eivota sme\u0107a\n     , ...\n  from system.schema_columnfamilies\n where keyspace_name = 'HR'<\/code><\/pre>\n<p><\/p>\n<p>Za ve\u0107inu drugih baza podataka tako\u0111er se mogu osmisliti sli\u010dni upiti (\u010dak i u Mongo postoji <noindex><a rel=\"nofollow\" href=\"https:\/\/docs.mongodb.com\/manual\/reference\/system-collections\/#%3Cdatabase%3E.system.namespaces\">specijalna sistemska kolekcija<\/a><\/noindex>, koja sadr\u017ei informacije o svim kolekcijama u sustavu).<\/p>\n<p><\/p>\n<p>Naravno, na taj na\u010din mo\u017eemo dobiti informacije ne samo o tablicama, ve\u0107 i o bilo kojem objektu. Povremeno ljubazni ljudi dijele takav kod za razne baze podataka, kao \u0161to je na primjer u seriji \u010dlanaka na Habr-u \"Funkcije za dokumentiranje baza podataka PostgreSQL\" (<noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/post\/415575\">ajb<\/a><\/noindex>, <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/post\/415897\">ben<\/a><\/noindex>, <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/post\/418597\">gim<\/a><\/noindex>). Naravno, dr\u017eati sav taj niz upita u glavi i stalno ih unositi \u2013 to je \"takvo si\" zadovoljstvo, stoga u svom omiljenom IDE\/editoru imam unaprijed pripremljen set snippeta za \u010desto kori\u0161tene upite, a ostaje samo unijeti imena objekata u predlo\u017eak.<\/p>\n<p><\/p>\n<p>Na kraju, ovaj na\u010din navigacije i pretra\u017eivanja objekata daleko je fleksibilniji, \u0161tedi puno vremena i omogu\u0107uje dobivanje to\u010dno onih informacija u obliku u kojem su trenutno potrebne (kao \u0161to je opisano u postu <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/company\/JetBrains\/blog\/342094\">\"Izvoz podataka iz baze podataka u bilo kojem formatu: \u0161to mogu IDE-ovi na platformi IntelliJ\"<\/a><\/noindex>).<\/p>\n<p><\/p>\n<h5 id=\"operacii-s-obektami\">Operacije s objekti<\/h5>\n<p><\/p>\n<p>Nakon \u0161to smo prona\u0161li i prou\u010dili potrebne objekte, pravo je vrijeme da s njima u\u010dinimo ne\u0161to korisno. Naravno, tako\u0111er ne skidaju\u0107i prste s tipkovnice.<\/p>\n<p><\/p>\n<p>Nije tajna da \u0107e jednostavno brisanje tablice izgledati gotovo identi\u010dno u svim bazama podataka:<\/p>\n<p><\/p>\n<pre><code class=\"sql\">drop table hr.persons<\/code><\/pre>\n<p><\/p>\n<p>Izrada tablica je ve\u0107 zanimljivija. Gotovo svaka DBMS (uklju\u010duju\u0107i mnoge NoSQL) na neki na\u010din podr\u017eava 'create table', a glavni dijelovi su \u010desto sli\u010dni (ime, popis kolona, tipovi podataka), no ostale pojedinosti mogu se zna\u010dajno razlikovati i ovise o unutarnjoj strukturi i mogu\u0107nostima odre\u0111enog DBMS-a. Moj omiljeni primjer je da u dokumentaciji Oracle-a postoje samo 'goli' BNF-ovi za sintaksu 'create table'. <noindex><a rel=\"nofollow\" href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/sqlrf\/sql-language-reference.pdf\">zauzimaju 31 stranicu<\/a><\/noindex>. Drugi DBMS-ovi imaju skromnije mogu\u0107nosti, ali svaki od njih tako\u0111er ima mno\u0161tvo zanimljivih i jedinstvenih zna\u010dajki za stvaranje tablica (<noindex><a rel=\"nofollow\" href=\"https:\/\/www.postgresql.org\/docs\/current\/static\/sql-createtable.html\">postgres<\/a><\/noindex>, <noindex><a rel=\"nofollow\" href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/create-table.html\">mysql<\/a><\/noindex>, <noindex><a rel=\"nofollow\" href=\"https:\/\/www.cockroachlabs.com\/docs\/stable\/create-table.html#expanded\">cockroach<\/a><\/noindex>, <noindex><a rel=\"nofollow\" href=\"https:\/\/docs.datastax.com\/en\/cql\/3.3\/cql\/cql_reference\/cqlCreateTable.html\">cassandra<\/a><\/noindex>). Te\u0161ko da \u0107e neki grafi\u010dki 'wizard' iz nove IDE (osobito univerzalne) mo\u0107i u potpunosti pokriti sve te mogu\u0107nosti, a ako i mo\u017ee, to \u0107e biti prizor koji nije za slabosti. Istodobno, pravilno i na vrijeme napisani operator <strong><em>create table<\/em><\/strong> omogu\u0107it \u0107e vam da bez problema iskoristite sve njih, u\u010dinite pohranu i pristup va\u0161im podacima pouzdanim, optimalnim i maksimalno udobnim.<\/p>\n<p><\/p>\n<p>Tako\u0111er, mnogi DBMS imaju svoje specifi\u010dne tipove objekata koji nisu prisutni u drugim DBMS-ima. Osim toga, mo\u017eemo izvoditi operacije ne samo nad objektima Baze podataka, ve\u0107 i nad samim DBMS-om, na primjer, 'ubiti' proces, osloboditi neki dio memorije, aktivirati pra\u0107enje, pre\u0107i u 'read only' na\u010din rada i mnogo toga drugog.<\/p>\n<p><\/p>\n<h5 id=\"a-teper-nemnogo-porisuem\">A sada malo da crtamo<\/h5>\n<p><\/p>\n<p>Jedan od naj\u010de\u0161\u0107ih zadataka je izraditi dijagram s objektima Baze podataka, vidjeti objekte i veze izme\u0111u njih na lijepoj slici. To mo\u017ee gotovo svaka grafi\u010dka IDE, pojedina\u010dne 'command line' utilite, specijalizirani grafi\u010dki alati i modelatori. Oni \u0107e vam ne\u0161to nacrtati 'kako znaju', a malo utjecati na taj proces mo\u017eete samo s nekoliko parametara u konfiguracijskoj datoteci ili kva\u010dicama u su\u010delju.<\/p>\n<p><\/p>\n<p>Me\u0111utim, ovaj problem mo\u017ee se rije\u0161iti mnogo jednostavnije, fleksibilnije i elegantnije, naravno, uz pomo\u0107 koda. Za izradnju dijagrama bilo koje slo\u017eenosti imamo nekoliko specijaliziranih jezika ozna\u010davanja (DOT, GraphML itd.), a uz njih cijeli niz aplikacija (GraphViz, PlantUML, Mermaid) koje znaju \u010ditati takve upute i vizualizirati ih u raznim formatima. Informacije o objektima i vezama izme\u0111u njih ve\u0107 znamo kako dobiti.<\/p>\n<p><\/p>\n<p>Evo malog primjera kako bi to moglo izgledati, koriste\u0107i PlantUML i <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/postgrespro\/blog\/316428\/\">demonstracijske baze podataka za PostgreSQL<\/a><\/noindex> (lijevo SQL upit koji generira potrebnu uputu za PlantUML, a desno rezultat):<\/p>\n<p>\n<img decoding=\"async\" alt=\"Iskustvo \u2018Database as Code\u2019\" src=\"\/wp-content\/uploads\/2020\/06\/c5cb2a138df1527abe47cbf8d695cb36.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<pre><code class=\"sql\">select '@startuml'||chr(10)||'hide methods'||chr(10)||'hide stereotypes' union all\nselect distinct ccu.table_name || ' --|&gt; ' ||\n       tc.table_name as val\n  from table_constraints as tc\n  join key_column_usage as kcu\n    on tc.constraint_name = kcu.constraint_name\n  join constraint_column_usage as ccu\n    on ccu.constraint_name = tc.constraint_name\n where tc.constraint_type = 'FOREIGN KEY'\n   and tc.table_name ~ '.*' union all\nselect '@enduml'<\/code><\/pre>\n<p><\/p>\n<p>A ako malo vi\u0161e potrudite, na osnovu <noindex><a rel=\"nofollow\" href=\"https:\/\/gist.github.com\/QuantumGhost\/0955a45383a0b6c0bc24f9654b3cb561\">ER predlo\u0161ka za PlantUML<\/a><\/noindex> mo\u017eete dobiti ne\u0161to \u0161to jako podsje\u0107a na pravu ER dijagram:<\/p>\n<p><\/p>\n<p>                        <b class=\"spoiler_title\">SQL upit malo kompliciraniji<\/b><\/p>\n<pre><code class=\"sql\">-- Naslov\nselect &#039;@startuml\n        !define Table(name,desc) class name as &quot;desc&quot; &lt;&lt; (T,#FFAAAA) &gt;&amp;gt;\n        !define primary_key(x) &lt;b&gt;x&lt;\/b&gt;\n        !define unique(x) &lt;color:green&gt;x&lt;\/color&gt;\n        !define not_null(x) &lt;u&gt;x&lt;\/u&gt;\n        hide methods\n        hide stereotypes&#039;\n union all\n-- Tablice\nselect format(&#039;Table(%s, &quot;%s n informacije o %s&quot;) {&#039;||chr(10), table_name, table_name, table_name) ||\n       (select string_agg(column_name || &#039; &#039; || upper(udt_name), chr(10))\n          from information_schema.columns\n         where table_schema = &#039;public&#039;\n           and table_name = t.table_name) || chr(10) || &#039;}&#039;\n  from information_schema.tables t\n where table_schema = &#039;public&#039;\n union all\n-- Odnosi izme\u0111u tablica\nselect distinct ccu.table_name || &#039; &quot;1&quot; --&amp;gt; &quot;0..N&quot; &#039; || tc.table_name || format(&#039; : &quot;A %s mo\u017ee imati mnogo %s&quot;&#039;, ccu.table_name, tc.table_name)\n  from information_schema.table_constraints as tc\n  join information_schema.key_column_usage as kcu on tc.constraint_name = kcu.constraint_name\n  join information_schema.constraint_column_usage as ccu on ccu.constraint_name = tc.constraint_name\n where tc.constraint_type = &#039;FOREIGN KEY&#039;\n   and ccu.constraint_schema = &#039;public&#039;\n   and tc.table_name ~ &#039;.*&#039;\n union all\n-- Podno\u017eje\nselect &#039;@enduml&#039;<\/code><\/pre>\n<p>\n<img decoding=\"async\" alt=\"Iskustvo \u2018Database as Code\u2019\" src=\"\/wp-content\/uploads\/2020\/06\/bd45420a4fd1bdfcf20cbe74a0638c7b.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p>Ako pa\u017eljivo pogledate, mnogi alati za vizualizaciju tako\u0111er koriste sli\u010dne upite. Me\u0111utim, ti su upiti obi\u010dno duboko <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/pgmodeler\/pgmodeler\/blob\/9c615c0b0871df3cd649983ce61e802b0ff0137b\/schemas\/catalog\/table.sch\">'utvrde' su u kodu same aplikacije i te\u0161ke su za razumjeti.<\/a><\/noindex>, da ne spominjem bilo kakvu njihovu modifikaciju.<\/p>\n<p><\/p>\n<h5 id=\"metriki-i-monitoring\">Metri\u010dki i monitoring<\/h5>\n<p><\/p>\n<p>Pre\u0111imo na tradicionalno slo\u017eenu temu \u2014 pra\u0107enje performansi baza podataka. Sjetit \u0107u se jedne kratke istinite pri\u010de koju mi je ispri\u010dao \"jedan moj prijatelj\". Na jednom projektu postojali su jedan mo\u0107ni DBA, a malo tko od programera ga je osobno poznavao, pa \u010dak ni vidio u\u017eivo (iako se govorilo da radi negdje u susjednoj zgradi). U \u010das \"X\", kada je proizvodni sustav velikog maloprodava\u010da opet po\u010deo \"slabo se osje\u0107ati\", on bi tiho slao snimke zaslona grafova iz Oracle Enterprise Manager-a, na kojima je pa\u017eljivo istaknuo kriti\u010dna mjesta crvenim markerom za \"jasnost\" (\u0161to, blago re\u010deno, nije puno pomoglo). I na osnovu te \"fotokartice\" morali smo lije\u010denje provoditi. Pri tome nitko nije imao pristup dragocjenom (u oba smisla te rije\u010di) Enterprise Manager-u, jer je sustav slo\u017een i skup, bo\u017ee sa\u010duvaj da programeri ne\u0161to nalete i sve sru\u0161e. Zbog toga su programeri \"empirijski\" pronalazili mjesto i uzrok usporavanja i izdavali zakrpu. Ako prijete\u0107e pismo od DBA nije ponovo dolazilo u bliskoj budu\u0107nosti, svi su s olak\u0161anjem uzdahnuli i vra\u0107ali se svojim trenutnim zadacima (do novog Pisma).<\/p>\n<p><\/p>\n<p>No, proces pra\u0107enja mo\u017ee izgledati veselije i prijateljskije, a najva\u017enije \u2014 dostupnije i transparentnije za sve. Bar osnovni dio, kao dodatak glavnim sustavima pra\u0107enja (koji su bez sumnje korisni i u mnogim slu\u010dajevima nezamjenjivi). Svaka DBMS slobodno i apsolutno besplatno spremna je podijeliti informacije o svom trenutnom stanju i performansama. U istoj \"krvavoj\" Oracle DB prakti\u010dki se sve informacije o performansama mogu dobiti iz sustavnih prikaza, po\u010dev\u0161i od procesa i sesija do stanja me\u0111ubufer cache-a (na primjer, <noindex><a rel=\"nofollow\" href=\"https:\/\/oracle-base.com\/dba\/scripts\">DBA skripte<\/a><\/noindex>, odjeljak \"Monitoring\"). U PostgreSQL-u tako\u0111er postoje cijele hrpe sustavnih prikaza za <noindex><a rel=\"nofollow\" href=\"https:\/\/www.postgresql.org\/docs\/current\/static\/monitoring-stats.html\">pra\u0107enje rada baze podataka<\/a><\/noindex>, posebice oni neizostavni u svakodnevnom \u017eivotu svakog DBA, kao \u0161to su <noindex><a rel=\"nofollow\" href=\"https:\/\/www.postgresql.org\/docs\/current\/static\/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW\">pg_stat_activity<\/a><\/noindex>, <noindex><a rel=\"nofollow\" href=\"https:\/\/www.postgresql.org\/docs\/current\/static\/monitoring-stats.html#PG-STAT-DATABASE-VIEW\">pg_stat_database<\/a><\/noindex>, <noindex><a rel=\"nofollow\" href=\"https:\/\/www.postgresql.org\/docs\/current\/static\/monitoring-stats.html#PG-STAT-BGWRITER-VIEW\">pg_stat_bgwriter<\/a><\/noindex>. U MySQL-u za to je predvi\u0111ena \u010dak i posebna shema <noindex><a rel=\"nofollow\" href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/performance-schema-table-descriptions.html\">performance_schema<\/a><\/noindex>. A u MongoDB ugra\u0111eni <noindex><a rel=\"nofollow\" href=\"https:\/\/docs.mongodb.com\/manual\/tutorial\/manage-the-database-profiler\/\">profilator<\/a><\/noindex> agregira podatke o performansama u sustavnu kolekciju <noindex><a rel=\"nofollow\" href=\"https:\/\/docs.mongodb.com\/manual\/tutorial\/manage-the-database-profiler\/#view-profiler-data\">system.profile<\/a><\/noindex>.<\/p>\n<p><\/p>\n<p>Dakle, opremljeni nekim sakuplja\u010dem metrika (Telegraf, Metricbeat, Collectd) koji mo\u017ee izvoditi prilago\u0111ene SQL upite, pohranom tih metrika (InfluxDB, Elasticsearch, Timescaledb) i vizualizatorom (Grafana, Kibana), mo\u017eemo dobiti prili\u010dno lagani i fleksibilni sustav nadzora koji \u0107e biti \u010dvrsto integriran s ostalim op\u0107im sistemskim metrikama (dobivenim, na primjer, od aplikacijskog servera, od OS-a itd.). Kao \u0161to je to slu\u010daj u pgwatch2, gdje se koristi kombinacija InfluxDB + Grafana i set upita prema sistemskim pogledima, kojima tako\u0111er mo\u017eemo <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/cybertec-postgresql\/pgwatch2#adding-metrics\">dodati prilago\u0111ene upite<\/a><\/noindex>.<\/p>\n<p><\/p>\n<h2 id=\"itogo\">U kona\u010dnici<\/h2>\n<p><\/p>\n<p>I to je samo pribli\u017ean popis onoga \u0161to se mo\u017ee u\u010diniti s na\u0161om BAZOM podataka pomo\u0107u obi\u010dnog SQL koda. Siguran sam da se mo\u017ee prona\u0107i jo\u0161 mno\u0161tvo primjena, pi\u0161ite u komentarima. A o tome kako (i \u0161to je najva\u017enije, za\u0161to) to sve automatizirati i uklju\u010diti u svoj CI\/CD pipeline \u0107emo razgovarati sljede\u0107i put.<\/p>\n<p>Izvor: <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/post\/426833\/\">habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>SQL, \u0447\u0442\u043e \u043c\u043e\u0436\u0435\u0442 \u0431\u044b\u0442\u044c \u043f\u0440\u043e\u0449\u0435? \u041a\u0430\u0436\u0434\u044b\u0439 \u0438\u0437 \u043d\u0430\u0441 \u043c\u043e\u0436\u0435\u0442 \u043d\u0430\u043f\u0438\u0441\u0430\u0442\u044c \u043f\u0440\u043e\u0441\u0442\u0435\u043d\u044c\u043a\u0438\u0439 \u0437\u0430\u043f\u0440\u043e\u0441 \u2014 \u043d\u0430\u0431\u0438\u0440\u0430\u0435\u043c select, \u043f\u0435\u0440\u0435\u0447\u0438\u0441\u043b\u044f\u0435\u043c \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u044b\u0435 \u043a\u043e\u043b\u043e\u043d\u043a\u0438, \u0437\u0430\u0442\u0435\u043c from, \u0438\u043c\u044f \u0442\u0430\u0431\u043b\u0438\u0446\u044b, \u043d\u0435\u043c\u043d\u043e\u0433\u043e \u0443\u0441\u043b\u043e\u0432\u0438\u0439 \u0432 where \u0438 \u0432\u0441\u0435 \u2014 \u043f\u043e\u043b\u0435\u0437\u043d\u044b\u0435 \u0434\u0430\u043d\u043d\u044b\u0435 \u0443 \u043d\u0430\u0441 \u0432 \u043a\u0430\u0440\u043c\u0430\u043d\u0435, \u043f\u0440\u0438\u0447\u0435\u043c (\u043f\u043e\u0447\u0442\u0438) \u043d\u0435\u0437\u0430\u0432\u0438\u0441\u0438\u043c\u043e \u043e\u0442 \u0442\u043e\u0433\u043e \u043a\u0430\u043a\u0430\u044f \u0421\u0423\u0411\u0414 \u0432 \u044d\u0442\u043e \u0432\u0440\u0435\u043c\u044f \u043d\u0430\u0445\u043e\u0434\u0438\u0442\u0441\u044f \u043f\u043e\u0434 \u043a\u0430\u043f\u043e\u0442\u043e\u043c (\u0430 \u043c\u043e\u0436\u0435\u0442 \u0438 \u043d\u0435 \u0421\u0423\u0411\u0414 \u0432\u043e\u0432\u0441\u0435). \u0412 [&hellip;]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":83650,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-83649","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t<!-- All in One SEO 5.0.5.1 - aioseo.com -->\n\t<meta name=\"description\" content=\"SQL, \u0447\u0442\u043e \u043c\u043e\u0436\u0435\u0442 \u0431\u044b\u0442\u044c \u043f\u0440\u043e\u0449\u0435?\" \/>\n\t<meta name=\"robots\" content=\"max-image-preview:large\" \/>\n\t<meta name=\"author\" content=\"Yuri Gagarin\"\/>\n\t<link rel=\"canonical\" href=\"https:\/\/prohoster.info\/hr\/blog\/administrirovanie\/database-as-sode-experience\" \/>\n\t\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.5.1\" \/>\n\t\t<meta property=\"og:locale\" content=\"hr_HR\" \/>\n\t\t<meta property=\"og:site_name\" content=\"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b\" \/>\n\t\t<meta property=\"og:type\" content=\"article\" \/>\n\t\t<meta property=\"og:title\" content=\"\ud83e\udd47\u00abDatabase as \u0421ode\u00bb Experience | ProHoster\" \/>\n\t\t<meta property=\"og:description\" content=\"SQL, \u0447\u0442\u043e \u043c\u043e\u0436\u0435\u0442 \u0431\u044b\u0442\u044c \u043f\u0440\u043e\u0449\u0435?\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/prohoster.info\/hr\/blog\/administrirovanie\/database-as-sode-experience\" \/>\n\t\t<meta property=\"og:image\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:secure_url\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:width\" content=\"350\" \/>\n\t\t<meta property=\"og:image:height\" content=\"350\" \/>\n\t\t<meta property=\"article:published_time\" content=\"2020-06-02T05:42:21+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2020-06-02T05:42:21+00:00\" \/>\n\t\t<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<!-- All in One SEO -->\n\n","aioseo_head_json":{"title":"\ud83e\udd47\u201eBaza podataka kao kod\u201c Iskustvo | ProHoster","description":"SQL, \u0161to mo\u017ee biti jednostavnije?","canonical_url":"https:\/\/prohoster.info\/hr\/blog\/administrirovanie\/database-as-sode-experience","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"hr_HR","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u00abDatabase as \u0421ode\u00bb Experience | ProHoster","og:description":"SQL, \u0447\u0442\u043e \u043c\u043e\u0436\u0435\u0442 \u0431\u044b\u0442\u044c \u043f\u0440\u043e\u0449\u0435?","og:url":"https:\/\/prohoster.info\/hr\/blog\/administrirovanie\/database-as-sode-experience","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2020-06-02T05:42:21+00:00","article:modified_time":"2020-06-02T05:42:21+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"83649","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":null,"breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 15:13:28","updated":"2022-10-02 10:23:46","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/hr\/wp-json\/wp\/v2\/posts\/83649","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/hr\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/hr\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/hr\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/hr\/wp-json\/wp\/v2\/comments?post=83649"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/hr\/wp-json\/wp\/v2\/posts\/83649\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/hr\/wp-json\/wp\/v2\/media\/83650"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/hr\/wp-json\/wp\/v2\/media?parent=83649"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/hr\/wp-json\/wp\/v2\/categories?post=83649"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/hr\/wp-json\/wp\/v2\/tags?post=83649"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}