Database dump survey

Hello all,
 
To make it easier for developers and analysts to access the information they want from the site, we are planning to offer database dumps for our public-facing data.¹
 
Though this could change in the future, currently the plan is to offer nightly dumps in the PostgreSQL custom dump format. This will make it easy for anyone who wants to use the data to simply restore the dump and have a fully operational schema with indices and foreign key constraints. In addition, these dumps will be consistent and represent a perfect snapshot of the database at the time they are taken.
 
We would like to know from the community what data would be most useful to include in these dumps. Please comment below with the data you would like and your usecase for it, or any questions you may have.

this won’t effect the overall performance of the site right?

What’s a database dump?

@Shimmering Spectacle  
No.

@Background Pony #F9D1  
that’s what i like to know, it’s sounds concerning.

@Background Pony #F9D1
 
@Shimmering Spectacle
 
A database dump is, when a database breaks up with you.

@Background Pony #F9D1  
Basically a partial export of the site’s database in a format that’s only interesting to nerds.

I’d be down with that.  
(Don’t think it’d have anything that you can’t already see from the website’s normal interface; it’ll just be stuff like # of favorites a picture has, etc.)

Interactions, comments, image deduplication data, hashes, representation paths for image objects, tags, tag aliases/implications data.  
Anything else is secondary in my book.

It would surely make archivers life easier.

@Luna  
Interactions would have to be limited to faves. Everything else is private information.

I’d like this, you know, because it’d be easier on your servers.  
edit: Full public facing dump, archiving everything. I already scrape every page recursively so this just makes it easier on your server with less queries from myself and like 50-60 other archivers that constantly and update.  
This would not stop new image queries however.
 
Deleted comments would be welcome in the dumps - it would take a lot of effort to stir up shit from a deleted comment pulled from a DB dump, it would also provide better transparency. If not, comment archivers will continue to run most likely resulting in more queries.

Could someone explain what this is, in english?

Sounds great, can’t wait to mess around with the data

@Communist Starlight  
A database dump is just a large excel file.  
A series of tables that have things that go to other things.
 
Key:value pairs generally but it can also be Key:key:key:Value:value:value:value:value Like this:
 
imageid|image title|image tags|image er|boolean nsfw
 
Please note that’s an dumb’d down example and is likely not anywhere close to their actual database.

@Communist Starlight  
Basically, every night the site will be providing a log of the site’s data. This is a gross oversimplification, mind you.

I’d also be interested in one such thing. Including image data, comments, comment history, tags, changes in tags, forums, forum topics and forum posts.
 
Usecase would be archiving.

Awesome

Exact time of posts?
 
Also rating tags numbers and their relation to characters. For example who has a higher percentage of suggestive images Celestia or Luna.

@Darkest Lunar Flower  
Exact time of posts is already visible in the markup (e.g. 2019-07-11T23:45:35Z) but that will most likely be included

@byte[]  
Ah thanks and the other idea? Is that one okay?

@Darkest Lunar Flower  
You will be able to extract it, but I have no interest in generating statistics for this dump.

@byte[]  
Rip ; ;

Is it possible to share the code for reverse search along with the hashes it use?
 
Usecase:  
I have my own picture archive, and I’d like to correlate them with derpibooru in order to  

@Darkest Lunar Flower  
You can find the exact time by hovering over the bit that says “Posted 3 minutes ago with your cursor”, and it’ll show you the GMT timestamp of the post.
 
As for the other stuff - that’s statistics. People will be able to calculate that with this info (and I’m sure some people will), but we’ll just be providing the RAW data. Dumping a database is pretty easy and straightforward, but running a bunch of calculations and analytics against it is a bit more resource-intensive, and we don’t wanna put additional strain on the server.