{"action":"created","comment":{"url":"https://api.github.com/repos/open-guides/og-aws/pulls/comments/82508065","pull_request_review_id":3413100,"id":82508065,"diff_hunk":"@@ -1140,26 +1140,33 @@ Redshift\n ### Redshift Basics\n \n -\t📒 [Homepage](https://aws.amazon.com/redshift/) ∙ [Developer guide](http://docs.aws.amazon.com/redshift/latest/dg/) ∙ [FAQ](https://aws.amazon.com/redshift/faqs/) ∙ [Pricing](https://aws.amazon.com/redshift/pricing/)\n--\t**Redshift** is AWS’ [data warehouse](https://en.wikipedia.org/wiki/Data_warehouse) solution, which is highly parallel, share-nothing, and columnar. It is very widely used. It [was built](https://en.wikipedia.org/wiki/Amazon_Redshift) with [ParAccel](https://en.wikipedia.org/wiki/ParAccel) and [Postgres](https://en.wikipedia.org/wiki/PostgreSQL).\n+-\t**Redshift** is AWS’ managed [data warehouse](https://en.wikipedia.org/wiki/Data_warehouse) solution, which is massively parallel, scalable, and columnar. It is very widely used. It [was built](https://en.wikipedia.org/wiki/Amazon_Redshift) using [ParAccel](https://en.wikipedia.org/wiki/ParAccel) technology and exposes [Postgres](https://en.wikipedia.org/wiki/PostgreSQL)-compatible interfaces.\n \n ### Redshift Alternatives and Lock-in\n \n -\t⛓🚪Whatever data warehouse you select, your business will likely be locked in for a long time. Also (and not coincidentally) the data warehouse market is highly fragmented. Selecting a data warehouse is a choice to be made carefully, with research and awareness of [the market landscape](https://www.datanami.com/2016/03/14/data-warehouse-market-ripe-disruption-gartner-says/) and what [business intelligence](https://en.wikipedia.org/wiki/Business_intelligence) tools you’ll be using.\n \n ### Redshift Tips\n \n--\t🔸Although Redshift is based on Postgres, its SQL dialect and performance profile are different.\n+-\tAlthough Redshift is mostly Postgres-compatible, its SQL dialect and performance profile are different.\n -\tRedshift supports only [11 primitive data types](https://docs.aws.amazon.com/redshift/latest/dg/c_Supported_data_types.html). ([List of unsupported Postgres types](https://docs.aws.amazon.com/redshift/latest/dg/c_unsupported-postgresql-datatypes.html)\\)\n -\tIt has a leader node and computation nodes (the leader node distributes queries to the computation ones). Note that some functions [can be executed only on the lead node.](https://docs.aws.amazon.com/redshift/latest/dg/c_SQL_functions_leader_node_only.html)\n--\t🔸Redshift does not support many Postgres functions, most notable date/time related or aggregates. See the [full list here](https://docs.aws.amazon.com/redshift/latest/dg/c_unsupported-postgresql-functions.html).\n -\tMajor 3rd-party BI tools support Redshift integration (see [Quora](https://www.quora.com/Which-BI-visualisation-solution-goes-best-with-Redshift)).\n+-\t[Top 10 Performance Tuning Techniques for Amazon Redshift](https://blogs.aws.amazon.com/bigdata/post/Tx31034QG0G3ED1/Top-10-Performance-Tuning-Techniques-for-Amazon-Redshift) provides an excellent list of performance tuning techniques.\n+-\t[Amazon Redshift Utils](https://github.com/awslabs/amazon-redshift-utils) contains useful utilities, scripts and views to simplify Redshift ops.\n \n ### Redshift Gotchas and Limitations\n \n--\t🔸While Redshift can handle heavy queries well, it does not scale horizontally, i.e. does not handle multiple queries in parallel. Therefore, if you expect a high parallel load, consider replicating or (if possible) sharding your data across multiple clusters.\n--\tRedshift data commit transactions are very expensive and serialized at the cluster level. Therefore, consider grouping multiple COPY commands into a single transaction whenever possible.\n--\t🔸Redshift does not support multi-AZ deployments. Building multi-AZ clusters is not trivial. [Here ](https://blogs.aws.amazon.com/bigdata/post/Tx13ZDHZANSX9UX/Building-Multi-AZ-or-Multi-Region-Amazon-Redshift-Clusters)is an example using Kinesis.\n--\t🔸Redshift has reserved keywords which are not present in Postgres (see full list [here](https://docs.aws.amazon.com/redshift/latest/dg/r_pg_keywords.html)). Watch out for DELTA ([Delta Encodings](https://docs.aws.amazon.com/redshift/latest/dg/c_Delta_encoding.html)).\n+-\tWhile Redshift can handle heavy queries well, its does not scale horizontally, i.e. does not handle multiple queries in parallel. Therefore, if you expect a high parallel load, consider replicating or (if possible) sharding your data across multiple clusters.\n+-\tLeader node is the single point of failure.\n+-\tRedshift data commit transactions are very expensive and serialized at the cluster level. Therefore, consider grouping multiple mutation commands (COPY/INSERT/UPDATE) commands into a single transaction whenever possible.\n+-\tRedshift does not support multi-AZ deployments. Building multi-AZ clusters is not trivial. [Here](https://blogs.aws.amazon.com/bigdata/post/Tx13ZDHZANSX9UX/Building-Multi-AZ-or-Multi-Region-Amazon-Redshift-Clusters) is an example using Kinesis.\n+-\tBeware of storing multiple small tables in Redshift. The way Redshift tables are laid out on disk makes it impractical. The minimum space required to store a table (in MB) is nodes * slices/node * columns. For example, on a 16 node cluster an empty table with 20 columns will occupy 640MB on disk.\n+-\tAlthough most Redshift queries parallel well at the compute node level, certain stages are executed on the leader node, which becomes the bottleneck.\n+-\tQuery performance degrades significatly during data ingestion. [WLM (Workload Management)](http://docs.aws.amazon.com/redshift/latest/dg/c_workload_mngmt_classification.html) tweaks help to some extent. However, if you need consistent read performance, consider having replica clusters (at the extra cost) and swap them during update.\n+-\tNever resize a live cluster. The resize operation takes 10-15 hours depending on the dataset size. In rare cases, the operation may also get stuck and you'll end up having the non-fuctional cluster. The safer approach is to create a new cluster from a snapshot, resize the new cluster and shutdown the old one.\n+-\tRedshift has reserved keywords which are not present in Postgres (see full list [here](https://docs.aws.amazon.com/redshift/latest/dg/r_pg_keywords.html)). Watch out for DELTA ([Delta Encodings](https://docs.aws.amazon.com/redshift/latest/dg/c_Delta_encoding.html)).\n+-\tRedshift does not support many Postgres functions, most notable date/time related or aggregates. See the [full list here](https://docs.aws.amazon.com/redshift/latest/dg/c_unsupported-postgresql-functions.html).","path":"README.md","position":37,"original_position":37,"commit_id":"107598a6fd7f7aed6a9537c3723f0b47f1bb9403","original_commit_id":"107598a6fd7f7aed6a9537c3723f0b47f1bb9403","user":{"login":"ThanosBaskous","id":1161523,"avatar_url":"https://avatars.githubusercontent.com/u/1161523?v=3","gravatar_id":"","url":"https://api.github.com/users/ThanosBaskous","html_url":"https://github.com/ThanosBaskous","followers_url":"https://api.github.com/users/ThanosBaskous/followers","following_url":"https://api.github.com/users/ThanosBaskous/following{/other_user}","gists_url":"https://api.github.com/users/ThanosBaskous/gists{/gist_id}","starred_url":"https://api.github.com/users/ThanosBaskous/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ThanosBaskous/subscriptions","organizations_url":"https://api.github.com/users/ThanosBaskous/orgs","repos_url":"https://api.github.com/users/ThanosBaskous/repos","events_url":"https://api.github.com/users/ThanosBaskous/events{/privacy}","received_events_url":"https://api.github.com/users/ThanosBaskous/received_events","type":"User","site_admin":false},"body":"Typo: \"notable\" -> \"notably\"\r\nSuggestion: \"most notably several date/time-related and aggregation functions.\"","created_at":"2016-10-08T19:07:54Z","updated_at":"2016-10-08T19:08:01Z","html_url":"https://github.com/open-guides/og-aws/pull/83#discussion_r82508065","pull_request_url":"https://api.github.com/repos/open-guides/og-aws/pulls/83","_links":{"self":{"href":"https://api.github.com/repos/open-guides/og-aws/pulls/comments/82508065"},"html":{"href":"https://github.com/open-guides/og-aws/pull/83#discussion_r82508065"},"pull_request":{"href":"https://api.github.com/repos/open-guides/og-aws/pulls/83"}}},"pull_request":{"url":"https://api.github.com/repos/open-guides/og-aws/pulls/83","id":88555132,"html_url":"https://github.com/open-guides/og-aws/pull/83","diff_url":"https://github.com/open-guides/og-aws/pull/83.diff","patch_url":"https://github.com/open-guides/og-aws/pull/83.patch","issue_url":"https://api.github.com/repos/open-guides/og-aws/issues/83","number":83,"state":"open","locked":false,"title":"Updated the Redshift section.","user":{"login":"max-zanko","id":10171129,"avatar_url":"https://avatars.githubusercontent.com/u/10171129?v=3","gravatar_id":"","url":"https://api.github.com/users/max-zanko","html_url":"https://github.com/max-zanko","followers_url":"https://api.github.com/users/max-zanko/followers","following_url":"https://api.github.com/users/max-zanko/following{/other_user}","gists_url":"https://api.github.com/users/max-zanko/gists{/gist_id}","starred_url":"https://api.github.com/users/max-zanko/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/max-zanko/subscriptions","organizations_url":"https://api.github.com/users/max-zanko/orgs","repos_url":"https://api.github.com/users/max-zanko/repos","events_url":"https://api.github.com/users/max-zanko/events{/privacy}","received_events_url":"https://api.github.com/users/max-zanko/received_events","type":"User","site_admin":false},"body":"More tips and gotchas.\r\nSome corrections.","created_at":"2016-10-08T18:46:24Z","updated_at":"2016-10-08T19:08:01Z","closed_at":null,"merged_at":null,"merge_commit_sha":"7e5723bdac76026cb6b87ef8cda8e6bc43da13db","assignee":null,"assignees":[],"milestone":null,"commits_url":"https://api.github.com/repos/open-guides/og-aws/pulls/83/commits","review_comments_url":"https://api.github.com/repos/open-guides/og-aws/pulls/83/comments","review_comment_url":"https://api.github.com/repos/open-guides/og-aws/pulls/comments{/number}","comments_url":"https://api.github.com/repos/open-guides/og-aws/issues/83/comments","statuses_url":"https://api.github.com/repos/open-guides/og-aws/statuses/107598a6fd7f7aed6a9537c3723f0b47f1bb9403","head":{"label":"max-zanko:master","ref":"master","sha":"107598a6fd7f7aed6a9537c3723f0b47f1bb9403","user":{"login":"max-zanko","id":10171129,"avatar_url":"https://avatars.githubusercontent.com/u/10171129?v=3","gravatar_id":"","url":"https://api.github.com/users/max-zanko","html_url":"https://github.com/max-zanko","followers_url":"https://api.github.com/users/max-zanko/followers","following_url":"https://api.github.com/users/max-zanko/following{/other_user}","gists_url":"https://api.github.com/users/max-zanko/gists{/gist_id}","starred_url":"https://api.github.com/users/max-zanko/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/max-zanko/subscriptions","organizations_url":"https://api.github.com/users/max-zanko/orgs","repos_url":"https://api.github.com/users/max-zanko/repos","events_url":"https://api.github.com/users/max-zanko/events{/privacy}","received_events_url":"https://api.github.com/users/max-zanko/received_events","type":"User","site_admin":false},"repo":{"id":70350446,"name":"og-aws","full_name":"max-zanko/og-aws","owner":{"login":"max-zanko","id":10171129,"avatar_url":"https://avatars.githubusercontent.com/u/10171129?v=3","gravatar_id":"","url":"https://api.github.com/users/max-zanko","html_url":"https://github.com/max-zanko","followers_url":"https://api.github.com/users/max-zanko/followers","following_url":"https://api.github.com/users/max-zanko/following{/other_user}","gists_url":"https://api.github.com/users/max-zanko/gists{/gist_id}","starred_url":"https://api.github.com/users/max-zanko/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/max-zanko/subscriptions","organizations_url":"https://api.github.com/users/max-zanko/orgs","repos_url":"https://api.github.com/users/max-zanko/repos","events_url":"https://api.github.com/users/max-zanko/events{/privacy}","received_events_url":"https://api.github.com/users/max-zanko/received_events","type":"User","site_admin":false},"private":false,"html_url":"https://github.com/max-zanko/og-aws","description":"📙 Amazon Web Services — a practical guide","fork":true,"url":"https://api.github.com/repos/max-zanko/og-aws","forks_url":"https://api.github.com/repos/max-zanko/og-aws/forks","keys_url":"https://api.github.com/repos/max-zanko/og-aws/keys{/key_id}","collaborators_url":"https://api.github.com/repos/max-zanko/og-aws/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/max-zanko/og-aws/teams","hooks_url":"https://api.github.com/repos/max-zanko/og-aws/hooks","issue_events_url":"https://api.github.com/repos/max-zanko/og-aws/issues/events{/number}","events_url":"https://api.github.com/repos/max-zanko/og-aws/events","assignees_url":"https://api.github.com/repos/max-zanko/og-aws/assignees{/user}","branches_url":"https://api.github.com/repos/max-zanko/og-aws/branches{/branch}","tags_url":"https://api.github.com/repos/max-zanko/og-aws/tags","blobs_url":"https://api.github.com/repos/max-zanko/og-aws/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/max-zanko/og-aws/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/max-zanko/og-aws/git/refs{/sha}","trees_url":"https://api.github.com/repos/max-zanko/og-aws/git/trees{/sha}","statuses_url":"https://api.github.com/repos/max-zanko/og-aws/statuses/{sha}","languages_url":"https://api.github.com/repos/max-zanko/og-aws/languages","stargazers_url":"https://api.github.com/repos/max-zanko/og-aws/stargazers","contributors_url":"https://api.github.com/repos/max-zanko/og-aws/contributors","subscribers_url":"https://api.github.com/repos/max-zanko/og-aws/subscribers","subscription_url":"https://api.github.com/repos/max-zanko/og-aws/subscription","commits_url":"https://api.github.com/repos/max-zanko/og-aws/commits{/sha}","git_commits_url":"https://api.github.com/repos/max-zanko/og-aws/git/commits{/sha}","comments_url":"https://api.github.com/repos/max-zanko/og-aws/comments{/number}","issue_comment_url":"https://api.github.com/repos/max-zanko/og-aws/issues/comments{/number}","contents_url":"https://api.github.com/repos/max-zanko/og-aws/contents/{+path}","compare_url":"https://api.github.com/repos/max-zanko/og-aws/compare/{base}...{head}","merges_url":"https://api.github.com/repos/max-zanko/og-aws/merges","archive_url":"https://api.github.com/repos/max-zanko/og-aws/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/max-zanko/og-aws/downloads","issues_url":"https://api.github.com/repos/max-zanko/og-aws/issues{/number}","pulls_url":"https://api.github.com/repos/max-zanko/og-aws/pulls{/number}","milestones_url":"https://api.github.com/repos/max-zanko/og-aws/milestones{/number}","notifications_url":"https://api.github.com/repos/max-zanko/og-aws/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/max-zanko/og-aws/labels{/name}","releases_url":"https://api.github.com/repos/max-zanko/og-aws/releases{/id}","deployments_url":"https://api.github.com/repos/max-zanko/og-aws/deployments","created_at":"2016-10-08T18:37:02Z","updated_at":"2016-10-08T18:37:04Z","pushed_at":"2016-10-08T18:43:30Z","git_url":"git://github.com/max-zanko/og-aws.git","ssh_url":"git@github.com:max-zanko/og-aws.git","clone_url":"https://github.com/max-zanko/og-aws.git","svn_url":"https://github.com/max-zanko/og-aws","homepage":"","size":3100,"stargazers_count":0,"watchers_count":0,"language":"Shell","has_issues":false,"has_downloads":true,"has_wiki":false,"has_pages":false,"forks_count":0,"mirror_url":null,"open_issues_count":0,"forks":0,"open_issues":0,"watchers":0,"default_branch":"master"}},"base":{"label":"open-guides:master","ref":"master","sha":"67e4629e44a66dc132c7699db297bca0c2ce5750","user":{"login":"open-guides","id":17152571,"avatar_url":"https://avatars.githubusercontent.com/u/17152571?v=3","gravatar_id":"","url":"https://api.github.com/users/open-guides","html_url":"https://github.com/open-guides","followers_url":"https://api.github.com/users/open-guides/followers","following_url":"https://api.github.com/users/open-guides/following{/other_user}","gists_url":"https://api.github.com/users/open-guides/gists{/gist_id}","starred_url":"https://api.github.com/users/open-guides/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/open-guides/subscriptions","organizations_url":"https://api.github.com/users/open-guides/orgs","repos_url":"https://api.github.com/users/open-guides/repos","events_url":"https://api.github.com/users/open-guides/events{/privacy}","received_events_url":"https://api.github.com/users/open-guides/received_events","type":"Organization","site_admin":false},"repo":{"id":63266213,"name":"og-aws","full_name":"open-guides/og-aws","owner":{"login":"open-guides","id":17152571,"avatar_url":"https://avatars.githubusercontent.com/u/17152571?v=3","gravatar_id":"","url":"https://api.github.com/users/open-guides","html_url":"https://github.com/open-guides","followers_url":"https://api.github.com/users/open-guides/followers","following_url":"https://api.github.com/users/open-guides/following{/other_user}","gists_url":"https://api.github.com/users/open-guides/gists{/gist_id}","starred_url":"https://api.github.com/users/open-guides/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/open-guides/subscriptions","organizations_url":"https://api.github.com/users/open-guides/orgs","repos_url":"https://api.github.com/users/open-guides/repos","events_url":"https://api.github.com/users/open-guides/events{/privacy}","received_events_url":"https://api.github.com/users/open-guides/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/open-guides/og-aws","description":"📙 Amazon Web Services — a practical guide","fork":false,"url":"https://api.github.com/repos/open-guides/og-aws","forks_url":"https://api.github.com/repos/open-guides/og-aws/forks","keys_url":"https://api.github.com/repos/open-guides/og-aws/keys{/key_id}","collaborators_url":"https://api.github.com/repos/open-guides/og-aws/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/open-guides/og-aws/teams","hooks_url":"https://api.github.com/repos/open-guides/og-aws/hooks","issue_events_url":"https://api.github.com/repos/open-guides/og-aws/issues/events{/number}","events_url":"https://api.github.com/repos/open-guides/og-aws/events","assignees_url":"https://api.github.com/repos/open-guides/og-aws/assignees{/user}","branches_url":"https://api.github.com/repos/open-guides/og-aws/branches{/branch}","tags_url":"https://api.github.com/repos/open-guides/og-aws/tags","blobs_url":"https://api.github.com/repos/open-guides/og-aws/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/open-guides/og-aws/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/open-guides/og-aws/git/refs{/sha}","trees_url":"https://api.github.com/repos/open-guides/og-aws/git/trees{/sha}","statuses_url":"https://api.github.com/repos/open-guides/og-aws/statuses/{sha}","languages_url":"https://api.github.com/repos/open-guides/og-aws/languages","stargazers_url":"https://api.github.com/repos/open-guides/og-aws/stargazers","contributors_url":"https://api.github.com/repos/open-guides/og-aws/contributors","subscribers_url":"https://api.github.com/repos/open-guides/og-aws/subscribers","subscription_url":"https://api.github.com/repos/open-guides/og-aws/subscription","commits_url":"https://api.github.com/repos/open-guides/og-aws/commits{/sha}","git_commits_url":"https://api.github.com/repos/open-guides/og-aws/git/commits{/sha}","comments_url":"https://api.github.com/repos/open-guides/og-aws/comments{/number}","issue_comment_url":"https://api.github.com/repos/open-guides/og-aws/issues/comments{/number}","contents_url":"https://api.github.com/repos/open-guides/og-aws/contents/{+path}","compare_url":"https://api.github.com/repos/open-guides/og-aws/compare/{base}...{head}","merges_url":"https://api.github.com/repos/open-guides/og-aws/merges","archive_url":"https://api.github.com/repos/open-guides/og-aws/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/open-guides/og-aws/downloads","issues_url":"https://api.github.com/repos/open-guides/og-aws/issues{/number}","pulls_url":"https://api.github.com/repos/open-guides/og-aws/pulls{/number}","milestones_url":"https://api.github.com/repos/open-guides/og-aws/milestones{/number}","notifications_url":"https://api.github.com/repos/open-guides/og-aws/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/open-guides/og-aws/labels{/name}","releases_url":"https://api.github.com/repos/open-guides/og-aws/releases{/id}","deployments_url":"https://api.github.com/repos/open-guides/og-aws/deployments","created_at":"2016-07-13T17:30:16Z","updated_at":"2016-10-08T18:46:23Z","pushed_at":"2016-10-08T18:50:16Z","git_url":"git://github.com/open-guides/og-aws.git","ssh_url":"git@github.com:open-guides/og-aws.git","clone_url":"https://github.com/open-guides/og-aws.git","svn_url":"https://github.com/open-guides/og-aws","homepage":"","size":3100,"stargazers_count":85,"watchers_count":85,"language":"Shell","has_issues":true,"has_downloads":true,"has_wiki":false,"has_pages":false,"forks_count":9,"mirror_url":null,"open_issues_count":47,"forks":9,"open_issues":47,"watchers":85,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/open-guides/og-aws/pulls/83"},"html":{"href":"https://github.com/open-guides/og-aws/pull/83"},"issue":{"href":"https://api.github.com/repos/open-guides/og-aws/issues/83"},"comments":{"href":"https://api.github.com/repos/open-guides/og-aws/issues/83/comments"},"review_comments":{"href":"https://api.github.com/repos/open-guides/og-aws/pulls/83/comments"},"review_comment":{"href":"https://api.github.com/repos/open-guides/og-aws/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/open-guides/og-aws/pulls/83/commits"},"statuses":{"href":"https://api.github.com/repos/open-guides/og-aws/statuses/107598a6fd7f7aed6a9537c3723f0b47f1bb9403"}}}}
{"actor":{"display_login":"ThanosBaskous"}}