4

I have a table in a PostreSQL database that holds advertisements. Every day, around 100k new advertisements are added and another 40k expire.

Which approach is more suitable:

  • having a disable flag but keeping them in the main database (index structure will also be altered)
  • having a disabled flag and moving them after a period of time to a storage database where we can analyse the data
carina_
  • 45
  • 1
  • 6
  • How exactly is the expiration defined? Maybe partitioning on the month might be one solution? –  Nov 29 '18 at 12:13

1 Answers1

0

The answer depends to some extent on how the data are used, but I find the second method preferable in general.

The advantages:

  • Your active database is small, which makes many queries faster and generally makes maintenance easier.
  • You are more flexible with data placement. For example, you can easily hold the old data on slower, cheaper storage or on a different machine altogether.

The disadvantage is of course that it is more complicated to access old data.

You can also consider a hybrid solution by having two partitions for each table, for the active and the old data. That way you can have them in different tablespaces or (using foreign data wrappers) even on different machines.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263