4 ms·

>> I am building an application that has 2 million rows of data. It will also be helpful to know what kind of hardware you are using for the database. How much

>> I am building an application that has 2 million rows of data. It will also be helpful to know what kind of hardware you are using for the database. How much RAM the machine has? Does the whole dataset fit in the RAM? If not, at least indexes should fit in the RAM. Disk type: HDD/SSD? Are you using indexes? Did you check "explain analyze"[1] to check your query plans to ensure that the indexes are being used or not? There are many things that affect database performance. >> I want to build something that involves billions of rows of data. I want to know how to speed it up. The key to this is "Shared nothing architecture" along with a Sharded Database. I have tried to explain this architecture here [2]. To understand Database sharding, I would recommend this Digital Ocean Article [3]. To learn more about highly scalable architectures I would suggest reading the real world architectures section [4] of the High Scalability Blog. [1] https://thoughtbot.com/blog/reading-an-explain-analyze-query-plan https://thoughtbot.com/blog/reading-an-explain-analyze-query... [2] https://mobisoftinfotech.com/resources/mguide/shared-nothing-architecture-the-key-to-scale-your-app-to-millions-of-users/ https://mobisoftinfotech.com/resources/mguide/shared-nothing... [3] https://www.digitalocean.com/community/tutorials/understanding-database-sharding https://www.digitalocean.com/community/tutorials/understandi... [4] http://highscalability.com/blog/category/example http://highscalability.com/blog/category/example

stonecharioteer 6y ago
Interesting. You're asking me questions I have not thought. We have an internal VM provider that we use, wrapped up in a way that does not give me a choice in the hardware. I can select RAM and how much storage I want, but nothing beyond that. This machine has 16GB RAM. The disc is not an SSD for certain. Thank you for the resources. I will go through them.
protonimitate 6y ago
> Did you check "explain analyze"[1] to check your query plans to ensure that the indexes are being used or not? Word of caution `explain analyze` actually executes the query and then returns the execution plan. Just `explain` will give you the estimated plan without executing (although usually less accurate than the analyze counterpart). Learned that one the hard way.

stonecharioteer 6y ago

Interesting. You're asking me questions I have not thought. We have an internal VM provider that we use, wrapped up in a way that does not give me a choice in the hardware. I can select RAM and how much storage I want, but nothing beyond that. This machine has 16GB RAM. The disc is not an SSD for certain. Thank you for the resources. I will go through them.

protonimitate 6y ago

> Did you check "explain analyze"[1] to check your query plans to ensure that the indexes are being used or not? Word of caution `explain analyze` actually executes the query and then returns the execution plan. Just `explain` will give you the estimated plan without executing (although usually less accurate than the analyze counterpart). Learned that one the hard way.