Postgres configuration suitable for a single 300 GB dataset (5 billion rows)
work_mem = 25GB



With only 32GB of RAM, this is much too high.  With two people working independently, and some queries potentially having multiple sort/hash nodes apiece, you can easily oversubscribed memory here.  I wouldn't set this this higher than 4 GB.  And even that is probably aggressive if you expect much parallelism, as each parallel worker takes work_mem independently. So unless you are going to keep a window open running "top" or similar and constantly monitor it, probably 1 GB is a better upper limit.


max_worker_processes = 8 
max_parallel_workers = 8
max_parallel_workers_per_gather = 4



I'd maybe double all of these.  Since you have chosen a burstable instance, I'm assuming there are long periods of idleness.  When it is active, you want to throw all the CPUs you have at "max_parallel_workers_per_gather".  There is no point saving half the CPUs for the benefit of another backend who probably doesn't exist at the moment. On the other hand, what AWS calls a vCPU is only about half a CPU, so maybe the current settings are about correct.

I didn't know what to make of your collation of "C.UTF-8", so I ran a test.  A CPU-bound index build under "C.UTF-8" took about the same amount of time as it did under "en_US", which was about two to three times as long as under "C".  (twice as long in version 12dev, three times as long in version 10.)  So I would recreate your database using "C", not "C.UTF-8", unless you have a librarian or a lawyer telling you that you can't do that.

The first where-less 'select count(*)...' after the data is loaded is going to have do much of the same work as VACUUM does, if the table hasn't already been vacuumed.  This will slow it down a lot, and so will not give you an accurate idea of what the steady state will look like.  So you should either make sure to VACUUM first, or discard the timing of the first 'select count(*)'.  Also, "forever" is not very precise, something like "I killed it after 5 hours" would at least give us a lower bound.


  Should I just give up on postgresql and resort to another database?


I don't know enough about them to recommend them by name, but a database designed specifically for analytics should certainly be better at that tailored task then a general purpose database like PostgreSQL.  There are many tradeoffs to be made, and when it really must choose between them PostgreSQL usually chooses in favor of ACID-compliant transaction processing, rather than batch processing. But squeezing the last ounce of performance out of any product is going to come with a steep learning curve. Flitting from product to product without putting in the time to learn each thoroughly will probably not lead to better results.