12,655 reputation
12048
bio website
location Munich
age
visits member for 3 years, 6 months
seen 51 mins ago

Nov
11
comment Does PostgreSQL have a variable character storage layer that optimizes storage space by automatic dedupe?
How long are those strings? Postgres compresses values automatically that exceed a certain length
Nov
10
comment MySQL equivalent of PostgreSQL's psql \c command
Technically you are not "switching users" with \c. You are creating a new connection with a different user.
Nov
6
comment Best design to handle multiple query keys
@MinimeDJ: it seems you have already made up your mind that the only useful solution is to use a single table. If that is the case I wonder why you ask the question in the first place (especially as you discard any other answer with "you don't know what you are talking about"). I agree with deszo and ypercube: the normalized data is the way to go.
Nov
5
comment Join does not not use index in PostgreSQL?
You want all rows from the news table, so an index isn't going to help (and I don't find 114ms that slow to retrieve 70000 rows)
Nov
4
comment Using transactions in sqlplus
A side note: in Postgres you can turn off autocommit as well and then you don't need begin to start a transaction.
Nov
3
comment How to determine a schema version at runtime
You can ask Liquibase to show the statements that would be executed if you did run the script. You can also just look at the meta table from Liquibase to determine what the last changeset was that got applied.
Nov
1
comment How to model when you could have many (thousands of) attributes to a table (product)?
If you are using Postgres have a look into the hstore data type. It's a very efficient and fast (indexable!) key/value store which is a much better fit to this problem than the EAV pattern.
Nov
1
comment Postgresql 9.3: Add Unique constraint using an existing unique index
@AdamMatan: yes the ALTER TABLE needs an exclusive lock (although for only a short time). If you have uncommitted transactions, then those will block this. Check for sessions that have the state "idle in transaction" in pg_stat_activity
Oct
31
comment PostgreSQL buffer page size limitations - rebuild with larger size?
postgresql.1045698.n5.nabble.com/…
Oct
31
comment PostgreSQL buffer page size limitations - rebuild with larger size?
"This seems awfully small during the terabyte era" there have been numerous discussion about the block size. For regular DBMS this is a pretty good choice. Oracle defaults to the same value (but can be configured for other sizes). SQL Server uses 8k as - and won't let you change that. What kind of index are you trying to create? What is the problem the index is supposed to solve? Very often questions asking to workaround some limits can be solved by taking a different approach.
Oct
31
comment How to determine a schema version at runtime
You don't do "diffs" with such a tool. You just run the script and it will update the schema as necessary. Doing schema management through "diffs" is the wrong approach.
Oct
31
comment How to determine a schema version at runtime
Liquibase manages that in its own table. You just run the liquibase script and it manages everything. Do read the information on their website. It's all explained there
Oct
31
comment Using Case in SQL
Which DBMS are you using? Postgres? Oracle?
Oct
31
comment How to determine a schema version at runtime
We use Liquibase for this.
Oct
30
comment What does a high level overview of the logic backing database queries look like?
These are about Postgres, but many of it probably applies to other DBMS as well: postgresql.org/docs/current/static/overview.html and momjian.us/main/writings/pgsql/internalpics.pdf and this: neilconway.org/talks/executor.pdf and neilconway.org/talks/optimizer/optimizer.pdf
Oct
30
comment Way to test alerts for backups?
Which DBMS are you talking about? SQL is just a query language used by all relational DBMS, it's not a DBMS product.
Oct
30
comment Data Analysis with MongoDB or MySQL
You might interested in this and this
Oct
30
comment CTE works as expected, but not when wrapped into a function
Then maybe named parameters for SQL function were introduced later. I tested that with 9.3
Oct
30
comment CTE works as expected, but not when wrapped into a function
@Gob00st: then you are using quite an old Postgres version. Named parameters for SQL functions were introduced in 9.0 (the oldest officially supported version). If that really is the case you should start planing your upgrade now.
Oct
30
comment CTE works as expected, but not when wrapped into a function
Sorry, there was a copy and paste error in the SQL function. And the conversion to the array needed an explicit type cast.