Posts

Calculating WAL Generation for DML Operations in PostgreSQL

By -
  PostgreSQL provides the EXPLAIN command with the ANALYZE and WAL options to measure the WAL generated by a DML operation. This feature helps database administrators investigate the impact of INSERT, UPDATE, and DELETE statements on WAL generation. In this article, we use EXPLAIN (ANALYZE, WAL) to measure the WAL generated when updating 10 million rows, compare the results with and without an index, and investigate the effect of enabling WAL compression. 1. Test environment The test uses a person table containing 10 million rows . The table occupies approximately 880 MB , and WAL compression is initially disabled. vahiddb = # \dt + List of tables Schema | Name | Type | Owner | Persistence | Access method | Size | Description --------+--------+-------+----------+-------------+---------------+--------+------------- public | person | table | postgres | permanent | heap | 880 MB | ( 1 row ) vahiddb = # show w...
Post a Comment
Read more

Prevent Accidental Duplicate Rows with JOIN TO ONE in Oracle AI Database 26ai

By -
  Oracle AI Database 23.26.2 introduced the JOIN TO ONE syntax to address a common problem with SQL joins: a query can unexpectedly return multiple rows for a row in the main table. This is especially useful when a query is designed to widen a table without increasing its number of rows. With JOIN TO ONE , Oracle raises an error when a subject row matches multiple rows in a joined table instead of silently returning the multiplied rows. In this article, we will use three tables: CUSTOMERS 1 | M ACCOUNTS 1 | M TRANSACTIONS The relationships are: One customer can have multiple accounts. Each account belongs to one customer. One account can have multiple transactions. Each transaction belongs to one account. For example: Vahid Yousefzadeh | + -- A1001 | + -- Transaction 1 + -- Transaction 2 + -- Transaction 3 The Normal JOIN Suppose we want to display each account together with its custom...
Post a Comment
Read more