[GolosSQL] A public SQL server database with all Golos blockchain data (english version)

Many people want to have access to data contained in the Golos blockchain to perform analysis on what’s going on or to find valuable information.

But not everybody has programming skills to gather data and compute the wanted result.

Therefore, I created a publicly available Microsoft SQL Server Database with all the blockchain data in it.

Why use a SQL database?

The main advantage having such a database is the fact data are structured and easily accessible from any application able to connect to a SQL Server database.
Having a SQL Server database makes it possible to produce quick answers to queries.

Simply put, a query is a question. You ask the server and it sends back an answer (called the query result set).
For example, when dealing with large amounts of data as Golos blockchain data, might want quick answers to questions (queries) such as:

• What was the Golos power down volume during the past six weeks?
• Which are the top 10 most rewarded post ever?

Browsing the blockchain over and over to compute such information is time and resource consuming.

If you don’t have a local copy of the blockchain, instead of downloading the whole data from it, you send your query and get only the requested information, saving tons of bandwidth.

Let’s have a look at some technical details

Database diagram

The Blocks table contains bare block information (timestamp, witness, …)
Each block can be linked to one or several record in Transactions
Depending on each transaction type, the associated transaction’s data is stored in the related "Tx****" table.

New transactions from the latest data blocks are injected in the database every 10 seconds.

The accounts table is populated/updated has follow:

• Each time a transaction involving an unknown account is injected in the database, the corresponding account data are inserted in the database.
• Each time a transaction involving a known account is injected in the database, the corresponding account dirty field is set to true
• Every 10 minutes, all accounts flagged as "dirty" are updated.

The procedure has been implemented as is to :

• avoid overloading the database server with too many account updates
• lower the Golos seed nodes load by querying accounts information less often

So be careful when your query involves the Accounts table. If the account is flagged as dirty, this means new transactions imported in the database may have impacted some fields value, but those values have not been updated yet.

Full text search

The database has been full text search enabled. This allow fast search of information within post and replies.

For example, if I want to know where anyone has spoken about, me, the following simple query will do the trick

SELECT author, title, body, url FROM TxComments WHERE CONTAINS(body, '@arcange')

Performances

Several indexes have been created to improve overall queries performances.
If you find you query to be slow, do not hesitate to contact me to analyze it and see how we can improve performances.

Let’s have a look at some technical details

Database Connection information

Here the information to connect and query the database:

Server: sql.golos.cloud
User: golos
Password: golos

Support

If you need help, have any comment or request, please use GolosSQL channel channel on chat.golos.io

Availability and performance

The SQL server is hosted in a datacenter with 24/7/365 availability.
Available output bandwidth is up to 500Mb/s

The server is currently hosted in a shared infrastructure.
I will monitor server load and if it requires more resources, I will allocate any reward to this post on a dedicated infrastructure.

Поддержите меня и мою работу в качестве делегата проголосовав за меня тут!

Если вам понравился этот пост,
не забудьте за него проголосовать или подписаться на меня или поделиться