Partitioning support for Event Store

[jokokko]

To avoid table bloat with the Marten Event Store, events table (mt_events by default) could be partitioned using PostgreSQL table partitioning.

While the table can already be manually partitioned after creation with table inheritance in PG 9.x, PG 10 will introduce native table partitioning with new prerequisites (and limitations): https://www.postgresql.org/docs/10/static/ddl-partitioning.html Given that "It is not possible to turn a regular table into a partitioned table or vice versa.", it would be extremely useful to be able to define a partitioning scheme upfront as Marten creates the necessary tables.

[cocowalla]

It would be great to also support this on arbitrary tables when using Marten as a document database, not just as an Event Store.

[jokokko]

Indeed. But if it's implemented for Event Store tables (well, mainly mt_events), should not be too big a step to generalize for all tables. The hardest thing likely is to offer a flexible way to define the actual partitioning scheme. Then again, being able to do it via Marten would be a huge advantage, as, afaik, tables cannot be partitioned afterwards (talking about native partitioning in PG 10).

[turbo]

I'm relying on Marten for most persistence in my app, but there are two massive tables that are still raw PGSQL because they need partitioning supports (by tenant, timestamp). It would be nice to handle this in Marten.

Although I'd urge you to wait for PG11, which will have quite a few partition improvements.

[oskardudycz]

@turbo currently you can use Conjoined Tenancy that will at least give you possibility to split it by tenant (also stream and events table). I'll have a look on the PG partitioning in PG 10.

[isen-ng]

Seems like PG11 has been released
https://www.postgresql.org/about/news/1894/

[jeremydmiller]

My thoughts:

Just use what Postgresql 11 can do

• Partitioning by tenant id should work with native Postgresql support, but I'd rather have someone test that before claiming so
• Allow users to identify streams by long numbers backed by a sequence and then allow native Postgresql to do its thing?
• See if Postgresql "range" partitioning can work by sequential UUIDs?
• Allow users to partition streams by the creation date (not sure how valuable that would be, seems like you'd be mostly querying by stream Id or metadata)
• If we did stream level metadata by duplicated columns, it seems like that would greatly aid in partitioning

If we programmatically "route" events to separate tables

• Gotta figure out how event/streams get assigned by logical partition. Do you do it by stream type, which makes you require the stream type in StartStream() and that hasn't been popular. Could you do it by event type? That's easier to handle in the routing in a way.
• The "routing" would need to be part of appending events, the async daemon (totally hoses up the async daemon the way it is today, but we can beat that), and any and all event store queries
• Route by metadata elements? That's more complexity
• Do we require users to explicitly choose the logical event store? That's easier, but might cause usage problems. Worth asking folks who care about this
• If we separate out the events, with today's design it's an event table, the stream table, a generated function to append to the table, and a sequence to track event numbers. If we partition, do we have one of all of these per logical event store?

Async Daemon

• The async daemon might need to take advantage of the partitioning for improved performance. Rebuilds might go much faster if you use "range" partitioning by event sequence #

[oskardudycz]

@jeremydmiller I think that maybe we could start with Native partitioning by stream type? Imho this could be the easiest way to start investigation around that topic. I could try to work on PoC. (eg. by adding new Tenancy "ByStreamType").

If we have the paritioning by stream type then we could try to extend that with the next levels like by date, stream id etc.

I agree that duplicate field would be helpfull and that it would be worth to checking if the MetaData would help us on partitioning.

Imho it would be also worth checking TimescaleDB as it might potentialy simplify that process a lot (@cocowalla did already some initial investigations).

p.s. nice introduction to partitioning https://severalnines.com/blog/how-take-advantage-new-partitioning-features-postgresql-11

[jeremydmiller]

@oskardudycz @mysticmind Getting back into this a little bit today. Some thoughts:

• Haven't spoken w/ y'all about this yet, but I kinda like the idea of letting folks use additional event collections instead of the single, default event collection we have today. That segments things real fast, but it adds all kinds of complexity down the line. So rather than today's stream type (or in addition to), we allow you to define a completely separate event collection (stream & events table)
• If you're using the async daemon in a polling mode, I'd vote to have this segmented by a range of the seq_id because that's how the async daemon hits the table.
• I agree about duplicating information from the stream to the event table if that helps the partitioning work

[jeremydmiller]

From the other day, @mysticmind, @oskardudycz, and I talked about:

• Adding some kind of new "is_archived" flag, then partitioning on that first
• Partition against the event sequence if users are using the async daemon
• Use indexes against the version field if users want that one.
• Index against stream id or key if the user intends to read events by stream

There's some opportunity to thin down the indexing for speed.

[jeremydmiller]

This isn't a slam dunk, and it's going to add some extra work to users. It makes perfect sense to partition against:

• is_archived by list. This would be tremendously helpful for performance if folks would be rigorous about moving event streams to the archived state
• tenant_id by either list or range if it exists. And I'd allow the users to choose how to do that.
• Sequence? It'd help the async daemon tremendously, but it would require either guessing upfront how their event table is going to grow, or setting up work to create new partitions as necessary. I don't think it'd be that necessary if we do the event archiving
• If using string identifiers for the stream id, maybe allow users to partition by the stream id? That might help tremendously if there's some kind of meaning.
• Partition by aggregate/stream type. Seems like a no-brainer.
• It might sometimes be valuable to partition by event type

For indexes:

• There's already a unique index for version + stream + sometimes tenant_id, depending on configuration

[nkosi23]

What would be the migration story of such a feature? Would there be a way for existing users to leverage such a new feature without too much pain?

[oskardudycz]

@nkosi23, we're still in the planning phase, we'd for sure provide the migration guide, but it may require copying data from old tables to new if you want to enable partitioning. See more in:

• https://engineering.workable.com/postgres-live-partitioning-of-existing-tables-15a99c16b291
• https://aws.amazon.com/blogs/database/partition-existing-tables-using-native-commands-in-amazon-rds-for-postgresql-and-amazon-aurora-postgresql/