Impact of CLUSTER on performance
Yes, there can be downsides. If another query looks at a different data segment not determined by the date, it might take a performance hit if rows are spread out over more data pages now. Just the same way as your first query profits. That completely depends on information not in your question.


  other queries using a PK of table (let say id_foo)


That could be anything. It depends on what you have and what you query exactly. Querying a single row is not affected either way, but multiple rows might be.

Be aware that CLUSTER rewrites the table in pristine condition like VACUUM FULL does (removes dead tuples, compacts the physical size of the table, rewrites indexes) So you might see an immediate positive effect on read performance independent of the sort order. (Much like you would get with VACUUM FULL.)
After CLUSTER, you may want to run a plain VACUUM on the table to update the visibility map, too - which may allow index-only scans.

All benefits of CLUSTER shrink with the write frequency.

Also, if you have many updates to the table, CLUSTER can actually hurt write performance by removing "wiggle room" for HOT updates on the same data page. You might be able to counter that effect with a FILLFACTOR setting below 100. Again, depends on locality of updated rows, etc.

Related:


Index optimization with dates


Either way, I would probably not index and cluster on my_timestamp::date, but on my_timestamp directly. Nothing lost, something gained. The cast is very cheap, but it's still cheaper not to cast at all. And the index can support more queries.

CREATE INDEX foo_my_timestamp_idx ON foo (my_timestamp);


Even though a date occupies only 4 bytes on disk and a timestamp occupies 8 bytes, the difference is typically lost to alignment padding for your case, and both indexes have exactly the same size.

The order of multiple rows on the same day resulting from your expression index is arbitrary. There can still be two identical timestamps, but with 6 fractional digits that's normally very unlikely. Aside from that you get a deterministic order of rows, which can have various advantages.

I also dropped the DESC key word since Postgres can read indexes backwards virtually as fast a forwards. (Sort order matters for multicolumn indexes, though!) More:


Optimizing queries on a range of timestamps (two columns)


Instead of:

SELECT * FROM foo
WHERE my_timestamp::date = '2016-07-25';


You would now use:

SELECT * FROM foo
WHERE  my_timestamp &gt;= '2016-07-25'  -- this is a timestamp literal now
WHERE  my_timestamp &lt;  '2016-07-26';


Same performance.

If you don't need the time component of the column at all, convert the column to date ...

How to roll back CLUSTER?

CLUSTER on a single table can be rolled back with ROLLBACK like any other regular command as long as the transaction has not been committed.

However, I quote the manual:


  CLUSTER without any parameter reclusters all the previously-clustered
  tables in the current database that the calling user owns, or all such
  tables if called by a superuser. This form of CLUSTER cannot be
  executed inside a transaction block.


You can always run CLUSTER with a different index to change the physical order of rows once more.