10 October,2022 by Rambler
Question: I'm dealing with a SQL Server to PostgreSQL conversion using the AWS Schema Conversion Tool Assessment Report . One of the responses in the Assessment Report is :
Issue 7681: PostgreSQL doesn't support clustered indexes
Recommended action: Revise your code to use non-clustered indexes and try again
What is the correct way forward?
Answer: From the Microsoft website - In SQL Server the definition of a Clustered Index is "A clustered index is an index which defines the physical order in which table records are stored in a database. Since there can be only one way in which records are physically stored in a database table, there can be only one clustered index per table. By default a clustered index is created on a primary key column"
In comparison PostgreSQL doesn't support clustered indexes directly. Similar characteristics can be implemented via the CLUSTER statement. The CLUSTER statement applies a table sorting process based on index already created in a table. By executing the CLUSTER statement , the data stored in the table is sorted physically - based on the index.
In comparison to SQL Server , when an entry is made in a table with a Clustered index the Clustering is applied automatically. When using the PostgreSQL CLUSTER statement it needs to be reapplied when there are new entries in a table
CREATE TABLE idtest_dbo.mytable ( my_id NUMERIC, my_value VARCHAR(200), CONSTRAINT pk_my_id PRIMARY KEY(my_id)); INSERT INTO idtest_dbo.mytable VALUES (1,'Prince'); INSERT INTO idtest_dbo.mytable VALUES (3,'Van Halen'); INSERT INTO idtest_dbo.mytable VALUES (2,'Satriani'); CLUSTER idtest_dbo.mytable USING pk_my_id; SELECT * FROM idtest_dbo.mytable;
Read more on converting SQL Server to PostgreSQL
A simple guide to AWS SCT multiserver assessment
Dive deep into database migration services AWS DMS and AWS SCT
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |