Limitless point-in-time recovery (PITR) is one of the hottest features in SingleStore 7.5, offering new, mission-critical capabilities for enterprise customers. Domenic Ravita takes a deep dive into PITR with SingleStore’s VP of Product Development, Rick Negrin, to find out how this new feature benefits database administrators, executives and developers.
A few weeks ago I blogged about the highlights of SingleStore 7.5, recapping our recent webinar, “What’s New in SingleStore Summer Release?” One of its most hotly anticipated capabilities is limitless point-in-time recovery (PITR) which, as it sounds like, allows you to recover a complete database to any point in time, over a pre-defined window. I wanted to know more about why limitless PITR is important, and to whom, so I sat down for a Zoom deep dive with Rick Negrin, Vice President of Development at SingleStore. Here’s an edited version of our conversation.
Domenic: There’s a lot of heat around SingleStore’s new limitless PITR capability. Why?
Rick: Limitless PITR is an important capability for mission critical applications, which have very high data integrity requirements. While SingleStore is the mission-critical backbone of many data-intensive applications, limitless PITR additionally allows enterprise customers to confidently rely on our database as a system of record. This is a common term in enterprise computing that means a database essentially serves as a “single source of truth.”
Obviously, a system of record is mission critical. It being a “source of truth” also means that the database is the only location for that data. If that data is lost, it’s gone and there is no way to reconstruct it from other sources. That is why data loss from a system of record can cripple an organization and has to be prevented at all costs. In contrast, in most data warehouses the data sourced from other systems. You can always rebuild a data warehouse, although it might take a really long time.
With limitless PITR, SingleStore is meeting the high-bar needs of an enterprise audience, which for years has had a fairly limited selection of relational databases to use as systems of record.
D: Tell me more about how limitless PITR works.
R: Limitless PITR is conceptually pretty simple. It allows you to recover a database to any point in time over a window you specify: a day, week, month, 90 days or more. SingleStore 7.5 will maintain all history of the data over that time, recoverable to any time granularity down to the microsecond, or recovered to a named milestone.
Let me emphasize that our granularity is impressive: rollback to the microsecond level in a database that is distributed, relational, multi-model and cloud native.
D: How is limitless PITR used in the real world? Who’s most excited about this new feature?
R: Database administrators (DBAs), IT and business executives, and developers are all excited because PITR allows A) recovery from database corruption or other problems, B) can function as a continuous online backup, and C) makes it very easy to use production data in development.
I’ll unpack each of those use cases. First, DBAs spend an inordinate amount of time cleaning up from errors like:
  • Someone accidentally dropped or truncated a table in the database.
  • A deployment failed, making changes to the database that are difficult to reverse.
  • A lot of data was accidentally deleted or modified, making it impossible to run one or more applications.
The ease with which SingleStore can get the database back to a valid state eliminates two thorny problems that DBAs commonly face in these and other types of disaster recovery scenarios: RPO and RTO.
  • RPO is recovery point objective; when you do a restore, how much data will you have lost? Can I get it back to the exact point where the system crashed, or a day or week before that? With a weekly backup, your RPO is a week. Daily backup has an RPO of a day, an hour and so forth. With continuous backup, which is what SingleStore delivers, RPO is near zero, eliminating potential windows of loss of critical data.
  • RTO is recovery time objective, or the time frame in which you want to be able to recover your data.
SingleStore 7.5 dramatically simplifies RPO. Because we store data continuously, a restore doesn’t require defaulting to the most recent version of the database; you can pick any point in time to restore it to. So, if you have a logical corruption and know when it occurred, you can restore the database to just before the point of corruption. You can take that data out just before the operation that corrupted it and thus not lose it.
We also have a feature called ‘milestones’ that is handy. Milestones are like bookmarks. You create one when you know you are doing something a bit risky, such as upgrading the application. That way if something goes wrong you don't have to figure out the starting point to the exact microsecond. You just restore back to the milestone, which was specified as the last known good point.
All of these features illustrate the big advantages of limitless PITR—it allows you to be very precise about what you restore to, and therefore you reduce your RPO to close to zero, if not zero.
To summarize, SingleStore unlimited storage databases can function as a continuous online backup.
D: Why do executives like continuous online backup?
R: In any industry, backing up data is an enormous cost of doing business that requires a lot of people and technology resources. All businesses back up databases for disaster recovery and business continuity purposes, but compliance regulations in some industries such as finance require institutions to maintain seven years of historical data. This data isn’t used that often, but when you need it, it’s critically important. Unfortunately, the risk around traditional backup processes is relatively high, because it’s not uncommon for data to become corrupted or have other problems.
We anticipate that some of our banking customers will keep a seven-year window on their data, freeing their DBAs to work on much more pressing issues that drive competitive advantage.
D: The SingleStore 7.5 release webinar said that limitless PITR does not eliminate the need to do backups. What gives?
Practically speaking, we expect to see 7.5 embraced as backup because of SingleStore’s architecture. When data is put into the cluster it’s automatically sent down to object store, providing limitless PITR and what is effectively continuous backup in highly secure and reliable S3.
If you want to keep the data for seven years, you just let it keep going to object store. Set the retention to infinite and you don’t have to do anything; the backup just accumulates. The storage does have a cost, but this approach eliminates the DBA time required to execute and manage the backups. DBAs can be redeployed to more meaningful tasks. The cost of storage is nominal; object store in S3 is 2.3 cents per month, per gigabyte. A 100-terabyte database—that’s 100,000 gigabytes—costs \$2,300 per month to store, a small fraction of an enterprise DBA’s salary.
D: Last question: What does limitless PITR offer developers?
Developers that want to test their application on up-to-the-minute production data can now do so. They can do limitless PITR of the production system, make a copy of the database, restore it to another server, and now have an up to date copy of the database for development and testing.
If you’re excited about limitless PITR and want to try it in your data workstream, get started with SingleStore today, go ahead and take advantage of our offer for \$500 in free product credits. Follow us on Twitter @SingleStoreDB and in our new Twitter channel for developers, too: @SingleStoreDevs.