I like the time based partitioning idea. I am going to explore it more. I usually try to stay away from triggers. They are hard to manage, and debug.
You understood my intent straight away. :) Thanks! On Sunday, July 1, 2012 6:30:03 AM UTC-7, Vlad K. wrote: > > > 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 view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/pIZ54cYScgYJ. 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.
