6 ms·

Just to be clear, standard SQL databases are not great for large-scale analytics. I know from first hand experience and a lot of pain. We tried using Postgres

Just to be clear, standard SQL databases are not great for large-scale analytics. I know from first hand experience and a lot of pain. We tried using Postgres with large analytics at my previous company https://threekit.com https://threekit.com but it is an absolute pain. Basically we started to collected detailed analytics and thus had a rapidly growing table of around 2B records of user events during their sessions. As it grew past a 500 million records it turned out to be impossible to query this table in any thing close to real-time - it was basically untouchable because it was so slow. I know I could have used some type of daily aggregation combined with a weekly aggregation, etc to roll up the data incrementally. A dev tried this and yeah, it hide the slow queries but then it became inflexible in terms of reporting. And writing and maintaining these cronjobs is a lot of work. But then I tried using BigQuery on my https://web3dsurvey.com https://web3dsurvey.com side project and I just recorded raw data and then wrote queries to do real-time aggregation (with a caching layer) in a large variety of ways. And it was near instant and easy and also very cheap. So then I deployed this strategy over at Threekit.com and it also was easy and fast and cheap. Even more flexible queries than Web3DSurvey at basically no developer cost or infrastructure costs. No more need for aggregating cron-jobs or trying to decide ahead of time how users want to slice and dice the data. Real time, flexible queries on the fly via BigQuery is the way. Also BigQuery bill for https://web3dsurvey.com https://web3dsurvey.com is like $0.25 month and it is dealing with millions of records in its 3 month window of stored data. Where as just running the cheapest Postgres SQL server on Google Cloud is like >$25/month and it is a slow one. I would never go back to traditional SQL for analytics - it was hard, slow, expensive and inflexible. Worst of all worlds.

rapfaria 2y ago
> 2B records of user events during their sessions. As it grew past a 500 million records it turned out to be impossible to query this table in any thing close to real-time - it was basically untouchable because it was so slow. This is a solved problem, and it seems the technical folks over there lacked the skills to make it work. Having indexes is just the tip of the iceberg. Composite indexes, partitioning, sharding, caching, etc, can lower reads to a few seconds on disk.
- bhouston 2y ago
  > This is a solved problem, and it seems the technical folks over there lacked the skills to make it work. Having indexes is just the tip of the iceberg. Composite indexes, partitioning, sharding, caching, etc, can lower reads to a few seconds on disk. Or just use BigQuery and it is works, it is cheaper to run (by 10x to 100x) and can be done by a junior dev rather than a PhD in Database configuration. I prefer simple solutions though - I also hate Kubernetes: https://benhouston3d.com/blog/why-i-left-kubernetes-for-google-cloud-run https://benhouston3d.com/blog/why-i-left-kubernetes-for-goog...
  - DrFalkyn 2y ago
    Indices are one the first things you learn in any decent DB course, so you don’t need a PhD But if BigTable just solves the problem it seems the way to go PostGres is popular for a reason, it’s ACID unlike BigTable You may run into these problems later on, you may not
    - bhouston 2y ago
      I love Postgres and use it a lot but not for analytics.
    - furstenheim 2y ago
      ACID is good and it has an implementation cost (vacuum for example). When doing analytics you do not care for ACID
  - majkinetor 2y ago
    I have 50 columns in a table with 10B records in a partitioned table. People can search using any combination. There are a huge number of possible composite indexes, so we don't have any, but we have every column indexed. Most queries take a minute or two, and we also experienced a sudden drop in performance even for queries that used to work OK. Not sure what to do next to improve query speed.
    - 2y ago
Galanwe 2y ago
> a rapidly growing table of around 2B records of user events during their sessions. As it grew past a 500 million records it turned out to be impossible to query this table in any thing close to real-time I mean, I don't know what you call "close to real time", and what kind of query you did, but I have Postgres serving requests from a 20B rows table just fine, with some light tweaking of indexes and partitions (I'm by no means a DBA). Unless we are digging much deeper than that in the specifics of the data and queries, I don't think any conclusion can be taken from either experience.
- Eikon 2y ago
  Same for https://www.merklemap.com/ https://www.merklemap.com/ The biggest table contains 30B records. A query that uses a B-tree index completes in a few microseconds. EXPLAIN ANALYZE SELECT * FROM table_name WHERE id = [ID_VALUE]; Index Scan using table_name_pkey on table_name (cost=0.71..2.93 rows=1 width=32) (actual time=0.042..0.042 rows=0 loops=1) Index Cond: (id = '[ID_VALUE]'::bigint) Planning Time: 0.056 ms Execution Time: 0.052 ms
  - jodrellblank 2y ago
    If the original comment is talking about saving money and running in Google cloud, could they be using virtual machines and HDD while both you and the parent talking about good speeds (and not money) are using physical servers and SSD?
    - Galanwe 2y ago
      I'm just your average Joe using RDS. The whole database itself is 3TB.
    - Eikon 2y ago
      I am indeed running PostgreSQL on bare metal with a 20TB NVMe pool, the DB itself is around 18 TB :) But that's the way to go, isn't it?
  - mmsimanga 2y ago
TuringNYC 2y ago
>> Just to be clear, standard SQL databases are not great for large-scale analytics. What do you mean by "standard" SQL? Were you using an OLTP or OLAP db when you faced these difficulties? Also, what makes BigQuery not a "standard SQL database" -- they use GoogleSQL, true, but many DBs have slight SQL variants.
- braiamp 2y ago
  According to their other comment standard means what it comes in the box by default.
- bhouston 2y ago
  > What do you mean by "standard" SQL? No extensions, just the default Postgres Cloud SQL offering from Google Cloud. > Also, what makes BigQuery not a "standard SQL database" -- they use GoogleSQL, true, but many DBs have slight SQL variants. I view BigQuery as not a standard DB because it really doesn't do transactions, rollbacks, foreign key constraints, indices, stored procedures, etc. I view it as a non-SQL database pretending to be SQL for query convenience.
  - gbin 2y ago
    I think you mean a relational database.
    - layer8 2y ago
      All the features mentioned are part of SQL.
      - nom 2y ago
        SQL is a language to interact with a relational database. It is not about the language.
      - jandrewrogers 2y ago
        SQL (the DML) is just a query language. It could be implemented on anything. Parts of SQL DDL is implementation specific. The SQL standard hardcodes the assumption of (by modern standards) a small-scale system and therefore will deviate significantly on large-scale systems. There is no practical way to make this "standard", you have to understand the implementation it reflects.
atombender 2y ago
Did you use plain Postgres tables or a columnar extension like Timescale, Citus, or pg_mooncake? Nobody in their right mind would argue that Postgres without columnar storage is good for analytics. However, it looks like these extensions can be quite decent, at least at queries (you might still run into write performance due to Postgres' OLTP architecture, but I've never benchmarked it). In OLAP terms the size of your data is tiny and this would probably work just fine. Personally I'm a huge fan of dedicated databases like ClickHouse. BigQuery gets very expensive as your data and query volume grows.
- bhouston 2y ago
  > Did you use plain Postgres tables or a columnar extension like Timescale or pg_mooncake? Plain Postgres tables hosted on Google Cloud SQL on a decent machine. > Nobody in their right mind would argue that Postgres without columnar storage is good for analytics. Which is what I am saying. Standard means without extensions. > Personally I'm a huge fan of dedicated databases like ClickHouse. Where do you run it? What type of machine? Can it be server less? > BigQuery gets very expensive as your data and query volume grows. I didn't experience this, it has been hard for me to make BigQuery cost a lot. Storage costs were basically nothing and we just set the clustering/partition on DB creation to be users/sessions/events and dates respectively. It aligned really well with general query patterns.
  - atombender 2y ago
    Your comment is confusing to me because it appears to have nothing to do with the article, which is not about using Postgres' heap tables. In fact, you're arguing for a purpose-built storage engine (BigQuery), which is what pg_mooncake also is, except the latter is integrated with Postgres. At my company we self-host ClickHouse, but if we started out today we'd definitely adopt ClickHouse Cloud. One big advantage of CH is that tables can be backed by ordinary cloud storage like S3 and GCS, which is incredibly cheap. BQ has support for external Iceberg tables in GCS, but (from what I recall) no write support. We are shuffling several TBs/week into BigQuery, and that is extremely expensive. Querying this amount of data (billions of rows per day) is also very expensive unless you purchase flat pricing. If your needs are really modest, then BQ is definitely very reasonable.
dig1 2y ago
As someone dealing with billions of records on it, BigQuery is far from cheap; G will not charge you much for storage as they will charge you for queries and data transfer. AFAIK, the cheapest Postgres server on GCP is very expensive compared to the usual Postgres installation (price/performance).
- michaelmior 2y ago
  What do you mean by "the usual Postgres installation"?
- renegade-otter 2y ago
  That and RDS are kind of a rip-off if you are trying to have a sandbox out there, short of running your own instance. For that, I found Digital Ocean to be very reasonable.
  - eastbound 2y ago
    DO’s pricing has increased a lot. I was surprised, I couldn’t start a $5 vm anymore, it’s worth checking again.
    - jmholla 2y ago
      You can, but I think you have to be in one of the regions that supports it. The generally (globally?) available lowest priced VM is $6 a month. https://www.digitalocean.com/pricing/droplets https://www.digitalocean.com/pricing/droplets
- sgarland 2y ago
  Yep. I used it once for a personal project involving GHTorrent [0], and each query was about $20. That may be peanuts for a business, but for someone playing around on their own, it's incredibly prohibitive. [0]: https://github.com/ghtorrent/ghtorrent.org/blob/master/gcloud.md https://github.com/ghtorrent/ghtorrent.org/blob/master/gclou...
  - 2y ago
Simon_O_Rourke 2y ago
> it was basically untouchable because it was so slow. I've worked with a 5B row table on Snowflake with (maybe) no indexes, and while somewhat slow you could still run reasonable queries on it in a minute or two.
- bhouston 2y ago
  I guess I liked the convenience with Bigquery of being able to do significant queries in a second or two on large data sets. It means I can do the query live for the user as they navigate the analytics dashboard and this simplifies design and implementation complexity significantly.
cyptus 2y ago
same. started writing data into parquet to analyze from there with a big gain in performance and hosting costs
sgarland 2y ago
> Basically we started to collected detailed analytics and thus had a rapidly growing table of around 2B records of user events during their sessions. As it grew past a 500 million records it turned out to be impossible to query this table in any thing close to real-time Analytics isn't typically something that needs real-time capabilities, for one. > a rapidly growing table [emphasis mine] I think I see part of the problem here. If you had a single table, that means it's completely denormalized, so your schema probably looked something like this (or wider): CREATE TABLE UserEvent ( id UUID PRIMARY KEY, user_id UUID NOT NULL, user_ip_address TEXT NOT NULL, user_agent TEXT NOT NULL, event_data JSONB, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), event_type TEXT ); CREATE INDEX UserEvent_user_id_idx ON UserEvent (user_id); CREATE INDEX UserEvent_created_at_idx ON UserEvent (created_at); The JSON blob might be anywhere from a few hundred bytes to well over a kilobyte, and probably duplicates data already present as a scalar, like IP address, user agent string, timestamp, etc. I'll use the middle ground and say the JSONB objects are on average 500 bytes when stored. Now, the rest. A UUID, if stored as its native type (or BINARY(16) in MySQL - don't sleep on this, MySQL folks; it makes a huge difference at scale) is 16 bytes. That's double the size of a BIGINT, and quadruple the size of an INT4. Also, unless you're using UUIDv7 (or UUIDv1, but no one does), it's not k-sortable. Since Postgres doesn't cluster tuples around the PK [yes, I know all indices in Postgres are technically secondary] like MySQL/InnoDB does, this doesn't immediately thrash the B+tree in the same way, but it does thrash the visibility map, and it does bloat the WAL. There are various arguments for why you shouldn't use a monotonic integer as a surrogate key, but IMO they're largely overblown, and there are workarounds to not publicly disclose it. IPv4 addresses, stored in dotted-quad as a string, are a maximum of 15 characters, storing in 16 bytes as TEXT or VARCHAR. If stored instead in the Postgres native INET type, that drops to 7 bytes, plus you get built-in validation. If you had INT4 UNSIGNED available (as MySQL does natively), you could even store them in their numeric form and save another 3 bytes, though you lose the validation. User Agent strings are huge, usually over 100 bytes. They're also not that unique, relatively speaking. Even if you need to know the patch version of the browser, anyone with a browser doing automatic updates is going to stay more or less in sync. The point is this could easily be a lookup table, with either a SMALLINT (2^15 - 1 maximum values, or 2^16 - 1 if you use unsigned values; possible with an extension in Postgres) or an INT (2^31 -1 maximum values) as the PK. Not going to touch on JSON objects because the things you might want to know are endless. TOAST and de-TOAST can be slow; if you need low latency, you should normalize your data. There may or may not be extracted scalars, which can be beneficial during queries. Again, though, lookup tables (or even native ENUM types, if the values are limited) are crucial at scale. As it stands, the table will have an average row size of 664 bytes (assuming an average of 12 bytes stored for the IP, 100 bytes stored for the UA, 500 bytes stored for the JSONB, and 12 bytes stored for the event type). That's 332 GB for 500,000,000 rows. You could shave a couple of bytes off by aligning columns [0], which saves 1 GB. If the IP addresses and UA strings were lookup tables, each with an INT4, that saves 104 bytes per row. If you made the PK for the table a BIGINT, that saves another 8 bytes per row. The total savings between column alignment and basic normalization is 114 bytes per row, or 57 GB. This doesn't touch on the indices, either. If you're using PG 13+, you get B+tree de-duplication [1] for free, which can help with some denormalized data, but not if you have anything with high cardinality, like a timestamp, or a UUID. With lookup tables, you would of course need to index those FKs (whether or not you're enforcing constraints), which adds some size, but is still a huge net gain. > I know I could have used some type of daily aggregation combined with a weekly aggregation, etc to roll up the data incrementally. A dev tried this and yeah, it hide the slow queries but then it became inflexible in terms of reporting. And writing and maintaining these cronjobs is a lot of work. And shifting your entire analytics workload isn't a lot of work? Between ROLLUP [2] and MATERIALIZED VIEW [3], which can automatically refresh itself with a cron, this doesn't seem that burdensome. > Also BigQuery bill for https://web3dsurvey.com https://web3dsurvey.com is like $0.25 month and it is dealing with millions of records in its 3 month window of stored data. Then you're in the free tier (<= 1 TiB/month of processed data), because after that it's $6.25/TiB. Also worth noting there is a massive difference between millions of rows and billions of rows. The former can be handled by practically any RDBMS on any hardware, with a completely unoptimized schema. The latter requires some thought if you want it to be performant. This isn't at all to say that specialized DBs don't have their place, because they absolutely do. If you need a KV store, use a KV store, not an RDBMS. If you need OLAP, use something designed for OLAP. The difference is scale. At startup or side project scale, you can easily do everything (including pub/sub) with an RDBMS, and if you put thought into its design and usage, you can take it a lot farther than you'd think. Eventually, you may hit a point where it's counter-productive to do so, and then you should look into breaking tasks out. The issue I see happening time and time again is devs have little to no expertise in DBs of any kind, but since everyone says "Postgres is all you need," they decide to use it for everything, except they don't know what they're doing. If you do that, yeah, you're gonna have problems fairly early on, and then you'll either throw your hands up and decide you really need a bevy of specialized DBs, caches, and message queues (which introduces a lot of complexity), or you'll vertically scale the DB. If you choose the latter, by the time you hit scaling limits, you're easily spending $25K/month on the DB alone. If you opt to hire someone with DB expertise at this point, you'll spend about that if not more in personnel costs, and not only will it take them weeks if not months to unravel everything, your devs will be constantly complaining that queries are now "too complex" because they have to do some JOINs, and they're being told to stop chucking everything into JSON. If instead, you took at most a week to learn some RDBMS basics by a. reading its manual front-to-back b. hands-on experience, trying things out you could almost certainly get much farther on much less. [0]: https://www.enterprisedb.com/blog/rocks-and-sand https://www.enterprisedb.com/blog/rocks-and-sand [1]: https://www.postgresql.org/docs/current/btree.html#BTREE-DEDUPLICATION https://www.postgresql.org/docs/current/btree.html#BTREE-DED... [2]: https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-GROUPING-SETS https://www.postgresql.org/docs/current/queries-table-expres... [3]: https://www.postgresql.org/docs/current/rules-materializedviews.html https://www.postgresql.org/docs/current/rules-materializedvi...
zhousun 2y ago
Thanks for the comment but you are mixing some terminologies. The core idea of mooncake is to built upon open columnar format + substitutable vectorized engine, while natively integrate with Postgres. So it is indeed closer to BigQuery (especially the newer bigquery with iceberg tables) than a 'standard SQL database'. It has all the nice properties of BigQuery (ObjectStore-native, ColumnStore, Vectorized execution...) and scaling is also not impossible.

rapfaria 2y ago

> 2B records of user events during their sessions. As it grew past a 500 million records it turned out to be impossible to query this table in any thing close to real-time - it was basically untouchable because it was so slow. This is a solved problem, and it seems the technical folks over there lacked the skills to make it work. Having indexes is just the tip of the iceberg. Composite indexes, partitioning, sharding, caching, etc, can lower reads to a few seconds on disk.

bhouston 2y ago
> This is a solved problem, and it seems the technical folks over there lacked the skills to make it work. Having indexes is just the tip of the iceberg. Composite indexes, partitioning, sharding, caching, etc, can lower reads to a few seconds on disk. Or just use BigQuery and it is works, it is cheaper to run (by 10x to 100x) and can be done by a junior dev rather than a PhD in Database configuration. I prefer simple solutions though - I also hate Kubernetes: https://benhouston3d.com/blog/why-i-left-kubernetes-for-google-cloud-run https://benhouston3d.com/blog/why-i-left-kubernetes-for-goog...
- DrFalkyn 2y ago
  Indices are one the first things you learn in any decent DB course, so you don’t need a PhD But if BigTable just solves the problem it seems the way to go PostGres is popular for a reason, it’s ACID unlike BigTable You may run into these problems later on, you may not
  - bhouston 2y ago
    I love Postgres and use it a lot but not for analytics.
  - furstenheim 2y ago
    ACID is good and it has an implementation cost (vacuum for example). When doing analytics you do not care for ACID
- majkinetor 2y ago
  I have 50 columns in a table with 10B records in a partitioned table. People can search using any combination. There are a huge number of possible composite indexes, so we don't have any, but we have every column indexed. Most queries take a minute or two, and we also experienced a sudden drop in performance even for queries that used to work OK. Not sure what to do next to improve query speed.
  - 2y ago

Galanwe 2y ago

> a rapidly growing table of around 2B records of user events during their sessions. As it grew past a 500 million records it turned out to be impossible to query this table in any thing close to real-time I mean, I don't know what you call "close to real time", and what kind of query you did, but I have Postgres serving requests from a 20B rows table just fine, with some light tweaking of indexes and partitions (I'm by no means a DBA). Unless we are digging much deeper than that in the specifics of the data and queries, I don't think any conclusion can be taken from either experience.

Eikon 2y ago
Same for https://www.merklemap.com/ https://www.merklemap.com/ The biggest table contains 30B records. A query that uses a B-tree index completes in a few microseconds. EXPLAIN ANALYZE SELECT * FROM table_name WHERE id = [ID_VALUE]; Index Scan using table_name_pkey on table_name (cost=0.71..2.93 rows=1 width=32) (actual time=0.042..0.042 rows=0 loops=1) Index Cond: (id = '[ID_VALUE]'::bigint) Planning Time: 0.056 ms Execution Time: 0.052 ms
- jodrellblank 2y ago
  If the original comment is talking about saving money and running in Google cloud, could they be using virtual machines and HDD while both you and the parent talking about good speeds (and not money) are using physical servers and SSD?
  - Galanwe 2y ago
    I'm just your average Joe using RDS. The whole database itself is 3TB.
  - Eikon 2y ago
    I am indeed running PostgreSQL on bare metal with a 20TB NVMe pool, the DB itself is around 18 TB :) But that's the way to go, isn't it?
- mmsimanga 2y ago

TuringNYC 2y ago

>> Just to be clear, standard SQL databases are not great for large-scale analytics. What do you mean by "standard" SQL? Were you using an OLTP or OLAP db when you faced these difficulties? Also, what makes BigQuery not a "standard SQL database" -- they use GoogleSQL, true, but many DBs have slight SQL variants.

braiamp 2y ago
According to their other comment standard means what it comes in the box by default.
bhouston 2y ago
> What do you mean by "standard" SQL? No extensions, just the default Postgres Cloud SQL offering from Google Cloud. > Also, what makes BigQuery not a "standard SQL database" -- they use GoogleSQL, true, but many DBs have slight SQL variants. I view BigQuery as not a standard DB because it really doesn't do transactions, rollbacks, foreign key constraints, indices, stored procedures, etc. I view it as a non-SQL database pretending to be SQL for query convenience.
- gbin 2y ago
  I think you mean a relational database.
  - layer8 2y ago
    All the features mentioned are part of SQL.
    - nom 2y ago
      SQL is a language to interact with a relational database. It is not about the language.
    - jandrewrogers 2y ago
      SQL (the DML) is just a query language. It could be implemented on anything. Parts of SQL DDL is implementation specific. The SQL standard hardcodes the assumption of (by modern standards) a small-scale system and therefore will deviate significantly on large-scale systems. There is no practical way to make this "standard", you have to understand the implementation it reflects.

atombender 2y ago

Did you use plain Postgres tables or a columnar extension like Timescale, Citus, or pg_mooncake? Nobody in their right mind would argue that Postgres without columnar storage is good for analytics. However, it looks like these extensions can be quite decent, at least at queries (you might still run into write performance due to Postgres' OLTP architecture, but I've never benchmarked it). In OLAP terms the size of your data is tiny and this would probably work just fine. Personally I'm a huge fan of dedicated databases like ClickHouse. BigQuery gets very expensive as your data and query volume grows.

bhouston 2y ago
> Did you use plain Postgres tables or a columnar extension like Timescale or pg_mooncake? Plain Postgres tables hosted on Google Cloud SQL on a decent machine. > Nobody in their right mind would argue that Postgres without columnar storage is good for analytics. Which is what I am saying. Standard means without extensions. > Personally I'm a huge fan of dedicated databases like ClickHouse. Where do you run it? What type of machine? Can it be server less? > BigQuery gets very expensive as your data and query volume grows. I didn't experience this, it has been hard for me to make BigQuery cost a lot. Storage costs were basically nothing and we just set the clustering/partition on DB creation to be users/sessions/events and dates respectively. It aligned really well with general query patterns.
- atombender 2y ago
  Your comment is confusing to me because it appears to have nothing to do with the article, which is not about using Postgres' heap tables. In fact, you're arguing for a purpose-built storage engine (BigQuery), which is what pg_mooncake also is, except the latter is integrated with Postgres. At my company we self-host ClickHouse, but if we started out today we'd definitely adopt ClickHouse Cloud. One big advantage of CH is that tables can be backed by ordinary cloud storage like S3 and GCS, which is incredibly cheap. BQ has support for external Iceberg tables in GCS, but (from what I recall) no write support. We are shuffling several TBs/week into BigQuery, and that is extremely expensive. Querying this amount of data (billions of rows per day) is also very expensive unless you purchase flat pricing. If your needs are really modest, then BQ is definitely very reasonable.

dig1 2y ago

As someone dealing with billions of records on it, BigQuery is far from cheap; G will not charge you much for storage as they will charge you for queries and data transfer. AFAIK, the cheapest Postgres server on GCP is very expensive compared to the usual Postgres installation (price/performance).

michaelmior 2y ago
What do you mean by "the usual Postgres installation"?
renegade-otter 2y ago
That and RDS are kind of a rip-off if you are trying to have a sandbox out there, short of running your own instance. For that, I found Digital Ocean to be very reasonable.
- eastbound 2y ago
  DO’s pricing has increased a lot. I was surprised, I couldn’t start a $5 vm anymore, it’s worth checking again.
  - jmholla 2y ago
    You can, but I think you have to be in one of the regions that supports it. The generally (globally?) available lowest priced VM is $6 a month. https://www.digitalocean.com/pricing/droplets https://www.digitalocean.com/pricing/droplets
sgarland 2y ago
Yep. I used it once for a personal project involving GHTorrent [0], and each query was about $20. That may be peanuts for a business, but for someone playing around on their own, it's incredibly prohibitive. [0]: https://github.com/ghtorrent/ghtorrent.org/blob/master/gcloud.md https://github.com/ghtorrent/ghtorrent.org/blob/master/gclou...
- 2y ago

Simon_O_Rourke 2y ago

> it was basically untouchable because it was so slow. I've worked with a 5B row table on Snowflake with (maybe) no indexes, and while somewhat slow you could still run reasonable queries on it in a minute or two.

bhouston 2y ago
I guess I liked the convenience with Bigquery of being able to do significant queries in a second or two on large data sets. It means I can do the query live for the user as they navigate the analytics dashboard and this simplifies design and implementation complexity significantly.

cyptus 2y ago

same. started writing data into parquet to analyze from there with a big gain in performance and hosting costs

sgarland 2y ago

> Basically we started to collected detailed analytics and thus had a rapidly growing table of around 2B records of user events during their sessions. As it grew past a 500 million records it turned out to be impossible to query this table in any thing close to real-time Analytics isn't typically something that needs real-time capabilities, for one. > a rapidly growing table [emphasis mine] I think I see part of the problem here. If you had a single table, that means it's completely denormalized, so your schema probably looked something like this (or wider): CREATE TABLE UserEvent ( id UUID PRIMARY KEY, user_id UUID NOT NULL, user_ip_address TEXT NOT NULL, user_agent TEXT NOT NULL, event_data JSONB, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), event_type TEXT ); CREATE INDEX UserEvent_user_id_idx ON UserEvent (user_id); CREATE INDEX UserEvent_created_at_idx ON UserEvent (created_at); The JSON blob might be anywhere from a few hundred bytes to well over a kilobyte, and probably duplicates data already present as a scalar, like IP address, user agent string, timestamp, etc. I'll use the middle ground and say the JSONB objects are on average 500 bytes when stored. Now, the rest. A UUID, if stored as its native type (or BINARY(16) in MySQL - don't sleep on this, MySQL folks; it makes a huge difference at scale) is 16 bytes. That's double the size of a BIGINT, and quadruple the size of an INT4. Also, unless you're using UUIDv7 (or UUIDv1, but no one does), it's not k-sortable. Since Postgres doesn't cluster tuples around the PK [yes, I know all indices in Postgres are technically secondary] like MySQL/InnoDB does, this doesn't immediately thrash the B+tree in the same way, but it does thrash the visibility map, and it does bloat the WAL. There are various arguments for why you shouldn't use a monotonic integer as a surrogate key, but IMO they're largely overblown, and there are workarounds to not publicly disclose it. IPv4 addresses, stored in dotted-quad as a string, are a maximum of 15 characters, storing in 16 bytes as TEXT or VARCHAR. If stored instead in the Postgres native INET type, that drops to 7 bytes, plus you get built-in validation. If you had INT4 UNSIGNED available (as MySQL does natively), you could even store them in their numeric form and save another 3 bytes, though you lose the validation. User Agent strings are huge, usually over 100 bytes. They're also not that unique, relatively speaking. Even if you need to know the patch version of the browser, anyone with a browser doing automatic updates is going to stay more or less in sync. The point is this could easily be a lookup table, with either a SMALLINT (2^15 - 1 maximum values, or 2^16 - 1 if you use unsigned values; possible with an extension in Postgres) or an INT (2^31 -1 maximum values) as the PK. Not going to touch on JSON objects because the things you might want to know are endless. TOAST and de-TOAST can be slow; if you need low latency, you should normalize your data. There may or may not be extracted scalars, which can be beneficial during queries. Again, though, lookup tables (or even native ENUM types, if the values are limited) are crucial at scale. As it stands, the table will have an average row size of 664 bytes (assuming an average of 12 bytes stored for the IP, 100 bytes stored for the UA, 500 bytes stored for the JSONB, and 12 bytes stored for the event type). That's 332 GB for 500,000,000 rows. You could shave a couple of bytes off by aligning columns [0], which saves 1 GB. If the IP addresses and UA strings were lookup tables, each with an INT4, that saves 104 bytes per row. If you made the PK for the table a BIGINT, that saves another 8 bytes per row. The total savings between column alignment and basic normalization is 114 bytes per row, or 57 GB. This doesn't touch on the indices, either. If you're using PG 13+, you get B+tree de-duplication [1] for free, which can help with some denormalized data, but not if you have anything with high cardinality, like a timestamp, or a UUID. With lookup tables, you would of course need to index those FKs (whether or not you're enforcing constraints), which adds some size, but is still a huge net gain. > I know I could have used some type of daily aggregation combined with a weekly aggregation, etc to roll up the data incrementally. A dev tried this and yeah, it hide the slow queries but then it became inflexible in terms of reporting. And writing and maintaining these cronjobs is a lot of work. And shifting your entire analytics workload isn't a lot of work? Between ROLLUP [2] and MATERIALIZED VIEW [3], which can automatically refresh itself with a cron, this doesn't seem that burdensome. > Also BigQuery bill for https://web3dsurvey.com https://web3dsurvey.com is like $0.25 month and it is dealing with millions of records in its 3 month window of stored data. Then you're in the free tier (<= 1 TiB/month of processed data), because after that it's $6.25/TiB. Also worth noting there is a massive difference between millions of rows and billions of rows. The former can be handled by practically any RDBMS on any hardware, with a completely unoptimized schema. The latter requires some thought if you want it to be performant. This isn't at all to say that specialized DBs don't have their place, because they absolutely do. If you need a KV store, use a KV store, not an RDBMS. If you need OLAP, use something designed for OLAP. The difference is scale. At startup or side project scale, you can easily do everything (including pub/sub) with an RDBMS, and if you put thought into its design and usage, you can take it a lot farther than you'd think. Eventually, you may hit a point where it's counter-productive to do so, and then you should look into breaking tasks out. The issue I see happening time and time again is devs have little to no expertise in DBs of any kind, but since everyone says "Postgres is all you need," they decide to use it for everything, except they don't know what they're doing. If you do that, yeah, you're gonna have problems fairly early on, and then you'll either throw your hands up and decide you really need a bevy of specialized DBs, caches, and message queues (which introduces a lot of complexity), or you'll vertically scale the DB. If you choose the latter, by the time you hit scaling limits, you're easily spending $25K/month on the DB alone. If you opt to hire someone with DB expertise at this point, you'll spend about that if not more in personnel costs, and not only will it take them weeks if not months to unravel everything, your devs will be constantly complaining that queries are now "too complex" because they have to do some JOINs, and they're being told to stop chucking everything into JSON. If instead, you took at most a week to learn some RDBMS basics by a. reading its manual front-to-back b. hands-on experience, trying things out you could almost certainly get much farther on much less. [0]: https://www.enterprisedb.com/blog/rocks-and-sand https://www.enterprisedb.com/blog/rocks-and-sand [1]: https://www.postgresql.org/docs/current/btree.html#BTREE-DEDUPLICATION https://www.postgresql.org/docs/current/btree.html#BTREE-DED... [2]: https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-GROUPING-SETS https://www.postgresql.org/docs/current/queries-table-expres... [3]: https://www.postgresql.org/docs/current/rules-materializedviews.html https://www.postgresql.org/docs/current/rules-materializedvi...

zhousun 2y ago

Thanks for the comment but you are mixing some terminologies. The core idea of mooncake is to built upon open columnar format + substitutable vectorized engine, while natively integrate with Postgres. So it is indeed closer to BigQuery (especially the newer bigquery with iceberg tables) than a 'standard SQL database'. It has all the nice properties of BigQuery (ObjectStore-native, ColumnStore, Vectorized execution...) and scaling is also not impossible.