Skip to content

Output plugin to PostgreSQL (Copy, Batch insert)#3912

Closed
otherpirate wants to merge 12 commits intoinfluxdata:masterfrom
otherpirate:postgresql_output
Closed

Output plugin to PostgreSQL (Copy, Batch insert)#3912
otherpirate wants to merge 12 commits intoinfluxdata:masterfrom
otherpirate:postgresql_output

Conversation

@otherpirate
Copy link
Copy Markdown
Contributor

@otherpirate otherpirate commented Mar 20, 2018

Hi,

I created these plugins to handling with large number of metrics.
I'm using Isolated transaction and grouping insert by table/metric name

1. Using Copy
Example: https://medium.com/@amoghagarwal/insert-optimisations-in-golang-26884b183b35
Docs: https://www.postgresql.org/docs/9.1/static/sql-copy.html

2. Using Batch
All values are added in same insert line.

Extra:
I already tried #3428 and it is just too slow when we have a lot of metrics.
This PR also solve #3408.

Benchmark between PostgreSQL(svenklemm), InfluxDB, PostgreSQL Copy and PostgreSQL Batch

#First test
Output [postgresql] wrote batch of 10000 metrics in 12.24790546s
Output [influxdb] wrote batch of 10000 metrics in 133.712335ms
Output [postgresql_copy] wrote batch of 10000 metrics in 181.033963ms
Output [postgresql_batch] wrote batch of 10000 metrics in 325.482989ms

#Second test
Output [postgresql] wrote batch of 10000 metrics in 12.110281988s
Output [influxdb] wrote batch of 10000 metrics in 83.051051ms
Output [postgresql_copy] wrote batch of 10000 metrics in 207.410926ms
Output [postgresql_batch] wrote batch of 10000 metrics in 285.334801ms

#Third test
Output [postgresql] wrote batch of 10000 metrics in 11.474782971s
Output [influxdb] wrote batch of 10000 metrics in 77.17222ms
Output [postgresql_copy] wrote batch of 10000 metrics in 158.511947ms
Output [postgresql_batch] wrote batch of 10000 metrics in 286.208872ms

Obs1: I'm using (TimeScaleDB)
Obs2: All benchmark are running in cloud (each database in equal and isolated machine)
Obs3: This plugin DO NOT create tables (I'm working on it)

Required for all PRs:

  • Signed CLA.
  • Associated README.md updated.
  • Has appropriate unit tests.

@nerzhul
Copy link
Copy Markdown
Contributor

nerzhul commented Mar 21, 2018

#3428

@otherpirate
Copy link
Copy Markdown
Contributor Author

otherpirate commented Mar 21, 2018

Hi @nerzhul

I saw this PR, I tested the code, and it's too slow for large number of metrics, please, take a look in my tests.

@otherpirate otherpirate mentioned this pull request Mar 21, 2018
Closed
3 tasks
@glinton
Copy link
Copy Markdown
Contributor

glinton commented Sep 10, 2018

@otherpirate do you mind updating this branch with master so tests pass (also, we've updated to use dep), thanks.

@endersonmaia
Copy link
Copy Markdown

endersonmaia commented Feb 12, 2019

it would be nice to have the jsonb implementation for tags and fields from #3428 with the COPY implementation from this PR

@otherpirate
Copy link
Copy Markdown
Contributor Author

otherpirate commented Mar 15, 2019

Hi @glinton, could you review this PR again?

@glinton glinton added this to the 1.11.0 milestone Mar 15, 2019
@danielnelson danielnelson removed this from the 1.11.0 milestone May 20, 2019
@sjwang90
Copy link
Copy Markdown
Contributor

sjwang90 commented Nov 20, 2020

Can you clarify the discrepancies between this PR and #3428? That PR is a little more active that we may want to go with that one.

@otherpirate
Copy link
Copy Markdown
Contributor Author

otherpirate commented Nov 20, 2020

Can you clarify the discrepancies between this PR and #3428? That PR is a little more active that we may want to go with that one.

In Globo company, we still using this plugin, but the first is more active.
When I write this plugin (2018) the #3428 had no Copy method, and we need the performance of the Copy.
I did not test the latest version of #3428 but probably is very good
My suggestion to you is, try both and find what's fit better in your case :)

@sjwang90 sjwang90 added this to the Planned milestone Dec 9, 2020
@sjwang90 sjwang90 added the plugin/output 1. Request for new output plugins 2. Issues/PRs that are related to out plugins label Jan 26, 2021
@sjwang90 sjwang90 removed this from the Planned milestone Jan 29, 2021
@sjwang90 sjwang90 mentioned this pull request Feb 1, 2021
Closed
3 tasks
@sjwang90
Copy link
Copy Markdown
Contributor

sjwang90 commented Feb 1, 2021

Looks like PR #8651 (comment) covers copy.

Let's move forward with PR #8651 - feel free to test that PR and offer any feedback.

@sjwang90 sjwang90 closed this Feb 1, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

No reviews

Assignees

No one assigned

Labels

area/postgresql new plugin plugin/output 1. Request for new output plugins 2. Issues/PRs that are related to out plugins

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

6 participants

@otherpirate @nerzhul @glinton @endersonmaia @sjwang90 @danielnelson