Just after having submitted the bug report, i saw that when a transaction have 4096 or more operations (inserts for
example),I see postgres generating files in pg_repslot/my_slot directory. Is that what you call "spilling to disk" ?
BTWmy hard disk is a SSD
To be more concise :
- ONE transaction with 4095 operations -> consumed in 80mS
- TEN transactions with 4095 operations each (so 40950 operations) -> 380mS (which as you said is very good -less than
lineargrowth-)
- ONE transaction with 4096 operations -> consumed in 4204mS (ouch ...). I confirm there are only 4096 changes in one
transaction
- TEN transactions with 4095 operations each (so 40950 operations) -> 34998mS, ouch again
I know the SQL interface is not the best thing but it matches my use case. Let me explain.
I'm currently evaluating postgres for my company (a quite big one) as a mixed JSON/SQL storage + notification system
basedon data change feeding a HTTP infrastructure. We use .NET client to communicate with database.
I've evaluated postgres against Oracle 12c (we have been using Oracle for 18 years), SQL Server, MongoDB (not ACID
whichis a real pain), OrientDB (promising but unreliable) and RethinkDB. The JSON implementation of postgres is in my
opinionvery very good, cleverly made and have good performance (much better than Oracle in many cases). I'm very
impressedoverall by everything i have tried in postgres.
I need some sort of change data capture mechanism (CDC) to detect changes on data (async is ok for me as long as i get
verylow latency). I could use triggers but performance would suffer. Reading the WAL log seems like it could be a good
approach.
The current "simple" interface to consume a slot is thru this SQL interface, that's why i used it while prototyping
things.
I also tried pg_recvlogical feeding a file, but got the exact same results so i'm stuck. What is observe is that the
spillingoccurs, and when the .snap file is created then pg_recvlogical will consume data but it does take a long time
exactlythe same time as pg_logical_slot_get_changes in fact.
BTW there is also a bug in pg_recvlogical with option -f - (output to stdout), pg_recvlogical tries to flush with fsync
whichdoes not work on windows and display an error message.
Best regards,
Olivier