6,969 reputation
31439
bio website
location Berlin, Germany
age 38
visits member for 2 years, 10 months
seen yesterday

Nov
7
comment Are PostgreSQL dynamic arrays more wasteful than plain text?
Exactly what do you mean by dynamic arrays? How do they differ from the usual arrays?
Nov
7
revised why pg_restore ignores --create ? Error: failed: FATAL: database “new_db” does not exist
deleted 10 characters in body
Nov
7
comment PostgreSQL: Creation date of a table
unfortunately, it won't work on 'plain' PostgreSQL as this view is a greenplum addition.
Nov
7
revised PostgreSQL: Creation date of a table
formatting
Nov
7
reviewed No Action Needed Change Data Types large Live table
Nov
7
reviewed Close Show only records with latest date by displaying ID once only
Nov
7
reviewed Close query to pull one row per id when multiple rows of each id exist in table
Nov
7
reviewed Close MySQL stored procedure return variable values from seconds query?
Nov
7
reviewed Close MongoDB object pointing
Nov
7
reviewed Close Time Series data for controllers connected to other devices
Nov
6
comment Best design to handle multiple query keys
So, could you please clarify why do you want only one table? That would possibly save us a lot of unnecessary discussion.
Nov
6
comment Best design to handle multiple query keys
@MinimeDJ your approach is simply not one for an RDBMS. That also means that you can do it, if you really want, but most probably you'll get an awful performance.
Nov
6
comment Assign to NEW by key in a Postgres trigger
I haven't thought about hstore, it's much simpler than my approach.
Nov
6
answered Assign to NEW by key in a Postgres trigger
Nov
6
comment Best design to handle multiple query keys
@MinimeDJ Yes, there is, it's called normalization ;)
Nov
6
comment Best design to handle multiple query keys
@MinimeDJ hm, there is no accepted answer there. Also, I think if you cannot normalize your data, PostgreSQL is not the right platform to do this.
Nov
6
comment Optimizing SELECT queries
Well, your table and the index on tag are very close in their size. Postgres simply decided that the sequential scan would be the cheaper. In the aggregate case, it's no wonder that aggregating 100M+ rows takes time. At the same time, it would be interesting to see the EXPLAIN (ANALYZE, BUFFERS) output to see the ratio between pages read from disk and pages cached.
Nov
5
comment How to optimize aggregation
The actual query and an output of EXPLAIN (ANALYZE, BUFFERS) from a slow run would help a lot.
Nov
5
comment sorting by partial key name in json column
I'd store the integer part separately. Then you may be able to have a slightly different JSON structure that can be searched to your liking.
Nov
5
comment Deadlock detected on CREATE TABLE in PostgreSQL
@khorvat you should probably flag your post on SO for migration to here, then the two posts will be merged and all collected information seen in one place.