Latest Contributions by VinceAngelo

The purpose for filegroups (back when Sybase created them in the mists of time) was to distribute data across devices within one container of the RDBMS. At the time, disk volumes were tiny (50Mb-200Mb on a workstation, 900Mb on a server), so "large" tables (1-2Gb) required multiple devices. Database admins would spend days and weeks organizing the striping for data and indexes across volumes in order to get optimal performance. Given the size of modern devices, where the controller cache is measured in gigabytes, and the block sizes may be 64-256k, and the device itself is composed of clusters of NVRAM with submillisecond seek time, the purpose of filegroup use has languished in significance, to the point that it may not matter.  It doesn't yet make sense for the RDBMS provider to remove the capability, but it certainly won't be emphasized in vendor training. More often than not, customizing filegroups will only encourage fragmentation, hurting overall performance.  Let's say you create ten data files on two independent disks (five each) for ONE large table. If the database is adding rows to block groups in round-robin order, then subsequent pages are likely to be 1/5th of the device storage away from each other, causing increased access latency over two files (one per disk). When you take auto-growth in file allocation and volume optimization into account, you're completely at the mercy of load order and lack of file defragmentation. This is likely why PostgreSQL has abandoned RDBMS control over disk access, and just uses the filesystem as-is, with a "tablespace" only being a mount point for the data to be written, and relying on defragmentation for the rest. However, if you have TEN small tables, accessed somewhat independently, as in the documentation you referenced, then you might get a benefit, if the data files aren't too fragmented. Unfortunately, the best way to tell if that optimization technique works in your context is to try two or more different configurations and decide if the difference is worth the effort of altering the DBTUNE. If you don't want to do the testing yourself, then using the recommended configuration should probably be the default. - V ... View more