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

Nov
14
wiki created hierarchical-data excerpt
Nov
14
suggested suggested edit on hierarchical-data tag wiki
Nov
14
suggested suggested edit on hierarchical-data tag wiki excerpt
Nov
14
revised Get child record from specific root
edited tags
Nov
14
suggested suggested edit on hierarchical-queries tag wiki
Nov
14
suggested suggested edit on hierarchical-queries tag wiki excerpt
Nov
14
comment SQL Server: performance of IDENTITY vs INT as primary key
An identity column is an integer column.
Nov
14
revised Is it normal to hard code the input of over 80 input fields?
edited tags
Nov
14
reviewed Edit suggested edit on Cannot drop a Postgres table
Nov
14
revised Cannot drop a Postgres table
Formatted query and removed thanks
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
13
revised How can I lock an INSERT operation
edited tags
Nov
13
revised MAX (id) function with MySQli
edited tags
Nov
12
reviewed Edit suggested edit on Get latest status of an item in the database
Nov
12
revised Get latest status of an item in the database
Added RDBMS tag
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
revised Adding a “position” number in Mysql
added 64 characters in body; edited tags
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