Created
March 1, 2013 18:11
-
-
Save gthomas/5066558 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Postgres | |
| -18 years old | |
| -Learned a lot of tricks via Oleg and Teodor | |
| -Still has a clean code base (should probably suck after 18 years) | |
| Forecast: | |
| 12 crazy features you might have missed | |
| demo | |
| wrap up | |
| containment operator | |
| @> | |
| dblink | |
| - query other databases from inside your datbase | |
| - create a connection, query away | |
| - not very performant | |
| with (life changing) | |
| - SQL has weird gnarly syntax, but is totally useful | |
| - like Python's with | |
| - gives queries a narrative | |
| - can be applied to subselects to essentially name them | |
| - makes complicated queries easier | |
| - chainable | |
| - recursive | |
| - caveat: optimization boundary (not major practically speaking) | |
| arrays | |
| - show the contents of groups | |
| - pick a random value | |
| - find a row that includes some array of tags (operator for this -- can be indexed) | |
| - unnestble into rows | |
| generate_series(start, end, step) | |
| - returns a table of numbers | |
| - time series | |
| - you can ignore the values (random numbers) | |
| listen/notify | |
| (mindblown.gif) | |
| - async notification between database connections | |
| - listen on a channel | |
| - notify messages are delivered | |
| -- good for work distribution or cache busting | |
| - useful for fan out message | |
| ranges (9.2) | |
| - get records in a time-range (for example) | |
| - union | |
| - exclusion constraint (works with indexes too) | |
| regexes | |
| (mindblown.gif(2)) | |
| -matching | |
| -lazy data generation regex_split_to_table | |
| -search and replace | |
| - better than like | |
| times and dates | |
| - timestamptz (always use, with timezone info) | |
| - you can do arithmetic with year intervals ::interval | |
| - parse human readable dates | |
| - round times to date (date_trunc('week', g)::date | |
| - generate series | |
| UUIDs | |
| - postgres makes them | |
| - use UUID for PKs (uuid_generate_v4) | |
| - requires an extension | |
| window functions | |
| - makes windowing much simpler (built in, don't write this code) | |
| - determine event duration | |
| - find the 6th decile ntile(10) | |
| - track state changes | |
| hstore | |
| - k,v store in column type ( '"key" => "val"' | |
| - basically a dictionary "schema-less sql" | |
| - don't have to change columns for frequently changing data | |
| - search for keys, search for values, calculate stats | |
| - generalized index on hstore | |
| - string values only, no nesting (must create type syntax if you need) | |
| honorable mention | |
| ----------------- | |
| full text search | |
| TOAST -- compression | |
| pgcrypto -- work with encryption in postgres | |
| monitoring functions for reflection --- what's happening (pg_stat_activity et. al. (system stat tables)) | |
| postGIS -- geographical functions for postgres map projections etc.... | |
| JSON and PL/V8 for "schemaless sql" -- pl/v8 is a v8 interpreter in postgres (WHHHHAT?) | |
| DEMO! | |
| noticed tags? huh? | |
| https://twitter.com/pvh | |
| dataclips, you can save a query/result combo for future inspection, export to spreadsheet |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment