CODEWITHSUNDEEP
postgresqlamazon-dynamodbdatabase-performance
Abhishek K Gowda
Abhishek K Gowda

Reputation: 45

Need suggestion on how to handle large table in PostgresSQL

I have a table of size 32Gb and the index size is around 38Gb in Postgres.

I have a column x which is not indexed. The table size is growing at 1GB per week. There are a lot of queries run on column x.

Each query on this table for column x is consuming 17% of my CPU and taking approx. 5~6sec to return the data with a heavy load on the database.

What is the best way to handle this? what is the industry standard?

I indexed the column x, and the size of the index increased by 2GB — Query time reduced to ~100ms.

I'm looking into DynamoDB to replicate the data of the table, but I am not sure if this is the correct way to proceed, hence this question.

I want the data access to be faster, also keeping in mind that this should cause a bottleneck in the feature.

As requested here is the query that runs:

database_backup1=> EXPLAIN ANALYZE SELECT * FROM "table_name" WHERE "table_name"."x" IN ('ID001', 'ID002', 'ID003', 'ID004', 'ID005') LIMIT 1;

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------
 Limit  (cost=0.00..56442.83 rows=100 width=1992) (actual time=0.010..155288.649 rows=7 loops=1)
   ->  Seq Scan on "table_name"  (cost=0.00..691424.62 rows=1225 width=1992) (actual time=0.009..155288.643 rows=7 loops=1)
         Filter: ((x)::text = ANY ('{ID001,ID002,ID003,ID004,ID005}'::text[]))
         Rows Removed by Filter: 9050574
 Planning time: 0.196 ms
 Execution time: 155288.691 ms
(6 rows)

Upvotes: 1

Views: 103

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246523

The execution plan indicates that your index is clearly the way to go.

If you run the query often, it is worth paying the price in storage space and data modification performance such an index incurs.

Of course I cannot say that with authority, but I don't believe that other database systems have a magic bullet that will make everything faster. If your data are suited for a relational model, PostgreSQL will be a good choice.

Upvotes: 1

Related Questions