Possible performance issues with huge PostgreSQL table
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.
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:
- event timestamp
- event type