On Tue, Jun 25, 2019 at 2:19 AM Prabhat Sahu <prabhat.s...@enterprisedb.com> wrote: > I have tested the TOAST patches(v3) with different storage options like(MAIN, > EXTERNAL, EXTENDED, etc.), and > combinations of compression and out-of-line storage options. > I have used a few dummy tables with various tuple count say 10k, 20k, 40k, > etc. with different column lengths. > Used manual CHECKPOINT option with (checkpoint_timeout = 1d, max_wal_size = > 10GB) before the test to avoid performance fluctuations, > and calculated the results as a median value of a few consecutive test > executions.
Thanks for testing. > All the observation looks good to me, > except for the "Test1" for SCC UPDATE with tuple count(10K/20K), for SCC > INSERT with tuple count(40K) there was a slightly increse in time taken > incase of "with patch" result. For a better observation, I also have ran the > same "Test 1" for higher tuple count(i.e. 80K), and it also looks fine. Did you run each test just once? How stable are the results? > While testing few concurrent transactions I have below query: > -- Concurrent transactions acquire a lock for TOAST option(ALTER TABLE .. SET > STORAGE .. MAIN/EXTERNAL/EXTENDED/ etc) > > -- Session 1: > CREATE TABLE a (a_id text PRIMARY KEY); > CREATE TABLE b (b_id text); > INSERT INTO a VALUES ('a'), ('b'); > INSERT INTO b VALUES ('a'), ('b'), ('b'); > > BEGIN; > ALTER TABLE b ADD CONSTRAINT bfk FOREIGN KEY (b_id) REFERENCES a (a_id); > -- Not Acquiring any lock For me, this acquires AccessShareLock and ShareRowExclusiveLock on the target table. rhaas=# select locktype, database, relation, pid, mode, granted from pg_locks where relation = 'b'::regclass; locktype | database | relation | pid | mode | granted ----------+----------+----------+-------+-----------------------+--------- relation | 16384 | 16872 | 93197 | AccessShareLock | t relation | 16384 | 16872 | 93197 | ShareRowExclusiveLock | t (2 rows) I don't see what that has to do with the topic at hand, though. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company