Reputation
12,532
Next tag badge:
733/1000 score
223/200 answers
Badges
7 32 59
Newest
 sql
Impact
~665k people reached

2d
comment cannot call json_array_elements on a scalar
Looks like your input is not necessarily a valid JSON object. Using your example input, json_array_elements() returns the expected three rows.
Apr
25
comment Most efficient way to retrieve data by timestamps
Could you please do an EXPLAIN (ANALYZE, BUFFERS) on a 'slow date'? Also, what are the index and table sizes you have?
Apr
22
comment Could auto_analyze cause inconsistent (and bad) query plans?
This is a very good question. I wouldn't blame autoanalyze for this happening, rather the underlying statistics settings. If the statistics (auto)analyze collects from the table leads to so bad plans (check the huge mismatch of the estimated and actual row numbers in the bottom node of the bad plan), one can play around with statistics settings. I am currently not in a position to check how this could work for an hstore, but usually raising statistics_target to a higher value helps. See tech.zalando.com/blog/… for an example.
Apr
14
comment Is a partition a relation?
I don't know for sure Greenplum handles this like PostgreSQL did at the time, but if yes, then yes :) A partition has its entry in pg_classes, for example, as a regular table.
Apr
14
comment Set a default value for a jsonb property in PostgreSQL
Do you mean you want to amend the value you get, if the "picture" key is missing or empty? Do you have only one document type? If multiple, do they all have (or need) a filename?
Apr
14
comment Restoring postgresql 9.4 crashed database
Which exact (minor) version is this? And where did you get the idea of the solution from?
Apr
9
comment Testing postgresql 9.4 streaming replication
It is not necessary to be 100% up to date for the slave to be started (and, to be more precise, to accept connections). It should be in a consistent state, which might mean some or no delay to the master. Furthermore, there are functions to test if there is a delay. Also, I wouldn't be overly confident about this VACUUM FULL trickery. The streaming might fail for a number of conditions, normally not corrupting anything - but if corruption occurs, rebuilding the slave from a fresh base backup is the safe way to go.
Apr
9
comment postgresql function index not being used by query
That 5% is not an exact number - it can be 10%, too, depending on circumstances.
Apr
7
comment Postgres sharding installation with citusdb
Or one can now install Citus as an extension, too.
Apr
6
comment Get total size for all Postgres or Greenplum tables including partitions
Why and how is this query not easy?
Apr
4
comment Measure the size of a PostgreSQL table row
This is just a small variation to an already existing solution. Also, the second query has multiple problems - not clear why you mention MBs there, and summing rounding errors makes absolutely no sense.
Apr
2
comment How to not “duplicate” a table if it has a unique constraint over many columns?
Please check the answer on a similar question: dba.stackexchange.com/a/119402/6219 - especially what Craig writes about concurrency.
Apr
1
comment Loop over string literals as input to function PostgreSQL 9.4
char is a bad idea, unless you are really sure the length of the number to be casted is always the same, in your case 1. Try SELECT 12345::char;
Mar
31
comment how many database tables is too much?
This question would be much more useful if you gave an example - apart from the student table, the existence of which tables made you worry?
Mar
30
comment How to not “duplicate” a table if it has a unique constraint over many columns?
Please note the following (from the documentation page I've added to your post): 'BRIN is designed for handling very large tables in which certain columns have some natural correlation with their physical location within the table.'
Mar
30
comment How to not “duplicate” a table if it has a unique constraint over many columns?
Why do you want to avoid that 'duplication', in the first place? Also, are there queries that might benefit form this unique index?
Mar
30
comment To partition or not to partition?
To me it is not really clear what you mean by 'partitioning for contractors'. Are the tables that contractors use different from the ones that belong to a customer? Does it ever happen that customer A has to access the data from customer B? If not, then separating customer specific data into a per-customer schema might be a way to go - but not necessarily for performance, but for separation of concerns (increased security/privacy and so on).
Mar
30
comment To partition or not to partition?
The rules and triggers definitely have overhead, and it's easy to measure. Additionally, they add complexity and make debugging (much) harder. Also, after going down this way several times, I'd suggest (without admittedly knowing all the details) to keep an empty parent table and one or more child partitions. When changing the partitioning scheme, this might come very handy.
Mar
29
comment Started using UUID's as primary key, would like to execute union of entities with BigSerial Primary Key
Casting to text should not add too much overhead - you can easily try it by comparing the execution times on the same subset (one with UUIDs, one with bigints) with and without casting.
Mar
26
comment How to handle multiple Oracle homes?
... and a README on the repo!