Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Postgres: an intro for new developers

Postgres: an intro for new developers

Selena Deckelmann

February 27, 2015

More Decks by Selena Deckelmann

Other Decks in Technology


  1. AMA • Please, ask questions. • Hands raised is useful

    so I look toward the voice. • Can I get a scribe volunteer?
  2. 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
  3. 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
  4. 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
  5. 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
  6. Framework support • Django prefers Postgres :) • Flask support

    is great • Tell me what frameworks I should know about :)
  7. 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
  8. Types: your secret weapon • Help you keep your data

    tidy • Make queries faster! • Makes migrations and testing easier
  9. 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
  10. 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.
  11. 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
  12. 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.
  13. 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;
  14. 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
  15. Test with ROLLBACK • Table and data changes are transaction

    safe in Postgres BEGIN; DROP TABLE important_things; -- oops ROLLBACK;
  16. 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
  17. 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)
  18. 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
  19. 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
  20. 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