The real problem is that with 7.4's buffering algorithm, the sequential
scans blow the other data out of the internal buffers of postgresql.
And, since a backup needs all the data in the tables, it's gonna seq
scan them anyway. the tables can still be accessed, just the access is
going to be slow because your other processes are fighting the backup
AND nothing in the buffer is likely to be useful to them, except the one
table currently being backed up.
On Mon, 2005-05-23 at 15:58, Thomas F. O'Connell wrote:
> A note about database design, though: there are thousands of tables
> in this database, most of them inherited. I haven't looked at the
> internals of pg_dump, but generally, how do the sequential scans
> work? Why would these prevent the tables from being accessed by
> queries that don't require exclusive locks?
>
> -tfo
>
> --
> Thomas F. O'Connell
> Co-Founder, Information Architect
> Sitening, LLC
>
> Strategic Open Source: Open Your i™
>
> http://www.sitening.com/
> 110 30th Avenue North, Suite 6
> Nashville, TN 37203-6320
> 615-260-0005
>
> On May 23, 2005, at 3:18 PM, Scott Marlowe wrote:
>
> > Basically, it sounds like postgresql is doing a lot of very long
> > sequential scans to do this backup. HAve you done a vacuum full
> > lately? It could be that you've got a lot of table bloat that's
> > making
> > the seq scans take so long.