Code Monkey home page Code Monkey logo

Comments (2)

patriknw avatar patriknw commented on August 17, 2024

The delete is also rather slow, and I guess that could be a reason for why it often has to be "restarted".

explain analyze delete from akka_projection_timestamp_offset_store WHERE slice BETWEEN 0 and 31 AND projection_name = 'test-projection-id-0' AND timestamp_offset < '2021-11-22 16:58:21.801179+00';
                                                                                            QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Delete on akka_projection_timestamp_offset_store  (cost=0.00..16.25 rows=100 width=32) (actual time=349.157..349.157 rows=0 loops=1)
   ->  Index Scan using akka_projection_timestamp_offset_store_pkey on akka_projection_timestamp_offset_store  (cost=0.00..16.25 rows=100 width=32) (actual time=349.155..349.155 rows=0 loops=1)
         Index Cond: ((slice >= 0) AND (slice <= 31) AND ((projection_name)::text = 'test-projection-id-0'::text))
         Filter: (timestamp_offset < '2021-11-22 16:58:21.801179+00'::timestamp with time zone)
         Rows Removed by Filter: 1
 Planning Time: 0.098 ms
 Execution Time: 349.219 ms

We could use an append only approach instead of upserts. Then the primary key could be like this:

CREATE TABLE IF NOT EXISTS akka_projection_timestamp_offset_store2 (
 projection_name VARCHAR(255) NOT NULL,
 projection_key VARCHAR(255) NOT NULL,
 slice INT NOT NULL,
 persistence_id VARCHAR(255) NOT NULL,
 seq_nr BIGINT NOT NULL,
 -- timestamp_offset is the db_timestamp of the original event
 timestamp_offset timestamp with time zone NOT NULL,
 -- last_updated is when the offset was stored
 -- the consumer lag is last_updated - timestamp_offset
 last_updated timestamp with time zone NOT NULL,
 PRIMARY KEY(slice ASC, projection_name ASC, timestamp_offset ASC, persistence_id ASC, seq_nr ASC)
);

and the deletes could fully use the prefix of the primary key:

explain analyze delete from akka_projection_timestamp_offset_store2 WHERE slice BETWEEN 0 and 31 AND projection_name = 'test-projection-id-0' AND timestamp_offset < '2021-11-22 16:58:21.801179+00';
                                                                                              QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Delete on akka_projection_timestamp_offset_store2  (cost=0.00..16.00 rows=100 width=32) (actual time=0.672..0.672 rows=0 loops=1)
   ->  Index Scan using akka_projection_timestamp_offset_store2_pkey on akka_projection_timestamp_offset_store2  (cost=0.00..16.00 rows=100 width=32) (actual time=0.671..0.671 rows=0 loops=1)
         Index Cond: ((slice >= 0) AND (slice <= 31) AND ((projection_name)::text = 'test-projection-id-0'::text) AND (timestamp_offset < '2021-11-22 16:58:21.801179+00'::timestamp with time zone))
 Planning Time: 3.769 ms
 Execution Time: 0.732 ms

from akka-persistence-r2dbc.

patriknw avatar patriknw commented on August 17, 2024

Let's see if #164 solves this.

from akka-persistence-r2dbc.

Related Issues (20)

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.