A few years ago, I presented at PostgresConf NYC about using Grafana to visualize data in PostgreSQL. The main message of the talk was that developers often set up monitoring and observability infrastructure. Still, there's a lot you can achieve by simply querying the data already stored in your database. Monitoring usually relies on metrics, logs, events, and traces generated from observing user or system actions. Similarly, by observing your database, which holds your application's state, you can capture similar types of events when changes occur, whether initiated by users or background processes.
Tracking user activities
Almost every database table represents an activity. For example, let’s say you have users that invite other users and create reports in your application. Your tables could be users, invitations, and reports. If each one of these tables has a created_at column representing when a record was created, you can visualize how many users, invitations, and reports were created over time.
Index your data for BI or analytics
Typically queries written for BI dashboards or analytics tools are very different from queries for your application logic. Your application typically queries data for a user or tenant at a time. Analytics queries focus on aggregates and generally are time-bound. This means you’ll need different kinds of indexes.
For instance, you might need to create a chart showing the number of users created in the past 30 days. An excellent index to add would be on the created_at column in the user's table.
Soft deletes and audit logs
Consider what happens when records are deleted. They can affect your dashboards. Consider using soft deletes so that when things are considered "deleted" by your application, the records are still there in the DB for analytics purposes. If things actually need to be deleted, like user data, you can consider creating a copy of some metadata for audit purposes in another table.
Make use of data you already have
Your database holds a wealth of information about product usage and user activities, and by structuring it effectively with an observable schema, you can get a lot of value without additional infrastructure. By tracking timestamps, thinking about indexes, and handling deletions thoughtfully, you'll be well on your way to harnessing the full potential of the data you already have.
