Thread: proposal: integration bloat tables (indexes) to core

proposal: integration bloat tables (indexes) to core

From
Pavel Stehule
Date:
Hi

There are lot of useful queries (views), that are on our wiki. Some queries are necessary for maintenance, and I am thinking these queries should be integrated part of Postgres.

Mainly queries for detecting table bloat, index bloat, But some queries over pg_locks should be useful too.

Notes, comments?

Regards

Pavel 

Re: proposal: integration bloat tables (indexes) to core

From
Robert Haas
Date:
On Mon, Jun 13, 2016 at 3:23 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> There are lot of useful queries (views), that are on our wiki. Some queries
> are necessary for maintenance, and I am thinking these queries should be
> integrated part of Postgres.
>
> Mainly queries for detecting table bloat, index bloat, But some queries over
> pg_locks should be useful too.
>
> Notes, comments?

It's likely to be hard to get agreement on which things to include.
But if we can, it might be worth doing.  It would be nice to do it as
an extension.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company



Re: proposal: integration bloat tables (indexes) to core

From
Pavel Stehule
Date:


2016-06-13 18:52 GMT+02:00 Robert Haas <robertmhaas@gmail.com>:
On Mon, Jun 13, 2016 at 3:23 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> There are lot of useful queries (views), that are on our wiki. Some queries
> are necessary for maintenance, and I am thinking these queries should be
> integrated part of Postgres.
>
> Mainly queries for detecting table bloat, index bloat, But some queries over
> pg_locks should be useful too.
>
> Notes, comments?

It's likely to be hard to get agreement on which things to include.
But if we can, it might be worth doing.  It would be nice to do it as
an extension.

maybe estimated pgstattuple functions ?

but bloating is too important, so I would to see solution in core more.

Regards

Pavel
 

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: proposal: integration bloat tables (indexes) to core

From
Tom Lane
Date:
Robert Haas <robertmhaas@gmail.com> writes:
> On Mon, Jun 13, 2016 at 3:23 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>> There are lot of useful queries (views), that are on our wiki. Some queries
>> are necessary for maintenance, and I am thinking these queries should be
>> integrated part of Postgres.

> It's likely to be hard to get agreement on which things to include.
> But if we can, it might be worth doing.  It would be nice to do it as
> an extension.

The problem with an extension is: when we make a core change that breaks
one of these views, which we will, how can you pg_upgrade a database
with the extension installed?  There's no provision for upgrading an
extension concurrently with the core upgrade.  Maybe there should be,
but I'm unclear how we could make that work.

At the same time, I'm pretty suspicious of putting stuff like this in
core, because the expectations for cross-version compatibility go up
by orders of magnitude as soon as we do that.
        regards, tom lane



Re: proposal: integration bloat tables (indexes) to core

From
Jim Nasby
Date:
On 6/13/16 12:16 PM, Tom Lane wrote:
> At the same time, I'm pretty suspicious of putting stuff like this in
> core, because the expectations for cross-version compatibility go up
> by orders of magnitude as soon as we do that.

On a first go-round, I don't think we should add entire views, but 
rather functions that serve specific purposes. For table bloat that 
means a function that returns what the heap size should be based on 
pg_stats. For locking, it means providing information about which PID is 
blocking which PID. After that, most everything else is just window 
dressing.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461



Re: proposal: integration bloat tables (indexes) to core

From
Pavel Stehule
Date:


2016-06-16 20:31 GMT+02:00 Jim Nasby <Jim.Nasby@bluetreble.com>:
On 6/13/16 12:16 PM, Tom Lane wrote:
At the same time, I'm pretty suspicious of putting stuff like this in
core, because the expectations for cross-version compatibility go up
by orders of magnitude as soon as we do that.

On a first go-round, I don't think we should add entire views, but rather functions that serve specific purposes. For table bloat that means a function that returns what the heap size should be based on pg_stats. For locking, it means providing information about which PID is blocking which PID. After that, most everything else is just window dressing.

could be

if you look on current bloating queries, then you can see pretty complex queries due implementation on high level. C implementation should be more faster. There are lot of changes in core, but these queries is working for PostgreSQL 8.2 to today, so they are relatively stable.

Regards

Pavel
 
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461

Re: proposal: integration bloat tables (indexes) to core

From
Vik Fearing
Date:
On 16/06/16 20:31, Jim Nasby wrote:
> On 6/13/16 12:16 PM, Tom Lane wrote:
>> At the same time, I'm pretty suspicious of putting stuff like this in
>> core, because the expectations for cross-version compatibility go up
>> by orders of magnitude as soon as we do that.
> 
> On a first go-round, I don't think we should add entire views, but
> rather functions that serve specific purposes. For table bloat that
> means a function that returns what the heap size should be based on
> pg_stats. For locking, it means providing information about which PID is
> blocking which PID. After that, most everything else is just window
> dressing.

We already have that second one: pg_blocking_pids(integer)
-- 
Vik Fearing                                          +33 6 46 75 15 36
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support



Re: proposal: integration bloat tables (indexes) to core

From
Thomas Kellerer
Date:
Tom Lane-2 wrote
> The problem with an extension is: when we make a core change that breaks
> one of these views, which we will, how can you pg_upgrade a database
> with the extension installed?  There's no provision for upgrading an
> extension concurrently with the core upgrade.  Maybe there should be,
> but I'm unclear how we could make that work.
> 
> At the same time, I'm pretty suspicious of putting stuff like this in
> core, because the expectations for cross-version compatibility go up
> by orders of magnitude as soon as we do that.

Why not provide a "SQL" or "Admin Scripts" directory as part of the
installation that contains community "recommended" scripts for things like
that? As those aren't extensions or somehow part of the data directory they
don't need to be migrated and pg_upgrade does not need to take care of that. 

When installing a new version, the new scripts that work with the new
version are installed automatically but will not overwrite the old version's
scripts as the new version typically is stored in a different directory. 





--
View this message in context:
http://postgresql.nabble.com/proposal-integration-bloat-tables-indexes-to-core-tp5907511p5908273.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.