Choosing the correct DB type

thisum 1 Reputation point

I have to create a DB which has around 130 million records which is around 90GB when loaded. I want to host this in the azure. DB modifications will happen once a day as a batch operation. Searching has to be done more often which should return the results within 2 seconds. Search includes text search (address) and a few other numerical fields.

I'm trying to figure out the most cost effective database but providing above mentioned performance. I've tested Single server PostgreSQL DB but the performance is terrible even after I added

General Purpose Gen-5 4 cores (20GB RAM) and 500GB storage (1500 IOPS).
(I've used indexes for the search parameters already)

Is SQL server a good alternative?

Azure SQL Database
Azure Database for PostgreSQL
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Alberto Morillo 33,176 Reputation points MVP

    I would say please do consider an Azure SQL DTU Model S6 or above. Use Premium tiers (P4 or later) if you want good IO storage performance.

    More suggestions, please consider do the data changes (that occur once a day) in batches not in just one batch. After those data changes, try to defragment indexes and update statistics and you will get the performance you are looking for.

    Check out the dynamic management view related to missing indexes as missing indexes on big tables can make you scale tiers on Azure SQL PaaS or face poor performance.

    Bottom line, I think you are missing to do daily database maintenance and take care of some possible missing indexes and query tuning and you will be fine.

    0 comments No comments

  2. thisum 1 Reputation point

    Thanks for the suggestions @Alberto Morillo

    Does that mean even If add more resources into the Single server PostgreSQL DB (i.e. more vCores - 40GB RAM) wouldn't give the performance I want?