From: Andrew Gierth <andrew@tao11.riddles.org.uk>
To: Ogden Brash <info@litika.com>
Cc: Jerry Sievers <gsievers19@comcast.net>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Modification of data in base folder and very large tables
Date: Thu, 10 Oct 2019 13:05:24 +0100
Message-ID: <877e5czua4.fsf@news-spur.riddles.org.uk> (raw)
In-Reply-To: <CAFCR_K2+T=iS_oTTOEdK67Za7gN0-2+LeK+m7=vjw9Zu0Jsdtg@mail.gmail.com>
References: <CAFCR_K3KraRUTKxZeYEGVyNd_6VMa+cgdW_WYn6k4JZz_m3prA@mail.gmail.com>
<87zhi9pljj.fsf@jsievers.enova.com>
<CAFCR_K2+T=iS_oTTOEdK67Za7gN0-2+LeK+m7=vjw9Zu0Jsdtg@mail.gmail.com>
>>>>> "Ogden" == Ogden Brash <info@litika.com> writes:
Ogden> I did the restore as a data only restore so that it would not
Ogden> try to recreate any tables.
Doing data-only restores is almost always a mistake.
pg_dump/pg_restore are very careful to create things in an order that
allows the data part of the restore to run quickly: tables are created
first without any indexes or constraints, then data is loaded, then
indexes and constraints are created in bulk afterwards.
If you do a data-only restore into an existing table, then it's up to
you to avoid performance problems.
Ogden> As an experiment, I am in the process of clustering the source
Ogden> database tables by the primary key constraint. I am hoping that
Ogden> if I redo the pg_dump after that, it will contain the records in
Ogden> more-or-less primary key order and on the subsequent pg_restore
Ogden> it should not have to spend the vast majority of the time on
Ogden> reading and seeking.
This is a waste of time; just restore the data without the primary key
in place and then create it at the end.
Ogden> It is surprising to me that the cluster operations (which also
Ogden> have to churn through the entire index and all the records) are
Ogden> going *much* faster than pg_restore.
That's because cluster, as with creation of a fresh index, can do a bulk
index build: sequentially read the table, sort the values (spilling to
temporary files if need be, but these are also read and written
sequentially), and write out the index data in one sequential pass.
--
Andrew (irc:RhodiumToad)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andrew@tao11.riddles.org.uk, info@litika.com, gsievers19@comcast.net, pgsql-performance@lists.postgresql.org
Subject: Re: Modification of data in base folder and very large tables
In-Reply-To: <877e5czua4.fsf@news-spur.riddles.org.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox