DB Streaming - Sample DB #88

Activity

1. jvdd commented on Sep 25, 2026

   

   jvdd

   on Sep 25, 2026

   Last edited by jvdd

   Contributor

   More actions

   Hey @richjli.

   Thanks for the clear description of your data. I believe it is exactly the type of data that FlexViz is made for: many long runs that you want to compare.

   FlexViz does not connect to PostgreSQL itself. It explores a Polars DataFrame (in memory) or a LazyFrame (for example Parquet files on disk). For your read pattern, I believe this might be sufficient: you choose about 100 runs, so one SQL query can load their rows into Polars (if it does not fit in memory, you can sink it into parquet). Then FlexViz can very efficiently shows them.

   In the future we plan to add support for (remote) databases with proper query pushdown, avoiding the need to gather all data in a polars frame / sinking to parquet.

   Example code:

   import polars as pl
   from flexviz import Dashboard
   
   URI = "postgresql://user:password@host:5432/dbname"
   
   # 1. One query: choose the runs by their header fields, join the detail rows,
   #    and keep only the columns that you want to plot.
   query = """
   SELECT h.id AS run_id, h.tool_id, h.recipe,
          d.elapsed_s, d.chamber_pressure, d.temperature
   FROM log_header AS h
   JOIN log_detail AS d ON d.header_id = h.id
   WHERE h.tool_id = 'ETCH-07'
     AND h.started_at >= '2026-09-01'
   """
   df = pl.read_database_uri(query, URI)  # pip install connectorx
   
   # 2. Explore. cache=True tells FlexViz that the data does not change,
   #    which enables live cross-filtering while you drag a selection.
   dash = Dashboard(df, cache=True)
   pressure = dash.add_figure(title="Chamber pressure")
   pressure.add_line(x="elapsed_s", y="chamber_pressure", group_by="run_id")
   temp = dash.add_figure(title="Temperature")
   temp.add_line(x="elapsed_s", y="temperature", group_by="run_id")
   dash.add_figure(title="Runs per recipe").add_bar(
       labels="recipe", values="run_id", agg="n_unique"
   )
   dash.add_figure(title="Pressure distribution").add_histogram(
       x="chamber_pressure", bins=60
   )
   dash.link_axes(on="elapsed_s")  # zoom one time axis, the other follows
   dash.show()

   Change the table and column names to yours. I tested this with a copy of your schema (200 runs of 5,000 rows each) in SQLite. It gives one line per run, and a run that behaves differently is easy to see. What you can do in the page:

   • Zoom a time axis. FlexViz loads more detail for the zoomed range, and the linked chart follows.
   • Drag a box over a part of the pressure histogram. The other charts then show only those rows.
   • Drag a box over one or more recipe bars to filter on those recipes. (A click on a bar does not filter yet. We track that in Support bar click #91.)

   I hope this helps. Please let me know whether this approach works. If not, or when you encounter any other issues / stuff that is not entirely clear, feel free to further share them. I'll happily look into it :)
2. richjli commented on Oct 6, 2026

   

   richjli

   on Oct 6, 2026

   Author

   More actions

   Hi @jvdd,

   Apologies for the late response and thank you for the example code.

   The query you provided is basically how I pull the data now, and my current approach uses sqlalchemy to fetch the data and converts it into a pandas dataframe. Reading the data directly into a polars df performs similarly when I compare the two methods.

   100 process logs:
   

   70 mixed logs
   

   I'd be interested to see how quickly I can get the data into FlexViz when you add support for remote databases with query pushdown. I suspect the biggest performance gains would be me optimizing the database itself though.

   Also, do you have an email or another preferred contact method? Or would you prefer that I open issues if I had questions/feedback/requests?

   I demoed FlexViz to my team and they love it, and would like to move forward using it for our visualization needs. There were some concerns about data security that I could not answer (like whether data stays strictly within our network) since we handle a lot of sensitive customer data. And there were also some other features that I think would make the experience more smooth like double clicking a legend item to isolate it for all charts (rather than just the chart you clicked) and improved hover tool-tips to only show the data for the trace your mouse is over.

   Again, I'm really glad I learned about FlexViz and it's great how well it performs for my use case. Thanks again for your help.
3. jvdd commented on Oct 8, 2026

   

   jvdd

   on Oct 8, 2026

   Contributor

   More actions

   Hi @richjli,

   Thanks for the details, and great to hear your team likes FlexViz! :)

   Performance findings

   I reproduced your data shape in PostgreSQL 17 (~100 runs × 1,800 rows, 211 columns after the join). With the database on the same machine, I measured:

   Load method	Time
   SQLAlchemy + pandas (211 columns)	17.6 s
   ConnectorX (211 columns)	2.3 s
   ConnectorX (5 required columns)	0.3 s

   Your ConnectorX load (~16.8 s) is roughly 7× slower than my local result. The difference could come from query execution, network transfer, or data conversion. I believe the following 2 things might be worth checking:

   • Query execution: Run EXPLAIN (ANALYZE, BUFFERS) on your query. If execution takes ~16 s (versus 0.6 s in my test), inspect the query plan, particularly the join. PostgreSQL doesn't automatically index foreign keys, so check for an index on log_detail(header_id). Note that EXPLAIN ANALYZE excludes network transfer time.
   • Query count: Are you fetching each run separately? In my tests, 100 queries took 6.6 s versus 2.3 s for one combined query locally, and 51 s versus 3.6 s over a 30 ms latency connection.

   Could you share the PostgreSQL execution time, your process-value column types, and whether you query each run separately?

   What you can already try in the current version

   Fetch only the columns needed for your charts and filters, ideally in one query:

   query = """
   SELECT h.id AS run_id, h.recipe,
          d.elapsed_s, d.chamber_pressure, d.temperature
   FROM log_header h
   JOIN log_detail d ON d.header_id = h.id
   WHERE h.tool_id = 'ETCH-07'
   """
   df = pl.read_database_uri(query, URI)  # ConnectorX

   This reduced my load time from 2.3 s to 0.3 s locally (database on same instance), and from 158 s to 3.8 s over a 20 Mbit/s connection. Subsequent zooms and selections took ~0.2 s.

   If your process values use PostgreSQL numeric, consider casting them to double precision in SQL for plotting compatibility (e.g. d.chamber_pressure::double precision AS chamber_pressure).

   Database query pushdown (in development)

   I've also prototyped SQL query pushdown for PostgreSQL, DuckDB, and ClickHouse. Instead of loading the dataset into Python, FlexViz queries the database for each chart update and retrieves only the aggregated results.

   On your data shape:

   Operation	Local	20 Mbit/s
   Initial chart	0.7 s	4.5–4.8 s
   Zoom / selection	0.4–0.55 s	1.7–3.4 s

   For your workload, the narrow ConnectorX load is currently faster, especially over slower networks. With only ~1,800 samples per run, a 1,000-point line still transfers much of the data on each update.

   Pushdown's main advantages are different: exploring any available column without reloading, querying fresh data, and keeping the full dataset in the database.

   This is not released yet. Currently, this only updates cross-filtered charts on mouse release (no live brushing) and doesn't support box plots. I'll post an update here when it's available.

   Other questions

   • Security: FlexViz runs in your own Python process, with the dashboard server bound to 127.0.0.1 by default. It doesn't send your data or telemetry to Flex Analytics. The browser loads Plotly.js and GridStack from jsDelivr; map charts also request OpenStreetMap tiles. The server has no built-in authentication, so external deployments need access controls. Let me know if you require a fully offline setup.
   • Feature requests: Both make sense, so I opened Double-click a legend item to isolate that group in every chart #158 (isolate a legend item in all charts) and Show only the series under the mouse in the hover label of a grouped line #159 (hover label for the series under the mouse only). For the hover label, you can already try dash.add_figure(hovermode="closest") -> each chart then shows only the point nearest to the mouse.

   Getting in touch

   GitHub issues are best for general questions and feature requests so others can benefit too.

   For anything sensitive (schemas, security requirements, etc.), feel free to email me at jeroen@flexanalytics.pro. FlexViz is free and open source, developed by Flex Analytics. We also help teams deploy it on their own infrastructure, and I'm happy to arrange a short call if useful!
4. richjli commented on Oct 14, 2026

   

   richjli

   on Oct 14, 2026

   Last edited by richjli

   Author

   More actions

   Thank you for the ideas. Upon reading your response, I realized that the database and the streamlit app were actually not on the same server and the slowness likely comes from network transfer time or additional operations after it lands in python.

   I ran the checks that you suggested though, and here were the findings:

   • EXPLAIN (ANALYZE, BUFFERS) shows the 100 process log query executes in about 636 ms in PostgreSQL.
   • FK-side index is present and being used.
   • In the current retrieval path, runs are not being querying separately then combined after. Retrieval is bulked.
   • The query’s value columns are mostly double precision, with some integer columns mixed in, so this does not look like a heavy-numeric conversion issue.
   • I do have column sets that allow the user to narrow the number of columns queried, and this speeds up the query significantly, but I also want to ensure that larger datasets or extra columns are retrieved efficiently to allow users freedom to explore the data.

   I put in a request to migrate the database to the same server where the app is deployed and I'm hoping that this will solve the slowness issue.

   Regarding my other questions:

   • Thank you for getting back to me about FlexViz's security details. My use case for FlexViz is within an authenticated Streamlit app and access would be restricted to our company environment over VPN. Based on that setup, would you consider the authentication concern sufficiently mitigated, assuming FlexViz is not separately exposed for direct access?
   • There is a possibility that we may eventually deploy the log viewer app inside an isolated environment without internet access. We may need a fully offline setup if we decide to do so and I can open a new issue for that request if we go forward with that use case.
   • Thank you for opening issues Double-click a legend item to isolate that group in every chart #158 and Show only the series under the mouse in the hover label of a grouped line #159, I will be following them closely.
   • Using GitHub issues for general questions makes sense, and for the more sensitive details I may follow up by email instead. I appreciate your willingness to hop on a short call, and I may reach out if I'm really stuck.
5. jvdd commented on Oct 17, 2026

   

   jvdd

   on Oct 17, 2026

   Contributor

   More actions

   Hi @richjli,

   Thanks for running these checks. They rule out the query (0.64 s, index used), so your ~17 s ConnectorX load is almost all transfer: 181k rows × 211 columns is ~300 MB, which takes ~16 s at ~150 Mbit/s.

   For the move:

   • You can check the gain today: run the same ConnectorX load on the database server itself. In my test it took 2.3 s.
   • Load with ConnectorX or ADBC, not SQLAlchemy + pandas. On the same machine, SQLAlchemy + pandas still took 17.6 s in my test.

   The move will not fix your 70 mixed logs (628k rows × 1,191 columns, 184 s): that is ~750M values, about 6 GB per load. That is what database support (#164) is for. FlexViz sends one query per chart and reads only the columns the charts use, so users can explore any column without loading them all. On your 100-log shape, with the database on the same machine, the first view took 0.7 s and a zoom or selection 0.4–0.55 s. It is not released yet, but feedback from your database would help us a lot:

   pip install "flexviz[sql] @ git+https://github.com/flex-analytics/flexviz@feat/sql-source"
   pip install adbc-driver-postgresql

   import flexviz as fv
   
   src = fv.SQLSource(
       "postgresql://user:password@host:5432/dbname",
       query="""
           SELECT h.id AS run_id, h.recipe, d.*
           FROM log_detail AS d JOIN log_header AS h ON d.header_id = h.id
           WHERE h.tool_id = 'ETCH-07'
       """,
   )
   dash = fv.Dashboard(src)
   dash.add_figure().add_line(x="elapsed_s", y="chamber_pressure", group_by="run_id")
   dash.add_figure().add_histogram(x="chamber_pressure", bins=60)
   dash.show()

   d.* is fine: in the test above the query returned 211 columns and FlexViz read only the 5 the charts used. Limits of this version: a selection updates the other charts on mouse release, not while dragging; no box plots yet; the API can still change. Guide: databases.md.

   Security

   VPN plus a login is enough only if the login checks every request to FlexViz, not only the Streamlit page:

   • st.login does not. It runs inside your script, and routes mounted with st.App(routes=[Mount("/flexviz", ...)]) never run the script. I tested this on Streamlit 1.66: /flexviz/... answers without a login.
   • A reverse proxy or SSO gateway that checks every path does, because the dashboard loads from the same host and port as Streamlit.
   • FlexViz on its own port (show(), flexviz serve) needs the same protection on that port.
     Quick check: open https:///flexviz/sources in a private window without logging in. If it returns JSON (even []), anyone on the VPN can query FlexViz.
     Two more things to know:
   • Each user who gets through can query every column of every registered source, not only the charts you show. Sources stay registered until the server stops and are shared by all users. So if your app registers a source per user selection, other users can query it too.
   • With SQLSource, connect with a read-only database user.

   Offline

   Not possible today: the page loads Plotly.js and GridStack from jsDelivr, and map charts load OpenStreetMap tiles. Please open an issue if you go that way.