![]() ![]() Each range's bounds are understood as being inclusive at the lower end and exclusive at the upper end. For example, one might partition by date ranges, or by ranges of identifiers for particular business objects. The table is partitioned into “ ranges” defined by a key column or set of columns, with no overlap between the ranges of values assigned to different partitions. PostgreSQL offers built-in support for the following forms of partitioning: The exact point at which a table will benefit from partitioning depends on the application, although a rule of thumb is that the size of the table should exceed the physical memory of the database server. These benefits will normally be worthwhile only when a table would otherwise be very large. Seldom-used data can be migrated to cheaper and slower storage media. These commands also entirely avoid the VACUUM overhead caused by a bulk DELETE. Dropping an individual partition using DROP TABLE, or doing ALTER TABLE DETACH PARTITION, is far faster than a bulk operation. When queries or updates access a large percentage of a single partition, performance can be improved by using a sequential scan of that partition instead of using an index, which would require random-access reads scattered across the whole table.īulk loads and deletes can be accomplished by adding or removing partitions, if the usage pattern is accounted for in the partitioning design. ![]() Partitioning effectively substitutes for the upper tree levels of indexes, making it more likely that the heavily-used parts of the indexes fit in memory. Query performance can be improved dramatically in certain situations, particularly when most of the heavily accessed rows of the table are in a single partition or a small number of partitions. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |