[GENERAL] Regression tests (Background Workers)

[Dharini]

Background process is initialized at server start and when trying to run
the tests i get the following error.

$ make installcheck
(using postmaster on Unix socket, default port)
== dropping database "contrib_regression" ==
ERROR:  DROP DATABASE cannot be executed from a function or multi-command
string
command failed: "/postgres/install/bin/psql" -X -c "DROP DATABASE IF EXISTS
\"contrib_regression\"" "postgres"
make: *** [installcheck] Error 2

Re: [GENERAL] pg_dump from a hot standby replication slave

[Sameer Kumar]

On Tue, 21 Jun 2016, 4:03 a.m. Johan Thomsen, 
wrote:

> Hi,
>
> In relation to this thread:
>
> https://www.postgresql.org/message-id/0B4197B9-5DB8-4475-A83C-38DA5965782A%40etasseo.com
>
> > I ran the pg_dump process again this morning, ensuring that the standby
> > parameters were set, and it completed successfully with the
> > hot_standby_feedback enabled.
>
> In case I were to set the hot_standby_feedback param to "on" at my
> slave node for the _only_ reason that I would like to make a dump of a
> database while replication is running, how would it affect the primary
> node performance-wise?
>

In my understanding it might create some bloats but those should get
cleaned eventually.
Though I think if you are using standby for read purpose, this parameter
should be kept on.


> Worth mentioning: I do not require the dumped data to be highly
> up-to-date, I just need a static snapshot, knowing perfectly well that
> data is updated continuously while dumping.
>
> Are there perhaps other ways to perform a dump of a hot standby node
> without affecting the primary node performance and without stopping or
> pausing the replication?
>

Are you using archiving as well?
Consider using pg_basebackup to take physical backup of the standby.

Version: postgres (PostgreSQL) 9.2.2
>
> Thank you in advance,
>
>
> Regards,
> Johan
>
>
> --
> Johan Thomsen
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
-- 
--
Best Regards
Sameer Kumar | DB Solution Architect
*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com

Re: [GENERAL] Vacuum full: alternatives?

[Scott Marlowe]

On Mon, Jun 20, 2016 at 3:18 AM, Job  wrote:
>
> Hello,
>
> we have a table with an heavy traffic of pg_bulkload and delete of records.
> The size pass, in only one day, for example for 1Gb to 4Gb and then 1Gb back.
>
> We have important problems on size and the only way to gain free space is 
> issueing a vacuum full .
> But the operation is very slow, sometimes 2/4 hours, and table is not 
> available for services as it is locked.
>
> We do not delete everything at one (in this case the truncate woudl resolve 
> the problem).
>
> The autovacuum is not able (same for normal vacuum) to free the spaces.
>
> Are there some suggestions or another way to manage this?

First off, from your comments I'm not sure you really get postgresql's
way of freeing space and reusing it via autovacuum. Basically
postgresql, autovacuum process marks space as free, and the backend
writes new data (inserts or updates) into the free space. You
eventually reach equilibrium of a sort when the vacuum is freeing up
space as quickly as it's being consumed, or faster. The problem occurs
when vacuum can't keep up with your delete / write and update rate
combined. If this is happening you need to:

A: Make sure your IO Subsystem is fast enough to handle BOTH your
update rate AND your vacuuming needed to keep up, You're better off
with a machine that can do 15,000 transactions per second running a
load of 1,000 than trying to handle it with a machine that can do
1,500 tps etc. Sizing the hardware is a whole other conversation.

AND

B: Make your autovacuum aggressive enough to NOT fall behind.

It's important to remember that autovacuum was built and designed in a
time when most databases lived on spinning media. It's designed to not
overload spinning discs with too much random IO. A super fast RAID-10
array from that time period could do 200 to 1,000 transactions per
second and that only with a top notch RAID controller etc. Regular
spinning discs have a maximum random write ops per second that measure
in the 100 per second range.

My laptop with no pgsql optimizations, can do 850 tps on it's SSD. A
server with 10 SSDs in RAID-5 can do 15,000 tps.  If you have a fast
IO subsystem and wish to utilize it with pgsql you're going to have to
examine whether or not autovacuum with default settings is fast enough
to keep up. Remmeber, Auto-vacuum, by design, is slow and tries not to
get in the way. It's fast enough for most mundane uses, but can't keep
up with a fast machine running hard. The default settings for
autovacuum to look at here are first these two.:

autovacuum_vacuum_cost_delay = 20ms
autovacuum_vacuum_cost_limit = 200

They govern how hard autovac works. By default autovac doesn't work
hard. Making it work too hard for a given machine can cause system
performance issues. I.e. it "gets in the way".

Lowering cost_delay is usually enough. As you approach 1ms autovac
starts to use a lot more bandwidth. I find that even on pretty fast
machines that are routinely doing 1,000 writes per second or more, 3ms
is fast enough to keep up with a cost limit of 200. 5ms is a good
compromise without getting too aggressive.

In contrast to autovacuum, REGULAR vacuum, by default, runs at full
throttle. It hits your db server hard, performance-wise. It has  zero
cost delay, so it works very hard. If you run it midday on a hard
working server you will almost certainly see the performance drop. The
difference between regular vacuum with a delay time of 0 and autovac
with a delay of 20ms is huge.

These settings become important if you have a LOT of tables or dbs.
Otherwise they're probably fine.

autovacuum_max_workers =3 # Adjust this last, unless you have
thousands of tables or dbs.
autovacuum_naptime = 1 min # How long to wait before checking the next
db. Default is usually fine unless you have a lot of dbs.

These settings tell autovacuum when to kick in. Keeping these low
enough to keep autovac busy is a good idea too:

autovacuum_vacuum_scale_factor
autovacuum_vacuum_threshold
autovacuum_analyze_scale_factor
autovacuum_analyze_threshold

I tend to go for threshold, which is an absolute number of rows
changed before autovac kicks off. Scale factor can be dangerous
because what seems small at the beginning, gets big fast. If it's 0.1
then that's 10%. 10% of 1,000 is 100 rows, but 10% of 1,000,000 is
100,000, which is a LOT of rows to ignore until you have more than
that that need vacuuming. Setting it to something like 100 or 1,000
will keep your db from growing hundreds of thousands ofr dead tuples
in a big table.

Either way you need to make sure your autovacuum is aggressive enough
to keep up with your db's throughput.

Checking for bloat. You can see what parts of your db are getting too
big.  First, go here:
https://www.keithf4.com/checking-for-postgresql-bloat/

The script there will let you check all your tables AND indexes for
bloat. This will let you know if you've got a simple space problem or
a vacuuming 

Re: [GENERAL] Regression in query optimizer when upgrading from 9.4.5 to 9.5.2?

[David G. Johnston]

On Fri, Jun 17, 2016 at 5:05 PM, Erdmann, Markus @ Bellevue <
markus.erdm...@cbre.com> wrote:

> Thank you Tom and David for your very helpful replies. We dumped and
> restored the RDS staging database on a local installation of pg and were
> not able to reproduce the issue in 9.5.2, which led us to try running a
> VACUUM ANALYZE and recreating indexes. After this we no longer saw a
> discrepancy between the query plan in the separate environments. Is this
> what you meant, Tom, by making sure to ANALYZE? Or did you mean including
> ANALYZE in EXPLAIN ANALYZE?
>
>
​Please don't top-post.

Yes, using "[VACUUM] ANALYZE" is what was meant (the VACUUM doesn't hurt -
and possibly helps - but wasn't required).  ANALYZE recomputes the
statistics for your database.  The apparent problem was that those
statistics were wrong which causes the planner to choose the wrong plan.

EXPLAIN ANALYZE doesn't help in that scenario - the explain and planner
both end up using the bad statistics.  The addition of ANALYZE to EXPLAIN
simply tells the system to not only explain your query but to execute it as
well (but discard the results).  Aside from sharing the same 7 characters
the two words have nothing in common.

I'm not sure how one would "...includ[e] ANALYZE in EXPLAIN ANALYZE" - you
only get to explain one statement at a time.

David J.

Re: [GENERAL] Help with namespaces in xpath (PostgreSQL 9.5.3)

[David G. Johnston]

On Sun, Jun 19, 2016 at 5:09 PM, Allan Kamau  wrote:

> I have an xml document from which I would like to extract the contents of
> several elements.
>
> I would like to use xpath to extract the contents of "name" from the xml
> document shown below.
>
> WITH x AS
> (
> SELECT
> '
> http://uniprot.org/uniprot; xmlns:xsi="
> http://www.w3.org/2001/XMLSchema-instance; xsi:schemaLocation="
> http://uniprot.org/uniprot http://www.uniprot.org/support/docs/uniprot.xsd
> ">
>  version="56">
> A0JM59
> UBP20_XENTR
> 
> 
> '::xml AS d
> )
> SELECT (xpath('/uniprot/entry/name/text()',a.d))[1]::text AS uniprot_name
> FROM
> x AS a
> ;
>
> The documentation for xpath() ("
> https://www.postgresql.org/docs/9.5/static/functions-xml.html;) describes
> "xpath(xpath, xml [, nsarray]").
>
> For the above xml document, what would be the two dimensional array
> "nsarray" for the xpath() function?
>

​Is there a specific part of the description and two examples that doesn't
make sense to you?

​Or more specifically, do you understand what namespaces are?​

ARRAY[
ARRAY['defaultns','http://uniprot.org/uniprot'],
ARRAY['xsi','http://www.w3.org/2001/XMLSchema-instance']
]​

In effect when the xpath function parses the XML document it tosses away
all of the document-local namespace aliases and instead associated the full
namespace URI with each element (in the DOM).  Since, in the xpath
expression, usually you'd want to refer to nodes in the DOM via their
namespace alias you need to tell the xpath function which aliases you
intend to use in the xpath and which full URI they correspond to.
Furthermore, there is not concept of a default namespace in the xpath
expression.  So while you can simply copy-paste the aliases and URIs from
all of the non-default namespace aliases you must also choose a unique
alias for the default namespace in the original document.

In the above I've copied the alias and namespace URI for the named "xsi"
alias and gave the name "defaultns" to the original document's default
namespace URI.

David J.

[GENERAL] pg_dump from a hot standby replication slave

[Johan Thomsen]

Hi,

In relation to this thread:
https://www.postgresql.org/message-id/0B4197B9-5DB8-4475-A83C-38DA5965782A%40etasseo.com

> I ran the pg_dump process again this morning, ensuring that the standby
> parameters were set, and it completed successfully with the
> hot_standby_feedback enabled.

In case I were to set the hot_standby_feedback param to "on" at my
slave node for the _only_ reason that I would like to make a dump of a
database while replication is running, how would it affect the primary
node performance-wise?

Worth mentioning: I do not require the dumped data to be highly
up-to-date, I just need a static snapshot, knowing perfectly well that
data is updated continuously while dumping.

Are there perhaps other ways to perform a dump of a hot standby node
without affecting the primary node performance and without stopping or
pausing the replication?
Version: postgres (PostgreSQL) 9.2.2

Thank you in advance,


Regards,
Johan


-- 
Johan Thomsen


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Help with namespaces in xpath (PostgreSQL 9.5.3)

[Allan Kamau]

I have an xml document from which I would like to extract the contents of
several elements.

I would like to use xpath to extract the contents of "name" from the xml
document shown below.

WITH x AS
(
SELECT
'
http://uniprot.org/uniprot; xmlns:xsi="
http://www.w3.org/2001/XMLSchema-instance; xsi:schemaLocation="
http://uniprot.org/uniprot http://www.uniprot.org/support/docs/uniprot.xsd;>

A0JM59
UBP20_XENTR


'::xml AS d
)
SELECT (xpath('/uniprot/entry/name/text()',a.d))[1]::text AS uniprot_name
FROM
x AS a
;



The documentation for xpath() ("
https://www.postgresql.org/docs/9.5/static/functions-xml.html;) describes
"xpath(xpath, xml [, nsarray]").

For the above xml document, what would be the two dimensional array
"nsarray" for the xpath() function?

-Allan.

[GENERAL] Help needed structuring Postgresql correlation query

[Tim Smith]

Hi,

My postgresql-fu is not good enough to write a query to achieve this
(some may well say r is a better suited tool to achieve this !).

I need to calculate what I would call a correlation window on a time
series of data, my table looks like this :

create table data(data_date date,data_measurement numeric);
insert into data values('2016-01-01',16.23);

insert into data values('2016-06-19',30.54);

My "target sample" would be the N most recent samples in the table
(e.g. 20, the most recent 20 days)

My "potential sample" would be a moving window of size N (the same
size N as above), starting at T0 (i.e. 2016-01-01 in this example) and
incrementing by one (i.e. 2016-01-01 + 20, then 2016-01-02+20 etc),
but the "target sample" would obviously be excluded.

The output needs to display window date range (or at least the start
date of the "potential sample" window) and the result
corr(target,potential).

Hope that makes sense

Tim


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Regression in query optimizer when upgrading from 9.4.5 to 9.5.2?

[Erdmann, Markus @ Bellevue]

Thank you Tom and David for your very helpful replies. We dumped and
restored the RDS staging database on a local installation of pg and were
not able to reproduce the issue in 9.5.2, which led us to try running a
VACUUM ANALYZE and recreating indexes. After this we no longer saw a
discrepancy between the query plan in the separate environments. Is this
what you meant, Tom, by making sure to ANALYZE? Or did you mean including
ANALYZE in EXPLAIN ANALYZE?


On 6/17/16, 11:17 AM, "Tom Lane"  wrote:

>"Erdmann, Markus @ Bellevue"  writes:
>> We¹re trying to debug a performance issue affecting our staging
>>database, and we¹ve narrowed it down to a difference in the query
>>optimizer in 9.5.2. Upgrading to 9.5 is important for us because we need
>>the ability to import foreign schemas.
>
>I think the core of the problem is the large rowcount estimation error
>here:
>
>>   ->  Bitmap Index Scan on
>>transactions_transaction_c69e55a4  (cost=0.00..18.02 rows=161 width=0)
>>(actual time=20.153..20.153 rows=269021 loops=1)
>> Index Cond: ((date_created >=
>>'2010-01-01'::date) AND (date_created <= '2015-12-31'::date))
>
>That's a pretty simple condition and it's hard to believe that 9.5 does it
>much differently than 9.4 did.  Perhaps you forgot to ANALYZE, or were
>using a larger statistics target in the 9.4 installation?
>
>   regards, tom lane



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: R: [GENERAL] Vacuum full: alternatives?

[Melvin Davidson]

On Mon, Jun 20, 2016 at 1:53 PM, Vik Fearing  wrote:

> On 20/06/16 17:25, Melvin Davidson wrote:
> >>And you haven't read Vik's reply. :)
> >
> > Yes I have. Vacuum wll not lock all tables at once, only the ones it is
> > currently working on, so the planner may have a slight delay,
> > but it will not be gigantic.
>
> I think you should try it.
>
> > I have proposed a reasonable solution to solve the problem in it's
> > entirety. Do you have a better one?
>
> You mean by partitioning?  That doesn't really solve any problem, except
> that vacfull-ing a partition should be faster than doing the whole
> enchilada.
> --
> Vik Fearing  +33 6 46 75 15 36
> http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
>

Vik,
Your comments make no sense to me.

>Or any SELECT on the parent at all.  The planner needs to examine the
>CHECK constraints on the children and can't do it if the child is locked
>in ACCESS EXCLUSIVE mode.

Nowhere in the documentation does it say that the planner needs to take
locks
or is even concerned with them. Locks are transient, so they do not figure
into
the query plan. If I am wrong, kindly point me to the documentation or url
that
shows contrary.

>I think you should try it.

Why would I even attempt that? We do not know the PostgreSQL version or O/S
as yet.
I do not have any info regarding table structure or any data. I have given
a suggestion
that will probably help solve the problem. I am not here to do any actual
work.

>That doesn't really solve any problem, except
>that vacfull-ing a partition should be faster than doing the whole
>enchilada.

That is exactly the point, because based on the original problem
description, the
data is transient.

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: R: [GENERAL] Vacuum full: alternatives?

[Vik Fearing]

On 20/06/16 17:25, Melvin Davidson wrote:
>>And you haven't read Vik's reply. :)
>
> Yes I have. Vacuum wll not lock all tables at once, only the ones it is
> currently working on, so the planner may have a slight delay,
> but it will not be gigantic.

I think you should try it.

> I have proposed a reasonable solution to solve the problem in it's
> entirety. Do you have a better one?

You mean by partitioning?  That doesn't really solve any problem, except
that vacfull-ing a partition should be faster than doing the whole
enchilada.
-- 
Vik Fearing  +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Vacuum full: alternatives?

[John R Pierce]

On 6/20/2016 8:51 AM, David G. Johnston wrote:



incorrect.   in fact, an update is performed identically to an
INSERT + DELETE(old)


Except for heap-only-tuple optimization, right?  We cannot build a HOT 
chain if the user requests a delete separately since their is no 
longer an association to trace from the old record.


I suspect this affects free space usage to some degree as well but I 
agree and believe that the reclaimed space is not forbidden to be used 
(I wouldn't rely on my word though and haven't tried to find relevant 
documentation).


yeah, HOT only works on updates that don't modify any indexed fields, 
and only if there's adequate free space in the same block.   If you have 
a update intensive table thats a candidate for HOT, I've been 
recommending setting that table's fill factor to 50-70% prior to 
populating it to leave freespace in every block.




--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Vacuum full: alternatives?

[David G. Johnston]

On Monday, June 20, 2016, John R Pierce  wrote:

> On 6/20/2016 8:03 AM, Scott Mead wrote:
>
>>
>> I believe that free space is only available to UPDATE, not INSERT.
>>
>
> incorrect.   in fact, an update is performed identically to an INSERT +
> DELETE(old)
>
>
Except for heap-only-tuple optimization, right?  We cannot build a HOT
chain if the user requests a delete separately since their is no longer an
association to trace from the old record.

I suspect this affects free space usage to some degree as well but I agree
and believe that the reclaimed space is not forbidden to be used (I
wouldn't rely on my word though and haven't tried to find relevant
documentation).

David J.

Re: R: [GENERAL] Vacuum full: alternatives?

[Jeff Janes]

On Mon, Jun 20, 2016 at 7:23 AM, Martín Marqués  wrote:
> El 20/06/16 a las 09:50, Melvin Davidson escribió:
>>
>>
>>>but it won't let it grow too (or am I missing something).
>>
>> Yes, you are missing something. By partioning and {Vacuum Full only the
>> table with data no longer needed}, the rest of the data remains
>> available to the users
>> AND space is reclaimed by the O/S, so it's the best of both worlds.
>
> That's not entirely true. Think about a SELECT which has to scan all
> child tables.
>

Yes, for the partitioning to be a good option, you would probably have
to arrange it such that you can prove that all tuples in a given
partition are eligible for deletion (or have already been deleted),
and then either truncate or dis-inherit the partition.  That still
requires a stringent lock, but it is only held for a very short time.

> Your are also adding another layer of complexity to the system.

I think that using pg_bulkload adds more complexity to the system than
partitioning would.  I wonder if they really need to use that, or if
they just picked it over COPY because it sounded like a free lunch.

I've just tested pg_bulkload with the default settings, and it
definitely isn't using the fsm to re-use freed space in the table.  If
they use WRITER = BUFFERED it would, though.

Cheers,

Jeff


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Vacuum full: alternatives?

[Chris Ernst]

On 06/20/2016 03:18 AM, Job wrote:
> Hello,
> 
> we have a table with an heavy traffic of pg_bulkload and delete of records.
> The size pass, in only one day, for example for 1Gb to 4Gb and then 1Gb back.
> 
> We have important problems on size and the only way to gain free space is 
> issueing a vacuum full .
> But the operation is very slow, sometimes 2/4 hours, and table is not 
> available for services as it is locked.
> 
> We do not delete everything at one (in this case the truncate woudl resolve 
> the problem).
> 
> The autovacuum is not able (same for normal vacuum) to free the spaces.
> 
> Are there some suggestions or another way to manage this?

Hi Francesco,

We use pg_repack (http://reorg.github.io/pg_repack/) for a similar
workload.  It allows what amounts to an online vacuum full.  The only
caveat is that you need to have the available disk space to fully
rebuild the table in parallel.

Hope that helps.

Cheers!

- Chris


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: R: [GENERAL] Vacuum full: alternatives?

[John R Pierce]

On 6/20/2016 8:03 AM, Scott Mead wrote:


I believe that free space is only available to UPDATE, not INSERT.


incorrect.   in fact, an update is performed identically to an INSERT + 
DELETE(old)



--
john r pierce, recycling bits in santa cruz



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: R: [GENERAL] Vacuum full: alternatives?

[Martín Marqués]

El 20/06/16 a las 11:52, Jeff Janes escribió:
> On Mon, Jun 20, 2016 at 3:13 AM, Andreas Kretschmer
>  wrote:
>>
>>
>> Am 20.06.2016 um 11:43 schrieb Job:
>>>
>>> Hi Andreas,
>>>
 I would suggest run only autovacuum, and with time you will see a not
 more growing table. There is no need for vacuum full.
>>>
>>> So new record, when will be pg_bulkloaded, will replace "marked-free"
>>> location?
>>
>> exactly, that's the task for vacuum
> 
> Are you sure that that is the case with pg_bulkload specifically?  It
> bypasses the shared buffers, so it would not surprise  me if it
> bypasses the free space map as well, and thus always appends its data
> to the end of the table.

I didn't do a super intensive check of pg_bulkload, but AFAICS it does
batches of COPY with PQputCopyData.

If the relation has free space which was reclaimed by vacuum/autovacuum
it will try to use that space and not extend the relation (which is more
expensive). This happens if used space on those pages is lower than the
fillfactor set for that table.

IMO, he should start setting autovacuum more aggressively, or running
aggressive vacuum, and see how that works.

Also, install pgstattuple and check free space on the relation to see
how much dead tuples and free space there is.

Those are my 2 cents.

-- 
Martín Marquéshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: R: [GENERAL] Vacuum full: alternatives?

[Melvin Davidson]

On Mon, Jun 20, 2016 at 11:18 AM, Martín Marqués 
wrote:

> El 20/06/16 a las 12:06, Melvin Davidson escribió:
> >
> > Martin and Vik,
> >
> >>...Think about a SELECT which has to scan all child tables.
> >
> > You are really digging for a corner case.
> > If a scan has to scan all child tables, then
> > A. it negates the ability to make partitions which are not used
> > and
> > B. The SELECT query is poorly crafted.
>
> And you haven't read Vik's reply. :)
>
> --
> Martín Marquéshttp://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>

>And you haven't read Vik's reply. :)
Yes I have. Vacuum wll not lock all tables at once, only the ones it is
currently working on, so the planner may have a slight delay,
but it will not be gigantic.
I have proposed a reasonable solution to solve the problem in it's
entirety. Do you have a better one?

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: R: [GENERAL] Vacuum full: alternatives?

[Martín Marqués]

El 20/06/16 a las 12:06, Melvin Davidson escribió:
>  
> Martin and Vik,
> 
>>...Think about a SELECT which has to scan all child tables.
> 
> You are really digging for a corner case.
> If a scan has to scan all child tables, then
> A. it negates the ability to make partitions which are not used
> and
> B. The SELECT query is poorly crafted.

And you haven't read Vik's reply. :)

-- 
Martín Marquéshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: R: [GENERAL] Vacuum full: alternatives?

[Guillaume Lelarge]

2016-06-20 17:03 GMT+02:00 Scott Mead :

>
>
> On Mon, Jun 20, 2016 at 6:13 AM, Andreas Kretschmer <
> andr...@a-kretschmer.de> wrote:
>
>>
>>
>> Am 20.06.2016 um 11:43 schrieb Job:
>>
>>> Hi Andreas,
>>>
>>> I would suggest run only autovacuum, and with time you will see a not
 more growing table. There is no need for vacuum full.

>>> So new record, when will be pg_bulkloaded, will replace "marked-free"
>>> location?
>>>
>>
>>
>> exactly, that's the task for vacuum
>>
>>
> I believe that free space is only available to UPDATE, not INSERT.
>
>
No, it's available for both.


-- 
Guillaume.
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com

Re: R: [GENERAL] Vacuum full: alternatives?

[Melvin Davidson]

On Mon, Jun 20, 2016 at 11:03 AM, Scott Mead  wrote:

>
>
> On Mon, Jun 20, 2016 at 6:13 AM, Andreas Kretschmer <
> andr...@a-kretschmer.de> wrote:
>
>>
>>
>> Am 20.06.2016 um 11:43 schrieb Job:
>>
>>> Hi Andreas,
>>>
>>> I would suggest run only autovacuum, and with time you will see a not
 more growing table. There is no need for vacuum full.

>>> So new record, when will be pg_bulkloaded, will replace "marked-free"
>>> location?
>>>
>>
>>
>> exactly, that's the task for vacuum
>>
>>
> I believe that free space is only available to UPDATE, not INSERT.
>
>
>
>>
>>
>> Andreas
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
>
> --
> --
> Scott Mead
> Sr. Architect
> *OpenSCG *
> http://openscg.com
>


Martin and Vik,

>...Think about a SELECT which has to scan all child tables.

You are really digging for a corner case.
If a scan has to scan all child tables, then
A. it negates the ability to make partitions which are not used
and
B. The SELECT query is poorly crafted.

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: R: [GENERAL] Vacuum full: alternatives?

[Scott Mead]

On Mon, Jun 20, 2016 at 6:13 AM, Andreas Kretschmer  wrote:

>
>
> Am 20.06.2016 um 11:43 schrieb Job:
>
>> Hi Andreas,
>>
>> I would suggest run only autovacuum, and with time you will see a not
>>> more growing table. There is no need for vacuum full.
>>>
>> So new record, when will be pg_bulkloaded, will replace "marked-free"
>> location?
>>
>
>
> exactly, that's the task for vacuum
>
>
I believe that free space is only available to UPDATE, not INSERT.



>
>
> Andreas
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
--
Scott Mead
Sr. Architect
*OpenSCG *
http://openscg.com

Re: R: [GENERAL] Vacuum full: alternatives?

[Jeff Janes]

On Mon, Jun 20, 2016 at 3:13 AM, Andreas Kretschmer
 wrote:
>
>
> Am 20.06.2016 um 11:43 schrieb Job:
>>
>> Hi Andreas,
>>
>>> I would suggest run only autovacuum, and with time you will see a not
>>> more growing table. There is no need for vacuum full.
>>
>> So new record, when will be pg_bulkloaded, will replace "marked-free"
>> location?
>
> exactly, that's the task for vacuum

Are you sure that that is the case with pg_bulkload specifically?  It
bypasses the shared buffers, so it would not surprise  me if it
bypasses the free space map as well, and thus always appends its data
to the end of the table.


Cheers,

Jeff


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: R: [GENERAL] Vacuum full: alternatives?

[Martín Marqués]

2016-06-20 11:30 GMT-03:00 Vik Fearing :
> On 20/06/16 16:23, Martín Marqués wrote:
>>
>> That's not entirely true. Think about a SELECT which has to scan all
>> child tables.
>
> Or any SELECT on the parent at all.  The planner needs to examine the
> CHECK constraints on the children and can't do it if the child is locked
> in ACCESS EXCLUSIVE mode.

Yeah, totally skipped my mind that, so partitioning is actually a bad
idea, if that's all they are looking to solve.

Thanks Vik for showing the oversight

-- 
Martín Marquéshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: R: [GENERAL] Vacuum full: alternatives?

[Alex Ignatov]

On 20.06.2016 17:30, Vik Fearing wrote:

On 20/06/16 16:23, Martín Marqués wrote:

El 20/06/16 a las 09:50, Melvin Davidson escribió:



but it won't let it grow too (or am I missing something).

Yes, you are missing something. By partioning and {Vacuum Full only the
table with data no longer needed}, the rest of the data remains
available to the users
AND space is reclaimed by the O/S, so it's the best of both worlds.

That's not entirely true. Think about a SELECT which has to scan all
child tables.

Or any SELECT on the parent at all.  The planner needs to examine the
CHECK constraints on the children and can't do it if the child is locked
in ACCESS EXCLUSIVE mode.

+1


Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: R: [GENERAL] Vacuum full: alternatives?

[Vik Fearing]

On 20/06/16 16:23, Martín Marqués wrote:
> El 20/06/16 a las 09:50, Melvin Davidson escribió:
>>
>>
>>> but it won't let it grow too (or am I missing something).
>>
>> Yes, you are missing something. By partioning and {Vacuum Full only the
>> table with data no longer needed}, the rest of the data remains
>> available to the users
>> AND space is reclaimed by the O/S, so it's the best of both worlds.
> 
> That's not entirely true. Think about a SELECT which has to scan all
> child tables.

Or any SELECT on the parent at all.  The planner needs to examine the
CHECK constraints on the children and can't do it if the child is locked
in ACCESS EXCLUSIVE mode.
-- 
Vik Fearing  +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: R: [GENERAL] Vacuum full: alternatives?

[Martín Marqués]

El 20/06/16 a las 09:50, Melvin Davidson escribió:
> 
> 
>>but it won't let it grow too (or am I missing something).
> 
> Yes, you are missing something. By partioning and {Vacuum Full only the
> table with data no longer needed}, the rest of the data remains
> available to the users
> AND space is reclaimed by the O/S, so it's the best of both worlds.

That's not entirely true. Think about a SELECT which has to scan all
child tables.

Your are also adding another layer of complexity to the system.

-- 
Martín Marquéshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

[Greg Navis]

Artur, thanks for help. I managed to add the new strategy to the index.
Hurray! I also discovered a bug in the process that I reported via the form.

I still have a few questions:

1. Naming - pg_trgm_match, match, threshold, trgm_check_match,
ThresholdStrategyNumber - are these good names?
2. I made trgm_check_match IMMUTABLE. Are there any other modifies that
should be there?
3. I defined % (text, pg_trgm_match) but didn't provide a commutator and
other helper procedures. Which of them should I implement?
4. Can I obtain query and nlimit with less code?
5. The attached patch replaced "res = (*(int *)  == *(int *) 
|| tmpsml > nlimit);" with "res = (tmpsml >= nlimit);" to fix the bug on my
machine. I'm not sure whether that's the long-term fix we want to have.
It's just there to help me make progress with trigrams.

Thanks for help.

Cheers
Greg
ᐧ
diff --git a/contrib/pg_trgm/pg_trgm--1.3.sql b/contrib/pg_trgm/pg_trgm--1.3.sql
index b279f7d..faa1fce 100644
--- a/contrib/pg_trgm/pg_trgm--1.3.sql
+++ b/contrib/pg_trgm/pg_trgm--1.3.sql
@@ -3,6 +3,8 @@
 -- complain if script is sourced in psql, rather than via CREATE EXTENSION
 \echo Use "CREATE EXTENSION pg_trgm" to load this file. \quit
 
+CREATE TYPE pg_trgm_match AS (match TEXT, threshold REAL);
+
 -- Deprecated function
 CREATE FUNCTION set_limit(float4)
 RETURNS float4
@@ -108,6 +110,18 @@ CREATE OPERATOR <->> (
 COMMUTATOR = '<<->'
 );
 
+CREATE OR REPLACE FUNCTION trgm_check_match(string TEXT, match pg_trgm_match) RETURNS bool AS $$
+BEGIN
+RETURN similarity(match.match, string) >= match.threshold;
+END;
+$$ LANGUAGE plpgsql IMMUTABLE;
+
+CREATE OPERATOR %(
+	leftarg = text,
+	rightarg = pg_trgm_match,
+	procedure = trgm_check_match
+);
+
 -- gist key
 CREATE FUNCTION gtrgm_in(cstring)
 RETURNS gtrgm
@@ -126,7 +140,7 @@ CREATE TYPE gtrgm (
 );
 
 -- support functions for gist
-CREATE FUNCTION gtrgm_consistent(internal,text,smallint,oid,internal)
+CREATE FUNCTION gtrgm_consistent(internal,anynonarray,smallint,oid,internal)
 RETURNS bool
 AS 'MODULE_PATHNAME'
 LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
@@ -171,7 +185,7 @@ CREATE OPERATOR CLASS gist_trgm_ops
 FOR TYPE text USING gist
 AS
 OPERATOR1   % (text, text),
-FUNCTION1   gtrgm_consistent (internal, text, smallint, oid, internal),
+FUNCTION1   gtrgm_consistent (internal, anynonarray, smallint, oid, internal),
 FUNCTION2   gtrgm_union (internal, internal),
 FUNCTION3   gtrgm_compress (internal),
 FUNCTION4   gtrgm_decompress (internal),
@@ -252,3 +266,6 @@ LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
 ALTER OPERATOR FAMILY gin_trgm_ops USING gin ADD
 OPERATOR7   %> (text, text),
 FUNCTION6  (text,text) gin_trgm_triconsistent (internal, int2, text, int4, internal, internal, internal);
+
+ALTER OPERATOR FAMILY gist_trgm_ops USING gist ADD
+OPERATOR9   % (text, pg_trgm_match);
diff --git a/contrib/pg_trgm/trgm.h b/contrib/pg_trgm/trgm.h
index 8cd88e7..f2b6008 100644
--- a/contrib/pg_trgm/trgm.h
+++ b/contrib/pg_trgm/trgm.h
@@ -34,6 +34,7 @@
 #define RegExpICaseStrategyNumber		6
 #define WordSimilarityStrategyNumber	7
 #define WordDistanceStrategyNumber		8
+#define ThresholdStrategyNumber			9
 
 typedef char trgm[3];
 
diff --git a/contrib/pg_trgm/trgm_gist.c b/contrib/pg_trgm/trgm_gist.c
index 3a5aff9..3884b13 100644
--- a/contrib/pg_trgm/trgm_gist.c
+++ b/contrib/pg_trgm/trgm_gist.c
@@ -5,7 +5,10 @@
 
 #include "trgm.h"
 
+#include "access/htup.h"
+#include "access/htup_details.h"
 #include "access/stratnum.h"
+#include "utils/typcache.h"
 #include "fmgr.h"
 
 
@@ -181,7 +184,7 @@ Datum
 gtrgm_consistent(PG_FUNCTION_ARGS)
 {
 	GISTENTRY  *entry = (GISTENTRY *) PG_GETARG_POINTER(0);
-	text	   *query = PG_GETARG_TEXT_P(1);
+	text	   *query;
 	StrategyNumber strategy = (StrategyNumber) PG_GETARG_UINT16(2);
 
 	/* Oid		subtype = PG_GETARG_OID(3); */
@@ -189,10 +192,43 @@ gtrgm_consistent(PG_FUNCTION_ARGS)
 	TRGM	   *key = (TRGM *) DatumGetPointer(entry->key);
 	TRGM	   *qtrg;
 	bool		res;
-	Size		querysize = VARSIZE(query);
+	Size		querysize;
 	gtrgm_consistent_cache *cache;
 	double		nlimit;
 
+	HeapTupleHeader	query_match;
+	OidtupType;
+	int32			tupTypmod;
+	TupleDesc		tupdesc;
+	HeapTupleData	tuple;
+	bool			isnull;
+
+	if (strategy == ThresholdStrategyNumber)
+	{
+		query_match = PG_GETARG_HEAPTUPLEHEADER(1);
+		tupType = HeapTupleHeaderGetTypeId(query_match);
+		tupTypmod = HeapTupleHeaderGetTypMod(query_match);
+		tupdesc = lookup_rowtype_tupdesc(tupType, tupTypmod);
+
+		tuple.t_len = HeapTupleHeaderGetDatumLength(query_match);
+		ItemPointerSetInvalid(&(tuple.t_self));
+		tuple.t_tableOid = InvalidOid;
+		tuple.t_data = query_match;
+
+		query = DatumGetTextP(fastgetattr(, 1, tupdesc, ));
+		querysize = VARSIZE(query);
+		nlimit = DatumGetFloat4(fastgetattr(, 2, tupdesc, ));
+
+		

Re: R: [GENERAL] Vacuum full: alternatives?

[Adarsh Sharma]

On Mon, Jun 20, 2016 at 6:20 PM, Melvin Davidson 
wrote:

>
> On Mon, Jun 20, 2016 at 8:29 AM, Rakesh Kumar 
> wrote:
>
>> But then autovaccum avoids this. Granted it won't give back free space to
>> OS, but it won't let it grow too (or am I missing something).
>>
>>
>> --
>> *From:* Job 
>> *To:* Rakesh Kumar ; "
>> pgsql-general@postgresql.org" 
>> *Sent:* Monday, June 20, 2016 5:39 AM
>> *Subject:* R: [GENERAL] Vacuum full: alternatives?
>>
>> Hi Rakesh,
>>
>> if i do not free disk space, after some days disk can become full.
>> Everyday we have a lot of pg_bulkload and delete.
>>
>> Thank you!
>> Francesco
>>
>> --
>> *Da:* pgsql-general-ow...@postgresql.org [
>> pgsql-general-ow...@postgresql.org] per conto di Rakesh Kumar [
>> rakeshkumar46...@gmail.com]
>> *Inviato:* lunedì 20 giugno 2016 11.34
>> *A:* pgsql-general@postgresql.org
>> *Oggetto:* Re: [GENERAL] Vacuum full: alternatives?
>>
>> Any reason why you need the space back? What is wrong with space
>> remaining constant at 4GB.
>>
>>
>> --
>> *From:* Job 
>> *To:* "pgsql-general@postgresql.org" 
>> *Sent:* Monday, June 20, 2016 5:18 AM
>> *Subject:* [GENERAL] Vacuum full: alternatives?
>>
>> Hello,
>>
>> we have a table with an heavy traffic of pg_bulkload and delete of
>> records.
>> The size pass, in only one day, for example for 1Gb to 4Gb and then 1Gb
>> back.
>>
>> We have important problems on size and the only way to gain free space is
>> issueing a vacuum full .
>> But the operation is very slow, sometimes 2/4 hours, and table is not
>> available for services as it is locked.
>>
>> We do not delete everything at one (in this case the truncate woudl
>> resolve the problem).
>>
>> The autovacuum is not able (same for normal vacuum) to free the spaces.
>>
>> Are there some suggestions or another way to manage this?
>>
>> Thank you!
>> Francesco
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>>
>>
>>
> > but it won't let it grow too (or am I missing something).
>
> Yes, you are missing something. By partioning and {Vacuum Full only the
> table with data no longer needed}, the rest of the data remains available
> to the users
> AND space is reclaimed by the O/S, so it's the best of both worlds.
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>


---

Few things you can try :

1. Partition your table daily
2. Tune your autovacuum parameters if you think autovacuum is not keeping
up with the fragmentation speed. fore.g :

alter table table_name set (autovacuum_enabled=true,
autovacuum_vacuum_threshold=5000, autovacuum_analyze_threshold=5000,
autovacuum_vacuum_scale_factor=0.1, autovacuum_analyze_scale_factor=0.2);

Read this before tuning :
https://www.postgresql.org/docs/9.1/static/runtime-config-autovacuum.html

3. If you can recreate/alter your table, create/alter with a fillfactor of
20 so that your deleted rows resides in the same page.It might use extra
space but you will face less fragmentation problems.
link : https://www.postgresql.org/docs/8.3/static/sql-createtable.html

However, i have faced one problem in past where we have streaming
replication setup of one master and 4 slaves. After all these tunings ,
autovacuum is not able to remove dead tuples and queries are getting slower
and slower.
After stopping all applications and streaming replicated slaves, i was able
to defrag the table properly. The doc says autovacuum will not remove any
dead tuples if it has any reference to those  dead tuples anywhere but i am
not sure how to find those dead tuples which are still being referenced :)

Thanks,
Adarsh Sharma

Re: R: [GENERAL] Vacuum full: alternatives?

[Melvin Davidson]

On Mon, Jun 20, 2016 at 8:29 AM, Rakesh Kumar 
wrote:

> But then autovaccum avoids this. Granted it won't give back free space to
> OS, but it won't let it grow too (or am I missing something).
>
>
> --
> *From:* Job 
> *To:* Rakesh Kumar ; "
> pgsql-general@postgresql.org" 
> *Sent:* Monday, June 20, 2016 5:39 AM
> *Subject:* R: [GENERAL] Vacuum full: alternatives?
>
> Hi Rakesh,
>
> if i do not free disk space, after some days disk can become full.
> Everyday we have a lot of pg_bulkload and delete.
>
> Thank you!
> Francesco
>
> --
> *Da:* pgsql-general-ow...@postgresql.org [
> pgsql-general-ow...@postgresql.org] per conto di Rakesh Kumar [
> rakeshkumar46...@gmail.com]
> *Inviato:* lunedì 20 giugno 2016 11.34
> *A:* pgsql-general@postgresql.org
> *Oggetto:* Re: [GENERAL] Vacuum full: alternatives?
>
> Any reason why you need the space back? What is wrong with space remaining
> constant at 4GB.
>
>
> --
> *From:* Job 
> *To:* "pgsql-general@postgresql.org" 
> *Sent:* Monday, June 20, 2016 5:18 AM
> *Subject:* [GENERAL] Vacuum full: alternatives?
>
> Hello,
>
> we have a table with an heavy traffic of pg_bulkload and delete of records.
> The size pass, in only one day, for example for 1Gb to 4Gb and then 1Gb
> back.
>
> We have important problems on size and the only way to gain free space is
> issueing a vacuum full .
> But the operation is very slow, sometimes 2/4 hours, and table is not
> available for services as it is locked.
>
> We do not delete everything at one (in this case the truncate woudl
> resolve the problem).
>
> The autovacuum is not able (same for normal vacuum) to free the spaces.
>
> Are there some suggestions or another way to manage this?
>
> Thank you!
> Francesco
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>
>
>
> but it won't let it grow too (or am I missing something).

Yes, you are missing something. By partioning and {Vacuum Full only the
table with data no longer needed}, the rest of the data remains available
to the users
AND space is reclaimed by the O/S, so it's the best of both worlds.

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: R: [GENERAL] Vacuum full: alternatives?

[Rakesh Kumar]

But then autovaccum avoids this. Granted it won't give back free space to OS, 
but it won't let it grow too (or am I missing something).

  From: Job 
 To: Rakesh Kumar ; "pgsql-general@postgresql.org" 
 
 Sent: Monday, June 20, 2016 5:39 AM
 Subject: R: [GENERAL] Vacuum full: alternatives?
   
#yiv6783361115 #yiv6783361115 --P 
{MARGIN-BOTTOM:0px;MARGIN-TOP:0px;}#yiv6783361115 Hi Rakesh, if i do not free 
disk space, after some days disk can become full.Everyday we have a lot of 
pg_bulkload and delete. Thank you!
Francesco Da: pgsql-general-ow...@postgresql.org 
[pgsql-general-ow...@postgresql.org] per conto di Rakesh Kumar 
[rakeshkumar46...@gmail.com]
Inviato: lunedì 20 giugno 2016 11.34
A: pgsql-general@postgresql.org
Oggetto: Re: [GENERAL] Vacuum full: alternatives?

Any reason why you need the space back? What is wrong with space remaining 
constant at 4GB.

From: Job 
To: "pgsql-general@postgresql.org" 
Sent: Monday, June 20, 2016 5:18 AM
Subject: [GENERAL] Vacuum full: alternatives?

Hello, 

we have a table with an heavy traffic of pg_bulkload and delete of records.
The size pass, in only one day, for example for 1Gb to 4Gb and then 1Gb back.

We have important problems on size and the only way to gain free space is 
issueing a vacuum full .
But the operation is very slow, sometimes 2/4 hours, and table is not available 
for services as it is locked.

We do not delete everything at one (in this case the truncate woudl resolve the 
problem).

The autovacuum is not able (same for normal vacuum) to free the spaces.

Are there some suggestions or another way to manage this?

Thank you!
Francesco

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



  

Re: R: [GENERAL] Vacuum full: alternatives?

[Melvin Davidson]

On Mon, Jun 20, 2016 at 6:13 AM, Andreas Kretschmer  wrote:

>
>
> Am 20.06.2016 um 11:43 schrieb Job:
>
>> Hi Andreas,
>>
>> I would suggest run only autovacuum, and with time you will see a not
>>> more growing table. There is no need for vacuum full.
>>>
>> So new record, when will be pg_bulkloaded, will replace "marked-free"
>> location?
>>
>
>
> exactly, that's the task for vacuum
>
>
>
> Andreas
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


>We do not delete everything at one (in this case the truncate woudl
resolve the problem).

Please, it is very important you provide PostgreSQL version & O/S, as
improvements to VACUUM may play a role here.

Is there any reason you cannot partition the table? Moving the data to
separate partitions
(based on a date or key field) will allow you to vacuum full only 1
partition at a time.

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: R: [GENERAL] Vacuum full: alternatives?

[Andreas Kretschmer]

Am 20.06.2016 um 11:43 schrieb Job:

Hi Andreas,


I would suggest run only autovacuum, and with time you will see a not
more growing table. There is no need for vacuum full.

So new record, when will be pg_bulkloaded, will replace "marked-free" location?



exactly, that's the task for vacuum


Andreas


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: R: [GENERAL] Vacuum full: alternatives?

[Robert Wysocki]

On Mon, 2016-06-20 at 11:43 +0200, Job wrote:
> Hi Andreas,
> 
> >I would suggest run only autovacuum, and with time you will see a not 
> >more growing table. There is no need for vacuum full.
> 
> So new record, when will be pg_bulkloaded, will replace "marked-free" 
> location?

Yes, but you may have to make autovacuum process more aggressive. Even
then it might not cope with the frequency of your bulk I/U/D.

There are many tools to use instead of VACUUM FULL though, have a look
at pg_reorg and pgcompact for example. Do not be afraid to use an
awesome tool called Google as well ;-) (This is like the very basic
problem everyone asks about, so you'll find many more in-depth answers
and articles; the phrase you want to google for is "postgresql bloat")

Cheers,
R.


NET-A-PORTER.COM



CONFIDENTIALITY NOTICE
The information in this email is confidential and is intended solely for the 
addressee. Access to this email by anyone else is unauthorised. If you are not 
the intended recipient, you must not read, use or disseminate the information. 
Any views expressed in this message are those of the individual sender, except 
where the sender specifically states them to be the views of Net-A-Porter Group 
Limited. 

The Net-A-Porter Group Limited is a company registered in England & Wales 
Number: 3820604 Registered Office: 1 The Village Offices, Westfield, Ariel Way, 
London, W12 7GF

R: [GENERAL] Vacuum full: alternatives?

[Job]

Hi Andreas,

>I would suggest run only autovacuum, and with time you will see a not 
>more growing table. There is no need for vacuum full.

So new record, when will be pg_bulkloaded, will replace "marked-free" location?

Thank you!
Francesco



Da: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] per 
conto di Andreas Kretschmer [andr...@a-kretschmer.de]
Inviato: lunedì 20 giugno 2016 11.37
A: pgsql-general@postgresql.org
Oggetto: Re: [GENERAL] Vacuum full: alternatives?

Am 20.06.2016 um 11:18 schrieb Job:
> Hello,
>
> we have a table with an heavy traffic of pg_bulkload and delete of records.
> The size pass, in only one day, for example for 1Gb to 4Gb and then 1Gb back.
>
> We have important problems on size and the only way to gain free space is 
> issueing a vacuum full .
> But the operation is very slow, sometimes 2/4 hours, and table is not 
> available for services as it is locked.
>
> We do not delete everything at one (in this case the truncate woudl resolve 
> the problem).
>
> The autovacuum is not able (same for normal vacuum) to free the spaces.
>

autovaccum marks space as free, but don't give the space back to os.

I would suggest run only autovacuum, and with time you will see a not
more growing table. There is no need for vacuum full.

Andreas


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

R: [GENERAL] Vacuum full: alternatives?

[Job]

Hi Rakesh,

if i do not free disk space, after some days disk can become full.
Everyday we have a lot of pg_bulkload and delete.

Thank you!
Francesco


Da: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] per 
conto di Rakesh Kumar [rakeshkumar46...@gmail.com]
Inviato: lunedì 20 giugno 2016 11.34
A: pgsql-general@postgresql.org
Oggetto: Re: [GENERAL] Vacuum full: alternatives?

Any reason why you need the space back? What is wrong with space remaining 
constant at 4GB.



From: Job 
To: "pgsql-general@postgresql.org" 
Sent: Monday, June 20, 2016 5:18 AM
Subject: [GENERAL] Vacuum full: alternatives?

Hello,

we have a table with an heavy traffic of pg_bulkload and delete of records.
The size pass, in only one day, for example for 1Gb to 4Gb and then 1Gb back.

We have important problems on size and the only way to gain free space is 
issueing a vacuum full .
But the operation is very slow, sometimes 2/4 hours, and table is not available 
for services as it is locked.

We do not delete everything at one (in this case the truncate woudl resolve the 
problem).

The autovacuum is not able (same for normal vacuum) to free the spaces.

Are there some suggestions or another way to manage this?

Thank you!
Francesco

--
Sent via pgsql-general mailing list 
(pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Vacuum full: alternatives?

[Andreas Kretschmer]

Am 20.06.2016 um 11:18 schrieb Job:

Hello,

we have a table with an heavy traffic of pg_bulkload and delete of records.
The size pass, in only one day, for example for 1Gb to 4Gb and then 1Gb back.

We have important problems on size and the only way to gain free space is issueing a 
vacuum full .
But the operation is very slow, sometimes 2/4 hours, and table is not available 
for services as it is locked.

We do not delete everything at one (in this case the truncate woudl resolve the 
problem).

The autovacuum is not able (same for normal vacuum) to free the spaces.



autovaccum marks space as free, but don't give the space back to os.

I would suggest run only autovacuum, and with time you will see a not 
more growing table. There is no need for vacuum full.


Andreas


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Vacuum full: alternatives?

[Rakesh Kumar]

Any reason why you need the space back? What is wrong with space remaining 
constant at 4GB.

  From: Job 
 To: "pgsql-general@postgresql.org"  
 Sent: Monday, June 20, 2016 5:18 AM
 Subject: [GENERAL] Vacuum full: alternatives?
   
Hello, 

we have a table with an heavy traffic of pg_bulkload and delete of records.
The size pass, in only one day, for example for 1Gb to 4Gb and then 1Gb back.

We have important problems on size and the only way to gain free space is 
issueing a vacuum full .
But the operation is very slow, sometimes 2/4 hours, and table is not available 
for services as it is locked.

We do not delete everything at one (in this case the truncate woudl resolve the 
problem).

The autovacuum is not able (same for normal vacuum) to free the spaces.

Are there some suggestions or another way to manage this?

Thank you!
Francesco

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

  

[GENERAL] Vacuum full: alternatives?

[Job]

Hello,

we have a table with an heavy traffic of pg_bulkload and delete of records.
The size pass, in only one day, for example for 1Gb to 4Gb and then 1Gb back.

We have important problems on size and the only way to gain free space is 
issueing a vacuum full .
But the operation is very slow, sometimes 2/4 hours, and table is not available 
for services as it is locked.

We do not delete everything at one (in this case the truncate woudl resolve the 
problem).

The autovacuum is not able (same for normal vacuum) to free the spaces.

Are there some suggestions or another way to manage this?

Thank you!
Francesco

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general