Re: perf tuning for 28 cores and 252GB RAM

What is that in bytes?  Do you only have that one table?

 

With 28 cores for only a few users, parallelization will probably be important.  That feature is fairly new to PostgreSQL and rapidly improving from version to version, so you will want to use the last version you can (v11).  And then increase the values for max_worker_processes, max_parallel_maintenance_workers, max_parallel_workers_per_gather, and max_parallel_workers.  With the potential for so many parallel workers running at once, you wouldn't want to go overboard on work_mem, maybe 2GB.  If you don't think all allowed users will be running large queries at the same time (because they are mostly thinking what query to run, or thinking about the results of the last one they ran, rather than actually running queries), then maybe higher than that.

If your entire database can comfortably fit in RAM, I would make shared_buffers large enough to hold the entire database.  If not, I would set the value small (say, 8GB) and let the OS do the heavy lifting of deciding what to keep in cache.  If you go with the first option, you probably want to use pg_prewarm after each restart to get the data into cache as fast as you can, rather than let it get loaded in naturally as you run queries;  Also, you would probably want to set random_page_cost and seq_page_cost quite low, like maybe 0.1 and 0.05.

You haven't described what kind of IO capacity and setup you have, knowing that could suggest other changes to make.  Also, seeing the results of `explain (analyze, buffers)`, especially with track_io_timing turned on, for some actual queries could provide good insight for what else might need changing.

Cheers,

Jeff