7,178 reputation
31740
bio website
location Berlin, Germany
age 39
visits member for 3 years
seen 2 hours ago

Jan
22
reviewed Close SQL Job for Monitoring Blockings on SQL Server
Jan
22
reviewed Close DataBase Back Up - with a Not Null Column
Jan
22
comment How to store thousands of properties related to a record in PostgreSQL?
Aaron's post summarizes the possible pitfalls very well. With careful design and planning, this may be a viable solution. It usually has relatively high development costs, as far as I can tell, so using some ready-to-use structures like hstore or jsonb can mean faster delivery. YMMV, as nearly always.
Jan
22
comment Lower() vs ilike on postgresql
It is also possible to use B-tree indexes for ILIKE and ~*, but only if the pattern starts with non-alphabetic characters, i.e., characters that are not affected by upper/lower case conversion.
Jan
22
comment How to store thousands of properties related to a record in PostgreSQL?
... or jsonb, if you have typed data (i.e., of non-text type). You can also try EAV, if you are brave.
Jan
22
revised Lower() vs ilike on postgresql
edited body
Jan
21
comment PostgreSQL and array of custom type
ARRAY[('tag', 'some value'), (...)]::contactinfo[] - this should work.
Jan
20
comment Is there any free, usable PostgreSQL formatter on Windows?
To tell the truth, I don't know any really good formatter that handles pl/pgsql code well. As far as I remember, this applies to all non-free tools I ever used, too.
Jan
19
comment Monitoring Amazon RDS with pgtop
@JackDouglas I added two words, is it specific enough now? :) Please note that this seems a real pain point with PostgreSQL.
Jan
19
revised Monitoring Amazon RDS with pgtop
try to turn the question into something more specific
Jan
13
comment Optimise huge Postgres table for fast read
The first idea is to create a unique index on the data (which you may need anyway), then CLUSTER the table along it. This way it is quite possible that the planner can choose an index only scan to fulfill the search (given that you always search for equality). Also, depending on the size of the actual data, it may be useful to have enough RAM (and setting shared_buffers accordingly) to keep the whole dataset in cache all the time.
Jan
8
answered Postgresql function to create table in other database
Jan
8
comment Postgresql function to create table in other database
@a_horse_with_no_name It is possible using dblink, isn't it?
Jan
7
comment Scaling PostgreSQL TRIGGER(s)
You may find checking PgQ useful, it uses C triggers for registering the data modification events.
Jan
7
answered Is there a way to “SELECT and TRUNCATE” without losing new data?
Jan
7
comment Is there a way to “SELECT and TRUNCATE” without losing new data?
Yes, that solution will work. The interesting part will be the performance. How many rows do you process (per minute, for example)?
Jan
7
revised Is there a way to “SELECT and TRUNCATE” without losing new data?
edited body
Jan
6
comment Should database be denormalized if I need an index across multiple tables
Well, it is duplicated, yes. The difference is that you don't have to maintain the denormalization logic, just keep refreshing the MV. If the tables are updated from batch jobs, this is probably the easiest way to go.
Jan
6
comment Should database be denormalized if I need an index across multiple tables
If you don't update your tables too often (or can refresh the materialized view often enough), you can create a materialized view from the two tables and create the index on it.
Jan
6
revised Should database be denormalized if I need an index across multiple tables
added 2 characters in body