neat postgres topics.md

• Constraints - super important for ensuring data integrity (bad data can't get into the DB)

  • UNIQUE indexes to ensure a record is unique on one or more column values
  • CHECK constraints allow some basic logic you can use (https://www.postgresql.org/docs/current/static/ddl-constraints.html)
  • FOREIGN KEY (FK)'s - very important for ensuring referential integrity. Lets the database knows about relations between tables, and what to do if a record is deleted or updated (how related tables should change) - e.g. should related records be deleted (ON DELETE CASCADE)
  • Anything more complicated than the above, you'll need to write a TRIGGER (https://www.postgresql.org/docs/current/static/sql-createtrigger.html)
    • Learning/using PL/pgSQL will be very important here (https://www.postgresql.org/docs/9.6/static/plpgsql.html). Although you can write triggers in other languages w/ extensions, such as Python or JavaScript, but usually you only do that if you need a specific library or something.

• Indexes - import for reads to be performant (while being cognizant that you'll be slowing down writes and using disk space) (https://www.postgresql.org/docs/current/static/indexes.html)

  • Partial indexes - only index records matching certain criteria (https://www.postgresql.org/docs/current/static/indexes-partial.html)
    • This can be super handy e.g. if you don't want to index on a column with NULL values, or say if you want to ensure email addresses are unique using a UNIQUE index, but you don't want to apply that indexes to deleted users
  • Functional indexes - create an index that uses a function (https://www.postgresql.org/docs/current/static/indexes-expressional.html)
  • GIN and GiST indexes, when to use them
  • Think about separate indexes vs. multicolumn indexes (Postgres can be smart about it - https://www.postgresql.org/docs/current/static/indexes-bitmap-scans.html)
  • CONCURRENTLY option to create index w/o blocking writes
  • EXPLAIN ANALYZE to debug slow query issues (helpful site to paste results into: https://explain.depesz.com/)

• Data types

  • JSONB - this is the new one that lets you index JSON data efficiently
    • Know how to query it (https://www.postgresql.org/docs/current/static/functions-json.html)
  • Range types - kinda like Ruby's interval notation. I'm using this right now w/ an exclusion constraint (https://www.postgresql.org/docs/current/static/rangetypes.html#RANGETYPES-CONSTRAINT) to ensure user subscriptions can't overlap between months (i.e. user can't get billed twice in a month)
  • ARRAY, HSTORE
  • Date/time types and functions https://www.postgresql.org/docs/current/static/functions-datetime.html

• Transactions / locking

  • Transaction isolation levels (https://www.postgresql.org/docs/current/static/transaction-iso.html)
  • Think about differente types of locks you can use (which are affected by tx isolation level)
  • Transactions don't magically make all writes safe! (https://blog.2ndquadrant.com/postgresql-anti-patterns-read-modify-write-cycles/)
  • Advisory locks (locks that are application-specific, not tied to an actual table/record)
  • now() in a txn will be an identical value throughout the entire txn. You may want clock_timestamp() instead (or maybe not).

• Performance / maintenance

  • Inheritance / partitioning tables (https://www.postgresql.org/docs/current/static/ddl-partitioning.html)
  • Table and index bloat (good explainer: https://www.compose.com/articles/postgresql-bloat-origins-monitoring-and-managing/)
  • Monitoring perf w/ pg_stat_statements / pghero (https://github.com/ankane/pghero)
  • PGTune - helps you calculate proper postgresql.conf values (http://pgtune.leopard.in.ua/)
  • PgBouncer to keep connection count low (will have to disable prepared statements in Rails)
  • Know about the WAL file and how postgres uses it internally, how to make and restore backups (check out WAL-E which can store to S3 https://github.com/wal-e/wal-e)

• Miscellaneous

  • Full text search capability (http://rachbelaid.com/postgres-full-text-search-is-good-enough/)
  • Window functions (https://www.postgresql.org/docs/current/static/functions-window.html)
  • CTEs (https://www.postgresql.org/docs/current/static/queries-with.html)
  • generate_series (http://letstalkdata.com/2013/08/postgresql-generate_series-tricks/)
  • UUIDs (uuid-ossp extension)
    • There's a lot of hot discussion about using UUIDs as primary keys
  • Useful extensions like PostGIS, postgresql-hll
  • Foreign data wrappers (FDWs) to communicate w/ other databases
  • How NULL comparisons (don't) work
    • Use IS (NOT) NULL, IS (NOT) DISTINCT FROM, COALESCE
  • psql - get comfortable w/ the CLI
    • pgAdmin is a good GUI alternative/supplement
  • template0 / template1 databases (https://www.postgresql.org/docs/current/static/manage-ag-templatedbs.html)