Comment by asah | Hacker News Reader

Comment by asah | Hacker News Reader

2c: if you need PostgreSQL elsewhere in your app anyway, then store your event data in PostgreSQL + FOSS reporting tools (apache superset, metabase, etc) until you hit ~2TB. After that, decide if you need 2TB online or just need daily/hourly summaries - if so, stick with PostgreSQL forever[1]. I have one client with 10TB+ and 1500 events per sec @ 600 bytes/rec (80GB/day before indexing), 2 days of detail online and the rest summarized and details moved to S3 where they can still query via Athena SQL[2]. They're paying <$2K for everything, including a reporting portal for their clients. AWS RDS multi-AZ with auto-failover (db.m7g.2xlarge) serving both inserts and reporting queries at <2% load. One engineer spends <5 hours per MONTH maintaining everything, in part because the business team builds their own charts/graphs.

Sure, with proprietary tools you get a dozen charts "out of the box" but with pgsql, your data is one place, there's one system to learn, one system to keep online/replicate/backup/restore, one system to secure, one system to scale, one vendor (vendor-equivalent) to manage and millions of engineers who know the system. Building a dozen charts takes an hour in systems like preset or metabase, and non-technical people can do it.

Note: I'm biased, but over 2 decades I've seen databases and reporting systems come & go, and good ol' PostgreSQL just gets better every year.

https://instances.vantage.sh/aws/rds/db.m7g.2xlarge?region=u...

[1] if you really need, there's PostgreSQL-compatible systems for additional scaling: Aurora for another 3-5x scaling, TimescaleDB for 10x, CitusDB for 10x+. With each, there's tradeoffs for being slightly-non-standard and thus I don't recommend using them until you really need.

[2] customer reporting dashboards require sub-second response, which is provided by PostgreSQL queries to indexed summary tables; Athena delivers in 1-2 sec via parallel scans.

Along these lines, if you need the ability to "time travel" and "recover overwritten state" and "reinterpret the events of the past" sometimes all you need is audit logs that maintain snapshots of pre-save data, and a script that identifies and collects instances of a specific sequence of events, which a human can review and bulk-apply as necessary to backfill the effects of new logic.

https://django-simple-history.readthedocs.io/en/latest/ and similar tools are a semi-reliable and simple solution for building audit tables, or you can add Postgres triggers if you need to audit direct database access.

I love event sourcing in theory. In practice, there's so much boilerplate necessary to add a new CRUD workflow, or to quickly and reliably roll out the types of interventions and hotfixes that early-to-mid-stage startups need to do all the time for unforeseen circumstances. Unless you're doing something like implementing payment processing rails, event sourcing may not be the right choice.

https://news.ycombinator.com/item?id=17817375 (2018) has some good conversations on the downsides to event sourcing as well.

The problem with audit logs for deriving state is that migrations are a forgetful operation. They’re great for telling you who changed what and when… but if the table lives long enough the audit logs will hold references to columns that no longer exist or data that is gone with no way to trust that it can be recovered properly.

The author has another post on that site on when to avoid event sourcing.

> I love event sourcing in theory. In practice, there's so much boilerplate necessary to add a new CRUD workflow, or to quickly and reliably roll out the types of interventions and hotfixes that early-to-mid-stage startups need to do all the time for unforeseen circumstances.

The biggest pain point I had working on an event sourced system at an early stage startup was around schema changes, where changes (especially quick or unplanned) introduced something pathological in event data for some period of time that we didn't realize was an issue until it conflicted with some change much later on.

Often the discovery of these issues would come at a bad time and block something important, e.g. because of small differences in dev environment seed data and real production data, leading to temptation and/or pressure to "just do a quick tiny mutation" to old events to address the problematic events from the past mistake, which realistically often just caused a different issue, because reasoning about all of the potential impacts of the change was hard.

These days in early-stage land I am only interested in event sourcing for contained areas of a product where it can provide real value, rather than as a cornerstone of an entire application.

From your experience, where does it bring value?

Im not the OP but I’d chime in: first and foremost it’s on the resume.

Of course im not bashing event sourcing altogether but right now resume boosting seems to be the driving force to apply it everywhere.

And this comes from my experience at my current workplace. Event sourcing has brought so much unnecesary complexity to a an older system and it’s not even implemented properly. The engineers who took the initiative padded their resumes then went on to greener pastures while leaving a giant mess behind.

I liked it on an application with a complex conversion flow that could be initiated from a few different starting points with a few required steps and a mix of optional steps that might be executed in different orders. Storing the user's profile data from each step as an event helped us model the whole process as a state machine, which made it easier to reason about this big complex thing, as well as to introspect the previous stages of a user's journey right in the application.

I've also thought it made sense on an application that regularly synced down data from 3rd party systems, where it wasn't uncommon to have some kind of weird data introduced and struggle to reconcile when/where it came from against changes made by application users on our end. Having everything as a series of events can be really awesome in that case.

> reasoning about all of the potential impacts of the change was hard

It's far easier for us as humans to think "this change will affect new data going forward" rather than "when I make a change in the present, it will transform all data from the past as if it had always been the case." Perhaps comic book writers would make good event sourcing engineers - but I digress.

One of the saving graces of event sourcing is that you can spin up a parallel world where you make those changes, rerun your entire event history, and do a diff between your production derived data and your proposed production derived data as of a point in time, identifying the actual situations where pathological cases would occur. But this takes a lot of discipline, and it's not perfect, because events don't exist in a vacuum - they are snapshots of a human being taking an action relative to the data they saw at the time, and if you're retconning the data they saw, it might not match the reasons they took the actions they did.

A common pathological case I find is: "Someone overrode value X to Y, now I have the ability to automate that fix - but did they mean to freeze Y for a different business reason outside the one I did? If at some time in the future it changes upstream from X to Z, should I keep the user's intent to freeze Y or change it to Z because the freeze to Y was just because we didn't have the fix?"

Relational approaches don't solve this, to be sure, and in fact there's worse tooling to have good answers to this question. But they do incentivize the discipline of talking to stakeholders before making a giant wide-scoped UPDATE on data at rest. Event sourcing's power to retcon makes it far too easy to say "let's just reinterpret the history, what's the worst that can happen?"

> I love event sourcing in theory.

I've never heard this term before. From a quick scan it seems like a design tool for reasoning with unfamiliar schemas but that doesn't match the conversation. Would you mind explaining the context around its use or suggesting a link?

https://martinfowler.com/eaaDev/EventSourcing.html

Even in theory, the article says: "The end is near: The end of flattened data and losing business information."

Honestly, I can't see how events are not more flattened data than a properly designed relational database.

In fact, IMHO the only reason to use events exclusively, is to sell one of these "fancy" NoSQL databases.

When a comment on HN has more merit than the article.

The only problem with postgres is that inserting has some interesting scaling problems. Putting a queue between the event sources and the db is usually recommended.

> Putting a queue between the event sources and the db is usually recommended.

Emphasis on _usually_. If your db is at 2% CPU utilization with/without queues ... you probably don't need a queue.

see my reply above - one classic case is db system maintenance.

"normalize until it hurts, denormalize until it works" is evergreen advice for scaling both reads and writes. Synchronously enforcing referential integrity and other forms of normalized constraints is what gets expensive.

Pat Helland has some really good writing on this stuff, e.g. https://pathelland.substack.com/p/i-am-so-glad-im-uncoordina...

And in between, at the coma, revise seriously your indexing policy and don't hesitate to remove unused and underused index (even on foreign key if you don't need them that much). People too often underestimate the impact of rebuilding and index on large inserts.

Absolutely, there's no beating the RUM Conjecture.

thanks! forgot to mention queuing (e.g. SQS), which is SUPER valuable, for example when you want to do large scale maintenance on the database (major version upgrade where the on-disk format can change)

SQS has eventual consistency, you can get the same message twice on 2 different intances for example (which was often the case for my projects). I would rather suggest Amazon MQ.

This is often possible to work around by adding a UUID to the message, on the sender side. Then either handle dupes at the DB level by ignoring duplicate inserts, or using something like redis. In practice, even with other queue systems, you can wind up with dupes due to bugs, timeouts, retries, like duplicate sends when the first one actually went through but wasn't acknowledged. I worked on systems sending thousands of messages/second through rabbitmq, originating from various third parties, and dupes would happen often enough that we needed to work around it on the receiving "worker" side.

SQS is weird in that you can get the same message in Japan and Germany at the exact same time and "race" to process the message. It's annoying af. I do not recommend SQS unless you are at some kind of scale (in company/team size, not revenue) where you can deal with it properly.

I used extensively at a previous company for longer running background tasks. It was simpler to use SQS than dealing with standing up our own RabbitMQ cluster. Their Amazon MQ service did not exist at the time. Our system was built to tolerate duplicates and it worked well enough. For something higher volume I'd definitely use RabbitMQ though.

A cluster! Yeah, if you have enough messages warranting a cluster, SQS might be simpler (but probably far more expensive). It's always tradeoffs I guess.

> Putting a queue between the event sources and the db is usually recommended.

That depends on the nature of the events and whether you can live with the database being out-of-date while the events are still in the queue.

Is the general idea to have a table with definition {id:uuid,created_at:timestamptz,data:jsonb}?

It’s difficult to get index functionality in JSONB, especially against diverse event structures and event definitions that evolve.

I guess I should become more familiar with: https://www.postgresql.org/docs/current/datatype-json.html#J...

yes re table definition.

To answer your question, for indexing there's a couple of options: - if you know which fields you want to index, use an expression index with a regular B-tree. - if you don't know which fields you want to index, use a GIN index on the whole field.

Blogpost which covers both cases: https://scalegrid.io/blog/using-jsonb-in-postgresql-how-to-e...

That said, I generally don't bother indexing the "raw" table, but instead create a parsed+summary table that breaks out the fields I want to index into 'real' sql columns and summarizes the data to reduce the number of records. In particular, I use a partitioned table for the raw data which keeps the data-per-table tiny enough to fit in RAM, and "real time" queries just hit the latest raw table(s). For big, real-time data, I sometimes use TABLESAMPLE queries to create estimates, or triggers to do exact counting (which is what NoSQL databases do, just without the nice structure of triggers).

In event sourcing, as described in DDD, the aggregate is more important. So there (also) has to be an aggregate Id, that's non-unique (one aggregate has many events).

Then, there's the optimistic locking system that you'll need if there's a lot of concurrency. For that, typically, a 'sequence' number is added. Simple, effective.

Last, you're quite often only interested in one, or a few, types when doing reporting. A stringly typed 'type' column is often Good Enough.

Created at is handy, but not crucial.

So, you'll want something like

{ Id: uuid, aggregate_id: ?, sequence: Int, type: String, data: jsonb }

Querying then fetches "everything for aggregate x, ordered by sequence". Possibly limited to type X (e.g. when rebuilding a query index). But the first is what happens every time an Aggregate is hydrated (i.e. ShoppingCart::load(”acme/1337”) ) so it has to be indexed for that, primarily.

Let’s say I want to setup such a system, any idea where I could find a detailed write up?

This is an extremely well documented postgresql event sourcing reference implementation: https://github.com/eugene-khyst/postgresql-event-sourcing

Seconded as an excellent resource.

My team used it as a reference point for creating an event store for the write model and separate projection based read model in Golang. If you happen to be a Java shop you could obviously use the implementation too.

Reply on news.ycombinator.com