12,820 reputation
12049
bio website
location Munich
age
visits member for 3 years, 7 months
seen 2 days ago

Nov
14
comment Looking for a recipe to merge multiple rows to single row
To me it sounds as if a materialized view is your best choice. The refresh is transactional so concurrent inserts are not seen during the refresh. The question is how often you need to refresh it and whether building the mview would be fast enough.
Nov
14
comment Need help designing a database
You don't have to worry about maintaining the index "manually", the database takes care of that (you might want to read the manual: postgresql.org/docs/current/static/indexes.html). The index does indeed incur a write overhead, but can speed up reading massively (it can also speed up the delete). Another option might be a materialized view that is refreshed nightly and only contains the most recent rows. What you have is essentially a greatest-n-per-group problem. Here is a discussion about this: stackoverflow.com/a/7630564/330315
Nov
13
comment Need help designing a database
"Since the last accessed time keeps changing it cant be made part of any index" - that statement is simply wrong. Why do you think you can't index that column? If your queries always include userid and the last_accessed_time value, then creating an index on that combination seems the natural approach that should be tested first. Not some over-engineered partitioning scheme(which seems to be a lot more work than a good old plain index. "Also there is no logical clustered index field for this table" Postgres does not have clustered indexes.
Nov
12
comment Are there any special characters that are never allowed in SQL?
that's not entirely true. a create procedure can contain a ; which doesn't terminate the statement. But as mateen talks about "queries" I guess that isn't an issue here. Of course he/she needs to check for quoted values select ';' from foo;
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.