5 ms·

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

• datadeft 3y ago
  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.
  • withinboredom 3y ago
    > 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.
    • asah 3y ago
      see my reply above - one classic case is db system maintenance.
  • refset 3y ago
    "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-uncoordinated https://pathelland.substack.com/p/i-am-so-glad-im-uncoordina...
    • ldng 3y ago
      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.
      • refset 3y ago
        Absolutely, there's no beating the RUM Conjecture.
  • asah 3y ago
    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)
    • 3y ago
• btown 3y ago
  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/ 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 https://news.ycombinator.com/item?id=17817375 (2018) has some good conversations on the downsides to event sourcing as well.
  • agentultra 3y ago
    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.
  • macNchz 3y ago
    > 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.
• dgellow 3y ago
  Let’s say I want to setup such a system, any idea where I could find a detailed write up?
  • ryanjshaw 3y ago
    This is an extremely well documented postgresql event sourcing reference implementation: https://github.com/eugene-khyst/postgresql-event-sourcing https://github.com/eugene-khyst/postgresql-event-sourcing
    • NomDePlum 3y ago
      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.
• dfee 3y ago
  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#JSON-INDEXING https://www.postgresql.org/docs/current/datatype-json.html#J...
  • asah 3y ago
    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-effectively-store-index-json-data-in-postgresql 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).
  • berkes 3y ago
    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.