class: center, middle # pgconf.eu 2018 -- Lisbon Nov 14th 2018, Tech Quarterly 2018-03 ------------------------------------------- Volker Fröhlich --------------- --- # Where, what? * https://pgconf.eu * Around 350 attendees, many of them German * About half of them were first-timers * 7% women in the audience * At least 10 gave presentations * 3 tracks and lightning talks --- # Topics * Keynote on geo data * Basics (psql, constraints, indexing, backups, ...) * Extensions * Logical replication * GPU/NVME --- # Basics * Temporary tables override regular tables * DOMAIN in PG -- Reusable constraints * CREATE UNIQUE INDEX ... WHERE * Several psql meta commands ``` \g, \gexec, \!, \watch, \if, \pset format ``` ``` SELECT * FROM (VALUES (1, 'one'), (2, 'two')) AS t (num, letter); ``` ??? g für einmal in Datei oder wiederholen gexec macht für jede Spalte des Ergebnisses als sql statement ! shell if seit 10 pset format html --- # Basics continued All of the below have slides available: * Talk about the nature of bloat * Talk about how parallel querys work and not * Talk about full-text-search * Talk about Backrest --- # Extensions * Timescale * Partman * hyperloglog -- Had its own talk * topn * zombo -- Create indices in ES * pg_cron -- Schedule jobs inside PG --- # Logical replication/decoding * Example of logical decoding to create an application cache * Decoding: Extract relevant persistent changes from a WAL into an easy-to-use format * Replication Slot Stream output plug-in * Plug-in is responsible for output format and filtering * Old and new value available --- # GPU/NVME * pg-strom * Very efficient if you process a great chunk of data at a time * Peer-to-peer DMA between NVME and graphic card --- # Bits and pieces * Lightning talks * Worst practices * Why did PG become successful? ??? Don't use postgres, Naturwissenschaft, GPU --- # Conclusions * Women feel attracted to DBMSes * It's worth looking at the psql man page * Logical replication/decoding might be able to replace pgq and NOTIFY for us, perhaps even some of our Redis update scripts? * NVME/GPU might be interesting ``` ```