Hello everyone, We are porting our application from PostgreSQL to YugabyteDB, and I have a database schema design question: Our original application running on PostgreSQL collects statistics data every 15 minutes and stores them in a database “stats” table, which is partitioned (via inheritance pa…

[image] vlst: For the purpose of queries that we need to run to produce reports of the performance tables’ data, I suppose I need to define a view the selects from among the 40 tables using UNION ALL. Currently, we have an index on timestamp field DESC on each performance table, so that we can p…

Thank you for your replies. I should also mention that the memory usage issue we are trying to overcome includes increased use of swap (up to 6.8 GB). From what I have seen, CPU usage on the YugabyteDB nodes is not an issue (1.4 out of 8 CPUs). However, these are all with respect to the data collec…

[image] vlst: When you refer to “pg_dump of partitions”, what exactly do you mean with the term “partitions”? He means you can run a pg_dump / ysql_dump to dump a single or group of tables/partitions. Because on colocated dbs, snapshots are still not supported. [image] vlst: I should als…

Thank you for the clarifications. Regarding how much data we expect to have per day: For our target of 50000 objects, if we take as an example our table that has 118 statistics columns, for each object there are 4 distinct values for “name” for this table (per our application business logic). Since…

[image] vlst: Regarding how much data we expect to have per day: For our target of 50000 objects, if we take as an example our table that has 118 statistics columns, for each object there are 4 distinct values for “name” for this table (per our application business logic). Since we collect data …

I need to investigate the indices further, I will let you know when I have found the reason for their existence. With respect to the amount of data inserted at peak times: I have taken as example the table that has 118 statistics columns. Currently we are running our initial test modified such tha…

@vlst with hundreds of columns, using packed rows (--ysql_enable_packed_row=true) can make a huge difference. You can set it in 2.14 we will confirm that it is supported for production. If not, the workaround may be to store all those values in one JSONB

I checked, packed rows are unfortunately still beta in 2.14. Will probably be GA in 2.16

Thank you for providing the tip for flushing tables. Based on this, here is the amount of data for each of our 3 nodes during the 18+ seconds when the data insertion is taking place: Node 1: 40 MB Node 2: 10 MB Node 3: 30 MB As far as the name/timestamp indices are concerned: Our original databas…

[image] vlst: "stats_pkey" PRIMARY KEY, lsm (name ASC, "timestamp" DESC) Can name be HASH here? Do you need range queries on name?

Database schema in YugabyteDB for storing statistics collected periodically

General

dorian_yugabyte October 20, 2022, 7:59am 22

You should flush the table before inserting and after inserting Is there a manual way to force a flush and a major (i.e. full) compaction of a table in YugaByte DB? - #3 by kannan to count the size on disk.

Topic		Replies	Views
Facing strange behaviour while checking ingestion rate in ysql table General	11	7436	September 6, 2023
How to shard tables for efficient join Design Discussions	16	7018	October 2, 2024
Query performance on table with indices General	10	5114	March 18, 2022
Help: 5 Level of master-detail tables, 50 billion records Design Discussions	7	6231	March 6, 2025
Why are there already 8 tablets in the wals sub-directory under the data directory after a database cluster is initialized? General	33	7434	July 31, 2024

Database schema in YugabyteDB for storing statistics collected periodically

Related topics