Provide more detailed explanation of database types

[imidata]

In pg_tune there is an option to set the database type: -T or --type : Specifies database type. Valid options are: DW, OLTP, Web, Mixed, Desktop

It would be helpful to provide more detailed explanation of the database types and what a user should consider when choosing one option over the others.

Thank you for all your hard work on pg_tune.

[ssbarnea]

I installed pgtune on debian and it's version 1.0, the dev repository reports 0.9 something and wasn't upgraded in 5 years. Later I found your fork but I don't know if your changes reached the Debian version or if its safe to use it with postgres 9.1.

And, yes I would like to know more about what are these setups about. In my case it's a dedicated postgres server but I do not see this as an option.

[nikkelj]

My guess, based on what I see in the differences these "operation modes" put out: DW = DataWarehouse => Your box is doing mostly warehouse-like db work. Results in giving biggest maintenance_work_mem, checkpoint_segments, default_statistics_target. Average work_mem. OLTP = Online Transaction Processing => Your box is getting hit hard with lots of little small-data transactions. Bigger work_mem, smaller maintenance_work_mem, average check_point_segments.
Web = Generic Web Server => Balanced, similar to OLTP, but fewer checkpoint_segments.
Mixed = Mixed usages => work_mem like DW, checkpoint_segments like Web, maintenance_work_mem like OLTP, Web. Desktop = Standalone usage => minimize footprint. Small default_statistics_target, maintenance_work_mem like OLTP, web, Mixed, default checkpoint_completion_target (undefined), smaller effective cache size, smaller work_mem, fewer checkpoint_segments, smaller shared_buffers.

In all cases, it seems to recommend wal_buffers = max, and roughly follows the shared_buffers 75% of shmax recommendation I've seen elsewhere.

[openfirmware]

I was also looking for a good explanation for the terms and found a good overview in the pgtune documentation included with chef-postgresql, provided by @davidc-donorschoose:

# dw -- Data Warehouse
#   * Typically I/O- or RAM-bound
#   * Large bulk loads of data
#   * Large complex reporting queries
#   * Also called "Decision Support" or "Business Intelligence"
#
# oltp -- Online Transaction Processing
#   * Typically CPU- or I/O-bound
#   * DB slightly larger than RAM to 1TB
#   * 20-40% small data write queries
#   * Some long transactions and complex read queries
#
# web -- Web Application
#   * Typically CPU-bound
#   * DB much smaller than RAM
#   * 90% or more simple queries
#
# mixed -- Mixed DW and OLTP characteristics
#   * A wide mixture of queries
#
# desktop -- Not a dedicated database
#   * A general workstation, perhaps for a developer