Greenplum Filespaces and Tablespaces

Greenplum is a fast, flexible, software-only analytics data processing engine that has the tools and features needed to make extensive use of any number of hardware or virtual environments that can be used for cluster deployment. One of those features discussed here is the use of file spaces to match data load and query activity with the underlying I/O volumes to support it. Once a physical file space is created across the cluster, it is mapped to a logical tablespace, which is then used during the table and index creation process.


In a traditional Greenplum cluster, segment servers are configured with a number of disk drives in two RAID groups, mounted as /data1 and /data2, where the primary and mirror segment directories reside. All objects such as tables and indexes are stored in the segment directories. In addition, all temporary files created during query operations are written to these same two file systems.



As technology continues to advance, there have been considerable improvements and innovations made to servers that allow for much more flexible and powerful Greenplum configurations.

Take for example the Dell PowerEdge R940, which can manage terabytes of RAM, has four CPU sockets to provision a very large number of Intel CPU cores, can be configured with a combination of traditional hard disk drives and newer solid state disks, in addition to being configured with up to twelve non-volatile memory express (or, NVME) drives.

The drive expansion and overall multi-tiered disk configuration can go even further by adding a Dell PowerVault MD3460 storage unit, capable of housing up to 60 drives of varying types and sizes. In this case, it is possible to configure Greenplum to use different file system groups for different purposes.

For example, if your ETL process is very change-data-capture heavy, requiring a high rate of IOPS, filespaces and tablespaces can be used to segregate those related table data objects to a fast, SSD-based ETL disk staging area. Standard 1.8 TB, 10K RPM drives in a RAID-5 or RAID-10 configuration can be mounted as more traditional data directories to house the bulk of the primary and mirror segment information.

Larger capacity but lower performing drives could be used to create a data storage area for historical data that is queried infrequently, but still needs to be online and available to the users for information gathering purposes. Finally, temporary files can be re-directed to alternate storage areas for fast data access pipelining and processing.



So what about Open Source Greenplum in the Cloud? Well, as it turns out, public cloud providers such as Amazon offer a number of tiered storage options that can be configured on a virtual machine. Using file spaces and tablespaces, it would be possible, for example, to create table and index groups in Throughput Optimized EBS volumes for traditional data sets, while using Provisioned IOPS SSDs for temporary files and potentially ETL work. For long-term retention of data in Greenplum internal tables, one could consider using Amazon’s Cold Storage HDD option.



In Greenplum, physical volumes are allocated as a filespace. This is the physical location on a disk, where object, data and index pages are stored. To define a filespace, there must be an available physical location for the primary segments, the mirror segments, and the master segment.

To create a filespace, use the gpfilespace utility. At creation time, the administrator supplies a name for the filespace and a physical path for the master, primary, and mirror segments to use for object storage. Once created across the cluster, the administrator then creates a logical tablespace that maps to the previously created filespace. From there, the tablespace name can be used when creating objects such as tables and indexes.


Tablespace and Filespace Example


To demonstrate the use of this feature, a three node cluster was deployed in Amazon EC2 using Open Source Greenplum 5.4.0 running on Ubuntu Server 16.04 LTS . The Instances were configured with both Thoroughput Optimized and Cold hard disk drive EBS volumes.


The thoroughput optimized EBS volumes are used for the standard /data1 and /data2 file systems. The cold hard disk EBS volume is used for the /historical file system, where a user defined filespace is created. Any object that supports the tablespace clause could then be used to locate the data in the /historical disk volume.


Once a disk volume is allocated across all nodes in the Greenplum Cluster, from the Master node as user gpadmin, run the gpfilespace utility with the -o . option to have a default configuration file generated. Provide a name for the filespace, and for the primary and mirror segments a base path to locate the related segment directories. In addition, a path for the master segment is provided so the Master process can keep track of what objects across the cluster live in the designated filespace/tablespace.

With all of the required parameters supplied to the utility, it will generate a configuration file (gpfilespace_config_yyyymmdd_xxxx) to be used for the filespace creation process.



Re-run gpfilespace with the –gpconfig option, along with the configuration file that was generated in the prior step. Greenplum will attempt to create a new file space on the provided disk volumes across the cluster.


Once the file space has been created, it is necessary to map the physical location to a logical name with the CREATE TABLESPACE command. This creates a globally addressable name that can then be used for any object that supports the TABLESPACE clause in its definition. The pg_tables and pg_indexes system catalog tables provide the tablespace location for each item (either default, or a named tablespace).


Backup and Restore with Tablespaces

With tablespaces and filespaces, there is no change to how a gpcrondump parallel backup is run in Greenplum. The resulting gp_dump files are still created for every primary DB segment in the cluster, with supporting segment/schema files on the Master.



On the Master node, in the backup directory there are schema-create and post-data files that contain the logical schema object definitions. It is in these schema files where named tablespaces are defined. Before a Greenplum backup is restored, ensure that the underlying disk volumes for the filespaces exist, that the filespaces are re-created (if necessary) using the gpfilespace utility, with all volumes accessible across the cluster.


Running a GPExpand On a Cluster with Filespaces


One final area to explore when filespaces and tablespaces are used in the Greenplum setup is when the cluster is expanded to more data nodes. On the newly added servers, ensure that all required disk volumes are created and accessible with the same base directories and permissions that are configured on the existing data nodes. Run the initial gpexpand process to generate the related configuration files.



In addition to the normal expansion configuration file, in the same directory a file with a .fs extension will also be created. This contains the filespace definitions to be used for the added primary and mirror segments on the cluster. When the gpexpand process is run a second time to perform the actual segment expansion across the new hardware, Greenplum will automatically create the necessary filespaces to store the tablespace data as tables are expanded across the new database segments.

See the documentation for more information on Greenplum and the use of tablespaces and filespaces for optimized disk I/O utilization in a cluster.