Hi Patrick,
I believe Vacuum full rebuilds the indexes automatically by default, as a
new copy of the table is created.
Because the indexes are new, no stats are available to the optimiser to
make an informed decision about whether to utilise it or not, so it doesn't.
Once the analyze is perform
On Thu, Sep 1, 2016 at 12:10 PM, dandl wrote:
> Sqlite has options to handle an update that causes a duplicate key. Is there
> anything similar in Postgres?
> This is not an UPSERT. The scenario is an UPDATE that changes some key field
> so that there is now a duplicate key. In Sqlite this handled
On Thu, Sep 1, 2016 at 10:32 AM, Patrick B wrote:
>
>
> 2016-09-01 11:53 GMT+12:00 Venkata B Nagothi :
>
>>
>> On Thu, Sep 1, 2016 at 8:41 AM, Patrick B
>> wrote:
>>
>>> Hi guys,
>>>
>>> A dev has ran a VACUUM FULL command into our test database running
>>> PostgreSQL 9.5 (I know... goddamn)
Sqlite has options to handle an update that causes a duplicate key. Is there
anything similar in Postgres?
This is not an UPSERT. The scenario is an UPDATE that changes some key field so
that there is now a duplicate key. In Sqlite this handled as:
UPDATE OR IGNORE table SET
UPDATE OR R
2016-09-01 11:53 GMT+12:00 Venkata B Nagothi :
>
> On Thu, Sep 1, 2016 at 8:41 AM, Patrick B
> wrote:
>
>> Hi guys,
>>
>> A dev has ran a VACUUM FULL command into our test database running
>> PostgreSQL 9.5 (I know... goddamn)...
>>
>> ... after the Vacuum Full, some queries start using SEQ s
On Thu, Sep 1, 2016 at 8:41 AM, Patrick B wrote:
> Hi guys,
>
> A dev has ran a VACUUM FULL command into our test database running
> PostgreSQL 9.5 (I know... goddamn)...
>
> ... after the Vacuum Full, some queries start using SEQ scans instead of
> indexes...
>
> Does that happen because of
Yes that very well could happen because the size of the table changed, as
well as stats being more accurate now. Just because you have a seq scan
doesn't mean the planer is making a bad choice.
On Thu, Sep 1, 2016 at 12:31 AM, Nicolas Grilly
wrote:
> In DB2, it seems possible to define a "clustering index" that determines
> how rows are physically ordered in the "table space" (the heap).
>
> The documentation says: "When a table has a clustering index, an INSERT
> statement causes DB2 t
Hi guys, I posted this question on the ADMIN list but will post here as
well so more people can comment...
https://www.postgresql.org/message-id/CAJNY3it_AfxJhmwMHtpiAbHG47GS5rJOAUgfHw%2BGm5OXCbUm7w%40mail.gmail.com
I've got a 2.3TB Database running at Rackspace... We'll be migrating it to
RDS Pos
Hi guys,
A dev has ran a VACUUM FULL command into our test database running
PostgreSQL 9.5 (I know... goddamn)...
... after the Vacuum Full, some queries start using SEQ scans instead of
indexes...
Does that happen because of the size of the table? The table that I'm
referring to is 150MB bi
On Tue, Aug 30, 2016 at 8:17 PM, Kenneth Marshall wrote:
> We have been using the extension pg_repack to keep a table groomed into
> cluster order. With an appropriate FILLFACTOR to keep updates on the same
> page, it works well. The issue is that it needs space to rebuild the new
> index/table.
On Thu, Sep 1, 2016 at 12:05 AM, Ben Chobot wrote:
> If what they did 3 years ago is similar to what you are trying to do
> today, who cares what they are doing today? (Besides using pg_repack
> instead of pg_reorg, of course.)
>
I'm curious because, in the meantime, Instagram could have stopped
On Aug 31, 2016, at 2:55 PM, Nicolas Grilly wrote:
>
> It looks like Instagram has been using pg_reorg (the ancestor of pg_repack)
> to keep all likes from the same user contiguous on disk, in order to minimize
> disk seeks.
>
> http://instagram-engineering.tumblr.com/post/40781627982/handling
On Tue, Aug 30, 2016 at 8:17 PM, Kenneth Marshall wrote:
> We have been using the extension pg_repack to keep a table groomed into
> cluster order. With an appropriate FILLFACTOR to keep updates on the same
> page, it works well. The issue is that it needs space to rebuild the new
> index/table.
On Wed, Aug 31, 2016 at 6:05 PM, Kenneth Marshall wrote:
> We just run it via cron. In our case, we run it once a day, but depending
> on
> your churn, it could be run once a week or more.
>
Could you provide some numbers: what is the size of the tables or tables
that are repacked? how long does
On Wed, Aug 31, 2016 at 06:06:54PM +0200, Nicolas Grilly wrote:
> On Wed, Aug 31, 2016 at 6:05 PM, Kenneth Marshall wrote:
>
> > We just run it via cron. In our case, we run it once a day, but depending
> > on
> > your churn, it could be run once a week or more.
> >
>
> Could you provide some nu
On Wed, Aug 31, 2016 at 05:23:50PM +0200, Nicolas Grilly wrote:
> On Tue, Aug 30, 2016 at 8:17 PM, Kenneth Marshall wrote:
>
> > We have been using the extension pg_repack to keep a table groomed into
> > cluster order. With an appropriate FILLFACTOR to keep updates on the same
> > page, it works
Mike Sofen wrote:
> For Nicolas’s situation, that would require 10,000 partitions – not very
> useful, and each partition would be very small.
>
This is exactly my conclusion about using partitions in my situation.
> In Postgres, as you mentioned, clustering is a “one time” operation but
> only
Eduardo Morras wrote:
> Check BRIN indexs, they are "designed for handling very large tables in
> which certain columns have some natural correlation with their physical
> location within the table", I think they fit your needs.
Yes, a BRIN index on the tenant ID would be very useful if the row
On Tue, Aug 30, 2016 at 8:17 PM, Kenneth Marshall wrote:
> We have been using the extension pg_repack to keep a table groomed into
> cluster order. With an appropriate FILLFACTOR to keep updates on the same
> page, it works well. The issue is that it needs space to rebuild the new
> index/table.
Hi,
FYI, the RH rpm contains the following comment in postgresql.conf,
which is not in the postgresql.org rpm. I found it helpful.
@@ -61,11 +61,7 @@
# defaults to 'localhost'; use
'*' for all # (change requires restart)
#port = 5432
21 matches
Mail list logo
