Well, after lots of experimentation, we have found that, yes, if you have a fat table with not enough rows to fill much of a block at the slice level using DISTSTYLE KEY, it is much more efficient, space-wise, to use DISTSTYLE ALL, even though this replicates more data.
For example, our fat tables have, say, 600000 rows and 1000 columns. Then, on a 5-node 32-slice setup, they were using up 1Mb per column per node per slice, or 160 Gb, and we were only filling less than 1% of that space.
DISTSTYLE ALL appears to not use all the slices.
So using DISTSTYLE ALL, we multiplied the data by 5, but this still takes up less than 1/20th of the space that the DISTSTYLE KEY tables did.
It slows down our queries a little (due to the need for IPC between the slices, and because the DISTSTYLE KEY means we have only the data on the node that matches our other data) but, since these tables are loaded once and never change after, we don't suffer from the other problems of DISTSTYLE ALL.
You need the USAGE
privilege (at least) for the schema as well:
GRANT USAGE ON SCHEMA something TO GROUP data_viewers;
Related Postgres example:
Remember you only granted permissions to already existing tables. Does not apply to tables created later. To cover those, too:
ALTER DEFAULT PRIVILEGES FOR USER role_that_creates_tables
IN SCHEMA public
GRANT SELECT ON TABLES TO GROUP data_viewers;
Amazon Redshift implemented DEFAULT PRIVILEGES
as well.
Here is a complete cookbook for Postgres:
Be aware of some differences between mainline Postgres and Redshift! Redshift sticks to separate users and groups, while Postgres replaced that with the universal concept of roles:
And I am not sure how Redshift handles sequences ...
Best Answer
PG_TABLE_DEF in Redshift only returns information about tables that are visible to the user, in other words, it will only show you the tables which are in the schema(s) which are defined in variable search_path. If PG_TABLE_DEF does not return the expected results, verify that the search_path parameter is set correctly to include the relevant schema(s).
Try this -
Then run your query -