first draft

2 files changed, 441 insertions, 0 deletions

diff --git a/content/posts/analytics/index.md b/content/posts/analytics/index.md
new file mode 100644
index 0000000..e098b3a
--- /dev/null
+++ b/content/posts/analytics/index.md
@@ -0,0 +1,304 @@
+---
+title: "Managing analytical workloads in startups"
+draft: true
+description: ""
+categories:
+  - PostgreSQL
+---
+
+## Current issues
+
+- Issues with querying large dataset in online postgres (90GB)
+- The dataset is analytics events and some business models.
+- Often used for AB testing purpose, for instance measuring conversion rates grouped by some experiment values.
+- We a have a readonly database, the queries are slow
+
+## Hypothesis/Solutions
+
+- Writing better queries: this also needs to be maintainable (e.g. we add more and more queries, it should not need to be carefully handcrafted)
+- Using another engine
+  - Postgres like but OLAP oriented
+  - Pure OLAP (Clockhouse, Duckdb)
+  - AWS for simplicity (Athena or Redshift Spectrum)
+  - Scripts on parquet files: maximum adaptability
+- How to get the data?
+  - it seems that it is already parquets
+  - it looks too slow to download all the data periodically: process in the cloud or incremental sync
+- Parquet:
+  - possible to cluster by event? by datetime?
+  - sounds like recreating bigquery
+
+## Exporting cloud data
+
+- In AWS RDS snapshot, we can export only part of the data. In our case, we can start by `greengobackend.public.core_experimentationanalyticsevent`
+- The whole database size is supposed to be 390GB, with 65GB of `core_experimentationanalyticsevent`
+- The exported folder in S3 is only 3.8GB of parquet files, impressive
+- There are 20M events in the database
+
+## DuckDB
+
+- We may try later to query from s3, for now simply download it
+- `aws s3 sync s3://bi-data-test-gg/export-experimentationanalyticsevent experimentationanalyticsevent`
+- As always when I use this type of tool, it ate my RAM and my desktop crashed
+- It looks like it's not the easiest on my machine to create table from parquet files
+  Let's try a simple summary request to compare potential performances
+
+```sql
+with growthbook_experimentationanalyticsevent as (
+select
+  sent_at AS timestamp,
+  device_information ->> 'greengo_device_id' AS device_id,
+  event ->> 'customEventID' AS custom_event_id
+from 'experimentationanalyticsevent/greengobackend/public.core_experimentationanalyticsevent/1/*.gz.parquet'
+)
+select custom_event_id, count(*) from growthbook_experimentationanalyticsevent group by custom_event_id;
+```
+
+I will then run the same query on my Big production postgres, and also on my readonly replica (smaller). It doesn't use more than 600MB on duckdb.
+
+| Context             | Duration |
+| ------------------- | -------- |
+| Duckdb              | 10.1s    |
+| Duckdb 16GB         | 10.0s    |
+| Postgres production | 180s     |
+| Postgres readonly   |          |
+
+## Analytical workload
+
+Then I'd like to run a real workload : an AB test experiment result.
+It requires only a few language tweaks and type cast.
+The result is slower than I expected (49.5s), but the same query on production did not complete in several minutes.
+
+The query ran directly on the parquet file: let's try to create a physical backed table within duckdb to see if it improves overall latency. `CREATE TABLE growthbook_experimentationanalyticsevent AS SELECT * FROM 'file.gz.parquet'`
+I get some conversion error while trying to create a table from the query.
+Creating the table takes more than 20GB of RAM, not easy on my system.
+The table is 50GB large. I expect queries to run faster du to less compression.
+
+> The memory_limit setting controls how much data DuckDB is allowed to keep in memory. By default, this is set to 80% of the physical RAM of your system (e.g., if your system has 16 GB RAM, this defaults to 12.8 GB). The memory limit can be changed using the following command: `SET memory_limit = '4GB';`
+
+Weirdly, it is slower when reading from the materialized table.
+
+We can try to go once step further, and materializing the CTE in a parquet file. `COPY (SELECT * FROM 'file.gz.parquet') TO 'file-2.gz.parquet' (FORMAT parquet)`. Desktop died once again, I will now limit memory to 16GB (and run again some of the benchmarks).
+
+I don't understand, I can select the CTE, but when I materialize it I have a format error. I'll use [TRY_CAST](https://duckdb.org/docs/stable/sql/expressions/cast.html#try_cast) instead of CAST.
+
+First I exported the CTE to a compressed parquet: it took >2m, it's 3.0GB large. Let's try to perform the query on it.
+
+Then export the CTE to a physical duckdb: the previous db size was 50GB, it took 2m42, the database is now 82GB, so I expect 32GB of new data. Let's perform the query on it.
+
+The results are shocking, I don't even have indices (ok maybe yuo don't do indices on OLAP databases).
+
+Some insights taken from explain analyze:
+
+- Table scan (20M rows) filtered into 14k rows takes 600ms (ok numbers don't add up)
+- The same parquet scan (14k of 20M) takes 940ms. It's very fast.
+- The multiple parquet scan take 80s, it's a bottleneck. The filtering seems to be done later.
+
+| Context                                | Duration |
+| -------------------------------------- | -------- |
+| CTE from parquet                       | 49.5s    |
+| CTE from physical table                | 1m22     |
+| CTE materialized in compressed parquet | 600ms    |
+| CTE materialized in physical table     | 300ms    |
+
+The expected results for reference:
+
+```
+variation	dimension	users	main_sum	main_sum_squares	covariate_sum	covariate_sum_squares	main_covariate_sum_product
+0	""	4647	603	603.0	10	10.0	3
+__multiple__	""	1	0	0.0	0	0.0	0
+1	""	4422	589	589.0	12	12.0	4
+```
+
+{{<details summary="Our sample analytics query for AB test experiment">}}
+
+```sql
+WITH growthbook_experimentationanalyticsevent AS (
+    SELECT
+        CAST(sent_at) AS timestamp,
+        device_information ->> 'greengo_device_id' AS device_id,
+        event ->> 'customEventID' AS custom_event_id,
+        event ->> 'elementLabel' AS element_label,
+        event ->> 'elementSecondaryLabel' AS element_secondary_label,
+        -- Strings
+        event -> 'value' -> 'experiment' ->> 'key' AS experiment_id,
+        event ->> 'featureKey' AS feature_key,
+        event ->> 'step' AS step,
+        event ->> 'filterItem' AS filter_item,
+        event ->> 'paymentType' AS payment_type,
+        event -> 'tripSearchFormValues' ->> 'tripSearchMode' AS trip_search_mode_from_form_values,
+        event -> 'hostingSearchParameters' ->> 'tripSearchMode' AS trip_search_mode_from_hosting_search_parameters,
+        event ->> 'bookingMode' AS booking_mode,
+        -- JSON
+        event -> 'featureValue' AS feature_value,
+        event -> 'value' AS _value,
+        -- Cast
+        CAST(event -> 'value' -> 'result' ->> 'variationId' AS int) AS variation_id,
+        CAST(event ->> 'index' AS int) AS _index,
+        CAST(event ->> 'isSelected' AS boolean) AS is_selected,
+        CAST(event ->> 'isScrollable' AS boolean) AS is_scrollable,
+        CAST(event ->> 'numberOfFullyVisibleFilters' AS int) AS number_of_fully_visible_filters,
+        CAST(event ->> 'value' AS varchar) AS value_as_string,
+        CAST(event ->> 'totalPrice' AS decimal) AS total_price,
+        CAST(event ->> 'totalPricePaidAsMoney' AS decimal) AS total_price_paid_as_money
+    FROM
+        core_experimentationanalyticsevent
+),
+__rawExperiment AS (
+    SELECT
+        device_id,
+        timestamp,
+        experiment_id,
+        variation_id
+    FROM
+        growthbook_experimentationanalyticsevent
+    WHERE
+        custom_event_id = 'CE0158'
+        AND timestamp BETWEEN '2025-03-18 18:00:00' AND '2025-04-11 07:01:02'
+),
+__experimentExposures AS (
+    -- Viewed Experiment
+    SELECT
+        e.device_id AS device_id,
+        CAST(e.variation_id AS varchar) AS variation,
+        e.timestamp AS timestamp
+    FROM
+        __rawExperiment e
+    WHERE
+        e.experiment_id = 'new-checkout-mobile-conversion-rate'
+        AND e.timestamp >= '2025-03-18 18:00:00'
+        AND e.timestamp <= '2025-04-11 07:01:02'
+),
+__experimentUnits AS (
+    -- One row per user
+    SELECT
+        e.device_id AS device_id,
+(
+            CASE WHEN COUNT(DISTINCT e.variation) > 1 THEN
+                '__multiple__'
+            ELSE
+                MAX(e.variation)
+            END) AS variation,
+        MIN(e.timestamp) AS first_exposure_timestamp
+    FROM
+        __experimentExposures e
+    GROUP BY
+        e.device_id
+),
+__distinctUsers AS (
+    SELECT
+        device_id,
+        CAST('' AS varchar) AS dimension,
+        variation,
+        first_exposure_timestamp AS timestamp,
+        DATE_TRUNC('day', first_exposure_timestamp) AS first_exposure_date,
+        first_exposure_timestamp AS preexposure_end,
+        first_exposure_timestamp - INTERVAL '336 hours' AS preexposure_start
+    FROM
+        __experimentUnits
+),
+__metric AS (
+    -- Metric ([LEGACY] Booking Purchase)
+    SELECT
+        device_id AS device_id,
+        1 AS value,
+        m.timestamp AS timestamp
+    FROM (
+        SELECT
+            timestamp,
+            device_id,
+            custom_event_id,
+            element_label,
+            element_secondary_label,
+            feature_key,
+            booking_mode,
+            step,
+            _index,
+            is_selected,
+            filter_item,
+            payment_type,
+            is_scrollable,
+            number_of_fully_visible_filters,
+            _value,
+            value_as_string,
+            trip_search_mode_from_form_values,
+            trip_search_mode_from_hosting_search_parameters,
+            total_price
+        FROM
+            growthbook_experimentationanalyticsevent
+        WHERE
+            timestamp BETWEEN '2025-03-04 18:00:00' AND '2025-04-11 07:01:02') m
+    WHERE
+        custom_event_id = 'CE0026'
+        AND m.timestamp >= '2025-03-04 18:00:00'
+        AND m.timestamp <= '2025-04-11 07:01:02'
+),
+__userMetricJoin AS (
+    SELECT
+        d.variation AS variation,
+        d.dimension AS dimension,
+        d.device_id AS device_id,
+(
+            CASE WHEN m.timestamp >= d.timestamp
+                AND m.timestamp <= '2025-04-11 07:01:02' THEN
+                m.value
+            ELSE
+                NULL
+            END) AS value
+FROM
+    __distinctUsers d
+    LEFT JOIN __metric m ON (m.device_id = d.device_id)
+),
+__userMetricAgg AS (
+    -- Add in the aggregate metric value for each user
+    SELECT
+        umj.variation AS variation,
+        umj.dimension AS dimension,
+        umj.device_id,
+        COALESCE(MAX(umj.value), 0) AS value
+    FROM
+        __userMetricJoin umj
+    GROUP BY
+        umj.variation,
+        umj.dimension,
+        umj.device_id
+),
+__userCovariateMetric AS (
+    SELECT
+        d.variation AS variation,
+        d.dimension AS dimension,
+        d.device_id AS device_id,
+        COALESCE(MAX(value), 0) AS value
+    FROM
+        __distinctUsers d
+        JOIN __metric m ON (m.device_id = d.device_id)
+    WHERE
+        m.timestamp >= d.preexposure_start
+        AND m.timestamp < d.preexposure_end
+    GROUP BY
+        d.variation,
+        d.dimension,
+        d.device_id)
+    -- One row per variation/dimension with aggregations
+    SELECT
+        m.variation AS variation,
+        m.dimension AS dimension,
+        COUNT(*) AS users,
+    SUM(COALESCE(m.value, 0)) AS main_sum,
+    SUM(POWER(COALESCE(m.value, 0), 2)) AS main_sum_squares,
+    SUM(COALESCE(c.value, 0)) AS covariate_sum,
+    SUM(POWER(COALESCE(c.value, 0), 2)) AS covariate_sum_squares,
+    SUM(COALESCE(m.value, 0) * COALESCE(c.value, 0)) AS main_covariate_sum_product
+FROM
+    __userMetricAgg m
+    LEFT JOIN __userCovariateMetric c ON (c.device_id = m.device_id)
+GROUP BY
+    m.variation,
+    m.dimension
+```
+
+{{</details>}}
+
+## Resources
+
+- https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ExportSnapshot.html
diff --git a/content/posts/postgres-aggregates-correction/index.md b/content/posts/postgres-aggregates-correction/index.md
new file mode 100644
index 0000000..e6e45af
--- /dev/null
+++ b/content/posts/postgres-aggregates-correction/index.md
@@ -0,0 +1,137 @@
+---
+title: ""
+draft: true
+date: 2026-03-07
+description: ""
+categories:
+    - PostgreSQL
+---
+
+In my [first post](/posts/postgres-aggregates/), I expected to show
+that there was a better alternative to subqueries for large,
+multi-join queries. The numbers were good, the query now took 90ms
+when the subquery one was 600ms.
+
+As I recently was sharing my postgres experience with a coworker,
+I noted something weird in the subquery plan: each branch had a 
+Bitmap Index Scan followed by a Bitmap Heap Scan. This is a common
+pattern, that we could say as "good enough". But when this is the bottleneck of a query (as it was with 420ms), it's a common idea
+to go and try replace these two blocks by a Index Only Scan.
+
+The idea of a Bitmap Index Scan + Bitmap Heap Scan is to use the index to filter (or prefilter) the rows from the index, which is really fast, and then go fetch the actual data from the main Heap
+memory (the table per se). For instance, instead of scanning 10GB
+of data, and dumping 90% of it, you use the index to find the 10%
+interesting rows, and then fetch the Heap blocks where the data
+lives. Note that you will actual fetch more than 10% of the disk
+data, because you are forced to load whole memory pages
+(around ~8KB by default). The Bitmap Heap Scan is really good when
+the chosen data lives close together, because you can fetch many
+rows in a single page read.
+
+However, when using subqueries, the independance between loops of
+the query leads to a bad correlation of heap pages: you may access
+several times during the query to the same heap page, but you will
+pay the cost several times too (in a single Bitmap Heap Scan, it's
+smart and groups data access from a single page).
+
+The other weird thing is that the query shouldn't even need to make
+a data fetch: the index is good enough to answer the query, we only
+need to know how many rows were matched for each product.
+
+## A simple fix
+
+The initial query contained `count(id)`, and replacing it with
+`count(*)` indeed improved the plan. We now have an Index Only Scan,
+and the query is down from 540ms (yeah, maybe a postgres upgrade
+since then) to 85ms. Still a bit behind the previous winner, now at
+75ms, but maybe not worth the complexity. Also note that the subquery
+version gains more performance as we use root table filters.
+
+## Going further
+
+I have mixed feelings about this updated version. When I discovered
+this, I thought the whole point of my article collapsed. But now
+that I did the work to show it, I'm not so sure. Here are some
+updated limitations to the use of subqueries.
+
+
+### Leaf filtering
+
+The Subquery approach is better for root table filtering. If you
+want to compute the same statistics, but only for products with
+a price over 900 (the price is random between 0 and 1000), then
+the Subquery approach will filter before running the subqueries,
+and in this case reduce the number of subquery loops by 10x.
+
+Subquery: 85ms -> 15ms
+CTE: 75ms -> 65ms
+
+However, when the filtering is in a leaf table, the Index Only Scan
+can no longer be used (`where customer_name ilike '%0'`). As
+expected, the Subquery gets back its Bitmap Heap Scan, and duration goes up. However, I can't explain why the CTE goes up too. The Parallel Sec Scan goes from 10ms to 100ms, and it doesn't make any sense.
+
+CTE: 75ms -> 145ms
+Subquery: 85ms -> 110ms
+
+### Disk usage
+
+Another important aspect is disk usage. In the CTE version, we read
+57MB from orders and 7MB from reviews. In the Subquery + Index Only
+Scan, we read 230MB from orders and 160MB from reviews. And in the
+Subquery + Bitmap Heap Scan, we read 230MB from orders index, 7600MB
+from orders, 160MB from reviews index and 780MB from reviews. This
+is explained by the very high number of uncorrelated loops.
+
+In this setup, it's not much of an issue, because the data is small
+(57MB to read all orders), I have much RAM available, and I'm the
+only one using it. But this is not the case in production systems.
+Obviously tables are much largers (can easily become several GBs),
+and data read from disk may often be discarded to let other queries
+cache disk pages. The global setting that controls how much memory
+is allocated to caching disk data is called `shared_buffers` (128MB
+on my machine).
+
+In a simple AWS RDS setup, you can expect your disk to have a
+~100MB/s read throughput, when your local setup may be 20x faster.
+
+I can use docker-compose and blkio_config settings to limit the disk.
+I will also use a much lower `shared_buffers` that can't cache all
+my data, like 16MB.
+
+```yaml
+services:
+  postgres:
+    image: postgres:18
+    restart: unless-stopped
+    command: postgres -c shared_buffers=16MB -c track_io_timing=on
+    environment:
+      POSTGRES_HOST_AUTH_METHOD: trust
+    ports:
+      - "5432:5432"
+    blkio_config:
+      device_read_bps:
+        - path: /dev/nvme0n1  # your disk device
+          rate: '125mb'       # gp3 baseline throughput
+      device_read_iops:
+        - path: /dev/nvme0n1
+          rate: 3000          # gp3 baseline IOPS
+      device_write_bps:
+        - path: /dev/nvme0n1
+          rate: '125mb'
+      device_write_iops:
+        - path: /dev/nvme0n1
+          rate: 3000
+```
+
+Subquery Bitmap Heap: 4600ms
+Subquery Index Only: 90ms
+CTE: 75ms
+
+I think I can explain the numbers. CTE, as before, only need to read
+once the data. So it's reading just the right amount from disk.
+Subquery Index Only read less data (index) but many times. The index
+fits in the cache, and the Shared Hit/Read ratio is very high (~x10).
+Subquery Bitmap Heap Scan gets dirty. Data from tables can't fit in
+cache, so each independant loop has a high chance of having to read
+a page from disk. The Shared Hit/Read ratio is ~0.5, and total data
+read from disk goes from 7.7MB in the previous case to 5.5GB.