Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a database that stores events. Events are generated at a 1000 per sec rate. We need to keep these events accessible for some years.
Usual use of these events is selecting some of them from the last 1-2 months.

Each event corresponds to a resource_id (estimated 5000).

Since just keeping all these in one table gets all maintenance and selects quite slow and resource consuming I was thinking on improving it in some ways:

a) separate every resource_id to each table. 5000+ tables. Selects from multiple resource_ids (maximum 100) will need some rewrite and unions or something.
b) separate every day to a different table. unions or something would be required when selects will require data from multiple days.
c) both the above (too extreme I think)
d) partitioning in someway on timestamp index and maybe even resource_id index. Is it worth it? Or "manual" partitioning of above suggestions is better?

The database is PostgreSQL.

share|improve this question
2  

2 Answers 2

up vote 4 down vote accepted

We have something simular, ~5000 events per seconds. Tables are partitioned by month and we store 5 years of data. That gives us a 60 partitions, works fine. It runs on PostgreSQL version 9.1, works better than any older version when using partitions.

EXPLAIN and EXPLAIN ANALYZE are your best friends to get your queries right.

share|improve this answer

This is a classic case for rolling partitions (or sliding window partitions). Before redesigning the table in thousands of children tables and rewriting your code please give a try to partitioning, like Erwin already advised.

Some articles:

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.