Tweeted twitter.com/#!/StackDBAs/status/252015313854541825
2 improved formatting
source | link

How should we handle rows which won't be queried once they are old in PostgreSQL?

We have a table in a PostgresPostgreSQL database which is growing on the order of millions of rows per day.

Each row consists of:

  • ID
  • Foreign user ID
  • Date and time
  • Other data
ID
Foreign user ID
Date and time
Other data

The date and time aren't strictly monotonic with ID (butID, however they are close).


 

When querying this table we are only interested in getting rows for a given foreign user ID, with a date and time in the last two weeks. Rows more than two weeks old will never be queried (but, however we'd like to keep them for archival purposes).

Given this special use case:

  1. Should we have an index on the date and time column?
  2. Is there any hint we can give that the date and time is (almost) monotonically increasing with ID.
  3. Should we look at trying to remove rows older than two weeks from the table, is that likely to improve performance?

How should we handle rows which won't be queried once they are old

We have a table in a Postgres database which is growing on the order of millions of rows per day.

Each row consists of:

  • ID
  • Foreign user ID
  • Date and time
  • Other data

The date and time aren't strictly monotonic with ID (but they are close).


 

When querying this table we are only interested in getting rows for a given foreign user ID, with a date and time in the last two weeks. Rows more than two weeks old will never be queried (but we'd like to keep them for archival purposes).

Given this special use case:

  1. Should we have an index on the date and time column?
  2. Is there any hint we can give that the date and time is (almost) monotonically increasing with ID.
  3. Should we look at trying to remove rows older than two weeks from the table, is that likely to improve performance?

How should we handle rows which won't be queried once they are old in PostgreSQL?

We have a table in a PostgreSQL database which is growing on the order of millions of rows per day.

Each row consists of:

ID
Foreign user ID
Date and time
Other data

The date and time aren't strictly monotonic with ID, however they are close.

When querying this table we are only interested in getting rows for a given foreign user ID, with a date and time in the last two weeks. Rows more than two weeks old will never be queried, however we'd like to keep them for archival purposes.

Given this special use case:

  1. Should we have an index on the date and time column?
  2. Is there any hint we can give that the date and time is (almost) monotonically increasing with ID.
  3. Should we look at trying to remove rows older than two weeks from the table, is that likely to improve performance?
1
source | link

How should we handle rows which won't be queried once they are old

We have a table in a Postgres database which is growing on the order of millions of rows per day.

Each row consists of:

  • ID
  • Foreign user ID
  • Date and time
  • Other data

The date and time aren't strictly monotonic with ID (but they are close).


When querying this table we are only interested in getting rows for a given foreign user ID, with a date and time in the last two weeks. Rows more than two weeks old will never be queried (but we'd like to keep them for archival purposes).

Given this special use case:

  1. Should we have an index on the date and time column?
  2. Is there any hint we can give that the date and time is (almost) monotonically increasing with ID.
  3. Should we look at trying to remove rows older than two weeks from the table, is that likely to improve performance?