Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [online-operations]

Performing physical (e.g. index or DDL) operations with increased concurrency (reduced locking).

Filter by
Sorted by
Tagged with
8 votes
1 answer
688 views

How to add index to a big table with 60M records without downtime?

we have been struggling with one issue in the past few days. We want to add an index to a huge table with 60M records. At first we tried adding it with basic mysql syntax. But it clogged our ...
Rohan's user avatar
  • 33
3 votes
1 answer
267 views

During an online piecemeal restore, is it possible to restore to a point-in-time?

I have a database (full recovery model) with multiple filegroups, that each have full and diff backups. I am able to replicate an online piecemeal restore (SQL Server 2019 Enterprise Edition) as it is ...
Matthias L's user avatar
1 vote
1 answer
709 views

Run pt-online-schema-change with multiple ALTER queries synchronously

I want to run 3 ALTER queries with pt-online-schema-change tool: --alter "ADD INDEX userid_sid_ts_fid (user_id, scorecard_id, timestamp, factor_id), DROP INDEX uidts, RENAME INDEX ...
Diego's user avatar
  • 113
1 vote
1 answer
68 views

Index creation on partitioned table is not completely offline in Standard Edition

We have SQL Server 2019 Standard Edition Microsoft SQL Server 2019 (RTM-CU10) (KB5001090) - 15.0.4123.1 (X64) Mar 22 2021 18:10:24 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit)...
sepupic's user avatar
  • 11k
1 vote
1 answer
2k views

Alter Table Drop Column INPLACE NoLock - until which point is the column being dropped accessible?

I am trying to run MySQL Alter INPLACE command to drop few columns from a very large 90GB table. While the Alter is running I am able to run the Select statement on the same table to ensure that the ...
Ali's user avatar
  • 305
1 vote
1 answer
590 views

Table is full even with innodb_file_per_table

I am trying to create an index to my table using alter query. My my.cnf file innodb_data_home_dir = /usr/local/mysql5/data innodb_data_file_path = ibdata1:60021538816;ibdata2:300M;ibdata3:30000M;...
Ankit Bansal's user avatar
2 votes
2 answers
1k views

Convert postgres varchar[] to text[] without downtime

As I discovered in researching this question, converting a column from varchar(100)[] to text[] requires a table rewrite and rebuilding indexes. Is there a way to manage this conversion without the ...
Felipe's user avatar
  • 305
3 votes
1 answer
2k views

Postgres varchar(100)[] -> text[] column conversion

I need to change a column type from varchar(100)[] to text[]. The table has >100M rows and heavy write load, so I'd like to avoid taking an access exclusive lock and rewriting the index. I saw in ...
Felipe's user avatar
  • 305
0 votes
1 answer
302 views

Online schema changes for cloudsql DB

I'm new to cloudsql for mysql. I have a table with trigger. I need to do some ddl changes on that table. However, I can't take any downtime. What I have tried so far: I tried with pt-online-schema-...
Shiwangini's user avatar
9 votes
3 answers
2k views

Online Index Rebuild of large table takes exclusive lock

I'm trying to rebuild the clustered index of a large table (77GB) on Azure SQL Database. There is high concurrent transactional activity on the table so I'm using the ONLINE=ON option. This has worked ...
Clement's user avatar
  • 536
0 votes
0 answers
971 views

Are online index REBUILD operation possible on every Azure SQL database?

I am trying to find informations about the possibility to operate online index REORGANIZE/REBUILD on a database. What I actually found in the Microsoft documentation is that: Reorganizing an index ...
Alexandre D.'s user avatar
0 votes
1 answer
412 views

How to determine if an ALTER statement will be a metadata-only operation O(1) or a size of data operation O(n)?

We are dealing with very large tables in Sql Azure. Size-of-data operations can lock tables for a very long time so we must be careful to run these only on week ends. Metadata operations are quick ...
Clement's user avatar
  • 536
0 votes
1 answer
643 views

Optimize table on large table online DDL Feature

I am running optimize table on MySQL table size 340GB in size and storage engine is InnoDB. mysql> show variables like '%tmp%'; +----------------------------+------------------+ | Variable_name ...
Adam Mulla's user avatar
1 vote
0 answers
636 views

Online Index Rebuild shows self blocking

I've been doing some testing on rebuilding some larger clustered indexes (with online + sort_in_tempdb on) and I'm observing something that I can't immediately explain. I have a hunch that this is ...
Kris Gruttemeyer's user avatar
2 votes
2 answers
528 views

How to make DDL changes to large tables and cause minimum server and object contention?

I work with a lot of large tables (billions to tens of billions of records in each) in a database I recently inherited. I see a few clear DDL changes that would benefit the use-cases of the database ...
J.D.'s user avatar
  • 37.8k
2 votes
0 answers
949 views

ALTER TABLE ... ADD COLUMN ... NOT NULL is not instant in PostgreSQL 11?

I have read the documentation and it states that adding a column with a non-null and non-volatile default value should not require a table rewrite. However, this does require a rewrite and blocks all ...
Elliot Chance's user avatar
3 votes
1 answer
808 views

Adding column into MySQL table size 150GB

I am trying to add column into MySQL table Table size 150GB Table storage engine= innodb MySQL version=5.6.34 Table have already created primary key(cluster index). More than 2 hours completed and ...
Adam Mulla's user avatar
20 votes
1 answer
2k views

What exactly is the 'online' in OLAP and OLTP?

I'm a bit confused because I'm questioning the definition of 'online' in OLTP and OLAP. I used to think that 'online' here means that we want our answer in bounded time and based on the data available ...
Zeruno's user avatar
  • 497
1 vote
0 answers
67 views

Azure SQL - Modifying Indexed Views without downtime

We have a SQL Azure database with a large indexed view that supports a website with high traffic during business hours. One of our goals from a DevOps perspective to be able to ship software (...
Scott's user avatar
  • 111
8 votes
0 answers
4k views

How do I rename a table with minimal downtime in a production postgres database?

I understand that renaming a table in postgres boils down to a simple catalog update. However, it also requires an ACCESS EXCLUSIVE lock to ensure transactions are safe. What would be the ideal way ...
Ragesh's user avatar
  • 181
7 votes
2 answers
10k views

SQL Server 2017 Enterprise vs Standard - Online Indexing and Online Schema Change

I read through Microsoft's "Editions and supported features of SQL Server 2017" doc to compare the feature differences between Enterprise and Standard edition. Two things not available in Standard ...
J.D.'s user avatar
  • 37.8k
14 votes
1 answer
26k views

Changing foreign key to ON DELETE CASCADE with least amount of impact

I have an existing foreign key that has ON DELETE NO ACTION defined. I need to change this foreign key to ON DELETE CASCADE. I can do this within a transaction: begin; alter table posts drop ...
TheCloudlessSky's user avatar
8 votes
1 answer
962 views

Does RE-BUILDing a HEAP cause downtime?

This is a bit of an embarrassing question, and I can't believe I've missed this for so many years. I have a vendor third-party database that has 401 heap tables. I've recently got into using Brent ...
Philip's user avatar
  • 362
28 votes
3 answers
32k views

Why would I ever prefer ALGORITHM=COPY to ALGORITHM=INPLACE?

Since MySQL 5.6 introduced online DDL, the ALTER TABLE command can optionally have either ALGORITHM=INPLACE or ALGORITHM=COPY specified. The overview of online DDL notes that, by default, INPLACE is ...
Mark Amery's user avatar
  • 1,075
9 votes
0 answers
957 views

How to minimize access exclusive table locks while reducing bloat or ALTER (ing) a table?

In some circumstances, I've been told not to do a VACUUM FULL (or CLUSTER) of a table that is in production, because that will exclusively lock it for longer than wished. The same might apply to ...
joanolo's user avatar
  • 13.5k
3 votes
2 answers
884 views

MySQL Online DDL Index Creation

I have a table with 25 million records and 10 columns on which I am trying to create indexes. According to the following documentation no table copy should occur but when I monitor disk usage it is ...
jayjayjay's user avatar
1 vote
0 answers
89 views

When are UPDATE / INSERT operations processed in Online DDL in MySQL 5.6?

MySQL 5.6 includes functionality called Online DDL which allows ALTER operations to proceed on tables without applying a LOCK to the table (technically referred to as allowing Concurrent DML). https:/...
Garreth McDaid's user avatar
7 votes
1 answer
81 views

Update an Indexed View "Online"

Say I have an indexed view that looks like this: ALTER VIEW dbo.MyIndexedView WITH SCHEMABINDING AS SELECT ord.SomeColumn, COUNT_BIG(*) AS Count FROM dbo.Ordered ord WHERE ord....
Vaccano's user avatar
  • 2,496
2 votes
1 answer
129 views

onsite and offsite backup question

We have an sql database of 165GB (includes filestreaming) and we do an onsite full backup every evening using veritas. We would like to also have an online backup in place just so that we don't need ...
user79385's user avatar
1 vote
1 answer
861 views

Order of DROP_EXISTING and ONLINE for CREATE INDEX

If I call a CREATE NONCLUSTERED INDEX using DROP_EXISTING = ON and ONLINE = ON what order are things done it? What I want is: New index is made in the TempDB Existing Index is dropped New index is ...
Vaccano's user avatar
  • 2,496
7 votes
2 answers
2k views

Different results rebuilding an index online and offline

I have a non-clustered, non-unique index on a foreign key column of type bigint. When I rebuild the index online, the average fragmentation drops to 3%, with 2 fragments, and 30 pages. When I run ...
RLT's user avatar
  • 173