I see two approaches here. The first is partitioning as it has been
suggested, except partitions need to be defined upfront, if I'm not
mistaken, at least in MySQL (expression + size), whereas in PostgreSQL
you need custom triggers on write operations on the main table, so I'm
not sure if per-customer partitioning would work as that would require
rewriting the triggers each time a customer is added or removed.
Perhaps time-based partitioning is better, e.g. monthly so you have 12
partitions. Or anything else with fixed number of partitions.
The other approach, which can be combined with partitioning, is pivot
tables and/or materialized views, especially if you need graphing. You
can have triggers that update hourly, daily, weekly, monthly pivot data
which is used for graphing, and you actually select through main log
data only if you need to recreate the pivot data or look for particular
record.
Unless I misunderstood the original intent?
On 07/01/2012 12:49 PM, Wolfgang Keller wrote:
Having a single table to store all the customer's logs didn't seem to
work because when the device is removed and the logs have to be
deleted the table gets locked for a while.
Huh? Bad choice of DBMS/OS? Bad application design?
My question is, is this a good idea? Am I missing something?
I would go for one table for all customers, but for _partitioning_ the
log table into one partition per customer.
A database such as e.g. PostgreSQL running on an operating system that
knows how to combine multitasking and I/O (read: _not_ MS Windows)
should be perfectly capable of serving clients while deleting an entire
table partition.
Sincerely,
Wolfgang
--
.oO V Oo.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
