Is it possible to set up indexing?

I’m not sure how to set up indexing in Panoply.

Hi Sally,

You can definitely set indexing in Panoply. However because currently we are using AWS Redshift under the hood we are actually using sort keys anddistribution keys.

In general Panoply will choose both automatically for you but you can change them at any time but you will need to create a new table for that as both sort and dist keys/styles cannot be changed for an existing table. The process will look like:

BEGIN;
CREATE TABLE <new table name>(<list of columns and their attributes>);
INSERT INTO <new table name> (SELECT * FROM <table name>);
END;

At this stage you will have two versions of your table (the original one and the new one with a temporary different name). To simplify the process it is recommended to create the new table with the same order of fields as they are in the original table.

BEGIN;
ALTER TABLE <table name> RENAME TO <temp table name>;
ALTER TABLE <new table name> RENAME TO <table name>;
DROP TABLE <temp table name>;
END;

Once the above syntax finishes you will have only one single table with the newly defined sort/dist keys with all the data from the original table.

Note that the reason that it is split into two different transactions is to maximize the efficiency and minimize the lock time on tables. Once you complete the creation and transfer of data to the new table (the longest part of the entire process) you will be left with only renaming the tables which is super fast.