7

At the core of my business (say, an online store) is a Postgres database that stores products and transactions. During day-to-day operations, the load on the database is not heavy.

However, there is now a need to implement dashboards, compute more metrics, generate reports and so on. Computing these statistics puts a bigger load on the system than anything that happens during normal operations. A single engineer making a hand-written SQL query can cause latency issues.

What are high-level approaches or tools for doing data science without putting the production database at risk?

Improve this question
1
  • 12
    If this actually becomes a problem, it can potentially be solved by adding a read-replica to the database and running analytics queries on there. After all, analytics should be read-only. Similarly, you might want to keep the data for analytics in a completely separate database, maybe even with different tables. If that lag is tolerable, you might restore your daily production DB backups into the analytics DB instance? However, it might turn out that your analytics aren't that performance-critical because the expensive analyses aren't running all the time. Commented Oct 21, 2023 at 18:27

3 Answers 3

Reset to default
12

I will start with the simplest solution, per amon's comment - create a read replica - if you are hosting your database in a cloud provider, this is typically just a few clicks or a few changes to your provisioning scripts.

Secondly, don't do development in your production database - it is usually the case that, AT MOST you will need a full backup of the production database for development purposes. Depending upon your use case you may choose to strip PII (Personally identifiable information) from your Dev DB and/or choose to reduce the size of the DB, to make it more manageable. So you can run a completely isolated Dev DB server using whichever data works for your process. Therefore nothing you do on the Dev DB server can impact production. Note: The SQL should also be considered "Code" so your release process should have performance tests, so you know what impact your changes will have before those changes are released to production (note: production in this case will likely be the read-replica).

The primary issue with read-replicas is typically they are completely read-only - you can't create intermediate calculation tables and you can't import data from other sources to use in your calculation. Hence the next step is typically to create a DWH (Data warehouse). The data from your production database needs to be synced to the DWH (there are several mechanisms to achieve that) however the crux of it is that the DWH is typically optimized for analytical operations and likely has the following characteristics:

  • May be using a different DB engine to your production database.
  • Typically is read/write - has the capability to create arbitrary tables to store intermediate calculations.
  • Aggregates data from multiple production database - hence is able to perform calculations that span multiple sources.
Improve this answer
1
  • Thank you for the response, I agree that creating a read replica is a good place to start. Commented Oct 22, 2023 at 11:37
9

The usual practice is to make a distinction between OLTP for day-to-day transaction processing, where users need high speed response time, and OLAP for in-depth analytics (dashboards and other analytics) that require more resources but can wait a little longer.

OLAP generally uses different data structures (e.g. cubes, snowflake schema) , that aim at boosting the performances of analytical queries. A simpler approach is to is to replicate the OLTP database for reporting purpose. While this may have performance limits for analytics due to sub-optimal data structures, it is relatively inexpensive (especially if you already have the dashboards), and somewhat scalable, as you could envisage more replicas (ideally feeding the additional replicas from a first replica to reduce OLPT resource consumption to the minimum for replication).

Data warehouses are fed periodically (every night, every fours hours, every hour), preferably loading incrementally new/changed data instead of a full load. But real-time duplication is also possible.

Another approach is to use the CQRS architecture. But in your case, this would require substantial reengineering, as you'd have to feed in real-time a second data model that would be optimised for the dashboards. And as you may very often enrich your dashboard sets, this is not necessarily the best option for you.

Improve this answer
1

I don't disagree with the prior answers, however, I would not start there. I would do some analysis and tracking of the overall performance of the system. Since the OP indicates that the load on the database is "not heavy", with attention to detail of the analytic load, perhaps better tuning is all that is needed.

Postgres is quite powerful and there are a lot of knobs that can be turned to optimize performance. Materialized views and conditional indexes are just two of the many ways to amortize analytic cost.

Or it may be determined that the more cost-effective and/or simpler solution is simply to bump the operating environment resources (bigger hardware) in conjunction with more effective tuning.

Bottom line, I would not assume I need to go outside the current architecture until I had proven to myself that it was the best way to go. I am a big believer in keeping things as simple as possible for as long as possible.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.