PostgreSQL – využíváme sílu nástroje Martin Strejc 12. 11. 20 12 boss.etnetera.cz

© 2012 ET NETERA a.s.

PostgreSQL – využíváme sílu nástrojeMartin Strejc12. 11. 2012

http://boss.etnetera.cz


Harmonogram

16:00 – 16:40 chápání DB, nové možnosti

16:40 – 16:50 pauza na kafe

16:50 – 17:20 ukázky funkcionalit PostgreSQL

17:20 – 17:30 volná diskuze


Co je pokrok?

Thinking out of the box :-)

Joe Coelko:

za 50 let byly hlavní milníky

děrnoštítková páska, harrdisk, SSD


Využívám PostgreSQL

1.2001 přechod z MySQL na PostgreSQL

2.MySQL spíše minoritně

3.řada úspěšných aplikací


Rozdíl mezi DPL a JPA

1.JPA / Hibernate není všeobjímající

2.JPA nevyužívá všechny fce aplikace

3.JPA netuší nic o návrhu, náročnosti,

neumí predikovat (ORM vs. JPA)


Rozdíl mezi DPL a JPA

1.DPL řeší problémy, konzultuje návrh struktury DB

2.Architekt umí rozkládat aplikaci, navrhnout DB

3.Architekt a administrátor musí komunikovat...


Proč raději PostgreSQL než MySQL?

1.nesmyslná otázka...

2.vlastně je to jedno, závisí na účelu

3.stanovím si při návrhu aplikace

4.vím funkce, které a proč chci využívat

5.dohodnu se s DPL

6.momentálně v pilotním režimu

Srovnání Postgres a MySQLhttps://docs.google.com/a/etnetera.cz/spreadsheet/ccc?key=0Ak3v1aRDwctmdFM1WDlsTlVSUEl4NHlEY040cnNaaUE#gid=0

https://docs.google.com/a/etnetera.cz/spreadsheet/ccc?key=0Ak3v1aRDwctmdFM1WDlsTlVSUEl4NHlEY040cnNaaUE#gid=0

https://docs.google.com/a/etnetera.cz/spreadsheet/ccc?key=0Ak3v1aRDwctmdFM1WDlsTlVSUEl4NHlEY040cnNaaUE#gid=0


Jak to v PostgreSQL funguje?

1.PostgreSQL je platforma

2. funkce, typy, indexy, rozšíření mají handler

3.PostgreSQL se zaměřuje na fukcionalitu

(MySQL se zaměřuje spíše na engine)


Zajímavé funkcionality PostgreSQL1. plná podpora SQL standardu včetne všech relačních dotazů

2. bezpečnost - rozsáhly systém ACL

3. jazyky PL/PgSQL a další (včetně Java, PHP, Perl, Python

nebo C pro rychlé zpracování dat přímo na serveru)

1. Vnitřní clusterování tabulky (např. archiv a ostrá data)

2. Externí zdroje dat (např. dokonce jiné typy DB)

3. Vlastní funkce a triggery (trigger je také funkce)

4. Řada dalších datových typu, včetne atypických datových typu + práce s nimi (BLOB, pole, XML)

5. Datový typ RANGE a práce s ním (porovnávání, průniky apod.)


Zajímavé funkcionality PostgreSQL9. Oborově zaměřené - JSON, GIS, BIS (JavaScript, geometrická

data, biometrická data)

10.Podpora XSLT přímo nad daty - většina programátorů často nemumí používat (XML, XSLT, XPath)

11.Různé další speciální funkce přímo na serveru, např. kryptování

12.Rozsáhlá podpora indexů, odložené kontroly, selektivní indexy a regulárními výrazy, vlastní indexy

13.Common Table Expression (CTE), obsahuje i rekurzivní výrazy

14.Návratové hodnoty po UPDATE a INSERT

15.Sekvence

16.Fulltext search a TSINDEX


Zajímavé funkcionality PostgreSQL17.Více schemat v rámci jedné databáze

(tedy 1 server - N databazi - M schémat) za současné kontroly referenční integrity dat

17.Fuzzy porovnáváni textových řetězců - vhodné pro velké texty

18.Client side data listener/notifications

19.Řada nástrojů na debugování SQL dotazů

20.Debugování/EXPLAIN Prepared Statement

21.TLS/SSL na úrovni connection

22.Velká řada rozšíření z pgFoundary (tam je skoro cokoliv, ale stabilita je dost různá, udávám spíše na okraj)


Mimo standard SQL'92 !?!

1.Databáze je málokdy zcela vyměnitelná

2.JPA neřeší práva

3.Další DB klienti mimo hlavní aplikaci

4.Změna návrhu aplikace – co je logika, co data

5.Proč bych měl navrhnout aplikaci jinak?


Praktické ukázky

1. Indexy

2.Common Tables Expression

3.Full Text Search a Indexy

4.Pl / PgSQL

5.COPY & CSV


Indexy1. -- CREATE INDEX user_nick_profi2. -- ON user_profile (nick ASC NULLS LAST)3. -- WHERE profi;

4. SELECT count(*) FROM user_profile;5. -- 10.210 rows

6. SELECT * FROM user_profile where profi;7. -- 11 rows

8. SELECT * FROM user_profile where profi and nick ilike 'd%';9. -- 2 rows

10. EXPLAIN ANALYZE SELECT * FROM user_profile where profi and nick ilike 'd%';

11. CREATE INDEX user_nick_profi12. ON user_profile (nick ASC NULLS LAST)13. WHERE profi;

14. SELECT * FROM user_profile where profi and nick ilike 'd%';

15. EXPLAIN ANALYZE SELECT * FROM user_profile where profi and nick ilike 'd%';

16. DROP INDEX user_nick_profi;


Common Table Expressions1. WITH u AS (2. SELECT up.id as user_id, up.nick as nick, f.nick as friend_nick FROM user_profile up 3. LEFT JOIN (SELECT * FROM user_profile, user_friend WHERE user_friend.friend_id = user_profile.id) f4. ON (up.id = f.user_id )5. )6. SELECT shop.id, shop.name, 'shop' as shop_type, u.* FROM shop 7. LEFT JOIN u ON (shop.user_id = u.user_id)8. UNION ALL9. SELECT eshop.id, eshop.name, 'e-shop' as shop_type, u.* FROM eshop 10. LEFT JOIN u ON (eshop.user_id = u.user_id);

11. WITH RECURSIVE t(n) AS (12. SELECT 113. UNION ALL14. SELECT n+1 FROM t15. )16. SELECT n FROM t LIMIT 100;

17. WITH RECURSIVE fact(i, f) AS (18. VALUES (2, 1)19. UNION ALL20. SELECT i + 1, i * f FROM fact21. )22. SELECT f FROM fact LIMIT 10;


Full Text Search1. SELECT count(*) FROM shop;2. -- 12.029

3. SELECT * FROM shop WHERE (to_tsvector(srch) @@ to_tsquery(lower('(oc|obchod)&!mexx')));4. -- 703 rows, 897 ms

5. EXPLAIN ANALYZE SELECT * FROM shop WHERE (to_tsvector(srch) @@ to_tsquery(lower('(oc|obchod)&!mexx')));

6. ALTER TABLE shop ADD COLUMN ts_vec tsvector;

7. UPDATE shop SET ts_vec = to_tsvector(srch);

8. CREATE INDEX shop_ts_idex ON shop USING gist(ts_vec);

9. SELECT * FROM shop WHERE (ts_vec @@ to_tsquery(lower('(oc|obchod)&!mexx')));10. -- 703 rows, 55 ms, cca 1:50

11. EXPLAIN ANALYZE SELECT * FROM shop WHERE (ts_vec @@ to_tsquery(lower('(oc|obchod)&!mexx')));

12. DROP INDEX shop_ts_idex;13. ALTER TABLE shop DROP COLUMN ts_vec;


Pl / PgSQL1. CREATE OR REPLACE FUNCTION shop_srch_trig()2. RETURNS trigger AS3. $BODY$BEGIN4. NEW.srch = lower(coalesce(NEW.name, ''::text) 5. || (' '::text) 6. || coalesce(NEW.street, ''::text) 7. || (' '::text) 8. || coalesce(NEW.email, ''::text) 9. || (' '::text) 10. || coalesce(NEW.url, ''::text) 11. || (' '::text) 12. || coalesce(NEW.description, ''::text));13. -- NEW.ts_vec = to_tsvector(NEW.srch)14. RETURN NEW;15. END;$BODY$16. LANGUAGE plpgsql VOLATILE17. COST 100;

18. CREATE TRIGGER shop_srch_trig19. BEFORE INSERT OR UPDATE20. ON shop21. FOR EACH ROW22. EXECUTE PROCEDURE shop_srch_trig();


COPY & CSV1. COPY user_profile TO '/var/lib/pgsql/user-profiles.csv' DELIMITERS ' ' CSV HEADER;2. -- vim /var/lib/pgsql/user-profiles.csv

3. CREATE TABLE user_profile_backup (LIKE user_profile);4. COPY user_profile_backup FROM '/var/lib/pgsql/user-profiles.csv' DELIMITERS ' ' CSV HEADER;5. -- rm /var/lib/pgsql/user-profiles.csv

6. SELECT * FROM user_profile_backup;

7. DROP TABLE user_profile_backup;


Osnova přednášky

1.Deploy a konfigurace

2.Základ aplikace

3.Konfigurace aplikace

4.Typy Wicket aplikací

5.Spojení se Spring


Success Stories

1.Odklepnuto.cz

2.Foxter.cz


Odklepnuto.cz

1.Aukční portál

2.Vývoj aplikace, konzultace při vývoji

3.Správa serverů

4.Velký nárůst návštěvnosti v roce 2007

5.>1000 registrací denně, 10000 UIP

6.Jeden server DB + App, cca 40 GB dat Postgres

7.Pět serverů během 2 měsíců, 200 GB dat

8.Optimalizace výkonu PostgreSQL

na nevýkonném hardware


Foxter.cz

1.Portál o nakupování

2.Velká roztříštěnost dat

3.Fulltext

4.120 tis. UIP měsíčně,

extrém 8 tis. UIP denně

1.Data 500 MB v DB (obrázky jsou mimo)

2.Selecty přes hodně tabulek

3.Min. HW konfigurace, snadná optimalizace,

hlavně indexy a vacuum


Dotazy

1. ... ?2. ... ?3. ... ?

Děkuji za pozornost :-)http://boss.etnetera.cz

Date post:	15-Mar-2016
Category:	Documents
Upload:	germane-gomez
View:	54 times
Download:	6 times

PostgreSQL – využíváme sílu nástroje Martin Strejc 12. 11. 20 12 boss.etnetera.cz

Documents