2

We have a database that contains around 235 GB of data (calculcated using pg_total_relation_size for each table) on a server with around 77GB of memory. The server is used for nothing else (aside from SSH, backup and similar administrative stuff). We are running postgres 9.2.

I extracted one table of 13GB and tested it on a seperate server with 4GB of memory to benchmark some queries, indexes, etc. outside of our production environment. I had some questions for this which can be seen here: https://stackoverflow.com/questions/26468982/avoiding-external-disk-sort-for-aggregate-query.

On the seperate server, the queries use index-only scans and run relatively fast (compared to pre-index creation with sequence scans). The same queries against the same table on the production machine does a sequence scan almost no matter what settings I set up (short of setting seq_page_cost lower than random_page_cost, which is a Bad Idea(tm).)

Our workload is to extract analytical data - we have around 10 columns that are raw numbers that we do a lot of SUM() aggregate queries on, usually grouped by an item ID. The data is updated constantly, mostly with UPDATE queries to existing rows to update per-day data is it comes in during the day (so a row for a specific day may be updated ~20 times in one day). The 13GB table is for one of our biggest customers and have around 6 million rows.

The customized settings from the production config looks like this:

shared_buffers = 20000MB
effective_cache_size = 56000MB
work_mem = 512MB
maintenance_work_mem = 2048MB
checkpoint_segments = 64
temp_buffers = 16MB
max_stack_depth = 4MB

wal_level = archive
archive_mode = on
archive_timeout = 6h
checkpoint_timeout = 1h
wal_writer_delay = 100
wal_keep_segments = 32

I am looking for some insights for how to create indexes (and alter queries), as obviously in the larger setup the query planner acts differently based on the amount of data.

  • Is it better with small indexes that only cover the small parts of the query to help with lookups or full covering indexes (for index-only scans)?
  • Does the above setup match my server specifications and workload?
  • Any good tips to speed up aggregate queries (mainly SUM) for all rows and grouped by item ID (I am aware we may need to look into column-store databases)?

Also, does any of the above stand out as being "wrong" or should I modify it? We are running it off locally attached disks, but backup is being sent off to NFS disks (SAN).

2

it is important to know, which queries you are running. otherwise it is close to impossible to give you any real advise.

however, there are two things you should know:

but as i said: i got to see your queries to really help.

  • Does fillfactor matter for INTEGER / BIGINT columns? We have 1-2 text columns that may get re-filled (where it would matter), but otherwise it seems that it would always take up the same space (I assume it reserves enough bytes for a complete 32/64-bit integer). – Christian P. Oct 27 '14 at 12:40
  • in general the rule is: keep identical data types next to each other. for integer it is fine but as is said: same types -> close together. – Hans-Jürgen Schönig Oct 27 '14 at 12:41
1

Since the data is updated constantly, you will have a hard time getting the benefits from index-only scans. They are mostly effective on read-only or read-mostly data.

If that is the case, adding columns to the index will likely be counterproductive. If any of the constant stream of updates were into previously-unindexed columns, those could have benefited from HOT updates, but now they can't.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.