Mempool

mempool

Mempool table holds the local Stacks node mempool transactions except already included in the canonical blockchain. Entries in mempool table are constantly updated as new transactions are accepted or processed.

Column	Type	Description
tx_id	text	hash of the mempool transaction
nonce	integer
tx_status	text
receipt_time	timestamp
fee_rate	bigint
sender_address	text
sponsored	boolean
post_condition_mode	text
post_conditions	jsonb	see below for details
anchor_mode	text
tx_type	text
payload	jsonb	tx_type specific payload, see below

post_conditions column

post_conditions is JSON array of the objects with the following fields:

Field	Type
type	text
condition_code	text
principal	text
asset	text
asset_value	{ <hex>, <repr> }

payload column

The payload contains transaction type-specific objects.

contract_call column

Field	Type
contract_id	text
function_name	text
function_signature	text
function_args	[{<name>,<type>,<hex>,<repr>}]

token_transfer column

Field	Type
recipient_address	text
amount	text
memo	text

smart_contract column

Field	Type
contract_id	text
source_code	text

Examples

Mempool transactions count by type

SELECT payload ->> 'tx_type', count(*) FROM mempool
  GROUP BY 1,
  ORDER BY 2 DESC

Top 10 contracts in mempool by number of calls

SELECT payload ->> 'contract_id', count(*) FROM mempool
  WHERE tx_type = 'contract_call'
  GROUP BY 1
  ORDER BY 2 DESC
  LIMIT 10

Function calls with count for a specific contract

SELECT payload ->> 'function_name', count(*) FROM mempool
  WHERE tx_type = 'contract_call' AND payload ->> 'contract_id' = 'SP3K8BC0PPEVCV7NZ6QSRWPQ2JE9E5B6N3PA0KBR9.alex-launchpad'
  GROUP BY 1
  ORDER BY 2 DESC

The arguments with parameters by counts for a specific contract and function call

SELECT value ->> 'name' name,
       value ->> 'repr' param,
       count(*)
  FROM mempool CROSS JOIN jsonb_array_elements(payload -> 'function_args')
  WHERE tx_type = 'contract_call'
   AND payload ->> 'contract_id' = 'SP3K8BC0PPEVCV7NZ6QSRWPQ2JE9E5B6N3PA0KBR9.alex-launchpad'
   AND payload ->> 'function_name' = 'claim'
  GROUP BY 1,2