Question: SQLAlchemy doc explain how to create a partitioned table. But it does not explains how to create partitions. So if I have this : #Skipping create_engine and metadata Base = declarative_base() class Measure(Base): __tablename__ = 'measures' __table_args__ = { postgresql_partition_by: 'RANGE (log_date)' } city_id = Column(Integer, not_null=True) log_date = Columne(Date, not_null=True) peaktemp = Column(Integer) unitsales = Column(Integer) class Measure2020(Base): """How am I suppposed to declare this ? """  I know that most of the I'll be doing SELECT * FROM measures WHERE logdate between XX and YY. But that seems interesting.

Answer: Maybe a bit late, but I would like to share what I built upon @moshevi 's and @Seb 's answers: In my IoT use-case, I required actual sub-partitioning (first level year, second level nodeid). Also I wanted to generalize it slightly. This is what I came up with: from sqlalchemy.ext.declarative import DeclarativeMeta from sqlalchemy.sql.ddl import DDL from sqlalchemy import event class PartitionByMeta(DeclarativeMeta): def __new__(cls, clsname, bases, attrs, *, partition_by, partition_type): @classmethod def get_partition_name(cls_, suffix): return f'{cls_.__tablename__}_{suffix}' @classmethod def create_partition(cls_, suffix, partition_stmt, subpartition_by=None, subpartition_type=None): if suffix not in cls_.partitions: partition = PartitionByMeta( f'{clsname}{suffix}', bases, {'__tablename__': cls_.get_partition_name(suffix)}, partition_type = subpartition_type, partition_by=subpartition_by, ) partition.__table__.add_is_dependent_on(cls_.__table__) event.listen( partition.__table__, 'after_create', DDL( # For non-year ranges, modify the FROM and TO below # LIST: IN ('first', 'second'); # RANGE: FROM ('{key}-01-01') TO ('{key+1}-01-01') f""" ALTER TABLE {cls_.__tablename__} ATTACH PARTITION {partition.__tablename__} {partition_stmt}; """ ) ) cls_.partitions[suffix] = partition return cls_.partitions[suffix] if partition_by is not None: attrs.update( { '__table_args__': attrs.get('__table_args__', ()) + (dict(postgresql_partition_by=f'{partition_type.upper()}({partition_by})'),), 'partitions': {}, 'partitioned_by': partition_by, 'get_partition_name': get_partition_name, 'create_partition': create_partition } ) return super().__new__(cls, clsname, bases, attrs)  Which is to be used as follows, assuming the respective VehicleDataMixin class to be created as introduced by @moshevi class VehicleData(VehicleDataMixin, Project, metaclass=PartitionByMeta, partition_by='timestamp',partition_type='RANGE'): __tablename__ = 'vehicle_data' __table_args__ = ( Index('ts_ch_nod_idx', "timestamp", "nodeid", "channelid", postgresql_using='brin'), UniqueConstraint('timestamp','nodeid','channelid', name='ts_ch_nod_constr') )  Which can then be subpartitoned iteratively like so (to be adapted)  for y in range(2017, 2021): # Creating tables for all known nodeids tbl_vehid_y = VehicleData.create_partition( f"{y}", partition_stmt=f"""FOR VALUES FROM ('{y}-01-01') TO ('{y+1}-01-01')""", subpartition_by='nodeid', subpartition_type='LIST' ) for i in {3, 4, 7, 9}: # Creating all the years below these nodeids including a default partition tbl_vehid_y.create_partition( f"nid{i}", partition_stmt=f"""FOR VALUES IN ('{i}')""" ) # Defaults (nodeid) per year partition tbl_vehid_y.create_partition("def", partition_stmt="DEFAULT") # Default to any other year than anticipated VehicleData.create_partition("def", partition_stmt="DEFAULT")  partition_by='timestamp' <= This is the column to partition by partition_type='RANGE' <= This is the (PSQL specific) partition type partition_stmt=f"""FOR VALUES IN ('{i}')""" <= This is the (PSQL specific) partitioning statement.