--- title: "Create indexes and track the progress" url: "https://docs.yugabyte.com/stable/explore/ysql-language-features/indexes-constraints/index-backfill/" --- # Create indexes and track the progress Understand how to create indexes without affecting ongoing queries Understand how YugabyteDB creates indexes without affecting ongoing queries YugabyteDB supports [online index backfill](https://github.com/yugabyte/yugabyte-db/blob/master/architecture/design/online-index-backfill.md "online index backfill"), that allows you to build indexes on tables that already have data, without affecting other concurrent writes. YugabyteDB also supports the [CREATE INDEX NONCONCURRENTLY](/stable/api/ysql/the-sql-language/statements/ddl_create_index/#nonconcurrently "CREATE INDEX NONCONCURRENTLY") statement to disable online index backfill. Online index backfill is enabled by default. ## Tracking index creation The current state of an index backfill can be viewed by executing the `pg_stat_progress_create_index` view to report the progress of the CREATE INDEX command execution. The view contains one row for each backend connection that is currently running a CREATE INDEX command, and the row entry is cleared after the completion of the command execution. The `pg_stat_progress_create_index` view can provide the following details: - Number of rows processed during an index backfill. - The current phase of the command is either `initializing` or `backfilling`. - Index progress report for all the different configurations of an index or index build such as non-concurrent index builds, GIN indexes, partial indexes, and include indexes. Columns such as `lockers_total`, `lockers_done`, `current_locker_pid`, `blocks_total`, and `blocks_done` in the `pg_stat_progress_create_index` view do not apply to YugabyteDB and always have null values. ## Setup Before you start The examples will run on any YugabyteDB universe. To create and connect to a universe, see [Set up YugabyteDB universe](/stable/explore/cluster-setup-local/). 1. From your local YugabyteDB installation directory, create an index on an existing table as follows: ```sql CREATE TABLE test(id int); ``` 2. Populate the table with some data. ```sql INSERT INTO test(id) SELECT n FROM generate_series(1,1000000) AS n; ``` 3. On a separate parallel YSQL connection on the same node, select from the view to see the progress of the command in a repeated fashion with `\watch 1` as follows: ```sql SELECT tbl.relname as tblname, idx.relname as indexname, command, phase, tuples_total, tuples_done FROM pg_stat_progress_create_index INNER JOIN pg_class as tbl on tbl.oid = relid INNER JOIN pg_class as idx on idx.oid = index_relid where tbl.relname = 'test'; \watch 1 ``` Initially, you will see an empty output like this. ```caddyfile tblname | indexname | command | phase | tuples_total | tuples_done ---------+-----------+---------+-------+--------------+------------- ``` 4. Now create an index on the `id` column. ```sql CREATE INDEX idx_id ON test(id); ``` 5. On the other terminal, you will see the progress on the index creation first with the first phase, `initializing` as: ```caddyfile tblname | indexname | command | phase | tuples_total | tuples_done ---------+-----------+---------------------------+--------------+--------------+------------- test | idx_id | CREATE INDEX CONCURRENTLY | initializing | 0 | 0 ``` And then you will see the index backfilling happen as: ```caddyfile tblname | indexname | command | phase | tuples_total | tuples_done ---------+-----------+---------------------------+-------------+--------------+------------- test | idx_id | CREATE INDEX CONCURRENTLY | backfilling | 0 | 0 ``` You will see the `tuples_done` count increasing as the backfilling progresses. When the backfilling is done, you will see the `tuples_done` count to be updated correctly. ```caddyfile tblname | indexname | command | phase | tuples_total | tuples_done ---------+-----------+---------------------------+-------------+--------------+------------- test | idx_id | CREATE INDEX CONCURRENTLY | backfilling | 0 | 1000000 ``` ## Memory usage Backfilling consumes some amount of memory. The memory consumption is directly proportional to the data size per-row, the number of write operations batched together, and the number of parallel backfills. You can view the approximate memory usage by executing the following SQL statement: ```sql SELECT indexname, tablet, now()-backend_start started, pg_size_pretty(allocated_mem_bytes) mem, pg_size_pretty(rss_mem_bytes) rss FROM ( SELECT allocated_mem_bytes, rss_mem_bytes, backend_start, query_start, query ,regexp_replace(query,'(BACKFILL INDEX) ([0-9]*) .* PARTITION (.*);','\2')::oid AS indexoid ,regexp_replace(query,'(BACKFILL INDEX) ([0-9]*) .* PARTITION (.*);','\3')::text AS tablet FROM pg_stat_activity WHERE query LIKE 'BACKFILL INDEX %' ) pg_stat_activity LEFT OUTER JOIN ( SELECT oid AS indexoid, relname AS indexname FROM pg_class ) pg_class USING(indexoid); ``` This should give you an output similar to the following when an index is being backfilled. ```caddyfile indexname | tablet | started | mem | rss -----------+---------+-----------------+-------+------- idx_id | x'aaaa' | 00:00:04.895382 | 27 MB | 25 MB ``` ## Limitations - In YugabyteDB, the `pg_stat_progress_create_index` view is local; it only has entries for CREATE INDEX commands issued by local YSQL clients. - In PostgreSQL, `tuples_done` and `tuples_total` refer to the tuples of the *index*. However, in YugabyteDB, these fields refer to the tuples of the *indexed table*. This discrepancy is only observed for partial indexes, where the reported progress is less than the actual progress. `tuples_total` is an estimate that is retrieved from `pg_class.reltuples`. - In YugabyteDB, `tuples_done` and `tuples_total` are not displayed (set to null) for temporary indexes. ## Learn more - [Primary keys](/stable/explore/ysql-language-features/indexes-constraints/primary-key-ysql/ "Primary keys") - [Secondary indexes](/stable/explore/ysql-language-features/indexes-constraints/secondary-indexes-ysql/ "Secondary indexes") - [Optimize query performance](/stable/launch-and-manage/monitor-and-alert/query-tuning/ "Optimize query performance")