Gather statements metrics to inform db schema decisions
Open, Needs TriagePublic
Actions

Assigned To

Authored By

	Jakob_WMDE
	Mon, Sep 14, 11:48 AM

Project Tags

Referenced Files

None

Subscribers

Aklapper

Jakob_WMDE

Description

We would like to gather some metrics to inform our db schema design decisions.

From T434870#12311884:

Total number of statements
Number of statements for each data type (item, string, property, etc.)
How many distinct values exists for each value type
Number of deprecated statements
Number of "best ranking" statements.
Number of statement in two large group of items: scholarly articles and astronomical objects. So We can assess what would be the impact of excluding them.
Number of statements with "no" or "some" value. So we can see how much is needed.

See T434870#12312068 for some initial numbers.

Details

Related Changes in GitLab:

Title	Reference	Author	Source Branch	Dest Branch
Run the census on the 2026-09-14 snapshot	repos/wmde/wikibase-reuse/wikidata-statement-count!12	itamar	summary	main
Trim the notebook and key the cache by snapshot	repos/wmde/wikibase-reuse/wikidata-statement-count!11	itamar	notebook-trim	main
Measure statement id forms and column bytes	repos/wmde/wikibase-reuse/wikidata-statement-count!10	itamar	identity-guid	main
Answer the review round on !1, !3 and !5	repos/wmde/wikibase-reuse/wikidata-statement-count!9	itamar	review-round	main
Count novalue and somevalue per data type	repos/wmde/wikibase-reuse/wikidata-statement-count!8	itamar	snak-types	main
Fold the categories metric into the subject flags cell	repos/wmde/wikibase-reuse/wikidata-statement-count!7	itamar	categories	main
Measure statements per rank	repos/wmde/wikibase-reuse/wikidata-statement-count!6	itamar	by-rank	main
Measure distinct values and pairs per value type	repos/wmde/wikibase-reuse/wikidata-statement-count!5	itamar	distinct-values	main
Read subject flags from the statements table	repos/wmde/wikibase-reuse/wikidata-statement-count!4	itamar	speed-optimization	main
First three metrics of the census on the flattened statements table	repos/wmde/wikibase-reuse/wikidata-statement-count!3	itamar	metric-total	main
Flatten statements into an Iceberg table	repos/wmde/wikibase-reuse/wikidata-statement-count!2	itamar	flatten-pass	main
Subject flags and class list for the statement census	repos/wmde/wikibase-reuse/wikidata-statement-count!1	itamar	subject-flags	main

Related Objects
Search...

Status	Assigned	Task
Open	None	T433596 Migrate GraphQL item search to MariaDB
Open	Jakob_WMDE	T434870 Create secondary statement store tables
Open	ItamarWMDE	T437880 Gather statements metrics to inform db schema decisions

Event Timeline

Jakob_WMDE created this task.Mon, Sep 14, 11:48 AM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptMon, Sep 14, 11:48 AM

ItamarWMDE claimed this task.Mon, Sep 14, 1:04 PM

Restricted Application added a project: User-ItamarWMDE. · View Herald TranscriptMon, Sep 14, 1:04 PM

ItamarWMDE moved this task from To Do to Doing on the Wikibase Reuse Team (Sprint 77) board.Mon, Sep 14, 1:05 PM

WMDE-leszek edited projects, added: Wikibase Reuse Team (Sprint 78); removed: Wikibase Reuse Team (Sprint 77).Wed, Sep 16, 9:49 AM

WMDE-leszek moved this task from To Do to Doing on the Wikibase Reuse Team (Sprint 78) board.Wed, Sep 16, 9:50 AM

itamar opened https://gitlab.wikimedia.org/repos/wmde/wikibase-reuse/wikidata-statement-count/-/merge_requests/1

Subject flags and class list for the statement census

itamar opened https://gitlab.wikimedia.org/repos/wmde/wikibase-reuse/wikidata-statement-count/-/merge_requests/2

Flatten statements into an Iceberg table

itamar opened https://gitlab.wikimedia.org/repos/wmde/wikibase-reuse/wikidata-statement-count/-/merge_requests/3

First three metrics of the census on the flattened statements table

itamar opened https://gitlab.wikimedia.org/repos/wmde/wikibase-reuse/wikidata-statement-count/-/merge_requests/4

Read subject flags from the statements table

itamar opened https://gitlab.wikimedia.org/repos/wmde/wikibase-reuse/wikidata-statement-count/-/merge_requests/5

Measure distinct values and pairs per value type

itamar opened https://gitlab.wikimedia.org/repos/wmde/wikibase-reuse/wikidata-statement-count/-/merge_requests/6

Measure statements per rank

itamar opened https://gitlab.wikimedia.org/repos/wmde/wikibase-reuse/wikidata-statement-count/-/merge_requests/7

Fold the categories metric into the subject flags cell

itamar opened https://gitlab.wikimedia.org/repos/wmde/wikibase-reuse/wikidata-statement-count/-/merge_requests/8

Count novalue and somevalue per data type

itamar opened https://gitlab.wikimedia.org/repos/wmde/wikibase-reuse/wikidata-statement-count/-/merge_requests/9

Answer the review round on !1, !3 and !5

itamar merged https://gitlab.wikimedia.org/repos/wmde/wikibase-reuse/wikidata-statement-count/-/merge_requests/1

Subject flags and class list for the statement census

itamar merged https://gitlab.wikimedia.org/repos/wmde/wikibase-reuse/wikidata-statement-count/-/merge_requests/2

Flatten statements into an Iceberg table

itamar merged https://gitlab.wikimedia.org/repos/wmde/wikibase-reuse/wikidata-statement-count/-/merge_requests/3

First three metrics of the census on the flattened statements table

itamar merged https://gitlab.wikimedia.org/repos/wmde/wikibase-reuse/wikidata-statement-count/-/merge_requests/4

Read subject flags from the statements table

itamar merged https://gitlab.wikimedia.org/repos/wmde/wikibase-reuse/wikidata-statement-count/-/merge_requests/5

Measure distinct values and pairs per value type

itamar merged https://gitlab.wikimedia.org/repos/wmde/wikibase-reuse/wikidata-statement-count/-/merge_requests/6

Measure statements per rank

itamar merged https://gitlab.wikimedia.org/repos/wmde/wikibase-reuse/wikidata-statement-count/-/merge_requests/7

Fold the categories metric into the subject flags cell

itamar opened https://gitlab.wikimedia.org/repos/wmde/wikibase-reuse/wikidata-statement-count/-/merge_requests/10

Classify statement ids by form

itamar merged https://gitlab.wikimedia.org/repos/wmde/wikibase-reuse/wikidata-statement-count/-/merge_requests/8

Count novalue and somevalue per data type

itamar merged https://gitlab.wikimedia.org/repos/wmde/wikibase-reuse/wikidata-statement-count/-/merge_requests/9

Answer the review round on !1, !3 and !5

itamar merged https://gitlab.wikimedia.org/repos/wmde/wikibase-reuse/wikidata-statement-count/-/merge_requests/10

Measure statement id forms and column bytes

Maintenance_bot removed a project: Patch-For-Review.Fri, Sep 25, 2:33 PM

itamar opened https://gitlab.wikimedia.org/repos/wmde/wikibase-reuse/wikidata-statement-count/-/merge_requests/11

Trim the notebook and key the cache by snapshot

Snapshot 2026-09-14, item statements only, one row per main snak. Full tables in the rendered notebook and the result CSVs.

Size: 1.78 billion item statements on 121 million items; 97.8% best-ranked, 0.7% deprecated; within 0.5% of the WDQS graph split total on T434870.
Exclusions: scholarly articles (WDQS rule) are 55.5% of statements on 38.0% of items; astronomical objects 8.5%; without both, 641 million statements (36%) remain.
Value kinds: entity values 49.6%, strings 35.6%, quantities 6.0%, time 4.2%; novalue and somevalue together 1.1 million (0.06%).
Repetition: 95% of entity values and 99.5% of time values occur on more than one statement; strings 53%, and 29% once scholarly articles are out.
Ranks: 0.37% preferred, 98.9% normal; the preferred total on T434870 reads 6,373,097 but its cells sum to 6,494,097; this snapshot counts 6,495,384 (6,361,245 main, 134,139 scholarly).
Identity: 91.2% of statement ids are the minted form (uppercase uuid), 7.9% lowercase uuids, 0.9% lowercase prefixes; 51 ids are not uuids at all, 4 ids are duplicated, and only (subject id, statement id) is unique. Wikibase never validates the uuid part.
Identity bytes: 107 GB at 60 bytes per row, 28 GB at 16 (best-ranked only: 104 and 28).
Caveats: 1 statement with a missing datatype; 33 statements on 2 items are both scholarly and astronomical.