Hi!
I recently switched from Oracle SQL to PostgreSQL.
In Oracle, I can easily delete a table or view that is used by existing
views. The system marks the affected views. I can then re-define the
deleted table or view and have all dependent views easily re-compiled. Done.
PostgreSQL instead is p
Hi,
We upgraded to 13 a couple of months ago on production but are still having an
issue with bloated partial indexes which have an impact on our partial queries
especially towards the end of a quarter when our quarterly-partitioned tables
are getting big. I have built 14 (on macOS catalina, 1
We have change autovacuum so that it runs more frequently
autovacuum_vacuum_scale_factor=0.035, the reason we have a partial index on the
status is that in a table of 300 million entries, only about 100 or so would
have status=‘IN_PROGRESS’ so we think this should be a nice small index and
many
Have you tried setting autovacuum to run quite aggressively, perhaps just
on this table? Have you tried an index on the status column only, rather
than partial?
Tom Dearman writes:
> We have change autovacuum so that it runs more frequently
> autovacuum_vacuum_scale_factor=0.035, the reason we have a partial index on
> the status is that in a table of 300 million entries, only about 100 or so
> would have status=‘IN_PROGRESS’ so we think this should be
Hi Alvaro, glad to hear from you!
This database is relatevely young, it was initdb'ed about a year ago or so and
it was initially at 10.x. I don't know the exact minor version but the major
version was 10 for sure.
The problematic row is actually visible:
SELECT COUNT(*) FROM pg_proc WHER
Tom-Tom:
On Fri, Jul 16, 2021 at 5:43 PM Tom Lane wrote:
> Tom Dearman writes:
> > We have change autovacuum so that it runs more frequently
> > autovacuum_vacuum_scale_factor=0.035, the reason we have a partial index on
> > the status is that in a table of 300 million entries, only about 100
Other indexes do bloat, but the percentage bloat is a lot less, presumably
because this is a partial index where the partial column has a high degree of
changes ie maybe 100 genuinely ‘live’ rows in a table of 300 million where
every row has gone through a state where it would have been in the i
On Fri, 2021-07-16 at 14:42 +0200, Markhof, Ingolf wrote:
> In Oracle, I can easily delete a table or view that is used by existing views.
> The system marks the affected views. I can then re-define the deleted table
> or
> view and have all dependent views easily re-compiled. Done.
... or you
This stored procedure ...
create or replace function validate_proj_csv (proj_csv varchar)
returns int
language plpgsql
as
$$
-- This function used in a check constraint in the public.projects table to
ensure that
-- all projects in column sibling_project_csv are valid projects.
DECLARE
proj_ar
On 7/16/21 3:26 PM, David Gauthier wrote:
This stored procedure ...
dvdb=# alter table projects add constraint validate_sibling_project_csv
check (validate_proj_csv(sibling_project_csv) = 0);
ERROR: upper bound of FOR loop cannot be null
CONTEXT: PL/pgSQL function validate_proj_csv(characte
On Fri, Jul 16, 2021 at 9:19 AM Tom Dearman wrote:
> Other indexes do bloat, but the percentage bloat is a lot less
I have to imagine that the remaining problems have a lot to do with
the fact that this is a partial index -- the partial index naturally
gets vacuumed much less frequently than what
On Fri, Jul 16, 2021 at 3:26 PM David Gauthier
wrote:
> This stored procedure ...
>
> create or replace function validate_proj_csv (proj_csv varchar)
>
It is a function - I don't think you can used stored procedures in check
constraints...
>
> dvdb=# alter table projects add constraint validat
13 matches
Mail list logo
