Thread: does pg_restore respect CLUSTER ON in the schema?
I have a database where several tables have been physically clustered on disk. Those clustering configuration parametersare stored in the schema (via ALTER TABLE mytable CLUSTER ON myindex). My question is, when i restore a backup of this database using pg_restore, is it restored with that physical clustering,or without? i.e. do i need to run a cluster command immediately after the restore, or is it already done? Thanks, Seth
On 11/6/2014 3:03 PM, Seth Pollack wrote: > I have a database where several tables have been physically clustered on disk. Those clustering configuration parametersare stored in the schema (via ALTER TABLE mytable CLUSTER ON myindex). > > My question is, when i restore a backup of this database using pg_restore, is it restored with that physical clustering,or without? i.e. do i need to run a cluster command immediately after the restore, or is it already done? when you restore, the data is inserted in new empty tables via COPY, which will write the records in whatever order they were dumped in, which is going to be the order they are in the original table at that point in time.. any waste space will be packed out, but the data will only be as clustered as it was when it was at the time of the dump. -- john r pierce 37N 122W somewhere on the middle of the left coast
Seth Pollack wrote > I have a database where several tables have been physically clustered on > disk. Those clustering configuration parameters are stored in the schema > (via ALTER TABLE mytable CLUSTER ON myindex). > > My question is, when i restore a backup of this database using pg_restore, > is it restored with that physical clustering, or without? i.e. do i need > to run a cluster command immediately after the restore, or is it already > done? Some assuming here... The COPY command that pg_dump uses will use a sequential scan over the table and return tuples as it encounters them. Upon restore the COPY will see those records in the source file in the same order and should insert them onto disk without shuffling. A sequential scan should cause the copy output to remain in cluster order for records that were previously clustered (keeping in mind MVCC mechanics) while any newer ones will likely be in actual insert order. The restored database should have a similar layout and thus the need to perform clustering would be dependent upon when you last ran cluster on the source database. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/does-pg-restore-respect-CLUSTER-ON-in-the-schema-tp5826007p5826009.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.