Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

New approach for updating spending columns in addresses table. #752

Open
chappjc opened this issue Oct 18, 2018 · 2 comments
Open

New approach for updating spending columns in addresses table. #752

chappjc opened this issue Oct 18, 2018 · 2 comments
Assignees

Comments

@chappjc
Copy link
Member

chappjc commented Oct 18, 2018

Motivation

Now that the block sync is much faster (between 60-90 minutes, even on a VM/VPS), the slowest part of initial sync is by far the stage where the spending information is updated in the addresses table (2-4 hours).

Current Solution

The function (*ChainDB).UpdateSpendingInfoInAllAddresses performs these updates in the following way:

  1. Retrieve all row IDs of the vins table.
  2. In chunks of 500 vins rows, retrieve funding tx (previous outpoint) info and spending tx (input) info.
    3. The spending transaction data is inserted into the addresses table. See the call to insertSpendingTxByPrptStmt by SetSpendingForVinDbIDs. insert moved to main sync loop
  3. The matching_tx_hash for the funding tx output is updated with the spending transaction hash.

As more blocks are mined, the percentage of rows of the addresses table with spending information (spent outputs / total outputs) approaches a fairly high steady state value (low unspent outputs / total outputs). Thus, most of the addresses table is updated.

Proposed Solution

Since most of the addresses table is updated, completely rewriting the entire addresses table and rebuilding the indexes is a fast approach. These steps were proposed for adding the matching_tx_index column, but can operate on the matching_tx_hash column too.

  1. Create a new table addresses_new via SELECT INTO or CREATE TABLE AS.
CREATE TABLE addresses_new AS
SELECT addresses.*,
       vins.tx_index AS matching_tx_index
FROM addresses
JOIN vins ON addresses.tx_hash=vins.prev_tx_hash
AND addresses.tx_vin_vout_index=vins.prev_tx_index
AND is_funding=TRUE
AND is_valid=valid_mainchain;

About a minute.

Now addresses_new has the new column, no indexes, and only data with is_funding=true.

  1. Append the spending addresses rows into addresses_new via INSERT INTO SELECT.
INSERT INTO addresses_new
SELECT addresses.*,
       vins.prev_tx_index AS matching_tx_index
FROM addresses
JOIN vins ON vins.id=tx_vin_vout_row_id
AND is_funding=FALSE;

About 40 seconds.

  1. The unspent funding rows, where matching_tx_hash=''. Something like:

    INSERT INTO addresses_new SELECT addresses.*, -1 AS matching_tx_index FROM addresses WHERE is_funding=TRUE AND matching_tx_hash='';

  2. Drop addresses table.

  3. Rename addresses_new to addresses.

  4. Index addresses.

The NOT NULL constraints on id and block_time need to be set, and id needs to be made into a SERIAL (not just a primary key) by making it's default value nextval('addresses_id_seq'::regclass). Look into how to make it SERIAL!

@chappjc chappjc self-assigned this Oct 18, 2018
@chappjc chappjc added the on hold label Nov 5, 2018
@chappjc
Copy link
Member Author

chappjc commented Nov 5, 2018

Putting this on hold since a major improvement in this was realized in PR #754.

@chappjc chappjc removed the on hold label Oct 29, 2019
@chappjc
Copy link
Member Author

chappjc commented Oct 29, 2019

Should be revisited.

@chappjc chappjc pinned this issue Apr 25, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant