News for hackers. Stuff that matters.
Re:Faster DISTINCT queries on PostgreSQL by jereze on Friday May 07, 2021 @03:18AM (#27073227)
I made first tests with Timescale 10 days ago with logs of a website. First result, I was impressed by the compression: 13GB of compressed CSV files - 1 PostgreSQL table of 63GB - 1 Timescale hypertable of 4GB. Then, I saw improvements on the performance of my queries, between 23% and 92% faster on the hypertable versus the table. This is definitely interesting and I will look into it further. I'm getting from "it's not a good idea to put weblogs on Postgresql" to "why not, if it helps simplify our infra".
Re:Faster DISTINCT queries on PostgreSQL by timescale11 on Thursday May 06, 2021 @05:18PM (#27069173)
I've been considering TimescaleDB for some use cases. Specifically to keep a transactional log of messages sent out.

Has anyone independently benchmarked TimescaleDB vs Postgres w/ B-Tree vs Postgres w/ BRIN? Given that most timescale data would benefit greatly from BRIN, I'm surprised their various blog posts never cover it.

I have discovered one person who tried to replicate some of the benchmarks around Postgres vs TimescaleDB (back in 2019), but they came up with data that contradicts the latest blog post around TimescaleDB vs Postgres performance, which makes me reconsider whether I really need what TimescaleDB provides. Maybe there have been large changes since then?

http://blog.coelho.net/database/2019/09/13/postgresql-vs-tim...

https://blog.timescale.com/blog/timescaledb-vs-6a696248104e/

Re:Faster DISTINCT queries on PostgreSQL by fny on Thursday May 06, 2021 @05:34PM (#27069317)
If you have a real use case, just build it with vanilla pg. Once you start feeling pain with pg, migrate to timescale and see what happens.

You may never end up needing the performance boost they advertise.

Re:Faster DISTINCT queries on PostgreSQL by nullwarp on Thursday May 06, 2021 @05:47PM (#27069435)
I'll just add it's extremely easy to migrate, pg_dump and then just load it back up.
Re:Faster DISTINCT queries on PostgreSQL by akra on Thursday May 06, 2021 @08:19PM (#27070470)
In the past I found this tip to be helpful: https://wiki.postgresql.org/wiki/Loose_indexscan. You can emulate it but it would be good for distinct to do this natively for default fast performance (distinct key queries) to avoid needing to know these tricks of the trade.
Re:Faster DISTINCT queries on PostgreSQL by knuthsat on Thursday May 06, 2021 @05:13PM (#27069115)
Does anyone know why something like binary search isn't a first simple choice for skipping to the next unique item, if the index is ordered?

Is it because of the tree structure of the index?

Re:Faster DISTINCT queries on PostgreSQL by djk447 on Thursday May 06, 2021 @05:43PM (#27069394)
(Disclaimer: I'm an engineer at Timescale, I didn't work directly on this feature but have some knowledge of what it does).

I'm not 100% sure I understand exactly what you're asking, but, perhaps some more info will be useful. First off, the goal with this isn't just to find the next unique item, it's to find the set of all the unique items, so you'd need to iterate the search, which is basically what the skipscan is doing internally.

Internally, the index is using something like a binary search (it's a btree, so slightly different, IIRC) to skip to the next unique item. A lot of the work here was in teaching the planner / executor to actually do that rather than the simpler, less efficient way that PG usually does it (brute force unique over a full scan). There's some more complexity on top of that in terms of teaching it to do that over all the chunks and then combine the results (because each has a separate index) and some more complexity in terms of knowing when you can use multi-column indexes and the like, but at its most basic level, it basically is using some sort of binary search to find the next item. But perhaps I'm missing what you're asking, feel free to clarify if I can better help here...

Re:Faster DISTINCT queries on PostgreSQL by knuthsat on Friday May 07, 2021 @06:45AM (#27074464)
If index is ordered, I imagined it as a flat array. I would go to the next bigger item using binary search. I guess SkipScan does a similar thing but the index is a btree.

Of course, things get more complicated when more tuples need to be distinct but I was a bit confused as to why the ordered property was not exploited by PG before (and still is not).

Re:Faster DISTINCT queries on PostgreSQL by fny on Thursday May 06, 2021 @05:30PM (#27069286)
Very rough sketch.

Say you have a table with two rows. One is time, the other is temperature. Your table is sorted by time, but you want all the unique temperatures.

Given the temperatures are unordered, you can't perform a binary search. If you also create a proper index against the times, your temperatures will also live in leaves of a tree that you'll need to access to get the values.

So trick 1 is to just index that column, like you said, a precomputed binary search if you will But since we're interested in distinct queries, we can optimize further by pointing to the next greatest value with the index tree too (hence skip scan.)

Re:Faster DISTINCT queries on PostgreSQL by zmmmmm on Thursday May 06, 2021 @05:45PM (#27069412)
> Say you have a table with two rows

You mean two columns, right?

Re:Faster DISTINCT queries on PostgreSQL by efficax on Thursday May 06, 2021 @08:09PM (#27070387)
Walking a btree just is binary search (effectively, although btrees have more than two branches and leaves)
Re:Faster DISTINCT queries on PostgreSQL by avinassh on Friday May 07, 2021 @03:14AM (#27073201)
What exactly is Timescale DB, is it built on top of Postgres or a plugin for postgres or postgres API compatible DB built from ground up?
Re:Faster DISTINCT queries on PostgreSQL by mfreed on Friday May 07, 2021 @10:40AM (#27076430)
TimescaleDB is implemented as an extension on Postgres, so native Postgres runs underneath.

("Underneath is not really the right way to think about it, because the extension model lets us hook into lots of different places in the Postgres code -- TimescaleDB generates a shared library .so, implemented in C, so runs in same process/memory space.)

But, the great thing of this is as Postgres improves, so does TimescaleDB. TimescaleDB launched with Postgres 9.6, and now supports Postgres 13 and all the great improvements that have been implemented between those two PG versions.