6 ms·
ListenBrainz moves to TimescaleDB
- pgt 6y ago+1 on escaping measurement names. Quoting from their source code: def get_escaped_measurement_name(user_name): # ... comment omitted return '"\\"{}\\""'.format(user_name.replace('\\', '\\\\\\\\').replace('"', '\\"').replace('\n', '\\\\\\\\n'))
- hoseja 6y agoThat looks somehow worse than regex in C literal.
- contravariant 6y agoAlthough it seems about par for the course for regexp in elisp.
- mfreed 6y agoDo you have a URL? Thanks!
- ilogik 6y agohttps://github.com/metabrainz/listenbrainz-server/blob/b0846d2a/listenbrainz/utils.py#L42 https://github.com/metabrainz/listenbrainz-server/blob/b0846...
- mfreed 6y agoThanks. I love developer comments -- we've all written things like "this is hacky and ugly" in our code :) # Note: influxdb-python first replaces each backslash in the username with two backslashes # and because in influx queries, we have to escape each backslash, overall each backslash # must be replaced by 4 backslashes. Yes, this is hacky and ugly.
- iliekcomputers 6y agoI wrote that comment 3 years ago! It definitely took me a week or two worth of work to figure out what was happening there. The problem was exacerbated by the fact that the influx cli client then had different behaviour compared to the python client. Not sure if that has been fixed, but this isn't code that I'm really proud of. :D
- dionian 6y agoWe all have code like that somewhere :)
- sgt 6y agoThere are some edge cases in InfluxDB where escaping queries becomes a dark art.
- silvester23 6y ago> If you ever write bad data to a measurement in InfluxDB, there is no way to change it Correct me if I'm wrong, but I'm fairly certain you can just write data with the same timestamp again and it gets updated. Deleting is also easily possible.
- sgt 6y agoYou can't for example delete data from a time period to another, to the best of my knowledge.
- sciurus 6y agoThis is actually pretty straightforward. For example, `DELETE FROM "foo" WHERE time >= now() - 2d AND time < now() - 1d`
- sgt 6y agoOk, we had an issue deleting data from a series. The delete just hung and never completed. I assumed this was not really possible on Influx. Perhaps this is an edge case.
- samhld 6y ago(Solution architect at InfluxData here) Out of curiosity...what was the need for this DELETE? Deleting (not dropping) being somewhat of a "second class citizen" was a design choice to make room for more pressing time series needs. In my experience, `DELETE`ing is rarely necessary.
- majkinetor 6y agoI often use metrics for giving reports via Grafana. They are usually 99% correct which is good enough for many cases. The benefit of doing so is that you already need and have nice dashboard for devs to follow what is going on with the system real time, and its just grafana account distant from customer. So we do this on several big gov systems. Sometimes customers complain that there is a slight difference between real state and what metrics show but its not a big deal and rarelly happens (when you have millions of things, is it really important to know 100% precise value in majority of contexts?) Recently my colegue was testing some script and added some huge numbers on metric that is used for one of those reports. We had to delete those tests as customer complained that now his total invoice number jumped to trillions.
- iliekcomputers 6y agoHey! I've been working on ListenBrainz [0] for the past 3-ish years. Happy to answer questions if anyone has any. [0]: https://listenbrainz.org https://listenbrainz.org
- paol 6y agoExcited to find out about the project. I regularly use MusicBrainz but didn't know about the sister projects. I'll definitely be creating a ListenBrainz account. As a long time last.fm user I occasionally worry about the future of the platform. (There have been long stretches of time where it seems to have been in maintenance mode). You seem to support bulk importing last.fm data right?
- iliekcomputers 6y agoYep, we do support bulk import of last.fm data [0]. We also have a Spotify importer that automatically imports stuff from Spotify, if you use Spotify, I would definitely recommend setting that up. We're a really small team (all volunteers), so we don't move with as much urgency as I'd like to, but we've been making slow but steady progress over the years. If you find any rough edges, or have any feedback, I'd be happy to hear, my email is in the HN profile. :) [0]: https://listenbrainz.org/profile/import https://listenbrainz.org/profile/import
- jeffbee 6y agoI read that whole page and I have no idea what the project does.
- dylz 6y agoIt's last.fm.
- iliekcomputers 6y agoYeah, we need to fix that landing page. It's basically an open repository of your music listening history.
- akulkarni 6y ago(TimescaleDB co-founder). Thanks for the kind words! I feel especially proud about the first point "openness" - this is something we strive for both technically and culturally. For example, we have a pretty active Slack channel[0] where you can ask us anything. We've probably given away $$$$ of free support over the years ;-) [0] https://slack.timescale.com/ https://slack.timescale.com/
- RedShift1 6y agoI'm in the process of moving from InfluxDB to TimescaleDB myself and can't wait to get rid of the hoops I have to jump through to get InfluxDB to answer some basic questions, mostly stemming from the fact that InfluxQL doesn't support boolean expressions. Something like 'SELECT MAX(temperature) > 10 FROM...' doesn't work.
- mayhemchaos 6y agoAgreed -- every time I tried to query data from the command line I could not get anything to work. I would usually find a piece of working python code and adapt that to run the query instead. 100% opposite of what a CLI interface should be.
- akulkarni 6y agoWelcome to the TimescaleDB community! In case it is helpful, we wrote a tool to easily migrate from InfluxDB to TimescaleDB: https://docs.timescale.com/latest/tutorials/outflux https://docs.timescale.com/latest/tutorials/outflux
- brokentone 6y agoI'm personally working on migrating a DB I've had for tracking some economic time-series data from Influx to TimescaleDB, and I have to say Outflux is the most savage tool name ever.
- 6y ago
- mfreed 6y agoFun fact: TimescaleDB exists because we were using InfluxDB + Postgres for a previous IoT project and also found it unworkable (developer experience, query language, reliability, scalability and performance, operations, etc). We first built TimescaleDB as "Postgres for time-series" for our own needs and then decided to open-source it for others. :-)
- zitterbewegung 6y agoHave you thought of making a TimescaleDB app like the Postgres.app for macOS? Or could I use Postgres.app to make a TimescaleDB app?
- deleted 6y ago[deleted]
- avthar 6y agoIt seems you can use the Postgres.app and install TimescaleDB on it Here's some instructions on how to do so: https://github.com/slashdotdash/til/blob/master/postgres/install-timescaledb.md https://github.com/slashdotdash/til/blob/master/postgres/ins...
- deleted 6y ago[deleted]
- rubyn00bie 6y agoYou can mount like any PostgreSQL version, including extensions, in Postgres.app. I’ve used both Timescale and Agens with it, with zero problems.
- akulkarni 6y agoNot sure if a coincidence, but someone just published this blog post today: Installing Timescaledb on Mac OS X with Postgres.app https://prathamesh.tech/2020/07/23/installing-timescaledb-on-mac-os-x-with-postgres-app/ https://prathamesh.tech/2020/07/23/installing-timescaledb-on...
- jarym 6y ago
- awinter-py 6y agotimescale is a postgres extension. 'postgres as a platform' is an interesting world to live in. postgres built-in RBAC is clunky or people would be relying on it, but an ecosystem of postgres plugins could include cleaner or smaller versions of this feature. Even things like complex migrations (github's gh-ost, for example) could exist as DB plugins.
- thejosh 6y agoI really want to love timescaledb, it's great.. except for the minor issue of not being able to back up. https://github.com/timescale/timescaledb/issues/1835 https://github.com/timescale/timescaledb/issues/1835
- akulkarni 6y agoTimescaleDB definitely supports backups :-) Here is a page from our docs on how to perform Backup & Restore: https://docs.timescale.com/latest/using-timescaledb/backup https://docs.timescale.com/latest/using-timescaledb/backup Not sure what's going on in that one Github issue, but we are looking into it.
- justinclift 6y agoIt seems to be affecting multiple people too. :(
- akulkarni 6y agoThat issue is now closed by the original author: "Data is successfully dumped. also i can see the constraints, indexes are also copied successfully." https://github.com/timescale/timescaledb/issues/1835 https://github.com/timescale/timescaledb/issues/1835
- justinclift 6y agoCool. :)
- brightball 6y agoThat seems like a pretty big deal. Does a WAL backup approach work?
- akulkarni 6y agoTimescaleDB offers a number of backup and restore options, including wal-e (WAL-based), pg_dump & pg_restore: https://docs.timescale.com/latest/using-timescaledb/backup https://docs.timescale.com/latest/using-timescaledb/backup There are hundreds of thousands of TimescaleDB databases in production so this is generally not an issue.
- 120bits 6y agoInteresting read and thanks for sharing. Not too long ago, I was asked to work on some analytics project and it required time-series data. I'm not a rockstar programmer and don't really know much about trends. So, I ended up googling and stumble upon InfluxDB. It felt like that right choice and I started playing with it. As the time passed, I realized that it might be a good software and I'm sure people love InfluxDB, but it wasn't the right choice for me. I didn't really like the docs, maybe its good now. And I had the same feeling about query syntax, it felt weird. I moved to TimescaleDB and never looked back. I have it production for almost 2 months now. 20 tables and over 100Million writes/week. One of things I really liked was staging, I don't use docker and or anything fancy. I have bash script that and it runs on centos box and all timescale extension and postgres database are packaged together. I was impressed by the timescale compression feature. I wasn't using it earlier because I had to be careful about what columns I need to segmentby. I would love to see some more features but I'm sure timescaledb team is already on it.
- mfreed 6y agoHey 120bits - thanks for the nice words! What new/other features would you like to see? (Also feel free to join slack.timescale.com or reach out at mike (at) timescale.com)
- jstrong 6y agoto each his own - I find influxdb somewhat flaky but the best part about it is not having to write the atrocious sql queries I would need to to get the same kind of windowed aggregations. `group by time(1h)` and so on is pretty handy.
- jbmsf 6y agoI'd love to hear more about how your data ingestion works. I'm thinking of implementing TimescaleDB myself, but in my initial read of the docs, the focus seemed to be managing the database, not getting data into the database...
- dominotw 6y agosame way you'd insert data into postgres.
- jbmsf 6y agoThat's not really helpful. Let's assume you have a distributed system; you probably don't want all of your system components connecting directly to TimescaleDB. You also probably want to have some layer that implements queuing and handles back pressure if it can't insert into the database at the rate that events are coming in. You may want to batch insert data. I'd assume that most anyone building a system like this at scale has to solve these problems; does everyone roll their own?
- k-rus 6y agoThere are number of tools, which can be used in front of TimescaleDB or PostgreSQL. There are plugins for Telegraf, Kafka, Prometheus as described in https://docs.timescale.com/latest/using-timescaledb/ingesting-data https://docs.timescale.com/latest/using-timescaledb/ingestin... Timescale builds connector and entire workflow to run Prometheus on top of TimescaleDB and support Grafana in flexible way: https://github.com/timescale/timescale-prometheus https://github.com/timescale/timescale-prometheus
- jbmsf 6y agoPerfect. Thank you for the reference.
- gregors 6y agoWe too started off with influx but it wasn't a good fit mainly due to use having issues with high cardinality. I don't know if this is still the case with current implementations, but what it boils down to is if your data is searchable by a "user_id" really look elsewhere. That might be an oversimplification but that's the gist of it. I was fully ready to just roll my own partitioned table and gave TimescaleDB a shot. It worked well. There was a bug we ran into, but it was an existing one documented on github and was addressed pretty quickly. I still like influx, and would use it again but beware of the cardinality issues.
- valyala 6y agoIf you have cardinality issues in InfluxDB, then just substitute InfluxDB with VictoriaMetrics :) [1] [1] https://medium.com/@valyala/insert-benchmarks-with-inch-influxdb-vs-victoriametrics-e31a41ae2893 https://medium.com/@valyala/insert-benchmarks-with-inch-infl...
- decafninja 6y agoAs someone who wants to pick up a time series DB to learn, what would be the best in terms of being the "industry standard"? InfluxDB? TimescaleDB? I'm familiar with some basics of kdb and use it often in my day job, but from what I understand that isn't widely used outside of finance?
- akulkarni 6y ago(I work at TimescaleDB.) If you are familiar with Postgres and/or SQL, then you may want to start with TimescaleDB. It's just Postgres for time-series. Full SQL, so it's possible to be productive instantly.
- valyala 6y agoThe following time series databases are popular right now: * ClickHouse (this is a general-purpose OLAP database, but it is easy to adapt it to time series workloads) * InfluxDB * TimescaleDB * M3DB * Cortex * VictoriaMetrics The last three of these TSDBs support PromQL query language - the most practical query language for typical time series queries [1]. So I'd recommend starting from learning PromQL and then evaluating time series databases from the list above. [1] https://medium.com/@valyala/promql-tutorial-for-beginners-9ab455142085 https://medium.com/@valyala/promql-tutorial-for-beginners-9a...
- rweichler 6y agoFigured I'd use this as an opportunity to plug my own service: https://eqe.fm https://eqe.fm Only works on jailbroken devices but it works well, has a local backup, and has been maintained (by me) for 2 years now. Server costs are $2.50/mo, so this will stay up as long as I am alive.
- iEchoic 6y agoHas anyone used Prometheus as well as TimescaleDB in production and have thoughts to share on those, comparatively?
- akulkarni 6y agoWe have quite a few in our Slack channel: slack.timescale.com Feel free to ask over there :) (Btw - TimescaleDB is designed to work with Prometheus. You can see more here: https://github.com/timescale/timescale-prometheus https://github.com/timescale/timescale-prometheus)
- osigurdson 6y agoIn some cases it is difficult to define the table columns up front. Instead, a few tables: Object, Property, Time and Value (example below) are defined which make it possible to create new items on the fly. This works reasonably well up to a few billion records in the value table. However it does end up taking a lot of space (covering indexes/requisite memory are required for performance). It would be great to see a Postgres compatible solution that solves this problem in a more optimal way than a stock RDMS. Object objectId objectName other... Property propertyId objectId FK propertyName other... Time timeId time other.. Value timeId FK propertyId FK value