jakob / jakob/Postico

Structure pane enhancements for Redshift

Open
#344 0 comments 4 reactions 0 assignees View on GitHub
Dominant language
Python
Stars
486
Forks
8
PR merge metrics
No merged PRs in 30d

Description

It would be amazing if the Structure pane had a few additional features for Redshift tables:

1. A column showing the column encoding (read-only). This might optionally be available to set when adding a new column.
http://docs.aws.amazon.com/redshift/latest/dg/c_Compression_encodings.html

2. A column indicating whether it is the `DISTKEY` or not. There can only be one of these per table.
http://docs.aws.amazon.com/redshift/latest/dg/t_Distributing_data.html

3. A column (probably the same as the previous column) indicating whether it is part of `SORTKEY` or not.
http://docs.aws.amazon.com/redshift/latest/dg/t_Sorting_data.html

4. Instead of showing indexes (which Redshift doesn’t support), show a table’s:
`DISTSTYLE` (`EVEN`, `KEY`, OR `ALL`; http://docs.aws.amazon.com/redshift/latest/dg/c_choosing_dist_sort.html)
`DISTKEY` (one per table, composed of a single column. Only set if `DISTSTYLE` is `KEY`)
SORTKEY (one per table, composed of any number of columns; ordering matters). The sort key may be either `COMPOUND` or `INTERLEAVED`.

These might optionally be available to set when creating a table. All are read-only once a table has been created.

Column details (`ENCODING`, `DISTKEY`, `SORTKEY`) may be found in `pg_table_def`. The `SORTKEY` column contains the position of a column in the sort key. If 0, it’s not part the sort key; if positive it is part of a compound sort key; and if negative it is part of an interleaved sort key.
http://docs.aws.amazon.com/redshift/latest/dg/r_PG_TABLE_DEF.html

```sql
select *
from pg_table_def
where schemaname = 'foo' and tablename = 'bar’;
```

The table `DISTSTYLE` is found in `pg_class` (0 = `EVEN`, 1 = `KEY`, 8 = `ALL`):
http://docs.aws.amazon.com/redshift/latest/dg/viewing-distribution-styles.html

```sql
select relname, reldiststyle
from pg_class;
```

`CREATE TABLE` details: http://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html

Contributor guide

No contributing guide indexed for this repository

Research direction

Start at the Structure pane entry point and review the supplied pg_table_def and pg_class queries, along with the CREATE TABLE details. Done means displaying Redshift encoding, DISTKEY, SORTKEY, and DISTSTYLE metadata, with any supported creation options and read-only behavior defined.

Written by the indexing model from the issue text.

Assessment

Tech stack
python, sql
Domain
databases, desktop
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
32/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.