PostgreSQL :- Create free pages in Table

Background

Wanted to intentionally create free pages in a PostgreSQL table.

Outline

  1. DDL
    • Create a new table ( dateRange)
  2. DML
    • Add data to table ( dateRange.insert.sql )
    • Update data in table ( dateRange.update.series.sql )
    • Delete data from table ( dateRange.delete.series.sql )
  3. Metadata
    • Extension
      • Review Free Space ( pg_freespacemap.sql )

Code

DDL

Create Table

drop table if exists daterange
;

create table dateRange
(
      id integer not null

    , dateValue timestamp not null

    , randValue decimal(20, 2) not null

    , seq       int not null
    	default 0

    , constraint pk_dateRange
      primary key
    (
    	id
    )
	with
	(
	  fillfactor=100
	)

)
;

create index indx_dateValue
on dateRange
(
  dateValue desc
)
with
(
  fillfactor=100
)
;

create index indx_randValue
on dateRange
(
  randValue desc
)
with
(
  fillfactor=100
)
;

;

ALTER TABLE daterange
SET
(
    autovacuum_enabled = false
  , toast.autovacuum_enabled = false
);

checkpoint;

Add Data

truncate table dateRange
;

insert into dateRange
(
      id -- integer not null

    , dateValue -- timestamp not null

    , randValue --decimal(20, 2) not null

)

select

	     rank()
            over
            (
              order by tblGS
            )

            as "interval"

	   , tblGS as "date"

	  /*
	   * Random Number between 1 and 1 million
	  */
	  , (random() * 1E6 ) :: int
  			as "random"	

from  generate_series
		(
			  current_date::timestamp -- start

			 , current_date + interval '5 year'

			, '1 minute'

	    ) tblGS
;

analyze dateRange
;

checkpoint;

Delete Data

delete
from dateRange
where id %11= 0
;

delete
from dateRange
where id %13 = 0
;

analyze dateRange;

checkpoint;

Update Data


update dateRange
set    randvalue = random() * 100000
where id %7 = 0
;

update dateRange
set    randvalue = random() * 100000
where id %9 = 0
;

analyze dateRange
;

checkpoint;

Metadata

pg_freespace

Code


with cteSetting
(
      name
    , setting
)

as
(
    select
              name
            , setting::integer

    from   pg_catalog.pg_settings

    where  name = 'block_size'
)
SELECT 

          tblN.nspname
            as "schema"

		, tblC.relname

        , avg(tblC.relpages)::int
            as relpages

        , avg(tblC.reltuples)::int
            as reltuples

        , count(*) as cnt

        , pg_size_pretty
            (
                cast
                (
                    avg(tblPGFS.avail) as bigint
                )
            )
            as "Av. freespace size"

        /*
             max(cteSBS.setting)
            as "blockSize"
        */

        , round
            (
                100
                    * avg
                    (
                        tblPGFS.avail
                    )
                    /
                    (
                        avg(cteSBS.setting)::int
                    )
                ,2
            ) as "Av. freespace ratio"		

FROM pg_catalog.pg_class tblC

join pg_catalog.pg_namespace tblN

    on tblC.relnamespace = tblN.oid

inner join pg_freespace(tblC.oid) tblPGFS

    on TRUE

inner join cteSetting cteSBS
    on cteSBS.name = 'block_size'

where   tblC.relkind = 'r'

and     tblN.nspname not in

            (
                  'information_schema'
                , 'pg_catalog'

            )

GROUP BY

          tblN.nspname 

        , tblC.relname

ORDER BY

          tblN.nspname 

        , tblC.relname

;

Output

self.lab.01.20190812.0158PM

Source Code Control

GitHub

DanielAdeniji/PostgreSQLSampleDBLab
Link

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s