Search documentation

Search for a topic, a control, or an event type.

    Docs / Databases / Limits, events and backups

    Limits, events and backups

    Understand statement and result bounds, committed row events, service backups and reopening a retained deleted database.

    To reopen a retained deleted database, sign in with the CLI as an account that has current Owner access. List the databases you can restore, then replace DELETED_DATABASE_ID with an existing ID from that list. This reopens the retained database and does not rewind earlier row changes.

    Reopen a deleted database
    oaiz db list-deleted
    
    oaiz db restore DELETED_DATABASE_ID

    Limits and row events

    A query, write, or function call stops after 30 seconds. That limit is separate from an ordinary transactional migration, which allows 60 seconds for one statement, 3 seconds to wait for a lock, and 2 minutes for the whole migration. Writes have no row-count or change-data size cap.

    Bounds on SQL, results and migrations
    LimitValue
    Row statement30 seconds
    Row SQL text256 KiB
    Returned rows1,000. The result is marked truncated. The write still commits.
    One returned cell1 MiB
    Returned result8 MiB. A write that returns more rolls back, apart from the row-count truncation above.
    Parameter JSON8 MiB for the values together
    Parameters65,535
    Function call result1,000 rows. A call that returns more is refused with storage-function-result-too-large.
    Function call arguments100
    A change above 1 MiBIn image mode, when a change’s before and after images together exceed 1 MiB, each value whose JSON text exceeds 64 bytes becomes null.
    Statements in one migration32
    One migration statement16,384 bytes
    One named migration script524,288 bytes, still within the statement bounds above
    Operator characters in a row256, also inside comments and string literals
    Migration timeOrdinary migrations: 60 seconds per statement, 3 seconds of lock wait, 2 minutes in total. A lone named concurrent index build has a 15-minute build limit and up to 19 minutes for apply.
    Object name63 bytes, and it does not start with oaiz_ or pg_ in any case
    Migration name1 to 255 bytes. It starts with a letter or digit. The rest is letters, digits, '_', '.' or '-'.

    A committed write with row changes produces one event. When it makes at most 100 row changes, the original before and after JSON totals at most 32 MiB, and UPDATE before and after rows can be matched, rowImagesOmitted is false and changes contains per-row images. Above either threshold, or when an UPDATE cannot be matched reliably, rowImagesOmitted is true and changes contains table and operation groups with counts. Omission applies to the whole event, even if the write changes only a few rows. Check the flag before using images.

    Every changes entry has the same six fields: table, operation, rowCount, record, old_record and errors. An image entry has rowCount set to 1. A summary entry has the group count, both image fields set to empty objects, and errors set to an empty array. In image mode, an individual large image can have long values replaced by null. Its errors field then contains Error 413: Payload Too Large. Otherwise errors is an empty array.

    An account reads database events through its database-source access, derived from database grants. SQL row policies do not filter the stored before and after payloads. An account that can read a database event can therefore see data in it that its row policies hide through SQL. Publication is best effort. A failure to publish does not undo the committed write.

    A workflow test with Write off rolls database row changes back. Turning Write on permits real changes and row events, even when Publish Events is off. PostgreSQL sequences can advance even when a test transaction rolls back.

    Row event with images

    This example shows selected fields from the properties of a rows-changed event. One order changes from pending to shipped. The changes array contains one entry with the row before and after the update.

    One updated order
    {
      "databaseId": "9fdc7618-14cc-4f34-b278-5b6e47016cab",
      "rowImagesOmitted": false,
      "changes": [
        {
          "table": "orders",
          "operation": "UPDATE",
          "rowCount": 1,
          "record": { "id": 42, "status": "shipped" },
          "old_record": { "id": 42, "status": "pending" },
          "errors": []
        }
      ]
    }

    In image mode, an INSERT has old_record set to null because the row has no previous state. A DELETE has record set to null because the row no longer exists.

    Row event with counts

    This example shows the same selected properties after one write updates 5,000 orders. Each changes entry groups one table and operation. The rowCount field gives the number of row changes in that group. The entry keeps the same fields as the image example. Both image fields are empty objects.

    Five thousand updated orders
    {
      "databaseId": "9fdc7618-14cc-4f34-b278-5b6e47016cab",
      "rowImagesOmitted": true,
      "changes": [
        {
          "table": "orders",
          "operation": "UPDATE",
          "rowCount": 5000,
          "record": {},
          "old_record": {},
          "errors": []
        }
      ]
    }

    Check rowImagesOmitted before reading record or old_record. For a summary, use rowCount or query the current table when needed. A query returns the current state. It cannot recover deleted rows or the values before an update.

    Backups

    OAIZ keeps provider-managed backups of the managed database service as a whole. Restoring one database to an earlier moment from those backups is not a tested operation. OAIZ has no control for downloading a backup, scheduling one, or restoring one database from a backup. OAIZ has not measured how much recent work a service restore would lose, or how long that restore would take.

    Deleting a database is a separate operation. An Owner can restore a deleted database within 30 days. The restored database retains its rows, schema, name and surviving grants. Restoration requires current Owner access and does not create grants. That restore does not undo row changes made before the deletion.