PostgreSQL in real life

Talk for the Computer Science degree at TecnoCampus Mataró

Transcript

1. PostgreSQL in real life 1 / 42
2. Pau Pérez @prez_pau Tech (mainly) at Coopdevs Working on: Open Food Network TimeOver ow Donalo.org Som Connexió ... 2 / 42
3. Coopdevs Software lliure per a l'economia social 3 / 42
4. What is PostgreSQL? 4 / 42
5. What is PostgreSQL? A free and open source object-relation DBMS 5 / 42
6. What is PostgreSQL? Relational Extensible SQL-standard compliant (mostly) ACID through MVCC Transactions Procedural language Triggers Views Replication 6 / 42
7. What is PostgreSQL? Concurrent DDL Rules CTEs Full-text search Pub/Sub TOAST (The Oversized-Attribute Storage Technique) Foreign Data Wrappers Parallel query execution JIT 7 / 42
8. What is PostgreSQL? Ingres, University of California, Berkeley, 1982 POSTGRES project starts in Berkeley, 1985 POSTGRES v1, 1989 MIT license, 1994 Replaced POSTQUEL with SQL + psql, 1995 Renamed to PostgreSQL and life outside Berkeley, 1996 v6.0 and PostgreSQL Global Development Group, 1997 8 / 42
9. Open Source No vendor lock-in + Rich ecosystem + Development pace 9 / 42
10. Partial indexes 10 / 42
11. Theory CREATE UNIQUE INDEX ON users (email) WHERE deleted_at is null; 11 / 42
12. Real life Som Connexió needs to match the consumptions to their invoice lines 12 / 42
13. Real life Som Connexió needs to match the consumptions to their invoice lines Mas Móvil lets them export the monthly consumptions 12 / 42
14. Real life Som Connexió needs to match the consumptions to their invoice lines Mas Móvil lets them export the monthly consumptions account_invoice_line has a polymorphic column 12 / 42
15. Real life Som Connexió needs to match the consumptions to their invoice lines Mas Móvil lets them export the monthly consumptions account_invoice_line has a polymorphic column We need to nd the appropriate invoice lines that match the Mas Móvil data 12 / 42
16. Real life CREATE INDEX CONCURRENTLY account_invoice_line_product_partial_idx ON account_invoice_line (product) WHERE origin LIKE 'contract.consumption,%';" 13 / 42
17. Real life Mediation process X times faster => Happy customer 14 / 42
18. Full-text search 15 / 42
19. Theory tsvector: represents a document in a form optimized for text search tsquery: represents a text query 16 / 42
20. Theory SELECT 'a fat cat sat on a mat rat'::tsvector @@ 'cat & rat'::tsquery; 17 / 42
21. Theory Full text search performs: Parsing documents into tokens Converting tokens into lexemes Storing preprocessed documents optimized for searching 18 / 42
22. Real life 19 / 42
23. Real life TimeOver ow's operational costs ↓↓ => We can contribute to the project 20 / 42
24. pg_stat_statements 21 / 42
25. Theory Separate module that tracks SQL statements' execution It comes with its view and utility functions 22 / 42
26. Theory SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5; query | INSERT INTO "sessions" ("created_at", "data", "session_id", .. calls | 5 total_time | 83.215 rows | 5 hit_percent | 73.5294117647058824 23 / 42
27. Real life Our users are annoyed with the slowness of the app. Where should we focus our efforts? 24 / 42
28. Real life query | SELECT COUNT(*) FROM "order_cycles" LEFT OUTER JOIN exchanges ON (exchanges.order_cycle_id = order_cycles.id) LEFT OUTER JOIN enterprises ON (enterprises.id = exchanges.sender_id OR enterprises.id = exchanges.receiver_id) WHERE order_cycles.coordinator_id IN (?) OR enterprises.id IN (?) calls | 30040 total_time | 626381.9099999 rows | 30040 hit_percent | 100.0000000000000000 25 / 42
29. Real life 26 / 42
30. Real life We spend time on where we can improve the most => The app will be noticeable faster 27 / 42
31. pg_stat_activity 28 / 42
32. Theory datid | 16386 datname | openfoodnetwork pid | 2763 usesysid | 16384 usename | ofn_user application_name | delayed_job client_addr | ::1 client_hostname | client_port | 57372 backend_start | 2019-06-05 07:52:16.372732+00 xact_start | query_start | 2019-06-05 09:40:43.282826+00 state_change | 2019-06-05 09:40:43.283123+00 waiting | f state | idle backend_xid | backend_xmin | query | UPDATE "delayed_jobs" SET locked_at = ... 29 / 42
33. Real life What the hell is going on! # select query from pg_stat_activity where pid = 1824; -[ RECORD 1 ]----+----------------------------------------------------------- query | SELECT "a"."id" AS "id" FROM "import_csv_log" AS "a" WHERE (("a"."parent" IN (3642820))) ORDER BY "a"."date_time" DESC, "a"."id" ASC` https://github.com/coopdevs/handbook/wiki/Investigar-queries-lentas 30 / 42
34. Real life Successful disaster management => Not-so-annoyed users 31 / 42
35. CHECK constraints 32 / 42
36. Theory CREATE TABLE products ( (...) sale_price numeric CHECK (sale_price > 0), CHECK (price > sale_price) ); 33 / 42
37. Theory CREATE TABLE fibonacci (i int CHECK (is_fib(i))); being is_ b a user-de ned function 34 / 42
38. Real life https://github.com/coopdevs/timeover ow/pull/421 35 / 42
39. Real life https://github.com/coopdevs/timeover ow/pull/348 36 / 42
40. PostgreSQL monitoring 37 / 42
41. Theory Statistics collector + powerful visualizations = observability 38 / 42
42. Real life 39 / 42
43. Q&A 40 / 42
44. Session resources gitlab.com/coopdevs/postgresql-in-real-life coopdevs.org github.com/coopdevs gitlab.com/coopdevs community.coopdevs.org 41 / 42
45. 42 / 42