Tag: concurrently

Waiting for PostgreSQL 14 – Add support for partitioned tables and indexes in REINDEX

On 8th of September 2020, Michael Paquier committed patch:

Add support for partitioned tables and indexes in REINDEX
 
Until now, REINDEX was not able to work with partitioned tables and
indexes, forcing users to reindex partitions one by one.  This extends
REINDEX INDEX and REINDEX TABLE so as they can accept a partitioned
index and table in input, respectively, to reindex all the partitions
assigned to them with physical storage (foreign tables, partitioned
tables and indexes are then discarded).
 
This shares some logic with schema and database REINDEX as each
partition gets processed in its own transaction after building a list of
relations to work on.  This choice has the advantage to minimize the
number of invalid indexes to one partition with REINDEX CONCURRENTLY in
the event a cancellation or failure in-flight, as the only indexes
handled at once in a single REINDEX CONCURRENTLY loop are the ones from
the partition being working on.
 
Isolation tests are added to emulate some cases I bumped into while
developing this feature, particularly with the concurrent drop of a
leaf partition reindexed.  However, this is rather limited as LOCK would
cause REINDEX to block in the first transaction building the list of
partitions.
 
Per its multi-transaction nature, this new flavor cannot run in a
transaction block, similarly to REINDEX SCHEMA, SYSTEM and DATABASE.
 
Author: Justin Pryzby, Michael Paquier
Reviewed-by: Anastasia Lubennikova
Discussion: https://postgr.es/m/db12e897-73ff-467e-94cb-4af03705435f.adger.lj@alibaba-inc.com

Continue reading Waiting for PostgreSQL 14 – Add support for partitioned tables and indexes in REINDEX

Waiting for PostgreSQL 12 – REINDEX CONCURRENTLY

On 29th of March 2019, Peter Eisentraut committed patch:

REINDEX CONCURRENTLY 
 
This adds the CONCURRENTLY option to the REINDEX command.  A REINDEX
CONCURRENTLY on a specific index creates a new index (like CREATE
INDEX CONCURRENTLY), then renames the old index away and the new index
in place and adjusts the dependencies, and then drops the old
index (like DROP INDEX CONCURRENTLY).  The REINDEX command also has
the capability to run its other variants (TABLE, DATABASE) with the
CONCURRENTLY option (but not SYSTEM).
 
The reindexdb command gets the --concurrently option.
 
Author: Michael Paquier, Andreas Karlsson, Peter Eisentraut
 
Discussion: https://www.postgresql.org/message-id/flat/-956b-4478-45ed-%402ndquadrant.com#

Continue reading Waiting for PostgreSQL 12 – REINDEX CONCURRENTLY

Waiting for 9.4 – Add support for REFRESH MATERIALIZED VIEW CONCURRENTLY.

On 16th of July, Kevin Grittner committed patch:

Add support for REFRESH MATERIALIZED VIEW CONCURRENTLY.
 
This allows reads to continue without any blocking while a REFRESH
runs.  The new data appears atomically as part of transaction
commit.
 
Review questioned the Assert that a matview was not a system
relation.  This will be addressed separately.
 
Reviewed by Hitoshi Harada, Robert Haas, Andres Freund.
Merged after review with security patch f3ab5d4.

Continue reading Waiting for 9.4 – Add support for REFRESH MATERIALIZED VIEW CONCURRENTLY.

Concurrent REINDEX of all indexes in database

Recent release of new versions of PostgreSQL suggests that you do reindex of all indexes. But this will take a while, and since we don't actually have ‘REINDEX CONCURRENTLY' command – it's a bit tricky.

So, since I will be doing this on several databases, decided to write a script that will handle the work for me.

Continue reading Concurrent REINDEX of all indexes in database

Waiting for 9.1 – Add UNIQUE/PRIMARY KEY with index

On 25th of January, Tom Lane committed patch:

Implement ALTER TABLE ADD UNIQUE/PRIMARY KEY USING INDEX.
 
This feature allows a UNIQUE OR pkey CONSTRAINT TO be created USING an
already-existing UNIQUE INDEX.  While the CONSTRAINT isn't very
functionally different from the bare index, it's nice TO be able TO do that
FOR documentation purposes.  The main advantage OVER just issuing a plain
ALTER TABLE ADD UNIQUE/PRIMARY KEY IS that the INDEX can be created WITH
CREATE INDEX CONCURRENTLY, so that there IS NOT a long INTERVAL WHERE the
TABLE IS locked against updates.
 
ON the way, refactor SOME OF the code IN DefineIndex() AND index_create()
so that we don't have to pass through those functions in order to create
the index constraint's catalog entries.  Also, IN parse_utilcmd.c, pass
around the ParseState pointer IN struct CreateStmtContext TO save ON
notation, AND ADD error location pointers TO SOME error reports that didn't
have one before.
 
Gurjeet Singh, reviewed by Steve Singer and Tom Lane

Continue reading Waiting for 9.1 – Add UNIQUE/PRIMARY KEY with index