1 / 23

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

PostgreSQL – využíváme sílu nástroje Martin Strejc 12. 11. 20 12 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 :-)

germane-gomez
Download Presentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. PostgreSQL – využíváme sílu nástroje Martin Strejc 12. 11. 2012 http://boss.etnetera.cz

  2. 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

  3. 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

  4. Využívám PostgreSQL 2001 přechod z MySQL na PostgreSQL MySQL spíše minoritně řada úspěšných aplikací

  5. Rozdíl mezi DPL a JPA JPA / Hibernate není všeobjímající JPA nevyužívá všechny fce aplikace JPA netuší nic o návrhu, náročnosti, neumí predikovat (ORM vs. JPA)

  6. Rozdíl mezi DPL a JPA DPL řeší problémy, konzultuje návrh struktury DB Architekt umí rozkládat aplikaci, navrhnout DB Architekt a administrátor musí komunikovat...

  7. Proč raději PostgreSQL než MySQL? • nesmyslná otázka... • vlastně je to jedno, závisí na účelu • stanovím si při návrhu aplikace • vím funkce, které a proč chci využívat • dohodnu se s DPL • momentálně v pilotním režimu Srovnání Postgres a MySQL https://docs.google.com/a/etnetera.cz/spreadsheet/ccc?key=0Ak3v1aRDwctmdFM1WDlsTlVSUEl4NHlEY040cnNaaUE#gid=0

  8. Jak to v PostgreSQL funguje? PostgreSQL je platforma funkce, typy, indexy, rozšíření mají handler PostgreSQL se zaměřuje na fukcionalitu (MySQL se zaměřuje spíše na engine)

  9. Zajímavé funkcionality PostgreSQL plná podpora SQL standardu včetne všech relačních dotazů bezpečnost - rozsáhly systém ACL jazyky PL/PgSQL a další (včetně Java, PHP, Perl, Python nebo C pro rychlé zpracování dat přímo na serveru) Vnitřní clusterování tabulky (např. archiv a ostrá data) Externí zdroje dat (např. dokonce jiné typy DB) Vlastní funkce a triggery (trigger je také funkce) Řada dalších datových typu, včetne atypických datových typu + práce s nimi (BLOB, pole, XML) Datový typ RANGE a práce s ním (porovnávání, průniky apod.)

  10. Zajímavé funkcionality PostgreSQL Oborově zaměřené - JSON, GIS, BIS (JavaScript, geometrická data, biometrická data) Podpora XSLT přímo nad daty - většina programátorů často nemumí používat (XML, XSLT, XPath) Různé další speciální funkce přímo na serveru, např. kryptování Rozsáhlá podpora indexů, odložené kontroly, selektivní indexy a regulárními výrazy, vlastní indexy Common Table Expression (CTE), obsahuje i rekurzivní výrazy Návratové hodnoty po UPDATE a INSERT Sekvence Fulltext search a TSINDEX

  11. Zajímavé funkcionality PostgreSQL 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 Fuzzy porovnáváni textových řetězců - vhodné pro velké texty Client side data listener/notifications Řada nástrojů na debugování SQL dotazů Debugování/EXPLAIN Prepared Statement TLS/SSL na úrovni connection Velká řada rozšíření z pgFoundary (tam je skoro cokoliv, ale stabilita je dost různá, udávám spíše na okraj)

  12. Mimo standard SQL'92 !?! Databáze je málokdy zcela vyměnitelná JPA neřeší práva Další DB klienti mimo hlavní aplikaci Změna návrhu aplikace – co je logika, co data Proč bych měl navrhnout aplikaci jinak?

  13. Praktické ukázky Indexy Common Tables Expression Full Text Search a Indexy Pl / PgSQL COPY & CSV

  14. Indexy -- CREATE INDEX user_nick_profi -- ON user_profile (nick ASC NULLS LAST) -- WHERE profi; SELECT count(*) FROM user_profile; -- 10.210 rows SELECT * FROM user_profile where profi; -- 11 rows SELECT * FROM user_profile where profi and nick ilike 'd%'; -- 2 rows EXPLAIN ANALYZE SELECT * FROM user_profile where profi and nick ilike 'd%'; CREATE INDEX user_nick_profi ON user_profile (nick ASC NULLS LAST) WHERE profi; SELECT * FROM user_profile where profi and nick ilike 'd%'; EXPLAIN ANALYZE SELECT * FROM user_profile where profi and nick ilike 'd%'; DROP INDEX user_nick_profi;

  15. Common Table Expressions WITH u AS ( SELECT up.id as user_id, up.nick as nick, f.nick as friend_nick FROM user_profile up LEFT JOIN (SELECT * FROM user_profile, user_friend WHERE user_friend.friend_id = user_profile.id) f ON (up.id = f.user_id ) ) SELECT shop.id, shop.name, 'shop' as shop_type, u.* FROM shop LEFT JOIN u ON (shop.user_id = u.user_id) UNION ALL SELECT eshop.id, eshop.name, 'e-shop' as shop_type, u.* FROM eshop LEFT JOIN u ON (eshop.user_id = u.user_id); WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT n+1 FROM t ) SELECT n FROM t LIMIT 100; WITH RECURSIVE fact(i, f) AS ( VALUES (2, 1) UNION ALL SELECT i + 1, i * f FROM fact ) SELECT f FROM fact LIMIT 10;

  16. Full Text Search SELECT count(*) FROM shop; -- 12.029 SELECT * FROM shop WHERE (to_tsvector(srch) @@ to_tsquery(lower('(oc|obchod)&!mexx'))); -- 703 rows, 897 ms EXPLAIN ANALYZE SELECT * FROM shop WHERE (to_tsvector(srch) @@ to_tsquery(lower('(oc|obchod)&!mexx'))); ALTER TABLE shop ADD COLUMN ts_vec tsvector; UPDATE shop SET ts_vec = to_tsvector(srch); CREATE INDEX shop_ts_idex ON shop USING gist(ts_vec); SELECT * FROM shop WHERE (ts_vec @@ to_tsquery(lower('(oc|obchod)&!mexx'))); -- 703 rows, 55 ms, cca 1:50 EXPLAIN ANALYZE SELECT * FROM shop WHERE (ts_vec @@ to_tsquery(lower('(oc|obchod)&!mexx'))); DROP INDEX shop_ts_idex; ALTER TABLE shop DROP COLUMN ts_vec;

  17. Pl / PgSQL CREATE OR REPLACE FUNCTION shop_srch_trig() RETURNS trigger AS $BODY$BEGIN NEW.srch = lower(coalesce(NEW.name, ''::text) || (' '::text) || coalesce(NEW.street, ''::text) || (' '::text) || coalesce(NEW.email, ''::text) || (' '::text) || coalesce(NEW.url, ''::text) || (' '::text) || coalesce(NEW.description, ''::text)); -- NEW.ts_vec = to_tsvector(NEW.srch) RETURN NEW; END;$BODY$ LANGUAGE plpgsql VOLATILE COST 100; CREATE TRIGGER shop_srch_trig BEFORE INSERT OR UPDATE ON shop FOR EACH ROW EXECUTE PROCEDURE shop_srch_trig();

  18. COPY & CSV COPY user_profile TO '/var/lib/pgsql/user-profiles.csv' DELIMITERS ' ' CSV HEADER; -- vim /var/lib/pgsql/user-profiles.csv CREATE TABLE user_profile_backup (LIKE user_profile); COPY user_profile_backup FROM '/var/lib/pgsql/user-profiles.csv' DELIMITERS ' ' CSV HEADER; -- rm /var/lib/pgsql/user-profiles.csv SELECT * FROM user_profile_backup; DROP TABLE user_profile_backup;

  19. Osnova přednášky Deploy a konfigurace Základ aplikace Konfigurace aplikace Typy Wicket aplikací Spojení se Spring

  20. Success Stories Odklepnuto.cz Foxter.cz

  21. Odklepnuto.cz Aukční portál Vývoj aplikace, konzultace při vývoji Správa serverů Velký nárůst návštěvnosti v roce 2007 >1000 registrací denně, 10000 UIP Jeden server DB + App, cca 40 GB dat Postgres Pět serverů během 2 měsíců, 200 GB dat Optimalizace výkonu PostgreSQL na nevýkonném hardware

  22. Foxter.cz Portál o nakupování Velká roztříštěnost dat Fulltext 120 tis. UIP měsíčně, extrém 8 tis. UIP denně Data 500 MB v DB (obrázky jsou mimo) Selecty přes hodně tabulek Min. HW konfigurace, snadná optimalizace, hlavně indexy a vacuum

  23. Dotazy • ... ? • ... ? • ... ? Děkuji za pozornost :-) http://boss.etnetera.cz

More Related