Načítanie tabuľky do vyrovnávacej pamäte

URL na aktualizované prezentácie + kniha: https://1drv.ms/u/s!AlrLrycbTQ1azqJfINoN_tzGyCVYoQ?e=kYRI55

-- Načítanie tabuľky do vyrovnávacej pamäte

 -- Vytvor tbl1
CREATE TABLE tbl_ItemTransactions_1
 (
     TranID SERIAL
     ,TransactionDate TIMESTAMPTZ
     ,TransactionName TEXT
 );

 -- Vloz data do tbl1
INSERT INTO tbl_ItemTransactions_1
(TransactionDate, TransactionName)
SELECT x, 'dbrnd'
FROM generate_series('2010-01-01 00:00:00'::timestamptz, '2018-02-01 00:00:00'::timestamptz,'1 minutes'::interval) a(x);

 -- Vytvor tbl2
CREATE TABLE tbl_ItemTransactions_2
 (
     TranID SERIAL
     ,TransactionDate TIMESTAMPTZ
     ,TransactionName TEXT
 );

 -- Vloz data do tbl2
INSERT INTO tbl_ItemTransactions_2
(TransactionDate, TransactionName)
SELECT x, 'dbrnd'
FROM generate_series('2010-01-01 00:00:00'::timestamptz, '2018-02-01 00:00:00'::timestamptz,'1 minutes'::interval) a(x);

-- Nacitaj rozsirenie pg_prewarm
CREATE EXTENSION pg_prewarm;

-- Teraz použite na tabuľku tbl_ItemTransactions_1 prefetch pg_prewarm:
SELECT pg_prewarm('tbl_ItemTransactions_1');

-- Analyzuj tb1
EXPLAIN ANALYZE SELECT * FROM tbl_ItemTransactions_1;

Planning time: 0.130 ms
Execution time: 356.237 ms

-- Analyzuj tb2
EXPLAIN ANALYZE SELECT *FROM tbl_ItemTransactions_2;

Planning time: 0.210 ms
Execution time: 752.010 ms


-- 2.
-- Kontrola stavu zdieľanej vyrovnávacej pamäte

-- Nacitaj rozsirenie pg_buffercache
CREATE EXTENSION pg_buffercache;
Script to check the status of Shared Buffer:

-- Spustu dopyt pre kontrolu Shared Buffer:
-- https://www.postgresql.org/docs/current/pgbuffercache.html#id-1.11.7.33.8
SELECT
    c.relname,
    count(*) AS buffers
FROM pg_buffercache b
INNER JOIN pg_class c
   ON b.relfilenode = pg_relation_filenode(c.oid)
        AND b.reldatabase IN (0, (SELECT oid FROM pg_database
WHERE datname = current_database()))
GROUP BY c.relname
ORDER BY 2 DESC;


--3. Clear Cache a Session

-- Ako funguje DISCARD
-- Pomocou DISCARD môžete uvoľniť interné zdroje databázových relácií.
-- Zahodiť temp, plán dopytov bežiacich sessions.

SET SESSION AUTHORIZATION DEFAULT;
RESET ALL;
DEALLOCATE ALL;
CLOSE ALL;
UNLISTEN *;
SELECT pg_advisory_unlock_all();

-- Uvoľniť všetky interné plány dotazov uložené v pamäti cache.
-- Release all internal cached query plans
DISCARD PLANS;

-- Zahodiť všetky dočasné tabuľky, ktoré sú vytvorené v aktuálnej relácii/session.
DISCARD TEMP;

-- DISCARD ALL: Pomocou tohto môžete obnoviť pôvodnú reláciu. Tento príkaz nemôžete vykonať v rámci transakčného bloku.

-- 4. Nájdenie relácie/session, ktoré blokujú ďalšie relácie/sessions v PostgreSQL

-- Namiesto hľadania blokovaných relácií alebo procesov je niekedy lepšie nájsť tie relácie, ktoré sú základom blokovania.
-- PostgreSQL DBA môže pomocou tohto skriptu nájsť príčinu blokovaných transakcií.

SELECT
    pl.pid as blocked_pid
    ,psa.usename as blocked_user
    ,pl2.pid as blocking_pid
    ,psa2.usename as blocking_user
    ,psa.query as blocked_statement
FROM pg_catalog.pg_locks pl
JOIN pg_catalog.pg_stat_activity psa
    ON pl.pid = psa.pid
JOIN pg_catalog.pg_locks pl2
JOIN pg_catalog.pg_stat_activity psa2
    ON pl2.pid = psa2.pid
    ON pl.transactionid = pl2.transactionid
        AND pl.pid != pl2.pid
WHERE NOT pl.granted;

--5. TOP 10 najdlhšie bežiacich dopytov pomocou pg_stat_statements

SELECT
    pd.datname
    ,pss.query AS SQLQuery
    ,pss.rows AS TotalRowCount
    ,(pss.total_time / 1000 / 60) AS TotalMinute
    ,((pss.total_time / 1000 / 60)/calls) as TotalAverageTime
FROM pg_stat_statements AS pss
INNER JOIN pg_database AS pd
    ON pss.dbid=pd.oid
ORDER BY 1 DESC
LIMIT 10;

-- 6. Nevyužívane indexy
SELECT
PSUI.indexrelid::regclass AS IndexName
,PSUI.relid::regclass AS TableName
FROM pg_stat_user_indexes AS PSUI
JOIN pg_index AS PI
    ON PSUI.IndexRelid = PI.IndexRelid
WHERE PSUI.idx_scan = 0
AND PI.indisunique IS FALSE;

-- 7. Duplicitné indexy
SELECT
    indrelid::regclass AS TableName
    ,array_agg(indexrelid::regclass) AS Indexes
FROM pg_index
GROUP BY
    indrelid
    ,indkey
HAVING COUNT(*) > 1;

-- Je zapnute AutoVacuum
show autovacuum;

/* VACUUM obnovuje úložisko obsadené mŕtvymi n-ticami/dead tuples.
V architektúre MCVV sa vymažú tuples, ktoré sa fyzicky neodstránia. Sú prítomné na disku, kým nie je dokončený VACUUM.

Ak na svojej tabuľke vykonávate časté UPDATE / DELETE, musíte nájsť fragmentáciu a vykonať na nej VACUUM. VACUUM nevyžaduje exkluzívny zámok tabuľky */
VACUUM Table_Name;

/* Nech už VACUUM získa priestor, ktorý si tento priestor môže vyžiadať, použije ho na ďalšie ukladanie. VACUUM FULL prepíše celú tabuľku údajmi a uvoľní všetok fragmentovaný priestor starej tabuľky.

Mali by sme sa vyhnúť VACUUM FULL, pretože zmenšuje celú tabuľku a zapisuje všetko do nového diskového bloku, ktorý vyžaduje viac zdrojov a miesto na disku na dokončenie tejto operácie.

Ak nájdete veľké množstvo mŕtvych riadkov, mali by ste v tejto tabuľke spustiť VACUUM FULL.
VACUUM FULL vyžaduje exkluzívny zámok na stole a tiež vyžaduje voľné miesto na disku rovnako ako veľkosť vašej tabuľky. */
VACUUM FULL Table_Name;

/* Po vykonaní VACUUM alebo VACUUM FULL musíte vykonať tento príkaz na aktualizáciu štatistík. Aktualizácia ANALYZE vyžaduje štatistiku a výsledky ukladá do katalógu systému pg_statistic.

Plánovač dotazov používa tieto štatistické informácie o databáze na prípravu efektívneho plánu vykonávania dotazov.
Môžete tiež spustiť VACUUM ANALYZE, ktorý vykoná prvý VACUUM a potom vykoná ANALYZE.
*/
ANALYZE Table_Name;
VACUUM ANALYZE Table_Name;

-- Kontrola stavu AutoVacuum pre všetky tabuľky
SELECT
    schemaname
    ,relname
    ,n_live_tup
    ,n_dead_tup
    ,last_autovacuum
FROM pg_stat_all_tables
ORDER BY n_dead_tup
    /(n_live_tup
      * current_setting('autovacuum_vacuum_scale_factor')::float8
      + current_setting('autovacuum_vacuum_threshold')::float8)
     DESC;

-- Vykonajte VACUUM FULL bez miesta na disku
/* VACUUM FULL získava všetok voľný priestor uvoľnený programom VACUUM fyzickým odstránením mŕtvych riadkov.

Táto metóda tiež vyžaduje viac miesta na disku, pretože zapíše novú kópiu tabuľky a neuvoľní starú kópiu, kým sa operácia neskončí. */

/* Aby sme tento problém vyriešili, potrebujeme ďalšie úložisko, ako ktorákoľvek iná sieťová jednotka alebo prenosný pevný disk.
Nezabudnite, že VACUUM FULL vyžaduje exkluzívny zámok pre tabuľku, takže počas tejto operácie nebude vaša tabuľka prístupná. */

-- Vytvorte nový tabuľkový priestor:
CREATE TABLESPACE temptablespace LOCATION '/path/../';

-- Skontrolujte aktuálny tabuľkový priestor tabuľky:
SELECT tablespace FROM pg_tables WHERE tablename = 'mybigtable';

-- Ak je NULL, má predvolený tabuľkový priestor.

-- Presuňte tabuľku do nového tabuľkového priestoru:
ALTER TABLE mybigtable SET TABLESPACE temptablespace;

-- Vykonajte VACUUM FULL:
VACUUM FULL mybigtable;

-- Presunúť tabuľku do starého tabuľkového priestoru: (presun na pg_default)
ALTER TABLE mybigtable SET TABLESPACE pg_default;

-- Dropnite tento dočasný tabuľkový priestor:
DROP TABLESPACE temptablespace;

--X1 Nájdenie celkového počtu živých a mŕtvych tíc (riadkov) tabuľky

-- Systémové funkcie a pg_class
SELECT
    relname AS ObjectName
    ,pg_stat_get_live_tuples(c.oid) AS LiveTuples
    ,pg_stat_get_dead_tuples(c.oid) AS DeadTuples
FROM pg_class c;

-- pg_stat_user_tables
SELECT
    relname AS TableName
    ,n_live_tup AS LiveTuples
    ,n_dead_tup AS DeadTuples
FROM pg_stat_user_tables;



-- XX 3. Non Superuser môže zrusit/killnut svoje vlastné spustené dopyty
-- Vytvor schemu
CREATE SCHEMA query_admin;
-- Vytvorte funkciu, ktorá zabije dopyt používateľa
CREATE OR REPLACE FUNCTION query_admin.kill_process(userpid integer)
RETURNS boolean AS $body$
DECLARE
    qry boolean;
BEGIN
    qry := (SELECT pg_catalog.pg_cancel_backend(pid)
            FROM pg_stat_activity
            WHERE usename=(select session_user)
            AND pid=userpid);
    RETURN qry;
END;
$body$
LANGUAGE plpgsql
SECURITY DEFINER
VOLATILE
RETURNS NULL ON NULL INPUT;

-- Udeľte povolenie na Public, aby k nemu mal prístup každý používateľ:
GRANT USAGE ON SCHEMA query_admin TO public;
GRANT EXECUTE ON FUNCTION query_admin.kill_process(pid integer) TO public;

-- Používateľ môže skontrolovať svoje bežiace dotazy process_id pomocou dotazu nižšie:

SELECT * FROM pg_stat_activity
WHERE usename = 'user_name';

-- Ukážka vykonania kill_process()
SELECT * FROM query_admin.kill_process(process_id);
SELECT * FROM query_admin.kill_process(14526);