Những sai lầm thường gặp khi thay đổi schema DB trong Postgres

 (postgres.ai)
5 điểm bởi GN⁺ 2024-04-29 | 1 bình luận | Chia sẻ qua WhatsApp
  • Migration schema trong Postgres đặc biệt rủi ro trong môi trường OLTP lớn, vì khóa, ghi lại toàn bộ bảng và độ trễ sao chép có thể dẫn đến sự cố vận hành
  • Rủi ro tập trung ở các thao tác gây ra quét toàn bộ và khóa kéo dài như thêm đồng thời DEFAULT và NOT NULL, tạo chỉ mục không dùng CONCURRENTLY, xóa cột ngay lập tức, thay đổi kiểu không an toàn, hoặc thêm khóa ngoại mà không qua bước xác thực
  • Từ PostgreSQL 11, chi phí của một số thao tác thêm cột đã giảm, nhưng với chỉ mục vẫn cần CREATE INDEX CONCURRENTLY, còn với khóa ngoại nên dùng quy trình như NOT VALID rồi VALIDATE CONSTRAINT để giảm tác động tới hệ thống đang chạy
  • Các thay đổi lớn nên được chia thành những lô nhỏ, đồng thời cần kiểm tra cả replica đọc, độ trễ sao chép, các đối tượng phụ thuộc và việc các instance ứng dụng cũ còn tham chiếu tới cột hay không
  • Cần kiểm thử trước trên dữ liệu ở quy mô production, và với các thao tác phá hủy thì chỉ nên tiến hành sau khi có triển khai nhiều giai đoạn cùng kế hoạch rollback đã được kiểm chứng

Tiền đề của migration schema

  • Ở đây, migration DB không có nghĩa là chuyển đổi DBMS mà là thay đổi schema DB
  • Các thay đổi được nhắm tới có ba đặc tính
    • Thay đổi được quản lý theo phiên bản, trong đó mỗi thay đổi có định danh riêng và quy trình áp dụng tự động
    • Thay đổi bất biến, tức sau khi áp dụng lên production thì không sửa lại mà chỉ thêm thay đổi mới
    • Thay đổi tăng dần, trong đó schema cơ sở dữ liệu tiến hóa từng bước
  • Trọng tâm là các use case OLTP như ứng dụng di động và web, nơi truy vấn chạy quá 1 giây thường bị xem là quá chậm
  • Với cơ sở dữ liệu nhỏ và mức độ hoạt động thấp, một số vấn đề có thể không lộ rõ, nhưng ở quy mô khoảng 10TiB và tải 10⁴~10⁵ giao dịch mỗi giây thì hầu hết vấn đề đều có thể xuất hiện
  • Database Lab Engine được dùng cho phát triển và kiểm thử với thin clone, có thể clone một cơ sở dữ liệu 10TiB trong vòng 10 giây để kiểm tra rủi ro thay đổi schema trước khi triển khai
  • GitLab Migration Style Guide là tài liệu tham khảo tổng hợp kinh nghiệm tự động triển khai nhiều thay đổi schema Postgres

Thêm cột và ghi lại bảng

  • Việc thêm một cột có cả DEFAULT và NOT NULL cùng lúc đặc biệt nguy hiểm trên các phiên bản PostgreSQL cũ
    • Trước PostgreSQL 11, thao tác này đòi hỏi phải ghi lại toàn bộ bảng
    • Với bảng lớn, việc này có thể mất hàng giờ hoặc hàng ngày, và trong thời gian đó sẽ phát sinh khóa ghi
  • Ví dụ nguy hiểm như sau
ALTER TABLE users ADD COLUMN status text DEFAULT 'active' NOT NULL;
  • Quy trình an toàn hơn là tách riêng việc thêm cột, cập nhật dữ liệu và thêm ràng buộc
    • Trước tiên thêm cột mà không có NOT NULL
    • Nếu cần thì cập nhật các hàng hiện có
    • Sau đó thêm ràng buộc NOT NULL
ALTER TABLE users ADD COLUMN status text DEFAULT 'active';

-- UPDATE users SET status = 'active' WHERE status IS NULL;

ALTER TABLE users ALTER COLUMN status SET NOT NULL;
  • Từ PostgreSQL 11 trở lên, việc thêm cột với giá trị DEFAULT không biến động không còn yêu cầu ghi lại bảng nữa

Tạo chỉ mục và thêm khóa ngoại

  • Nếu tạo chỉ mục mà không dùng CONCURRENTLY, thao tác tạo chỉ mục tiêu chuẩn sẽ giữ khóa độc quyền trên bảng
    • Mọi thao tác ghi và một phần thao tác đọc có thể bị chặn cho tới khi việc tạo chỉ mục hoàn tất
  • Ví dụ nguy hiểm như sau
CREATE INDEX idx_users_email ON users(email);
  • Trong khi hệ thống đang vận hành, dùng CREATE INDEX CONCURRENTLY sẽ an toàn hơn
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
  • CONCURRENTLY có một số hạn chế
    • Mất nhiều thời gian hơn nhưng không chặn truy cập vào bảng
    • Không thể dùng bên trong transaction block
    • Nếu thất bại, có thể để lại chỉ mục không hợp lệ cần phải xóa
  • Nếu thêm trực tiếp ràng buộc khóa ngoại trên bảng lớn, hệ thống sẽ quét toàn bộ bảng để xác thực dữ liệu hiện có và gây ra khóa kéo dài
  • Quy trình an toàn hơn là trước tiên thêm ràng buộc với NOT VALID, sau đó xác thực vào thời điểm lưu lượng thấp
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user_id
FOREIGN KEY (user_id) REFERENCES users(id)
NOT VALID;

ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user_id;

Xóa cột và thay đổi kiểu dữ liệu

  • Nếu xóa cột ngay trong production, lỗi ứng dụng có thể xảy ra khi mã ứng dụng vẫn còn tham chiếu tới cột đó
  • Việc xóa cột nên được thực hiện theo nhiều giai đoạn
    • Trước tiên triển khai mã ứng dụng không còn sử dụng cột đó
    • Chờ tới khi tất cả các instance ứng dụng cũ đã được thay thế
    • Xóa cột trong một migration riêng
  • Thay đổi kiểu của cột có thể gây ra ghi lại bảng hoặc vấn đề tương thích
    • Có thể dẫn tới downtime, mất dữ liệu hoặc lỗi ứng dụng
  • Ví dụ có vấn đề như sau
ALTER TABLE users ALTER COLUMN id TYPE bigint;
ALTER TABLE users ALTER COLUMN email TYPE varchar(100);
  • Khi đổi từ integer sang bigint, cần dùng quy trình nhiều giai đoạn với một cột mới
  • Khi rút ngắn độ dài varchar, cần kiểm tra dữ liệu trước và xem xét liệu thay đổi đó có thực sự cần thiết hay không

Thay đổi lớn, sao chép và các đối tượng phụ thuộc

  • Nên tránh các migration sửa quá nhiều dữ liệu trong một transaction duy nhất
    • Làm tăng tranh chấp khóa và mức sử dụng bộ nhớ
    • Kéo dài thời gian phục hồi khi có sự cố
    • Có thể làm tăng độ trễ sao chép
  • Migration dữ liệu quy mô lớn sẽ an toàn hơn nếu được chia thành các batch nhỏ
  • Cũng cần xem xét tác động của migration tới replica đọc và độ trễ sao chép
    • Migration lớn có thể tạo ra độ trễ sao chép đáng kể
    • Có thể ảnh hưởng đến hiệu năng của replica đọc
  • Cần kiểm tra cả các đối tượng phụ thuộc vào cột hoặc bảng được sửa đổi
    • Nếu bỏ sót các đối tượng phụ thuộc như view, function, trigger thì có thể gây ra lỗi dây chuyền hoặc cần thêm can thiệp thủ công

Kiểm thử và kế hoạch rollback

  • Nếu chỉ kiểm thử migration trên bộ dữ liệu phát triển nhỏ, sẽ khó đánh giá được đặc tính hiệu năng trên bộ dữ liệu lớn
  • Cần kiểm thử trên bản clone dữ liệu ở quy mô production, và có thể dùng các công cụ như Database Lab Engine
  • Nếu không có cách hoàn tác migration khi sự cố xảy ra, vấn đề trong production có thể dẫn đến downtime kéo dài
  • Đặc biệt với các thao tác phá hủy, cần có kế hoạch rollback đã được kiểm chứng
  • Nền tảng của thay đổi schema an toàn gồm những điểm sau
    • Kiểm thử trên dữ liệu ở quy mô production
    • Dùng cách tiếp cận nhiều giai đoạn cho các thao tác rủi ro
    • Tận dụng các tính năng PostgreSQL như CONCURRENTLY và NOT VALID
    • Giám sát hiệu năng và tác động tới sao chép
    • Luôn chuẩn bị sẵn kế hoạch rollback

Bài viết liên quan

1 bình luận

 
GN⁺ 2024-04-29
Ý kiến trên Hacker News

  • Tôi rất thích Postgres, nhưng phần lớn nội dung trong bài này là những điều có thể tránh được và đáng lưu ý. Tuy vậy, theo tôi điểm tệ nhất của Postgres là quản lý vai trò
    Tính năng này mạnh mẽ nên nếu dùng tốt thì rất tuyệt, nhưng quá trình làm cho nó thực sự hoạt động lại có cảm giác như ma thuật hắc ám. Nhiều chỗ trong giao diện giống như những câu thần chú khó hiểu mà không biết có hoạt động đúng như kỳ vọng hay không, và đó là một cách khủng khiếp để quản lý một thứ quan trọng như vậy
    Phần hướng dẫn sử dụng về mục này cũng mỏng, chỉ cho biết đại khái nó nên hoạt động thế nào trong một phạm vi use case hẹp. Nếu không diễn ra như dự đoán, bạn phải thử sai để tìm xem mình đã làm sai gì, còn cách đúng thì vẫn không rõ ràng. Muốn migrate một DB có phân quyền người dùng phức tạp thì thật sự rất vất vả
    Tôi cảm thấy chắc phải dành khoảng một tháng để viết một cookbook. Nếu chỉ cần một người đọc nó mà không phải khóc rồi ngủ thiếp đi thì cũng đáng giá

    • Tôi đồng ý rằng IAM của PostgreSQL phức tạp. Nó phức tạp vì hệ phân cấp đối tượng có 3 tầng là Database, Schema, Tables, và còn có các quyền được cấp ngầm cho chủ sở hữu đối tượng DB
      Để SELECT từ bảng, cần có CONNECT trên Database, USAGE trên Schema, và quyền này được cấp ngầm cho chủ sở hữu Schema. Cũng cần SELECT trên Table, và quyền này được cấp ngầm cho chủ sở hữu bảng
      Để xem quyền, phải hiểu các mục ACL theo dạng grantee=privilege-abbreviation[]/grantor:. Quyền Database có thể xem bằng \l+, quyền Schema bằng \dn+, quyền Table bằng \dp+
      Danh sách quyền nằm ở đây. Ví dụ user=arwdDxt/postgres là trạng thái vai trò postgres đã cấp mọi quyền cho người dùng
      Nếu cột grantee của một đối tượng để trống, nó có thể nghĩa là quyền mặc định của chủ sở hữu, tức mọi quyền, hoặc cũng có thể nghĩa là quyền dành cho vai trò PUBLIC, tức mọi vai trò đang tồn tại. Ví dụ là =r/postgres
      Dùng Schema public còn dễ gây rối hơn. Vì Schema có quyền CREATE, nếu tạo bảng bằng cùng người dùng dùng để truy vấn dữ liệu thì quyền chủ sở hữu sẽ được gắn mặc định và có thể truy vấn ngay
    • Tài liệu postgREST, vốn dựa vào vai trò để xác thực, cũng có vẻ không chi tiết lắm: https://postgrest.org/en/v12/explanations/db_authz.html
      Nếu bạn nghiêm túc viết một cookbook về vai trò trong Postgres và mở kiểu Kickstarter, tôi có lẽ sẽ là một trong những người đầu tiên ủng hộ
    • Tôi đồng ý với câu “làm cho nó hoạt động giống như ma thuật hắc ám”. Năm ngoái tôi đã triển khai một server postgREST đơn giản có gắn bảo mật cấp hàng, và con đường để tới đó khá khó khăn
      Nhưng một khi đã chạy được thì nó thật sự giống như phép màu, còn bản thân các cơ chế liên quan thì bất ngờ là khá đơn giản
    • Nếu có bài như vậy thì tôi sẽ đọc. Quản lý vai trò đòi hỏi đoán mò khá nhiều, và kết quả là vai trò bị gán quyền quá mức xảy ra quá thường xuyên
    • Rất mong bạn viết. Nội dung ở mức đó thì tôi sẵn sàng trả khoảng 20 đô la

  • Nếu chạy migration Schema trong môi trường production, bạn nên dùng lock_timeout
    Ngay cả những thay đổi nhìn qua có vẻ vô hại và gần như hoàn tất tức thì trong môi trường test, như xóa bảng có khóa ngoại hoặc xóa khóa ngoại, cũng có thể gặp xung đột khóa trên DB production có nhiều traffic do các transaction hiện có hoặc autovacuum
    Lệnh ALTER đó sẽ giữ khóa ACCESS EXCLUSIVE trong khi chờ khóa của transaction đầu tiên, và như vậy mọi truy vấn tới bảng bị khóa đều sẽ bị chặn
    Khi vận hành Postgres ở quy mô đáng kể, các xung đột kiểu này chỉ là vấn đề thời gian. Nếu đặt lock_timeout, thay vì chờ trong khi chặn mọi truy vấn khác, migration sẽ thất bại khi hết thời gian giới hạn

    • statement_timeout bao gồm cả thời gian chờ khóa, nên có thể ước lượng tốt hơn tác động lên các bảng bận
      Nếu đặt giới hạn là 5 giây, bạn biết tổng thời gian gián đoạn tối đa là 5 giây, và các transaction sau đó vẫn tiếp tục. Nếu chỉ dùng lock_timeout, bạn không kiểm soát được sau khi lấy được khóa thì tác vụ sẽ mất bao lâu, và do traffic đồng thời, nó có thể nhanh hoặc chậm
    • Tùy phiên bản Postgres, việc một truy vấn DML cụ thể có giữ khóa độc quyền hay không có thể khác nhau khá lớn
      Tôi tự hỏi có cách hay nào để phân tích truy vấn và cho biết nó sẽ giữ loại khóa nào không. Khi không chắc, tôi vẫn luôn phải đọc lại tài liệu
    • Lời khuyên hay. Tuy nhiên, về mặt kỹ thuật, theo tôi hiểu thì không phải nó đã lấy được khóa ACCESS EXCLUSIVE rồi mới chờ, mà là đang chờ vì hàng đợi khóa
      ALTER đang ở trạng thái chờ các khóa thấp hơn ACCESS EXCLUSIVE được nhả ra
    • Làm vậy thì ALTER có thể sẽ không bao giờ chạy được. Nếu bảng đó có đủ traffic thì có thể xảy ra như vậy
      Trong trường hợp này, nếu ứng dụng có thể phục hồi, tôi nghĩ cách tốt nhất là kill các truy vấn đang chạy khác đang chặn ALTER

  • Tôi tham khảo hướng dẫn Safe Migrations in Ecto của Fly.io nhiều lần mỗi tuần. Ecto là DB adapter của Elixir
    Đây là tài liệu tham khảo rất hữu ích để nhanh chóng kiểm tra liệu migration mặc định đã đủ chưa, hay cần một quy trình phức tạp hơn
    https://fly.io/phoenix-files/safe-ecto-migrations/

  • Điều khiến tôi ngạc nhiên nhất về index trong Postgres khi còn mới bắt đầu là UNIQUE index có thể ảnh hưởng đến kết quả của các truy vấn đồng thời do các khóa bổ sung
    Một truy vấn như INSERT INTO foo (bar) (SELECT max(bar) + 1 FROM foo); nếu chạy đồng thời ở chế độ mặc định có thể chèn các giá trị bar trùng nhau. Vì một transaction có thể không nhìn thấy giá trị lớn nhất mới do transaction khác tạo ra
    Nếu thêm UNIQUE index, có vẻ như transaction “thua” sẽ nhận lỗi ràng buộc, nhưng thực tế cả hai transaction đều thành công và race condition cũng biến mất

    • Điều đó không đúng. Subtransaction thua trong cuộc tranh chấp index sẽ bị hủy
      =# INSERT INTO foo (bar) (SELECT max(bar) + 1 FROM foo);
      ERROR: duplicate key value violates unique constraint "foo_bar_idx"
      DETAIL: Key (bar)=(2) already exists.
    • Nếu ý là ngay cả khi có UNIQUE index, cả hai lần chèn đều thành công và cuối cùng vẫn có giá trị trùng, thì nếu đúng, đó là bug
    • Nếu tôi không nhầm, có thể làm không downtime bằng cách tạo index thường với CONCURRENTLY rồi tạo UNIQUE constraint chưa được kiểm tra
      Ràng buộc đó chỉ áp dụng cho các INSERT/UPDATE mới. Sau đó chạy VALIDATE trên ràng buộc thì nó trở thành UNIQUE constraint hoàn chỉnh
    • Nếu thấy điều đó đáng ngạc nhiên thì tôi cho rằng là vì đã tiếp xúc quá nhiều với ngôn ngữ mệnh lệnh
      Tôi đồng ý là chuyện này phổ biến, nhưng vấn đề nằm ở phát triển phần mềm nói chung hơn là ở Postgres
    • Điều đó xảy ra ở mức cô lập nào?

  • Vì những cái bẫy như vậy, tôi đã tạo Reshape [0] với mục tiêu tự động hóa schema migration không downtime
    Không thể nói là tránh được mọi vấn đề, nhưng chúng tôi đang xây dựng một sản phẩm mới hướng tới mục tiêu đó. Nếu bạn quan tâm đến lĩnh vực này, đặc biệt là Postgres, tôi muốn được liên hệ: fabian@reshapedb.com
    [0] https://github.com/fabianlindfors/reshape

    • Có khả năng nó hoạt động trên crdb không?

  • Một lỗi khác tôi thường thấy là khi sao chép bảng thì bỏ sót index
    CREATE TABLE SELECT * FROM WHERE <> không hoạt động theo cách đó. Mọi người thường làm vậy khi muốn tạo bảng backup hoặc xóa hàng loạt

    • Nếu là tạo bảng backup, tức là chuẩn bị làm một thao tác phức tạp và mơ hồ có thể phá hỏng mọi thứ theo cách không thể dự đoán ngay, thì tôi hoàn toàn không quan tâm đến index hay constraint
      Tôi muốn có một bản sao dữ liệu hiện hữu ngay tại chỗ, dù có thể sẽ không dùng đến, để khỏi phải khôi phục từ backup DB và WAL. Tạo index là lãng phí thời gian server và dung lượng đĩa
      Nếu mọi thứ rối lên hoặc thật sự cần, có thể tạo các index đó sau
    • Vậy bạn cũng có thể nói luôn cách phù hợp là gì không?

  • Phần “Case 2. lạm dụng IF [NOT] EXISTS” không đưa ra ví dụ lạm dụng hay
    Và thực ra dùng như vậy là đúng. Gọn gàng, đơn giản và không có cạm bẫy ẩn. Nếu chỉ có vài bảng thì công cụ schema migration là gánh nặng quá mức

    • Cạm bẫy rất đơn giản: “che vấn đề bằng logic và làm tăng rủi ro trạng thái bất thường”
      Dán băng cá nhân lên dữ liệu xấu không giải quyết vấn đề, chỉ che nó đi. Tùy loại vấn đề, sau này nó có thể nổ theo cách không lường trước, vào thời điểm tệ nhất
      Trong trường hợp này, “dữ liệu xấu” là bảng, cột, view lẽ ra phải tồn tại hoặc không tồn tại nhưng lại ở trạng thái ngược lại. Tại sao một bảng chưa được phép tồn tại lại đang tồn tại? Xóa thất bại à? Schema của bảng hiện có có đúng không? Cùng một migration có bị chạy nhầm hai lần không?
      Sau mỗi migration, schema phải ở đúng trạng thái chính xác. Nếu migration có IF [NOT] EXISTS, điều đó nghĩa là sau migration trước, schema đã không còn ở trạng thái chính xác. Không chắc chắn về trạng thái schema là điều không tốt
    • Tôi nghĩ bài viết giải thích khá tốt sự lạm dụng này. Điểm cốt lõi là các thay đổi schema theo đường riêng là vấn đề về quy trình và workflow, nên phải tự giải quyết trực tiếp
      Nếu cột trong bảng đã tồn tại khác với cột mà migration định tạo thì sao? IF EXISTS sẽ khiến migration báo thành công nhưng để schema ở trạng thái xấu. Trong trường hợp như vậy, tốt hơn là để migration fail fast

  • Một góp ý nhỏ về việc dùng int4 làm surrogate primary key
    Điều quan trọng không phải là kích thước bảng mà là kích thước index chứ? Kích thước bảng vốn đã có header 23 byte và padding căn chỉnh, nên chênh 4 byte không ảnh hưởng nhiều. Nhưng nếu đưa được nhiều index hơn vào bộ nhớ thì có thể có lợi. Mỗi entry index có header 8 byte
    Ngoài ra, ví dụ 1 tỷ hàng trong bài đã quá gần giới hạn tối đa của int4, nên thấy hơi bất an
    Dù vậy bài viết rất hay

    • Đúng. Có kích thước index và cả kích thước trên đĩa nữa. Postgres đóng gói các hàng của bảng khá chặt trên đĩa, nhưng trong RAM thì không
      Vậy điều đó có nghĩa là một page 8KB trên đĩa có thể lớn hơn 8KB trong RAM sao?
      Có vẻ nó chỉ ảnh hưởng đến working memory của dữ liệu hàng trong bảng. Điều đó vẫn quan trọng, nhất là vì trong Postgres các hàng có thứ tự ngẫu nhiên nên locality của range query rất tệ. Tuy nhiên tôi không nghĩ đó là insight mang tính quyết định

  • Tôi là một developer nhìn chung được bảo vệ khỏi các vấn đề liên quan đến DB. Trong Django, tôi biết tạo migration, tạo bảng model, truy vấn bằng ORM, nhưng rất nhiều thứ diễn ra bên trong vẫn giống như ma thuật đen
    Giờ tôi bắt đầu công ty nên lo rằng mình sẽ phải đối mặt với những vấn đề này và tự giải quyết. Tôi nên tiếp cận thế nào để học những việc cần làm trong môi trường phát triển?

    • Cứ thất bại rồi học từ sai lầm. Hoặc thuê developer để cùng thất bại và cùng học

  • Tôi thích Postgres, nhưng thật sự ghét việc nó không có cách batch update/delete tích hợp sẵn
    Đây là phần khó chịu nhất, và gần như tháng nào tôi cũng phải viết lại batcher mỗi khi đụng tường