Alternative-1: Table Partitioning
Partitioning我一读就立即想到exactly相同的表结构。我不是 DBA，也没有太多使用它的生产经验（在 PostgreSQL 上更是如此），但是
请阅读PostgreSQL - Partitioning文档。表分区旨在准确解决您遇到的问题，但超过 1K 的表/分区听起来很有挑战性；因此，请在论坛/SO 上进行更多研究，以了解与此主题相关的可扩展性问题。
鉴于您最常用的两个搜索条件，datetime组件非常重要，因此必须有可靠的索引策略。如果你决定一起去partitioningroot，明显的分区策略将基于日期范围。与最新数据相比，这可能允许您将旧数据划分为不同的块，特别是假设旧数据（几乎从未）更新，因此物理布局将是密集且高效的；而您可以采用另一种策略来获取更多“最新”数据。
Alternative-2: trick SQLAlchemy
这基本上通过欺骗 SA 假设所有这些来使您的示例代码工作TimeSeries are children一个实体使用Concrete Table Inheritance。下面的代码是独立的，创建了 50 个包含最少数据的表。但是，如果您已经有一个数据库，它应该允许您相当快地检查性能，以便您可以在可能性很小的情况下做出决定。
from datetime import date, datetime
from sqlalchemy import create_engine, Column, String, Integer, DateTime, Float, ForeignKey, func
from sqlalchemy.orm import sessionmaker, relationship, configure_mappers, joinedload
from sqlalchemy.ext.declarative import declarative_base, declared_attr
from sqlalchemy.ext.declarative import AbstractConcreteBase, ConcreteBase
engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base(engine)
# MODEL
class Location(Base):
__tablename__ = 'locations'
id = Column(Integer, primary_key=True)
table_name = Column(String(50), unique=True)
lon = Column(Float)
lat = Column(Float)
class TSBase(AbstractConcreteBase, Base):
@declared_attr
def table_name(cls):
return Column(String(50), ForeignKey('locations.table_name'))
def make_timeseries(name):
class TimeSeries(TSBase):
__tablename__ = name
__mapper_args__ = { 'polymorphic_identity': name, 'concrete':True}
datetime = Column(DateTime, primary_key=True)
value = Column(Float)
def __init__(self, datetime, value, table_name=name ):
self.table_name = table_name
self.datetime = datetime
self.value = value
return TimeSeries
def _test_model():
_NUM = 50
# 0. generate classes for all tables
TS_list = [make_timeseries('ts{}'.format(1+i)) for i in range(_NUM)]
TS1, TS2, TS3 = TS_list[:3] # just to have some named ones
Base.metadata.create_all()
print('-'*80)
# 1. configure mappers
configure_mappers()
# 2. define relationship
Location.timeseries = relationship(TSBase, lazy="dynamic")
print('-'*80)
# 3. add some test data
session.add_all([Location(table_name='ts{}'.format(1+i), lat=5+i, lon=1+i*2)
for i in range(_NUM)])
session.commit()
print('-'*80)
session.add(TS1(datetime(2001,1,1,3), 999))
session.add(TS1(datetime(2001,1,2,2), 1))
session.add(TS2(datetime(2001,1,2,8), 33))
session.add(TS2(datetime(2002,1,2,18,50), -555))
session.add(TS3(datetime(2005,1,3,3,33), 8))
session.commit()
# Query-1: get all timeseries of one Location
#qs = session.query(Location).first()
qs = session.query(Location).filter(Location.table_name == "ts1").first()
print(qs)
print(qs.timeseries.all())
assert 2 == len(qs.timeseries.all())
print('-'*80)
# Query-2: select all location with data between date-A and date-B
dateA, dateB = date(2001,1,1), date(2003,12,31)
qs = (session.query(Location)
.join(TSBase, Location.timeseries)
.filter(TSBase.datetime >= dateA)
.filter(TSBase.datetime <= dateB)
).all()
print(qs)
assert 2 == len(qs)
print('-'*80)
# Query-3: select all data (including coordinates) for date A
dateA = date(2001,1,1)
qs = (session.query(Location.lat, Location.lon, TSBase.datetime, TSBase.value)
.join(TSBase, Location.timeseries)
.filter(func.date(TSBase.datetime) == dateA)
).all()
print(qs)
# @note: qs is list of tuples; easy export to CSV
assert 1 == len(qs)
print('-'*80)
if __name__ == '__main__':
_test_model()
Alternative-3: a-la BigData
如果您确实在使用数据库时遇到性能问题，我可能会尝试：
- 仍然像现在一样将数据保存在单独的表/数据库/模式中
- 使用数据库引擎提供的“本机”解决方案批量导入数据
- use MapReduce-like analysis.
- 在这里，我将继续使用 python 和 sqlalchemy 并实现自己的分布式查询和聚合（或找到现有的东西）。显然，只有当您不需要直接在数据库上生成这些结果时，这才有效。
edit-1: Alternative-4: TimeSeries databases
我没有大规模使用这些的经验，但绝对是一个值得考虑的选择。
如果您稍后能分享您的发现和整个决策过程，那就太好了。
