question

thisum-2011 avatar image
0 Votes"
thisum-2011 asked AlbertoMorillo commented

Choosing the correct DB type

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-databaseazure-database-postgresql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered AlbertoMorillo edited

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thisum-2011 avatar image
0 Votes"
thisum-2011 answered AlbertoMorillo commented

Thanks for the suggestions @AlbertoMorillo

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?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

My experience is with Azure SQL Database (SQL Server flavor of PaaS). What I mean is you have to do database maintenance and query tuning on PaaS databases or you will end up paying the price $$$ of having to scale up service tier to mitigate poor performance.

If your databases are tuned for good performance and you regularly maintain them, you can use lower level tiers and save money.

0 Votes 0 ·