2

I've developed a shop management software using Postgresql and JAVA. I would like to know which of the solutions below is the best one for my specific situation:

  1. Only one database with huge tables (maybe partitioned);
  2. Lots of independent databases distributed into several servers;

Details of my specific situation:

  1. There are 8,000 shops that use the software.
  2. Each shop has its own database that has no relationship with the other shops databases.
  3. There are no customization to the shops: the database structure is always the same for all of them.
  4. Today each shop has the software and database installed locally. But I want to change that, transferring the databases to online servers so they could be accessed from anywhere by the users (through the software).
  5. Each database has 50 tables. The 7 bigger ones have 300,000 records each and have relationship among them. The other tables are small, with less than 5,000 records each.
  6. On average the size o the database folder is about 200 MB in disc.
  7. Each database can have 2 or 3 persons connecting to it during the day. But, on average, it will have only 1 person using the software per shop.

Pros and Cons of each solution:

According to lots of similar cases that I've read, I know that there are pros and cons for each solution.

Solution 1

Only one database with huge tables (maybe partitioned)

Pros:

  • Easy to maintain;
  • Easy to recover from a server crash;
  • Easy to make backups;

Cons:

  • There would be tables with 2.5 billion records (8k shops * 300k records)
  • Slow queries for huge tables, even using partitions;
  • Any problem with the database would affect all shops.
  • Expensive hosting servers. A server with a great configuration is more expensive than several servers with medium configuration.

Doubts:

  • What would be the minimum configuration needed to run this solution? If possible, a configuration plan from Digital Ocean (https://www.digitalocean.com/pricing)
  • I could use Data Warehouse to separate old data, but the bigger tables would still big for recent data, with 250 million records. Would it help make it the right solution? If so what would be the minimum server configuration?
  • Would "list partitioning" based on shop_id help sufficiently? What would be the drawbacks? Is it OK to have 7 tables with 8,000 partitions each?

Solution 2

Lots of independent databases distributed into several servers

Pros:

  • Bigger tables with only 300 thousands records;
  • Very fast queries due the small tables;
  • A problem with a database would affect only one shop;
  • Cheap hosting servers. Several regular servers cost less than a server with a great configuration

Cons:

  • Difficult to maintain;
  • Difficult to make backups;
  • Difficult to recover from a server crash. Lots of databases in the same server could get corrupted in the same time due a server crash. It would be hard to do with it.
  • Need to develop extra code to deal with all databases at the same time. For example: script to backup all databases, script to add a new column at a specific table on all databases, code to validate users and point them to the right server and database.

Doubts:

  • Considering that I will use the $20 servers of Digital Ocean, which have 2 core processor and 2 GB memory, how much databases could I have for each server?
  • My main concern about this solution is the possibility of lots of databases get corrupted at the same time due a server crash. In case of a server crash what would be the percentage of the databases that would be corrupted and, then, need a recovery?

Conclusion

I know the pros and cons of each solution. But I am still not able to decide what is the best solution to my specific case. Also, I have a lot of doubts like what server configuration to choose, how many databases can I have in a single server, how often databases get corrupted after a system crash, how really difficult it is to maintain lots of databases and how big a table can be without becoming slow.

I know that there are a plenty of this subject in the internet. I've read a lot and I'm still not sure which solution to choose. I would not spend so much time writing this if I weren't really in need of help.

Please clarify my doubts and help me choose the best solution.

  • One of the biggest concerns I see with solution 1 is the number of connections. If you have 8000 shops and 2-3 users logged in from each, it means something around 20k connections. This is well beyond the capabilities of a simple PostgreSQL installation. Even using some connection pooling, this would be more than tricky. The size of the machine usually depends on the size of the DB. If the whole DB fits into memory, you are in a very comfortable situation. Could you possibly tell how big the average DB is (in disk size)? – dezso Nov 14 '14 at 19:33
  • Now I've checked the server options you linked. As they offer SSDs for every size, it makes life a bit easier as disk reads are much cheaper than with spinning disks. I'd suggest putting as many of you DBs on the cheapest option (where the relative disk size is the biggest) as you can, and run some benchmarks there. – dezso Nov 14 '14 at 19:38
  • @dezso I've checked the size of the DB in a Windows installation of the software. The folder of the database has about 200 MB in disc. I will insert this information in the question. Thanks. – Vini Nov 14 '14 at 19:52
  • @dezso Actually I think that the most part of the time it will have just one person connected per shop. Eventually, sometimes there will be 2 or 3. But not at the same time for all shops. I will correct the question about this too. Would ti be ok to have something around 8000 connections? – Vini Nov 14 '14 at 19:57
  • See dba.stackexchange.com/questions/82558/… We actually have some DBs with up to 1200 allowed connections but it is far from being optimal. – dezso Nov 14 '14 at 20:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.