On Tue, Apr 1, 2014 at 8:21 PM, Jonathan Vanasco <jonat...@findmeon.com> wrote:
> i didn't catch this before...
>
> backref_name = "timeseries_%s" % name ## might be better as an the id
> of the location
> location = relationship('Location', backref=backref(backref_name,
> lazy='dynamic'))
>
> I don't think he can do one table. originally i did, but I looked into some
> high-performance sql blogs and posts -- postgres starts to crap-out on
> performance around a billion rows. it can handle the storing fine, and it's
> still rock-solid, but it's not fast... and seems to degrade quickly.
> everyone says you need to start partitioning your tables and indexes at that
> point. he's got a location-based partition system already.
Well, that's not quite accurate.
You're welcome to take this to the pg performance list, but,
basically, it all depends on indexing and query patterns.
I've been doing fine with OLAP on around 500M-row unpartitioned (but
cleverly indexed) tables. Problem is, the OP is quite an unnatural
load for a relational engine.
Notice, however, that the proposed schema is, pretty much, a
partitioned schema. Partitioned on location. So, a simple way to map
this on SQLAlchemy, is to simply map the main table and let postgres
do the partitioning.
If you really want to go for the 2000 tables approach, I'd suggest
that - let pg do the partitioning.
Just a heads up: pg's partitioning is designed around hundreds of
tables, not thousands. You might hit a wall there.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
