3

I've just finished constructing a table of ~835 million rows using Google's ngram dataset, aggregated on the years in which they occurred so that each 2-,3-,4-, and 5-gram is represented by a single row in the table. Each word in each ngram also has a part of speech that I generated using TextBlob and textblob-aptagger, which is also stored in the ngram's row. This table is the only table in the database, and now that all the rows I could ever want are in the table, I don't expect to insert new rows or update existing ones ever again. At most, I may be deleting a undesired ngram from time to time, but even that shouldn't happen or at least should be kept to a minimum. All of this said, I still have yet to generate all of the indices I need, but I have a plan for that and will be doing it shortly.

My question is this: how can I optimize my database (particularly my configuration file) for periodic reads that occur together in short bursts, at the sacrifice of the speed of inserts, updates and deletes, all of which I should not be performing from here on out?

Specs (I realize these are sub-optimal, but I'm currently on a very tight budget): Amazon RDS; db.t2.micro; 1vCPU, 1GB RAM, SSD (currently 100GB--expect to go as high as 200GB once I've created the indices I need, and hopefully not higher); PostgreSQL 9.3.5-R1; the OS is UNIX-based, but some crazy custom Amazon stuffs and cannot be connected to directly, so any changes I can make are mostly limited to Postgres.

I've already done some research and set the following to 1.0 per several sources because of the SSD: random_page_cost and seq_page_cost. Does anyone disagree with this decision? I'm very open to suggestions!

Thanks in advance for any assistance.

Improve this question
4
  • increasing shared_buffers comes to mind. You might also want to go through this: wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
    – user1822
    Feb 6, 2015 at 15:25
  • @a_horse_with_no_name with RDS, it is not that easy to do most of those changes, I'm afraid.
    – dezso
    Feb 6, 2015 at 16:52
  • @a_horse_with_no_name (and dezso): I'm open to switching hosts if I absolutely have to, so thanks for the link. I'm currently building all the indices I'll need, then I'll have some time to put some of those suggestions into practice.
    – James Pizzurro
    Feb 6, 2015 at 18:25
  • 1
    If you're on RDS you have minimal tuning options. The host specs are absurd anyway, your mobile phone is probably faster than that. Anyway, what littke you can do should be mainly guided by explain analyze results.
    – Craig Ringer
    Feb 7, 2015 at 10:39

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.