Matt, I assume you are running a single-host PostgreSQL. If so, your numbers don't look too bad I would say. You may want to consider the distributed variant, which is Greenplum. However I am not aware of any deployment of our extension in such environments.
On Fri, Jul 9, 2021 at 2:41 PM Will Lauer <[email protected]> wrote: > Matt, > > In my production case, I'm building sketches using java in an ETL pipeline > and then loading them into a Druid datamart, which aggregates them together > when it receives queries. Queries might aggregate several hundred sketches > all the way to many millions (the average number is probably in the 100's > of thousands), depending on the time frame involved in the query and the > particular dimensions selected. The majority of our queries (95%+) return > in less than 10 seconds. This is running on a cluster with between 150 and > 200 nodes. > > We are investigating implementing this in an alternative database, but > haven't gotten that database working in a performant way yet (due to some > problems with the databases' API, not due to sketches), but are working > with the vendor to find some workarounds. > > Will > > <http://www.verizonmedia.com> > > Will Lauer > > Senior Principal Architect, Audience & Advertising Reporting > Data Platforms & Systems Engineering > > M 508 561 6427 > 1908 S. First St > Champaign, IL 61822 > > > <https://urldefense.proofpoint.com/v2/url?u=http-3A__www.facebook.com_verizonmedia&d=DwMFaQ&c=sWW_bEwW_mLyN3Kx2v57Q8e-CRbmiT9yOhqES_g_wVY&r=0TpvE_u2hS1ubQhK3gLhy94YgZm2k_r8JHJnqgjOXx4&m=JkYL6uq0qoDR1Cvko3w9WWpX6sPJ5r64kDiNY_i0Stk&s=57XsolCiQCmaWB6pOS1IQ3j3GHdH3P95fd1GxvaPJ2M&e=> > > <https://urldefense.proofpoint.com/v2/url?u=http-3A__twitter.com_verizonmedia&d=DwMFaQ&c=sWW_bEwW_mLyN3Kx2v57Q8e-CRbmiT9yOhqES_g_wVY&r=0TpvE_u2hS1ubQhK3gLhy94YgZm2k_r8JHJnqgjOXx4&m=JkYL6uq0qoDR1Cvko3w9WWpX6sPJ5r64kDiNY_i0Stk&s=sYwbOS3PJaMGg7HGlH8AtHTxJrjAr-zbzNptyihuvDM&e=> > > <https://urldefense.proofpoint.com/v2/url?u=https-3A__www.linkedin.com_company_verizon-2Dmedia_&d=DwMFaQ&c=sWW_bEwW_mLyN3Kx2v57Q8e-CRbmiT9yOhqES_g_wVY&r=0TpvE_u2hS1ubQhK3gLhy94YgZm2k_r8JHJnqgjOXx4&m=JkYL6uq0qoDR1Cvko3w9WWpX6sPJ5r64kDiNY_i0Stk&s=KqW7eRDxcvjFALxVALwlain6zSytoHqDJLipg3rSunM&e=> > > <https://urldefense.proofpoint.com/v2/url?u=http-3A__www.instagram.com_verizonmedia&d=DwMFaQ&c=sWW_bEwW_mLyN3Kx2v57Q8e-CRbmiT9yOhqES_g_wVY&r=0TpvE_u2hS1ubQhK3gLhy94YgZm2k_r8JHJnqgjOXx4&m=JkYL6uq0qoDR1Cvko3w9WWpX6sPJ5r64kDiNY_i0Stk&s=trPaP60q5pMtDCgPGneuxL0UMfJ8DnavcbkMHNiHj9Y&e=> > > > > On Fri, Jul 9, 2021 at 2:57 PM Matthew Farkas <[email protected]> wrote: > >> Hi, >> >> I'm running PG 13.3 and pg-datasketches 1.3.0 (I built from master after >> running into this issue >> <https://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_apache_datasketches-2Dpostgresql_issues_34&d=DwMFaQ&c=sWW_bEwW_mLyN3Kx2v57Q8e-CRbmiT9yOhqES_g_wVY&r=vGHo2vqhE2ZeS_hHdb4Y3eoJ4WjVKhEg5Xld1w9ptEQ&m=4tX6hAxcgLT0zeFgrAKVZ-oxngSqXLrUVy9rYDZIPZE&s=bEI9ZIoMM-58NW0wMXeJ0Ben3Mg0BYk2FamasN9e75A&e=> >> ). >> >> So some rough numbers- I have a week-hour table with 168 user_id >> sketches, all would be estimates and not exact, and that is taking 21ms for >> unioning those 168 sketches. >> - 13k sketches is taking 1-2s >> - 13m sketches was taking ~2min yesterday (I must have updated a config >> that hurt this, though, I'm cancelling the query after 9mins now) >> >> Will- >> Thanks for the background. So you're combining the sketches in Java- are >> you retrieving them from a db? Also, how many sketches are you typically >> merging? >> >> >> >> *Matthew Z. Farkas* >> >> Data Science @ Spotify >> MS Northwestern University, BS Georgia Tech >> >> m: (770) 337-2709 >> e: [email protected] >> >> >> <https://urldefense.proofpoint.com/v2/url?u=https-3A__www.linkedin.com_in_matthewzfarkas&d=DwMFaQ&c=sWW_bEwW_mLyN3Kx2v57Q8e-CRbmiT9yOhqES_g_wVY&r=vGHo2vqhE2ZeS_hHdb4Y3eoJ4WjVKhEg5Xld1w9ptEQ&m=4tX6hAxcgLT0zeFgrAKVZ-oxngSqXLrUVy9rYDZIPZE&s=wR4KZ0n2kgAyu0WCCxyxdMddHWTfnUSaY9H4r9fjJ2U&e=> >> >> >> On Fri, Jul 9, 2021 at 1:53 PM Alexander Saydakov >> <[email protected]> wrote: >> >>> Hi Matt, >>> What version of PostgreSQL and DataSketches are you using? >>> Could you give some numbers? How many sketches? How long does the union >>> take? >>> >>> The graph you are referring to was based on performance in Druid I >>> believe. So it may or may not be transferable to PostgreSQL. We did not do >>> a large-scale test in PostgreSQL. >>> >>> Also we have a performance improvement in the works, which is supposed >>> to avoid some cost of deserialization of Theta sketches. It might speed >>> things up 10-15% according to some preliminary testing. >>> >>> >>> >>> On Fri, Jul 9, 2021 at 10:32 AM Matthew Farkas <[email protected]> >>> wrote: >>> >>>> Hi Will, >>>> >>>> Thanks for the quick response! For your questions: >>>> >>>> 1. Yup, looking at Theta sketches for set operations. >>>> 2. So I'm creating the initial sketches in dataflow like so, with >>>> K=4096 (so lgK=12) right now: >>>> UpdateSketch userSketch = UpdateSketch.builder().build(K); >>>> userSketch.update(requestValue.userId()) >>>> // pass to PG using >>>> ByteString.copyFrom(userSketch.compact().toByteArray()); >>>> 3. By "sketch size", do you mean the number of uniques in each sketch? >>>> If so, there's a good bit of variance in sketch size, as I'm segmenting (by >>>> dimensions like demo, geo, etc.) users and saving a sketch for each >>>> segment. >>>> 4. I do not know the proportion that are in direct vs. estimation. >>>> (Admittedly, I'm not familiar with the differences there, will check it >>>> out.) Is this explicitly set? Or maybe determined based on K & sketch size. >>>> >>>> One thing I found interesting was that doing a >>>> `THETA_SKETCH_UNION(user_id_sketch, 10)` on all sketches vastly improved >>>> query time (70s to 6s), and produced the exact same results. I expected the >>>> results to be the same, since lgK=12 when originally creating the sketches, >>>> but I'm not sure why that would improve query time. >>>> >>>> Thanks again! >>>> >>>> >>>> >>>> *Matthew Z. Farkas* >>>> >>>> Data Science @ Spotify >>>> MS Northwestern University, BS Georgia Tech >>>> >>>> m: (770) 337-2709 >>>> e: [email protected] >>>> >>>> >>>> <https://urldefense.proofpoint.com/v2/url?u=https-3A__www.linkedin.com_in_matthewzfarkas&d=DwMFaQ&c=sWW_bEwW_mLyN3Kx2v57Q8e-CRbmiT9yOhqES_g_wVY&r=0TpvE_u2hS1ubQhK3gLhy94YgZm2k_r8JHJnqgjOXx4&m=3trc9dYkJzjsSQRfnDur7ImwclKqOBk4r-JAAZZewII&s=zHLsL8UzcCcVZJGnwJ_cAY9tZt12_0GAe-aetSX7hRs&e=> >>>> >>>> >>>> On Fri, Jul 9, 2021 at 1:13 PM Will Lauer >>>> <[email protected]> wrote: >>>> >>>>> Welcome Matt! >>>>> >>>>> One of the others is probably best qualified to answer your question, >>>>> but I'll chime in early with a couple of questions. The performance of >>>>> merging depends on many factors, including type of sketch and sketch size. >>>>> I'm assuming from the link you posted that you are dealing with Theta >>>>> sketches, for count unique operations. Can you confirm that? If so, what's >>>>> the logK you are using? What is the sketch size? Do you happen to know >>>>> what >>>>> proportion of your sketches are in estimation mode vs exact mode? >>>>> >>>>> Will >>>>> >>>>> <http://www.verizonmedia.com> >>>>> >>>>> Will Lauer >>>>> >>>>> Senior Principal Architect, Audience & Advertising Reporting >>>>> Data Platforms & Systems Engineering >>>>> >>>>> M 508 561 6427 >>>>> 1908 S. First St >>>>> Champaign, IL 61822 >>>>> >>>>> >>>>> <https://urldefense.proofpoint.com/v2/url?u=http-3A__www.facebook.com_verizonmedia&d=DwMFaQ&c=sWW_bEwW_mLyN3Kx2v57Q8e-CRbmiT9yOhqES_g_wVY&r=0TpvE_u2hS1ubQhK3gLhy94YgZm2k_r8JHJnqgjOXx4&m=3trc9dYkJzjsSQRfnDur7ImwclKqOBk4r-JAAZZewII&s=jRrfF2nGEDNEOSN9u2TMIRbAao3Qya1dLiv0QLMNIrw&e=> >>>>> >>>>> <https://urldefense.proofpoint.com/v2/url?u=http-3A__twitter.com_verizonmedia&d=DwMFaQ&c=sWW_bEwW_mLyN3Kx2v57Q8e-CRbmiT9yOhqES_g_wVY&r=0TpvE_u2hS1ubQhK3gLhy94YgZm2k_r8JHJnqgjOXx4&m=3trc9dYkJzjsSQRfnDur7ImwclKqOBk4r-JAAZZewII&s=R7lAUjJWXf1nxnzQVpYAnTkOe0Nj7JensDwaKj9B-r0&e=> >>>>> >>>>> <https://urldefense.proofpoint.com/v2/url?u=https-3A__www.linkedin.com_company_verizon-2Dmedia_&d=DwMFaQ&c=sWW_bEwW_mLyN3Kx2v57Q8e-CRbmiT9yOhqES_g_wVY&r=0TpvE_u2hS1ubQhK3gLhy94YgZm2k_r8JHJnqgjOXx4&m=3trc9dYkJzjsSQRfnDur7ImwclKqOBk4r-JAAZZewII&s=l_zRh61jHy17fBuu9BQPIqxm4y9-HZCwKEtwhH8Qnos&e=> >>>>> >>>>> <https://urldefense.proofpoint.com/v2/url?u=http-3A__www.instagram.com_verizonmedia&d=DwMFaQ&c=sWW_bEwW_mLyN3Kx2v57Q8e-CRbmiT9yOhqES_g_wVY&r=0TpvE_u2hS1ubQhK3gLhy94YgZm2k_r8JHJnqgjOXx4&m=3trc9dYkJzjsSQRfnDur7ImwclKqOBk4r-JAAZZewII&s=L5CKzXaeysdQ8JJq0pCGb3V6CM43b-vd-9vUK5qEgk8&e=> >>>>> >>>>> >>>>> >>>>> On Fri, Jul 9, 2021 at 12:02 PM Matthew Farkas <[email protected]> >>>>> wrote: >>>>> >>>>>> Hi, >>>>>> >>>>>> My name is Matt and I'm a data engineer at Spotify. I'm testing out >>>>>> trying Data Sketches with Postgres, and running into some >>>>>> performance issues. I'm seeing merge times much slower than what I'm >>>>>> seeing >>>>>> in the docs here >>>>>> <https://urldefense.proofpoint.com/v2/url?u=https-3A__datasketches.apache.org_docs_Theta_ThetaMergeSpeed.html&d=DwMFaQ&c=sWW_bEwW_mLyN3Kx2v57Q8e-CRbmiT9yOhqES_g_wVY&r=vGHo2vqhE2ZeS_hHdb4Y3eoJ4WjVKhEg5Xld1w9ptEQ&m=wfXanJfFTJqpoX0hDe-0GzEkE5YndUaxQMI4dCAQM3c&s=R8BDffIXwyiZ46IUKowhz2-gQqGfpM3u-KkwplE4Ing&e=> >>>>>> (millions >>>>>> of sketches/sec). >>>>>> >>>>>> In my case, I've pre-computed many sketches, inserted then into PG, >>>>>> then I'm running queries in PG and doing the merging there. My hunch is >>>>>> that there's something wrong with my Postgres configs, which I've tried >>>>>> tweaking extensively but haven't been able to improve query time. >>>>>> >>>>>> My question is if anyone knows what type of performance can be >>>>>> expected in Postgres and if anyone has any examples/tips in general from >>>>>> their implementations. >>>>>> >>>>>> Also, this is my first message to this list, so please let me know if >>>>>> I should be directing it anywhere else! >>>>>> >>>>>> Thanks!! >>>>>> Matt >>>>>> >>>>>> >>>>>> >>>>>> *Matthew Z. Farkas* >>>>>> >>>>>> Data Science @ Spotify >>>>>> MS Northwestern University, BS Georgia Tech >>>>>> >>>>>> m: (770) 337-2709 >>>>>> e: [email protected] >>>>>> >>>>>> >>>>>> <https://urldefense.proofpoint.com/v2/url?u=https-3A__www.linkedin.com_in_matthewzfarkas&d=DwMFaQ&c=sWW_bEwW_mLyN3Kx2v57Q8e-CRbmiT9yOhqES_g_wVY&r=vGHo2vqhE2ZeS_hHdb4Y3eoJ4WjVKhEg5Xld1w9ptEQ&m=wfXanJfFTJqpoX0hDe-0GzEkE5YndUaxQMI4dCAQM3c&s=WBAi_Zz2AI6QpCCX6AsWbHRrBwTG4JtAMLfzxzllOU4&e=> >>>>>> >>>>>
