date: 2025-10-26
by: Avery


Scraping of gelbooru api.

May contain deleted posts.

Negative IDs on the tags table mean the tag was listed in the post response, but its details aren't available through the gelbooru tags api.

Count of posts by Rating:
rating_name |post_count|
------------+----------+
Sensitive   |   5595742|
General     |   2682397|
Explicit    |   2377926|
Questionable|   1571064|
Safe        |        29|

Count of tags by type:
tag_type  |total_tags|total_tag_usage|
----------+----------+---------------+
Artist    |    506011|       11207081|
General   |    323206|      351580251|
Character |    226270|       18247000|
Copyright |     49205|       18156724|
Deprecated|      2990|       33548549|
Metadata  |       501|       20854199|

Explicit/Questionable to General ratio: 
explicit_like_posts|general_posts|explicit_like_to_general_ratio|
-------------------+-------------+------------------------------+
            3948990|      2682397|                          1.47|

Schema:
CREATE TABLE posts (
    id BIGSERIAL PRIMARY KEY,
    created_at TIMESTAMP NOT NULL,
    score BIGINT NOT NULL DEFAULT 0,
    width INTEGER NOT NULL,
    height INTEGER NOT NULL,
    md5 VARCHAR(32) NOT NULL,
    directory VARCHAR(255) NOT NULL,
    image VARCHAR(255) NOT NULL,
    rating SMALLINT NOT NULL,
    source TEXT,
    change BIGINT NOT NULL DEFAULT 0,
    owner VARCHAR(255),
    creator_id BIGINT NOT NULL,
    parent_id BIGINT,
    sample INTEGER NOT NULL DEFAULT 0,
    preview_height INTEGER NOT NULL,
    preview_width INTEGER NOT NULL,
    title TEXT,
    has_notes BOOLEAN NOT NULL DEFAULT false,
    has_comments BOOLEAN NOT NULL DEFAULT false,
    file_url TEXT NOT NULL,
    preview_url TEXT NOT NULL,
    sample_url TEXT NOT NULL,
    sample_height INTEGER NOT NULL,
    sample_width INTEGER NOT NULL,
    status SMALLINT NOT NULL,
    post_locked INTEGER NOT NULL DEFAULT 0,
    has_children BOOLEAN NOT NULL DEFAULT false,
    CONSTRAINT unique_md5 UNIQUE (md5)
);

CREATE TABLE tags (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    count BIGINT NOT NULL DEFAULT 0,
    type BIGINT NOT NULL DEFAULT 0,
    ambiguous BIGINT NOT NULL DEFAULT 0,
    CONSTRAINT unique_tag_name UNIQUE (name)
);

CREATE TABLE post_tags (
    post_id BIGINT NOT NULL,
    tag_id BIGINT NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);


Where

Tag types:
General     =  0
Artist      =  1
Unknown     =  2
Copyright   =  3
Character   =  4
Metadata    =  5
Deprecated  =  6

Tag rating:
Explicit      =  0
General       =  1
Questionable  =  2
Safe          =  3
Sensitive     =  4