Questions tagged [online-operations]

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

Filter by
Sorted by
Tagged with
0 votes
0 answers
27 views

pg_online_schema_change isn't working well with partitioned table

I'm trying out/using pg_online_schema_change to make schema changes online for postgresql. when I inserted some data on my partitioned table and after that just quickly ran pgosc on that partitioned ...
user avatar
  • 326
1 vote
1 answer
281 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 ...
user avatar
  • 195
1 vote
1 answer
169 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;...
user avatar
2 votes
2 answers
193 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 ...
user avatar
  • 243
3 votes
1 answer
391 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 ...
user avatar
  • 243
0 votes
1 answer
105 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-...
user avatar
  • 326
6 votes
3 answers
660 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 ...
user avatar
  • 446
0 votes
0 answers
539 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 ...
user avatar
0 votes
1 answer
73 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 ...
user avatar
  • 446
0 votes
1 answer
306 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 ...
user avatar
1 vote
0 answers
277 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 ...
user avatar
2 votes
2 answers
255 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 ...
user avatar
  • 25.6k
2 votes
0 answers
515 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 ...
user avatar
3 votes
1 answer
257 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 ...
user avatar
18 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 ...
user avatar
  • 437
1 vote
0 answers
53 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 (...
user avatar
  • 111
5 votes
0 answers
2k 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 ...
user avatar
  • 151
7 votes
2 answers
9k 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 ...
user avatar
  • 25.6k
11 votes
1 answer
16k 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 ...
user avatar
8 votes
1 answer
833 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 ...
user avatar
  • 340
28 votes
3 answers
21k 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 ...
user avatar
7 votes
0 answers
755 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 ...
user avatar
  • 12.5k
3 votes
2 answers
668 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 ...
user avatar
1 vote
0 answers
83 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:/...
user avatar
7 votes
1 answer
79 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....
user avatar
  • 2,404
2 votes
1 answer
126 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 ...
user avatar
1 vote
1 answer
657 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 ...
user avatar
  • 2,404
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 ...
user avatar
  • 173