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?

Answers


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:

  • event timestamp
  • event type
  • user_id

Need Your Help

Checking app version from market place in windows phone 8

c# windows-phone-8

I need to compare current app version and market place app version.

Path 'PROPFIND' is forbidden?

asp.net asp.net-mvc http iis-6

I am receiving the following error but can't seem to make sense out of it within the context that it's happening: