From: Josh Berkus <josh@agliodbs.com>
To: Madison Kelly <linux@alteeve.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Need suggestion high-level suggestion on how to solve a performance problem
Date: Thu, 7 Jul 2005 18:04:28 -0700
Message-ID: <200507071804.29189.josh@agliodbs.com> (raw)
In-Reply-To: <42CD2A7A.10603@alteeve.com>
References: <42CD2A7A.10603@alteeve.com>
Madison,
> The problem comes when the user toggles a directory branch's backup
> flag (a simple check box beside the directory name). If it's a directory
> near the end of a branch it is fast enough. If they toggle a single file
> it is nearly instant. However if they toggle say the root directory, so
> every file and directory below it needs to be updated, it can take
> 500-600sec to return. Obviously this is no good.
>
> What I need is a scheme for being able to say, essentially:
>
> UPDATE file_info_1 SET file_backup='t' WHERE file_parent_dir~'^/';
Well, from the sound of it the problem is not selecting the files to be
updated, it's updating them.
What I would do, personally, is *not* store an update flag for each file.
Instead, I would store the update flag for the directory which was
selected. If users want to exclude certain files and subdirectories, I'd
also include a dont_update flag. When it's time to back up, you simply
check the tree for the most immediate update or don't update flag above
each file.
For the table itself, I'd consider using ltree for the directory tree
structure. It has some nice features which makes it siginifcanly better
than using a delimited text field.
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
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: josh@agliodbs.com, linux@alteeve.com
Subject: Re: Need suggestion high-level suggestion on how to solve a performance problem
In-Reply-To: <200507071804.29189.josh@agliodbs.com>
* 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