How to efficiently implement vertical partitioning in PostgreSQL for a large table?

Question

I have a large table (table_A) in PostgreSQL with the following characteristics:

51 columns
500 million records
Approximate size: 200GB

I frequently perform CRUD operations, but these operations are limited to only 10 columns. The remaining 40 columns are rarely used and are mostly utilized for reporting purposes.

To improve performance, I am considering vertical partitioning by splitting the table into two parts:

table_A_main: Contains 11 columns (including uniqueid, which serves as the primary key).
table_A_details: Contains the remaining 41 columns (including uniqueid, which is also the primary key).

To query the complete dataset, I would use a JOIN or create a VIEW:

SELECT * 
FROM table_A_main 
JOIN table_A_details 
ON table_A_main.uniqueid = table_A_details.uniqueid;

CREATE OR REPLACE VIEW table_A AS 
SELECT * 
FROM table_A_main 
JOIN table_A_details 
ON table_A_main.uniqueid = table_A_details.uniqueid;

I am not able to use inheritance because I have already implemented partitioning. Are there better alternatives or optimizations for vertically partitioning a large table like this?

Any advice, best practices, or additional considerations would be greatly appreciated!

I think the design really improve UPDATE performance as you only have to read 1/5 of what you reading now, mean less I/O and efficient shared buffers usage. — goodfella
– goodfella, Commented Jan 24 at 7:54

Mike Tefft · Accepted Answer · 2025-01-23 19:36:21Z

Why do you think this will improve performance?

Any existing code or queries that reference table_A will now have to incur a join to a second table. Are you planning to change existing code and queries to use table_A_main? If not, you will be worse off.

I think you would find greater benefit looking elsewhere.

If you do decide to pursue this, then at least write your view as

CREATE OR REPLACE VIEW table_A AS 
SELECT * 
FROM table_A_main 
LEFT JOIN table_A_details 
ON table_A_main.uniqueid = table_A_details.uniqueid;

so that the optimizer may be able to avoid accessing table_A_details if no columns from that table (expressed in the view) are referenced. With the inner join you have, table_A_details can never be skipped because it is necessary to ensure that a matching row actually exists in table_A_details.

Stack Exchange Network

How to efficiently implement vertical partitioning in PostgreSQL for a large table?

1 Answer 1

Hot Network Questions

How to efficiently implement vertical partitioning in PostgreSQL for a large table?

1 Answer 1

Related

Hot Network Questions