6

I'm working on a software which gathers snapshots from cameras and saves the record of that snapshot into a snapshots table. We have thousands of cameras and hundreds of millions of snapshot records. Every snapshot record contains only a small amount of data - id, camera_id, timestamp and notes (string field for additional information).

We're starting to notice performance issues in some of the queries, mainly with fetching a number of snapshots in a given time range. Aside from upgrading the hardware that Postgres is running on, we're looking for alternative ways to improve the performance of the whole system.

The team had a similar situation before using MongoDB, with one table for cameras and one for snapshots. They switched to using a separate snapshots table for each camera, resulting in a couple of thousands of snapshot tables. This improved performance by 10x without causing new problems.

Now we're facing the same problem on Postgres. Even though the above solution worked, it doesn't seem like something that a database would be optimized for. Would it be a crazy idea to do the same thing in Postgres? Is there a known limitation in a number of tables you'd normally use? I wasn't able to find good information for this use-case.

Blakes Seven
  • 49,422
  • 14
  • 129
  • 135
Milos
  • 311
  • 3
  • 9
  • Have you already done everything possible to improve performance without denormalising the database? Indices, sharding and whatnot? – deceze Jul 31 '15 at 12:55
  • @deceze We have optimized some of the queries and we have an index on `snapshots_created_at_camera_id_index`. We haven't done sharding yet. – Milos Jul 31 '15 at 13:03
  • 2
    First, check your results from EXPLAIN ANALYZE. Second, table partitioning (per month or whatever) could be an option, but you have to check it for your situation. – Frank Heikens Jul 31 '15 at 13:43

1 Answers1

2

Looks like partitions could be useful for you, but I see a note to say that large numbers (>100) of partitions can be detrimental. You would probably need several thousand partitions, though this may not have a negative impact if your queries never involve more than one partition at a time. From my knoweldge of Evercam, they might not.

Community
  • 1
  • 1
Marco Herbst
  • 21
  • 3