300 inserts per minute, 6 million rows per month

Question

I have a Django application using Postgresql 9.3 that is about to grow in load to about 300 inserts per minute (peak load) and about 6 million rows per month in one table. Also there should be a lot of queries on that same table, nothing complicated, just a sum grouping by and indexed field.

The table looks like this:

        Column        |           Type           | Modifiers                                
----------------------+--------------------------+-----------
 id                   | integer                  | not null default nextval('seq'::regclass)
 commercial_entity_id | integer                  | not null
 commercial_branch_id | integer                  | 
 when                 | timestamp with time zone | not null
 currency_id          | character varying(3)     | not null
 amount               | numeric(10,2)            | not null
 loyalty_account_id   | integer                  | not null
 code                 | character varying(16)    | not null

Can anyone tell me how much hardware should I provide for this load or what would be a good way to handle this?

Right now it is running on an Amazon AWS S3 m3.large with 2 vCPU and 7.5 GB of RAM. I guess this would not be enough but I don't have any real world experience to know better.

Update:

After some help from Meta I reformulate my question as this:

How you should go about determining what sort of hardware would I need to handle this load? Where can I learn another changes that could be done to the database to improve its performance?

Answer 1

There are basically two ways to answer your question: speculation and simulation.

For the former you still have not provided much useful information, such as:

• What is your current workload and how does your server behave today?
• What are your current data volumes?
• What are you planning to do with your 6 million new rows a month? Keep them indefinitely? Archive them after 3 months?

As for simulation, it would likely take you only a day or two to clone your existing database, creating a test system; download and install Apache JMeter; write a simple test plan generating your 300 inserts/second plus your queries; and run it to see how the system actually behaves under your projected workload. This will let you (or a temporary consultant, as suggested by others) to identify performance bottlenecks and determine whether they can be resolved by throwing additional hardware at them.

Answer 2

1. How you should go about determining what sort of hardware would I need to handle this load?
   • Profile your expected load on your current hardware. If the hardware can't handle the load find a way to reduce the load, or upgrade your hardware.
   • Profile your expected load on virtualized/cloud base hardware, scaling as required.
   • Guess based on past experience and then use one of the above options to fine tune.
2. Where can I learn another changes that could be done to the database to improve its performance?
   • Leverage one of the many resources on the Internet where database optimization is discussed.
   • Take a formal course in your database targeted at learning how to optimize performance.
   • Hire a temporary consultant to give you advice and/or specific training.
   • Hire a senior DBA who has the skills, and can train others.