From: pavan95 <pavan.postgresdba@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Re: Heap Only Update
Date: Thu, 1 Feb 2018 04:14:05 -0700 (MST)
Message-ID: <1517483645048-0.post@n3.nabble.com> (raw)
In-Reply-To: <CAByegeLBV=bLHy9jV0whnU=qSmVsqTCp0yp23FPqPHCDcaeZWA@mail.gmail.com>
References: <CAByegeLBV=bLHy9jV0whnU=qSmVsqTCp0yp23FPqPHCDcaeZWA@mail.gmail.com>
Hi Ali,
Before changing the default value of Fillfactor, we should measure the size
of Table Row.
For knowing the row size is very important because if table row size is
larger, we should not change the default value of Fillfactor.
When we are doing performance optimization, this is very important to find
the size of the Data page and Table row, otherwise unnecessary we are
dealing with high fragmentation and executing VACUUM FULL or VACUUM again
and again.
The default Fillfactor is 100 and it is better, but not in all situations.
*When your table has frequent updates, this is not a better solution because
it requires more CPU and IO for different data page operations which
actually degrade the performance*.
The solution is to first measure the size of the tuple and if tuple size is
not that much bigger, we can reduce the value of default Fillfactor.
If your total row size is under 8kb, you can take decision to alter table
storage parameters.
You can use below script to measure the size of the tuple and if that size
is not that big you can go ahead in decreasing the value of fill factor:
WITH cteTableInfo AS
(
SELECT
COUNT(1) AS ct
,SUM(length(t::text)) AS TextLength
,'public.your_table_name'::regclass AS TableName
FROM public.your_table_name AS t
)
,cteRowSize AS
(
SELECT ARRAY [pg_relation_size(TableName)
, pg_relation_size(TableName, 'vm')
, pg_relation_size(TableName, 'fsm')
, pg_table_size(TableName)
, pg_indexes_size(TableName)
, pg_total_relation_size(TableName)
, TextLength
] AS val
, ARRAY ['Total Relation Size'
, 'Visibility Map'
, 'Free Space Map'
, 'Table Included Toast Size'
, 'Indexes Size'
, 'Total Toast and Indexes Size'
, 'Live Row Byte Size'
] AS Name
FROM cteTableInfo
)
SELECT
unnest(name) AS Description
,unnest(val) AS Bytes
,pg_size_pretty(unnest(val)) AS BytesPretty
,unnest(val) / ct AS bytes_per_row
FROM cteTableInfo, cteRowSize
UNION ALL SELECT '------------------------------', NULL, NULL, NULL
UNION ALL SELECT 'TotalRows', ct, NULL, NULL FROM cteTableInfo
UNION ALL SELECT 'LiveTuples', pg_stat_get_live_tuples(TableName), NULL,
NULL FROM cteTableInfo
UNION ALL SELECT 'DeadTuples', pg_stat_get_dead_tuples(TableName), NULL,
NULL FROM cteTableInfo;
Regards,
Pavan
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
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-admin@postgresql.org
Cc: pavan.postgresdba@gmail.com
Subject: Re: Heap Only Update
In-Reply-To: <1517483645048-0.post@n3.nabble.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