I have worked with many different database systems in my life and as long as only one process needs to write at the same time I will always prefer to use SQLite. Read operations are super fast on SSD, because of the all-in-one-file architecture. No database service is required, therefore there is no interface in between through which the data needs to go.
SQLite is an amazing library that gets embedded inside the application that makes use of. As a self-contained, file-based database, SQLite offers an amazing set of tools to handle all sorts of data with much less constraint and ease compared to hosted, process based (server) relational databases.
When an application uses SQLite, the integration works with functional and direct calls made to a file holding the data (i.e. SQLite database) instead of communicating through an interface of sorts (i.e. ports, sockets). This makes SQLite extremely fast and efficient, and also powerful thanks to the library's underlying technology.
Well, maybe I used to work with more complex data and data-flows but it seems to me that sooner or later to run into database limitations.
For example 1 simultaneous write is quite a severe catch. Also highload app (imagine you need to scale 10 times with the growth of steemit users) demands data separation into hot and cold sets. Indexes and recent partitions on SSD and old data HDD plus you can decompose most of tables into cold-hot rows. So you really need to write new sets into db and manage/process another chunks simultaneously.
And lets not forget completely about replication/failover... It would be sad if you had lost your data and now end up with 3 days downtime in order to re-process all blockchain data in single thread.
I know full-sale DB engine is an investment of resources but calculated and with strong roi prospects.
Well, maybe I used to work with more complex data ...
Yes, that's exactly the point. I worked for 8 years for large companies in the logistics sector. We mainly used enterprise servers by Oracle, Sybase and Informix, which of course work on a higher level than SQLite, because they are highly configurable and come with huge performance and stability benefits for simultaneous write operations. For such case I would not work with SQLite and I think it would not even be possible.
But as long as I can run SteemWorld with my current setup, I will stick to SQLite. There is only one process that needs to write to the databases and I don't think that this will change in the near future. Re-indexing the data can be executed in a separate backup path without having any server downtime. Maybe at some point in future I will tell you that I needed to upgrade to a more complex engine, who knows :) For now it's working great.
I have worked with many different database systems in my life and as long as only one process needs to write at the same time I will always prefer to use SQLite. Read operations are super fast on SSD, because of the all-in-one-file architecture. No database service is required, therefore there is no interface in between through which the data needs to go.
More details can be found in the original article:
SQLite vs MySQL vs PostgreSQL: A Comparison Of Relational Database Management Systems
Well, maybe I used to work with more complex data and data-flows but it seems to me that sooner or later to run into database limitations.
For example 1 simultaneous write is quite a severe catch. Also highload app (imagine you need to scale 10 times with the growth of steemit users) demands data separation into hot and cold sets. Indexes and recent partitions on SSD and old data HDD plus you can decompose most of tables into cold-hot rows. So you really need to write new sets into db and manage/process another chunks simultaneously.
And lets not forget completely about replication/failover... It would be sad if you had lost your data and now end up with 3 days downtime in order to re-process all blockchain data in single thread.
I know full-sale DB engine is an investment of resources but calculated and with strong roi prospects.
Just my 2 satoshis.
Cheers!
Yes, that's exactly the point. I worked for 8 years for large companies in the logistics sector. We mainly used enterprise servers by Oracle, Sybase and Informix, which of course work on a higher level than SQLite, because they are highly configurable and come with huge performance and stability benefits for simultaneous write operations. For such case I would not work with SQLite and I think it would not even be possible.
But as long as I can run SteemWorld with my current setup, I will stick to SQLite. There is only one process that needs to write to the databases and I don't think that this will change in the near future. Re-indexing the data can be executed in a separate backup path without having any server downtime. Maybe at some point in future I will tell you that I needed to upgrade to a more complex engine, who knows :) For now it's working great.