Postgres Postgres: An introduction Selena Deckelmann Mozilla @selenamarie [email protected]

AMA ● Please, ask questions. ● Hands raised is useful so I look toward the voice. ● Can I get a scribe volunteer?

Socorro ● Crash storage and analysis for Breakpad, the crash collection client for Firefox ● 3-5 million crashes per day ● S3, Postgres, Elastic Search, RabbitMQ ● Python, .js and some C ● http://socorro.readthedocs.org

No content

about:crashes

crash-stats.mozilla.com

No content

No content

No content

Postgres Developer Summit, 2008

History of Postgres ● Code first written in 1986 ● Open sourced in 1996 (version 6.0) ● Current released version: 9.4 ● 300-500 contributors per year ● Extremely active developer and user community

My story ● Worked on an ERP migration for a bike company from Mac OS 9 DB Qube → Postgres ● Was already into open source, started a user group ● Started running conferences ● Was a consultant for a few years ● Worked on a pathological schema with ~500k+ tables, 100k schemas

Key features ● SQL system ● Strict attention to standards ● Procedural languages in the database like pl/v8 (.js) and pl/python ● Rich developer interfaces ● Designed for stability and data safety

Framework support ● Django prefers Postgres :) ● Flask support is great ● Tell me what frameworks I should know about :)

Cloudy Postgres ● Heroku heroku addons:add heroku­postgresql:hobby­dev ● Amazon RDS Postgres ● Engine Yard ● Rackspace

DIY Postgres ● There are Puppet/Chef (or whatever) recipes out there! ● Main files to be concerned about: postgresql.conf # DB config (GUCs) pg_hba.conf # access control recovery.conf # replication ● Postgres team distributes RPMs and .deb and we have packagers for most major distros ● OS X? Postgres.app for local dev http://postgresapp.com/ Wild west for reproducible deploys. brew maybe? ● Windows has good support from EnterpriseDB

Types: your secret weapon ● Help you keep your data tidy ● Make queries faster! ● Makes migrations and testing easier

Types: your secret weapon ● Core: TEXT, INTEGER, BIGINT, BOOLEAN, FLOAT, NUMERIC, DATE, TIMESTAMPTZ and so many more! ● Common: UUID, JSON, JSONB (9.4), tsearch types (full text search) ● Third party: PostGIS – We guess half of all Postgres users use PostGIS

Built in functions ● http://www.postgresql.org/docs/9.4/static/functions.ht ml ● Tons of work already done for you for doing useful and common things – Network address manipulation: http://www.postgresql.org/docs/9.4/static/functions -net.html – Date/time functions and operators: http://www.postgresql.org/docs/9.4/static/functions -datetime.html ● Excellent support for range types means lots of awesome, easy to write queries for you.

protips ● JOIN using columns of the same type ● Never SELECT * ● Indexes can be made from functions ● Test with ROLLBACK ● EXPLAIN and EXPLAIN ANALYZE ● ANALYZE is your friend ● pg_stat_user_tables/indexes ● UNLOGGED tables

JOIN using columns of the same type ● Example of something that seems ok, but turns out bad at 200m rows: SELECT things, stuff FROM foo JOIN bar ON foo.uuid = bar.uuid::text; ● Use the type system. It will help you.

Never SELECT * ● Seems ok: SELECT * FROM simple_table; ● Hmmm: SELECT * FROM simple_table JOIN splendid_table ON simple_table.id = splendid_table.id; ● Errrgghhhhh... SELECT * FROM simple_table s1 JOIN splendid_table s2 ON s1.id = s2.id JOIN superb_table s3 ON s1.id = s3.id JOIN whatevs_table s4 ON s1.thing = s4.thing;

Indexes can be made with functions ● Useful for matching common query predicates CREATE INDEX new_index ON raw_crashes(get_text('name', json_data)); ● Good for glue between feature updates or stepping stones for schema refactoring

Test with ROLLBACK ● Table and data changes are transaction safe in Postgres BEGIN; DROP TABLE important_things; -- oops ROLLBACK;

EXPLAIN and EXPLAIN ANALYZE ● EXPLAIN is how Postgres can tell you how it decided to execute a query. ● Easy help with the output: http://explain.depesz.com/ ● EXPLAIN ANALYZE actually runs the query (caution!) and then includes real durations

ANALYZE is your friend ● ANALYZE is how statistics about your tables are created – Creates a histogram of the contents of each column and common values (if relevant) – Used by the planner to choose efficient plans for queries ● ANALYZE after bulk data loads! – Autoanalyze runs periodically on its own, but right after huge data imports stats can be out of date ● Increase default_statistics_target if your EXPLAIN plans are wacky/indeterminate (default is 100, and fine for many situations)

pg_stat_user_tables/indexes ● Tons of info about tables can be accessed with SELECT ● Estimated row count, index scans, sequential scans, dead/live tuples and autovacuum/autoanalyze runs ● Common use: find out indexes aren't being used or tables have a ton of expensive seq_scans

Unlogged tables ● Caution: MongoDB mode ● You can create UNLOGGED tables CREATE UNLOGGED TABLE foo (bar int); ● Fast ● Not replicated, not saved if database crashes or shutdown uncleanly ● Can be useful for test suites (do your own benchmark!) ● Lots of other uses if you're working with ephemeral data

Great resources ● Postgres Guide http://www.postgresguide.com/ ● The main docs: http://www.postgresql.org/docs/9.4/static/ ● Planet Postgres: http://planet.postgresql.org/ ● Mailing lists: [email protected] , [email protected], [email protected], [email protected] ● IRC: Freenode #postgresql

Postgres Postgres: An introduction Selena Deckelmann Mozilla @selenamarie [email protected]