PGConf Silicon Valley 2016
2016-11-14..2016-11-16
San Francisco Bay Area, United States
[PGConf Silicon Valley 2015, PGConf Silicon Valley 2016]
who it's for
database administrators, PostgreSQL developers, backend developers, DevOps engineers, data engineers, site reliability engineers, application developers
topics
connection pooling with PgBouncer; jsonb and array datatypes; TOAST large-value storage; WAL durability and checkpoint tuning; autovacuum internals and tuning; in-database machine learning (Apache Madlib); page-level compression with ZSTD; logical and multi-master replication; PL/pgSQL functions and debugging; pgbench custom workloads and benchmarking; transactional DDL and serializable isolation; containerized clustered PostgreSQL
| title | author | length |
|---|---|---|
| Understanding Advanced Datatypes in PostgreSQL | Peter Van Hardenberg | 57m |
Advocates defaulting to bigserial or uuid for IDs and using text over varchar; recommends timestamp with time zone always and jsonb instead of json. Describes… | ||
| Simplify your API | Manuel Zahariev | 45m |
A small C++ service called Sticks exposes PostgreSQL functions as HTTP endpoints so a function returns multiple format outputs (JSON, XML, CSV, XLSX, ODS) by… | ||
| PostgreSQL Top Ten Features | Simon Riggs | 51m |
Postgres provides concrete, usable features: transactional DDL so CREATE/ALTER/DROP run inside transactions; true serializable isolation implemented to avoid… | ||
| PostgreSQL The Hard Way | James Miller | 32m |
Upgrades PostgreSQL 9.2 to 9.4 across seven clusters (37 servers, largest 667 GB) aiming for four‑hour maintenance windows by using PG upgrade with hard links… | ||
| PgBouncer Connection Pooling and Routing | Petr Jelinek | 46m |
PGbouncer sits between apps and Postgres as a lightweight proxy that handles thousands of concurrent connections on one CPU, drops DB concurrency by mapping… | ||
| Distributed In-Database Machine Leaning | Frank McQuillan | 55m |
Apache Madlib brings machine learning into the database and runs in-database on Greenplum, HAWQ and Postgres. Algorithms are implemented in C++ using Eigen and… | ||
| In core compression-How to shrink your database | Anastasia Lubennikova | 34m |
Demonstrates a Postgres Pro Enterprise page-level compressor (CFS) that writes compressed variable-size pages on eviction, tracks them in a map file and runs… | ||
| How does PostgreSQL actually work | Peter Eisentraut | 51m |
PostgreSQL is about 880,000 lines of code (≈730k C) and processes a query by passing the SQL string from psql through libpq protocol messages (Q, T, D, Z)… | ||
| Fun with Functions | Joe Conway | 56m |
Covers concrete PostgreSQL function mechanics: exact create function syntax, argument modes (IN/OUT/INOUT/VARIADIC), polymorphic types and defaults… | ||
| Durability in Postgres | Andres Freund | 57m |
Explains how Postgres actually achieves durability (WAL in 16MB segments, checkpoints that fsync dirty shared_buffers) and what to tune: checkpoint_timeout… | ||
| Designing the Right Schema to Power Heap | Dan Robinson | 01h 09m |
Heap installs a snippet that captures every click and UI event so analyses are retroactive; that forces redesigns of the schema from normalized tables to… | ||
| Debugging your PL/pgSQL code | Jim Mlodgenski | 44m |
Debugging PL/pgSQL uses two concrete tools: the PL debugger (shippped in BigSQL, requires shared_preload_libraries and pgAdmin3) that lets sessions step, set… | ||
| Becoming a SQL Guru | Stella Nisenbaum | 54m |
Covers concrete SQL features with examples and gotchas: use of the FILTER clause (SQL-1994) to simplify conditional aggregates, GROUPING SETS / ROLLUP / CUBE… | ||
| Dynamic Database Credentials | Sean Chittenden | 53m |
Vault issues short‑lived PostgreSQL accounts on demand, creates GRANTs, and revokes them with a PG terminate when leases expire so credentials are tied to a… | ||
| Making Data Dance | Clark Barrett | 33m |
Window functions like lead, lag, first_value, last_value and row_number are used to group and collapse chat messages by user and room, with examples that… | ||
| Understanding Autovacuum | Dan Robinson | 55m |
Explains exactly what autovacuum does: scans tables to add dead row space to the free space map, remove dead index entries, and replace old XIDs with a… | ||
| pg paxos Table Replication through Distributed Consensus | Marco Slot | 46m |
A Postgres extension called PG Paxos (pgos/PG paos) implements multi-Paxos-style table replication in ~1,000 lines of plpgsql using dblink, running on Postgres… | ||
| Multi master replication for painless fault tolerance | Stas Kelvich | 53m |
A multimaster system built as a Postgres Pro extension runs on a Postgres 9.6 patch and turns every commit into a prepare, uses a modified logical replication… | ||
| Implementing a Graph Database based on PostSQL | Kisung Kim | 29m |
Azen Graph implements a disk‑backed, OLTP graph engine on PostgreSQL, shipping v0.9 that supports 70% of openCypher, JDBC/ODBC/Python clients and a web… | ||
| How to use the DBT 3 test kit | Mark Wong | 52m |
DBT3 is a TPCH-derived workload that generates data from gigabytes to tens of terabytes and runs three stages: parallel load, a 21-query power test, and… | ||
| Database Hardware Selection Guidelines | Bruce Momjian | 53m |
Benchmarking with pg_test_fsync shows real differences: an SSD with proper power protection delivers about 6000 fsync/s vs 80 fsync/s for a magnetic drive with… | ||
| Connection pooling routing and queueing with pgBouncer | Peter Eisentraut | 51m |
pgBouncer reduces connection overhead dramatically: 10,000 connects: 24s → 4s and with a small query 27s → 6s, while a long-lived session with 100k selects… | ||
| Governor: Postgres HA Orchestator | Chris Winslett | 54m |
Governor is a Postgres HA orchestrator that originally uses etcd + Python Governor to store WAL positions, run initialization races, and provide automatic… | ||
| PostgresQL as an Application Modernization | Charles Finley | 43m |
Claim: migrate legacy systems onto Postgres and you get SQL/ODBC/JDBC/REST access day one while keeping original behavior. Implementation: build a driver that… | ||
| Are NoSQL databases more reliable? | Alan Jones | 24m |
Claims a Postgres setup can get NoSQL-style majority-commit reliability by continuously reconfiguring quorum groups instead of simple standby promotion. Built… | ||
| Containerized Clustered PostgreSQL | Josh Berkus | 54m |
Argues that containerized clustered PostgreSQL runs best on Kubernetes StatefulSet (petset) and shows working prototypes: a Patron/Patroni daemon run as PID 1… | ||
| Gophers riding Elephants | AJ Bahnken | 39m |
Builds two Go tools: PG Detective parses multi‑GB pcap files with the go‑packet library to correlate Postgres parse packets and responses, finds a 1:1000… | ||
| Pl/cuda the fusion of HPC grade power | Kohei KaiGai | 51m |
Adds procedural C-blocks inside PostgreSQL CREATE FUNCTION that compile to GPU kernels and run on ZPU/CUDA devices, with infrastructure to allocate device… | ||
| PostgreSQL's secret NoSQL superpowers | Amanda Gilmore | 59m |
Uses PostgreSQL 9.5 and shows exact commands: xmlparse + XPath to extract titles from a book catalog but XML cannot be updated natively; creates the hstore… | ||
| Securing PostgreSQL | Christophe Pettus | 50m |
Patches and patching policy matter: subscribe to PGSQL announce, apply security updates immediately, and make someone responsible. Network rules are concrete… | ||
| Sort vs Hash A Duality | Peter V Geoghegan | 55m |
Claim: OLTP gets single-digit‑ms performance on seven‑join queries by using nested loop + B‑tree index lookups that are effectively independent of dataset… | ||
| Using Postgres XL | Richard Silva | 59m |
Javelin moves large analytics workloads onto Postgres XL across 10 machines (8 cores, 16GB RAM, ~1TB each) and cuts queries from ~5 minutes to seconds… | ||
| PGLogical the logical replication for Postgresql | Petr Jelinek | 50m |
A Postgres extension called PGLogical extension implements logical decoding-based replication for 9.4+ and aims to replace trigger-based tools; it ships as SQL… | ||
| Understanding Advanced Datatypes in PostgreSQL | Peter Van Hardenberg | 57m |
Use bigserial or UUID for surrogate keys (create the uuid-ossp extension if needed); prefer text not varchar for strings; default timestamps to timestamp with… | ||
| nothing matches | ||