I am working on a django project using Postgresql. The use case is as follows:

A user needs to upload an excel (containing 2.5-4K rows) 2-3 times daily. There will be 100s of users (somewhere between 1100-1200).

Now, the issue is the app is taking 1.2-1.8 seconds to write the data in the db. During this time, anyone else viewing the webapp is seeing a "server busy" error.

How can I get around this issue, so that: 1. Write speed can improve 2. Other users can use the webapp and maybe in worst case, we can also have concurrent uploading of excel possible?

closed as too broad by Max Vernon, mustaccio, RLF, Marco, Tom V Mar 9 '17 at 10:43

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Writers do not block readers in Postgres, so it's unclear to me why your application would "lock up" during that period. What exactly is your application doing when you get the "server busy"? Does the import use a lot of connections? Can you use the database from a SQL client during the import? Do you get any error messages in the Postgres logfiles or the logfile of your application? If yes, what are those errors? What technology is your application? Is it using a connection pool? If yes which one? If yes how many connections are configured? Does the import use the same connection pool? – a_horse_with_no_name Mar 8 '17 at 19:06
  • @ a_horse_with_no_name, I am studying the log and finding out the errors related to the same, will try to resolve the issue. Thanks a lot mate with your questions. I believe these questions would ideally help me find out the root cause. Thanks again (I can't upvote a comment, can I?) – Sayantan Mar 9 '17 at 2:35

I'm not sure you understand.

  1. PostgreSQL has max_connections. If that number is hit people won't be able to connect to the database. If your server is waiting you may get a "server busy" message or the like. As @a_horse_with_no_name suggests connection pooling may solve this problem.
  2. You can speed up writes by batching them. When you convert from your "excel format" to SQL are you doing one insert, or one insert per row? Consider inserting into an unlogged table if you're inserting one row at a time, and then running a single INSERT INTO SELECT to UPSERT into the master.
  3. You should also check out the non-durability settings for faster writes
  • Thanks @Eavn. This was a great answer. Surely, I need to get my head rolled with te max_connection. It's not insert 1 row at a time, it's like read all rows and check their integrity, then upload at a go. It might take a bit more RAM, but about 4K rows won't matter much there. Will try to take care of the system now – Sayantan Mar 9 '17 at 2:29

Not the answer you're looking for? Browse other questions tagged or ask your own question.