Follow dba-ninja.com

Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions

dba-ninja.com Links

Dba_db2_button

Sqlserver_dba_button

PostgreSQL doesn't support clustered indexes

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


Author: Rambler (http://www.dba-ninja.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

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.

Working...

Post a comment on PostgreSQL doesn't support clustered indexes


dba-ninja.com