I'm relatively new to setting up PostgreSQL databases in general and AWS.
I'm trying to figure out what are the practical differences between Amazon RDS Instance Types. Some context about my org and use cases:
I started with the Free Tier for now db.t2.micro just to gain some experience, develop, and test out what I need. However, it's hard for me to understand how this compares to other instances. Our team is also very budget conscious right now so I'm trying to scope this out before advocating to upgrade.
Example operations that I'm trying to do
\copy) of a 11 GB file (35,401,551 rows and 40 columns) took about 27 minutes. Would upgrading the instance help with that? If so, I'm not sure what I would want to upgrade to?select count(*) from that table took about 15 minutes (in the first run; in the second run it took about 3 minutes, probably because it's caching statistics? 3 minutes still seems slow to me.). Again, not sure if upgrading the instance would help with that? I should also note that I have no added primary keys or indices. So there might be other ways to optimize performance without upgrading the instance? For example, it might be possible that partitioning would be helpful: https://www.postgresql.org/docs/10/ddl-partitioning.html. The table has a state column that is very often one of the first filters users use as often users only want to see one or a set of particular states.
select ColA, count(*) group by ColA took about 2.5 minutesEDIT Just noting some other useful resources I've found:
Primarily with instance types within RDS the key differences are as follows:
T instance family uses burstable credits, once this is depleted your CPU is capped).As you're using a T2.micro you only have a single CPU available. Once the credits are depleted you will only be capped at 20% maximum CPU too which will limit what you're able to do.
I would also note if you are able to invest more in the AWS platform there is a dedicated data warehouse solution for analytics which is Redshift, this is likely to perform better but will cost more, its also forked from Postgres.
If sticking to Postgres in RDS is what you're looking to do you should also consider Aurora Postgres, it generally provides 2-3 times the performance of regular Postgres on RDS.