Evaluating engineering tools? Get the comparison in Google Sheets

Connection

psql -h -p 5439 -U -d
Connect to Redshift cluster using PostgreSQL client

aws redshift get-cluster-credentials --cluster-identifier --db-user
Get temporary credentials for authentication

JDBC Connection String
jdbc:redshift://:5439/

Database Administration

CREATE USER username PASSWORD 'password';
Create a new user

DROP USER username;
Remove a user

GRANT permission ON object TO username;
Grant permissions

REVOKE permission ON object FROM username;
Revoke permissions

CREATE DATABASE dbname;
Create a new database

DROP DATABASE dbname;
Delete a database

Monitoring

SELECT * FROM stv_sessions;
View current sessions

SELECT * FROM stv_inflight;
View currently running queries

SELECT * FROM svv_table_info;
View table metadata and statistics

SELECT * FROM stl_query;
View query history

SELECT * FROM stl_alert_event_log;
View system alerts

SELECT * FROM svl_query_summary;
View query performance summary

Performance Optimization

VACUUM [FULL] [table_name];
Reclaim space and resort rows

ANALYZE [table_name];
Update statistics for query optimizer

EXPLAIN SELECT * FROM table;
Show query execution plan

SELECT * FROM svv_table_info ORDER BY unsorted DESC;
Find tables that need VACUUM

SELECT * FROM stl_alert_event_log WHERE event LIKE '%Disk%';
Check for disk space alerts

Data Loading

COPY table FROM 's3://bucket/path' IAM_ROLE 'arn:aws:iam::account-id:role/role-name';
Load data from S3

COPY table FROM 's3://bucket/path' IAM_ROLE 'arn:aws:iam::account-id:role/role-name' DELIMITER ',' IGNOREHEADER 1;
Load CSV data with header

COPY table FROM 's3://bucket/path' IAM_ROLE 'arn:aws:iam::account-id:role/role-name' JSON 'auto';
Load JSON formatted data

UNLOAD ('SELECT * FROM table') TO 's3://bucket/path' IAM_ROLE 'arn:aws:iam::account-id:role/role-name';
Export data to S3

System Tables/Views

SELECT * FROM pg_table_def;
View table definitions

SELECT * FROM stv_blocklist;
View data block distribution

SELECT * FROM pg_database;
List all databases

SELECT * FROM pg_user;
List all users

SELECT * FROM stv_tbl_perm;
View permanent tables

Maintenance

ALTER TABLE table_name ADD COLUMN column_name datatype;
Add a column to a table

ALTER TABLE table_name DROP COLUMN column_name;
Drop a column from a table

TRUNCATE table_name;
Delete all rows from a table

ALTER TABLE table_name RENAME TO new_table_name;
Rename a table

SELECT PG_GET_LATE_BINDING_VIEW_COLS() COLS FROM pg_get_late_binding_view_cols() COLS;
View late binding view columns