[HN Gopher] Non blocking and zero downtime ALTER statements in P...
___________________________________________________________________
Non blocking and zero downtime ALTER statements in PostgreSQL with
pg-osc
Author : postgresqlDB
Score : 83 points
Date : 2022-03-06 18:37 UTC (4 hours ago)
(HTM) web link (www.shayon.dev)
(TXT) w3m dump (www.shayon.dev)
| xdfgh1112 wrote:
| Worth noting that this approach uses the external tool technique,
| which makes a copy of the table with triggers.
|
| MySQL and MariaDB both support native online DDL, which makes
| alter statements non-blocking and zero downtime in most cases, in
| even in-place (no whole table data copy) in some cases.
|
| pt-online-schema-change is still useful when you want control on
| when the tables are swapped over.
| evanelias wrote:
| Historically, native online DDL in MySQL 5.6+ / MariaDB 10+
| isn't replication-friendly -- despite being non-blocking on the
| primary, it blocks the replication stream application on
| replicas, which makes it basically unusable at scale. (This is
| inherently a trade-off of MySQL/MariaDB using logical
| replication, instead of physical replication of the tx log /
| WAL.)
|
| The newer INSTANT algo in MySQL 8 and MariaDB 10.3+ solves
| this, but it is only usable for a limited subset of alter
| operations, such as adding a new column. That's one of the most
| common ALTER cases, so this feature is quite nice, but it
| certainly doesn't solve everything.
|
| For this reason, external tools such as pt-online-schema-change
| are still pretty essential for MySQL/MariaDB deployments of any
| non-trivial size.
|
| MariaDB 10.8, which is still pre-GA, adds a clever solution to
| the replication problem:
| https://jira.mariadb.org/browse/MDEV-11675 . It will be
| interesting to see if there are any real-world operational
| drawbacks to this approach, and seeing if MySQL offers this
| soon as well.
| fabianlindfors wrote:
| Nice tool! Shadow tables are quite convenient as they support
| arbitrary ALTER TABLE statements but they also seem a bit
| wasteful to me. I'm working on a similar tool called Reshape
| (http://github.com/fabianlindfors/reshape) which instead uses
| temporary columns to reduce the time a migration takes and the
| extra storage necessary.
| shayonj wrote:
| Hello! Author of the post here -
|
| Yeah I agree, it's certainly a bit wasteful, especially during
| the operation. You can clean up the table automatically in the
| end with --drop. Love the concept and path with Reshape btw, I
| think its very innovative.
| trollied wrote:
| This is the same approach that Oracle uses with
| DBMS_REDEFINITION:
| https://docs.oracle.com/database/121/ARPLS/d_redefi.htm#ARPL...
|
| People that have never used the Oracle RDBMS give it grief
| because of Larry, the rep of the company, etc, which is a shame
| because the DB is great. This feature is more than 20 years old.
|
| I'm pleased that Postgres is the best of the open source
| databases & it leading as far as functionality goes.
| codeflo wrote:
| > People that have never used the Oracle RDBMS give it grief
| because of Larry, the rep of the company, etc
|
| It's also not developer friendly, like at all. Starting with
| licensing, that insane installer, arcane configuration,
| documentation, error messages, standards conformity, column
| name limits. Our team hated every second of using it.
| tibiapejagala wrote:
| I haven't read the source code, but how does it handle
| materialized views? Views refer to source tables by relation id,
| not by name, so by default you end up with views pointing to old
| tables, which also means can't drop them.
|
| My use case is somewhat different. I have ~400M row tables which
| are not updated live, but I rebuild them from new source data,
| because it is faster that way (lots of columns, indices and FKs).
| There are also materialized views based on these tables,
| similarly with multiple indices.
|
| I wrote some sql scripts using information_schema, which prepare
| new tables for data import, rebuild indices, FKs and then swap
| tables. After that scripts recreate materialized views from
| definitions and swap them. All happens without ACCESS EXCLUSIVE
| lock, so it can be still used by the backend. It sucks, though. I
| wouldn't mind if there was a way to have views use table names,
| so I could just refresh them after swapping tables.
| NickNameNick wrote:
| Given that Postgresql has transactional DDL, some of the planned
| safety features seem redundant.
| shayonj wrote:
| Yeah, I have been mostly erring on the side of caution since
| the use cases can vary a lot. Transactional DDLs are a big
| savior for sure. Were there specific features that seemed
| redundant? I am always looking to iterate on this. - Author of
| the post
| shayonj wrote:
| Author of the post here - Thank you and appreciate the
| submission.
| [deleted]
| mberning wrote:
| That's pretty cool. I have used pt-online-schema-change on a few
| occasions in the past on mariadb and it worked great.
___________________________________________________________________
(page generated 2022-03-06 23:00 UTC)