Here, we change the table’s compression settings.ĪLTER TABLE foo SET (compresslevel=9, compresstype=zlib) We can achieve this with the ALTER TABLE SET command. +-+-+-+-+-+-+-ĪLTER TABLE foo SET ACCESS METHOD ao_row ĪLTER TABLE foo SET ACCESS METHOD ao_column Ĭolumn | Type | Collation | Nullable | Default | Storage | Stats target | Compression Type | Compression Level | Block Size | Description INSERT INTO foo SELECT generate_series(1, 10000) Ĭolumn | Type | Collation | Nullable | Default | Storage | Stats target | Description Similarly, for an AOCO table, the access method is ao_column. Modifying a heap table to an AO table constitutes a change of it’s access method from heap to ao_row. In this example, we start off with a heap table and modify its storage and storage options. In GPDB7, we now define “table access methods (AM)” which is the term used to tell us whether our table is a heap, AO or AOCO table. As an additional benefit, any dead rows in the original table are left on the side of the road. Under the hood, we grab an AccessExclusiveLock for the duration of the entire operation, rewrite the catalog and then rewrite the entire table. For instance, adding a column to a heap/row-oriented AO table requires a table rewrite. In essence, this is no different from other ALTER TABLE operations that rewrite the table. Multi-step processes like these are not only inconvenient, but leave room for partial states.ĪLTER TABLE will remove all of this complexity and will offer a seamless experience. Furthermore, if we are dealing with partitioned tables, one would need to drop, create and then reattach the partition. Sometimes CASCADE can be too far-reaching, especially if there are complex depenencies and dependencies have to be carefully examined. However that doesn’t really solve the problem of dependent objects – if there are dependent objects such as views (or worse views on top of views), all of those objects will have to be dropped, before the table can be dropped. One could CREATE TABLE LIKE the new table, which would create many of the associated objects, given the user exhaustively enumerates them in the INCLUDING clause. If there were any indexes they would have to be recreated too. Today, if a user wants to change the storage characteristics of a table, they have to dump the data somewhere, drop, re-CREATE the table and then reload the table. One could also determine that the access pattern of a table is chiefly dealing with a subset of columns – in which case the table could be turned into a columnar table.
Similarly, one could crank up the compression settings of older partitions that won’t be seeing frequent updates.
Older and colder partitions will benefit from the AO format whereas newer, hotter and frequently updated partitions could be heap. As an example, partitions carrying time-series data are perfect candidates for this feature. This feature will help users change their table’s storage characteristics dynamically with changes in the access pattern of their tables. We are also supporting the capability to change the storage options (reloptions) of the table, meaning a user can change the compression settings of the table, blocksize etc. Further, this extends to regular tables as well as partitioned tables. This means that users can now go from a heap table to an AO or AOCO table (or any manner of combinations of the above). We are introducing a capability to alter the storage characteristics of an already populated table with the ALTER TABLE command in GPDB 7.
0 kommentar(er)
