SteemSQL - Database update for custom_json operations

in steemsql •  7 months ago  (edited)

Custom_json type operations are gaining momentum into the Steem blockchain. As they are cheap and versatile, they are more and more used by Steem (d)Apps.

Something interesting is that, while the blockchain enables (d)Apps to sign custom_json operations with multiple authorities, this feature hasn’t been used… until recently.

Indeed, I was recently in contact with @starkerz (developer for the #3speak project he runs with @theycallmedan) when I got error notifications from SteemSQL’s database injector because of custom_json operations with multiple signing authorities generated by their platform.

If you want to read more on why they are generating such operations, check this post from @wehmoen published a few days ago.

On the SteemSQL side, the main problem was that the TxCustoms was designed to store only one authority and not an array of authorities. This enables the creation of an index to speed up queries.

Changes implemented in the TxCustoms table

The columns required_posting_auths and required_auths type has been changed from varchar(16) to varchar(MAX) and now contain a JSON array of strings.

As mentioned in a previous SteemSQL update post, the database design will no more be a pure 1 to 1 match between the blockchain format and the SQL table design.

Therefore, I have added 2 new columns in the TxCustoms table:

  • required_posting_auth: this column will contain the first authority found in the required_posting_auths column if any
  • required_auth: this column will contain the first authority found in the required_auths column if any

If you are an analyst or have developed some applications relying on the previous type of data, the easiest way to adapt your existing process(es) is to remove the "s" at the end of the name of the column(s).

I plan to do more and more database revamping in the future to improve performances, resources consumptions and ease of use from the SteemSQL user’s perspective.

Thanks for reading!

Support me and my work for the Steem community.

Vote for my witness

Authors get paid when people like you upvote their post.
If you enjoyed what you read here, create your account today and start earning FREE STEEM!
Sort Order:  

Fantastic work!
What I was wondering already for some time: how big is this database?
It must be huuuuuuuuuuuuge ...

Thank you @pundito
SteemSQL database is actually more than 1.5TB 😅

Interesting at the very least! Even if I honestly got pretty confused reading this!

Posted using Partiko iOS

It might be useful to you to read this post if you want to better understand what SteemSQL is about. Then, if you find yourself still confused, feel free to ask questions.

well done! did a good thing

this will be very beneficial for us and the future progress of steemite.

You are welcome :)

I'm still confused about this, I hope you can explain it in more detail.

Great work brother you write so much informative content and blogs.


hey, I wasn't able to get server to work. Has something changed?

Sorry being late to reply (holidays time)
Support for SteemSQL is provided on!
If it doesn't work for you, you can also contact me on, Discord or Telegram

What we gain from using this software?????

An SQL database of the Steem blockchain.

SteemSQL is not a software but a service available to anyone interested in easily finding information about the Steem blockchain.

You will find more information about it here


Thanks for this post.

You're welcome @omrfrq18

Interesting 🤔

Thanks for the post.