2 Minor typos and formatting.
source | link

WEWe 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.

eachEach 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 iI 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?

theThe database is postgresqlPostgreSQL.

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

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.

1
source | link

Huge database logging of event type rows and ways to optimize it

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