russ garrett talking about postgres

russ-garrett.org

** russ garrett on postgres
   - I like it as a default database when I don't know what database I want to use
*** schemas
    - I like schemas and I hate schemaless databases
    - it's useful to be able to enforce your schema before the data
      gets written to disk
      - rather than in your ORM!
      - CHECK constraints: ensure balance >= 0
*** schema changes often aren't great
    - this is why people don't like schemas
    - in mysql, adding a column to a table locks the table while it
      adds the column to every row
*** postgres makes this better
    - zero-downtime schema changes
    - all schema changes are transactional
    - some schema changes are O(1)
      - adding a nullable column without a default
      - dropping column
      - some trivial column type changes (varchar to text, small int
        to big int)
    - CREATE INDEX CONCURRENTLY
      - not strictly concurrent
    - some dancing to add a non-nullable column
      - add nullable nondefaulted column
      - set default on column
      - update all rows (takes a while, but doesn't block writes)
      - remove nullable from column
*** schemaless
    - can use hstore, xml, or json for column types
      - hstore since time immemorial
      - xml since 8.3
      - json since 9.3
*** querying json
    - SELECT name FROM articles WHERE data->>'type' = 'post';
    - SELECT data->'keywords'->>0 FROM articles;
    - this is quite slow because it has to parse the json in every row
*** indexing json fields
    - CREATE INDEX articles_data ON articles USING GIN(data);
    - or
    - CREATE INDEX articles_data ON articles(data->>'type');
*** code in the database
    - 0:12:07 :: postgres has loads of stored procedure languages
      - 0:12:38 :: pgSQL, tcl, perl, python (included)
      - 0:12:47 :: extensions: js, ruby, scheme, java, php
    - 0:13:34 :: <example javascript stored procedure>
*** backups
    - 0:15:05 :: log replication can be used for automated incremental
                 backups
    - 0:15:53 :: always less than 5 minutes old (though can be
                 configured to be even younger)
*** WAL (write ahead log) archiving using WAL-E
    - 0:17:41 :: automates base backups
    - 0:17:58 :: saves log segments to S3 or other block stores
    - 0:18:13 :: compresses (LZO) and encrypts (GPG)
    - 0:18:25 :: automates segment retrieval
    - 0:19:12 :: having incremental WAL backups allows point-in-time recovery
      - 0:19:36 :: eg to right before someone ran an UPDATE without a WHERE
*** slaves
    - 0:20:34 :: "cold slave" reads log incremental backups
    - 0:21:01 :: separate setting to allow reads from slaves
    - 0:21:12 :: cold slave has same guarantees -- less than 5 minutes old
    - 0:21:22 :: can do streaming replication instead
    - 0:21:30 :: synchronous replication: postgres waits for at least
                 one of the slaves to write to disk before completing
                 a commit (slow unless they're on the same network)
*** sharding and clustering
    - 0:23:38 :: all the good clustering solutions for postgres are
                 commercial
    - 0:24:28 :: if you have have a dataset which is huge, postgres is
                 not the right database
    - 0:24:42 :: if it's merely large, postgres on a beefy server
                 might be easier in the long run than a distributed
                 solution
*** GIS and such
    - 0:25:10 :: geographic querying
    - 0:25:18 :: PostGIS is great, but it's a bit weird
    - 0:25:24 :: =SELECT AddGeometryColumn('tube_stations', 'geom', 4326, 'POINT', 2);=
      - 0:26:02 :: ^ wtf. this is how you add a geometry-aware column
    - 0:27:05 :: =INSERT INTO tube_stations(name, geom) VALUES ('Holborn', ST_GeomFromTest('POINT....))=
    - 0:27:48 :: supports indexes:
    - 0:27:48 :: =CREATE INDEX tube_stations_geom_gist ON tube_stations USING GIST (geom);=
    - 0:28:51 :: index enables queries of "what's in this box"?
    - 0:29:23 :: PostGIS is also supported by other GIS applications
*** credits
    - 0:30:00 :: twitter: @russss
    - 0:30:08 :: http://russ.garrett.co.uk/talks/postgres-gds
*** q&a
    - 0:32:21 :: what's the failover process for synchronous replication?
      - 0:32:33 :: automating it is hard
      - 0:32:52 :: once something comes out of replication mode, it
                   enters a different timeline
    - 0:33:43 :: where does postgres need resources? where would you
                 spend money on postgres?
      - 0:34:05 :: a lot of OSS contributors are from companies with
                   money to throw at postgres
      - 0:34:30 :: I would say clustering, but it's such a huge thing
                   that I don't know how you'd go about running that
    - 0:38:42 :: user model & authentication model: is there an easy
                 way to explain it? what do you end up doing in a prod
                 system?
      - 0:39:07 :: once you have the knack for it, it's not that hard
                   to comprehend but it is a bit weird
      - 0:39:22 :: why? it's at heart a fairly old school unix
                   database
      - 0:39:39 :: you have to configure the db user and then
                   configure their access
      - 0:40:48 :: the default configuration in most distros is a bit
                   of a faff
    - 0:41:03 :: is vacuuming still painful?
      - 0:41:10 :: don't let txns run for a long time
      - 0:41:17 :: they're especially bad in postgres because it keeps
                   the state of the db as it was at the start of txn
      - 0:42:09 :: other than that, autovacuuming should take care of everything.