Hacker News Reader

Comment by aoms | Hacker News Reader

This instance must cost you a ton

More than I care to admit!

As mentioned downthread, we're doing some work soon to remove the audit table from the database, which will cut storage usage by over 50%.

> we're doing some work soon to remove the audit table from the database

Out of pure curiosity, what are you replacing it with (if anything)? Just a simple rotating log file?

Exact strategy to be determined—we're looking at various data layers at the moment. I wish we could do something simple like a rotating log file, but we want to be able to query it in the app (for instance, to show recent logins).

Very interesting! Looking forward to the next blog post on that ;)

Have you considered an OLAP database like Clickhouse or QuestDB? An OLAP database would be a much better fit for audit tables given the read and append-only writing requirements, would compress better, and you might be able to fit it directly without changing any app code with a Postgresql foreign data wrapper.

I was thinking TimescaleDB

You can partition your audit/event table by time period and archive old events [1] or you can avoid the records hitting the database in the first place by generating the events elsewhere to begin with [2].

[1] https://github.com/icheishvili/audit-trigger [2] https://github.com/eulerto/wal2json

What's your strategy?

Using compression? Second instance for old data? Creating in-between states?

Just curious :)

Seems to be around 1784$/monthly according to the pricing calculator

Let me save you a bunch of money: https://adamj.eu/tech/2021/04/13/reindexing-all-tables-after...

This technique saved us from seriously increasing the cost of our Heroku Postgres instance. Thank goodness it exists and works so well. Multiple 80+ GB indexes shrinks down to less than 10GB after just a couple of hours.

Reply on news.ycombinator.com