The website I'm create has "Events". Events have a title, date, and the userids of the people involved. An event can be anything from following someone, creating a new post, etc. I was thinking of storing all events in a single table, but I could see this getting very big very quickly.
Am I doing it right? When I need to search the table for, say, event pertaining to a certain user, how bad of a toll would that be on the system? Could I optimise it somehow?
You would add indexes on the columns you most frequently use in WHERE clauses, e.g. if you are frequently selecting all events that pertain to a certain user, you should create an index on the user_id column.
http://www.postgresql.org/docs/9.1/static/sql-createindex.html
As long as the data in that table is normalized, you should be OK. If you find that read queries on that table slow down, you can add an index to some of the columns, but you should keep in mind that this will slow down writes to that table.
If you find that the performance is too slow, you can switch to using some NoSQL database that's better optimized for large tables.
If table will be really big, you can use partitioning: http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html but you must choose a good partition key - good candidates are:
It really depends on the quantity of events. For example, we have a table with 490 m records in PostgreSQL and it starts being too heavy for building reports and executing scripts after 250-300 m records. Even indexes didn't help. So it could be a good idea splitting the table in parts choosing the type of event or the time period. Let's say one table for every year or quarter
