PostgreSQL

PostgreSQL’s partitioning implementation in Architect is done purely at the database level. That means that Architect creates several triggers and functions and inserts them directly into the database, so even if you issue direct insert statement from database console and not from the ORM, everything will work as expected and record will be inserted into the correct partition, if partition doesn’t exist, it will be created for you automatically. Also partitions may be created in any order and not only from lower to higher.

Supported types

range

Range partitioning maps data to partitions based on ranges of values of the partitioning column. It is the most common type of partitioning and is often used with dates or integers, but it can be used with other types as well. Architect supports several subtypes for range partitioning type.

date

Date subtype is used to partition table by date ranges. For example, the y2005m01 partition would contain rows with partitioning column values from 2005-01-01 to 2005-01-31. This subtype has the following constraints:

  • day - a new partition will be created every day
  • week - a new partition will be created every week
  • month - a new partition will be created every month
  • year - a new partition will be created every year
import architect

@architect.install('partition', type='range', subtype='date', constraint='month', column='columnname')
class Model(object):
    pass

integer

New in version 0.4.0.

Integer subtype is used to partition table by integer ranges, for example, one may want to create a new partition for every 100 rows of data, i.e. rows with id 37 and id 68 will go to partition which collects ids from 1 to 100. Integer subtype also supports negative and zero values, so it can be used not only with ids but with any integer column, signed or unsigned.

import architect

@architect.install('partition', type='range', subtype='integer', constraint='100', column='columnname')
class Model(object):
    pass

string_firstchars

New in version 0.4.0.

String firstchars subtype is used to partition table by selecting first N characters from a string, for example, we want to have partitions based on the first 5 characters of a string, i.e. strings “foobar” and “foobarbaz” will go to partition named “fooba”, but string “foo” will go to its own partition “foo” because it doesn’t have enough characters, string “yadayada” will go to partition “yaday” etc.

import architect

@architect.install('partition', type='range', subtype='string_firstchars', constraint='5', column='columnname')
class Model(object):
    pass

string_lastchars

New in version 0.4.0.

This subtype is the same as string_firstchars except that it counts from the end of string and not from the beginning, i.e. string “foobar” will go to partition “oobar”, string “foobarbaz” to partition “arbaz” etc.

import architect

@architect.install('partition', type='range', subtype='string_lastchars', constraint='5', column='columnname')
class Model(object):
    pass

Performance

string_firstchars and string_lastchars both use lower and substr functions to determine the partition. In order for PostgreSQL’s query planner to be able to prune the partition effectively, your query must include a filter like lower(substr(columnname, 1, 5)) = 'fooba'. Also note that unless your table has an index on this function, PostgreSQL may not be able to provide an accurate estimate for the number of rows a query will return.

Limitations

  • Not all partitioning types are supported. New ones will be added in next releases of Architect.