Somewhat unrelated but note to anyone who wants to swap out partition keys. Don't create a clone of the table with the new partition key and insert data. It messes up the query planner massively and makes everything much slower.
On Mon, May 25, 2020 at 12:48 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Cedric Leong <cedricleong@gmail.com> writes: > Just in case someone is interested enough to answer this. Does anyone know > if the performance for a date column vs a timestamp column as a partition > key is large?
I doubt it's even measurable, at least on 64-bit machines. You're basically talking about 32-bit integer comparisons vs 64-bit integer comparisons.
On a 32-bit machine it's possible that an index on a date column will be physically smaller, so you could get some wins from reduced I/O. But on (most?) 64-bit machines that difference goes away too, because of alignment restrictions.
As always, YMMV; it never hurts to do your own testing.