12,790 reputation
12049
bio website
location Munich
age
visits member for 3 years, 7 months
seen 5 hours ago

Dec
4
comment Return total number of rows and selected (aggregated) data
You can add a column containing the total count using: count(*) over () as total_count (but that will be repeated for each row)
Dec
2
comment How not to display column name during select query
Which DBMS and SQL client are you using?
Dec
1
comment Is it possible to create materialized view based on another materialized view in postgres?
No, there is no such limitation. But you need to refresh them in the correct order.
Nov
28
comment Using Aerospike without SSD
Aerospike claims it's an in-memory database. So why would the harddisk play any role if everything is in memory?
Nov
27
comment PostgreSQL, UPDATE CASE statement uses huge amount of space
That's caused by Postgres' MVCC implementation (wiki.postgresql.org/wiki/MVCC) which essentially means that the update creates a new copy of each row. One way to alleviate this might be to do the update in batches (e.g. of 5 million) and then run a vacuum analyze after each batch. Autovacuum will not kick in for that table until the update has been committed
Nov
27
comment PostgreSQL, UPDATE CASE statement uses huge amount of space
The indexes won't help at all here as you are updating all rows in the table. To improve the speed I would drop all indexes that include the fcode column.
Nov
26
comment How should I delete branch from adjacency list in SQL Server?
I would define the FK constraint as on delete cascade and then simply delete the starting node.
Nov
26
comment Veryfing integrity of custom order column with pure SQL
Please post the table definition as create table and some sample data (ideally as insert statement). Even better: create a short example on sqlfiddle.com
Nov
26
comment Performance Benchmark - MySQL vs PostgreSQL
Plus you probably don't exploit specific features of a DBMS. A query using window functions in Postgres will most probably be really slow when re-written for MySQL. I once migrated a stored procedure that did an aggregation in MySQL and ran for about 1 hour to a single UPDATE statement in Postgres that finished in 10 minutes.
Nov
26
comment Performance Benchmark - MySQL vs PostgreSQL
While I agree that pgbench is a valuable tool, you can't use it to compare two different DBMS (unless you can re-create the exact same workload with a different tool on the other DBMS)
Nov
26
comment Performance Benchmark - MySQL vs PostgreSQL
Simply timing some queries is not a valid benchmark to begin with. You are completely ignoring concurrent access with that test. If you have multiple transactions reading and writing to the same tables this makes a huge difference. Benchmarking specific queries in a single user environment only makes sense if that's how the DB will be used later.
Nov
26
comment Get list of characters in column
@Colin'tHart: not really, no ;)
Nov
25
comment Can any one Explain me What is the Use of Cascading Replication in Postgresql?
If you do a switchover from master to slave1, you still have a spare slave if the now active master also fails. Otherwise you'd be running with no failover computer at all.
Nov
24
comment Increase performance WITHOUT adding indexes
I wouldn't call statements "which are only performed every few seconds" rare. The are executed often enough to warrant an index. I have never seen a DBMS where adding an index would make the statement slower because parsing (the step where the best index is searched for) became an issue. When it comes to performance don't just assume things. Test them. Your concern about the DML is a valid one though, but I have seen only a few systems where adding an index really made the DML so bad that it had to be removed. But again: you have to test this.
Nov
24
comment How long to learn PostgreSQL?
"Entirely removing Postgres can be a bit tricky" - to "play" around I usually only unzip the archives (e.g. the zip on Windows) and run initdb manually. To "throw away" such an installation, all I need to do is to delete the directory where I unzipped the archive.
Nov
24
comment Error on DDL statement never invokes CATCH
"I understand that DDL statements are implicit COMMIT" - I think you are confusing Oracle (or MySQL) and SQL Server.
Nov
24
comment How long to learn PostgreSQL?
Make sure you take the time to go through the manual, at least the chapter "SQL Language": postgresql.org/docs/current/static/sql.html
Nov
23
comment How to restrict access to current user record and its rights in postgres
"Is it possible and reasonable to create plpgsql methods for those operations" - yes, that is possible and probably a bit safer than using a view.
Nov
22
comment Prevent Primary Key increment when an INSERT statement fail in PostgreSQL
You can't prevent. That's the whole point of using sequences. And you don't have to care either. The generated id is just some artificial value that has no meaning whatsoever. Whether your artificial key is 1, 42, 36387 or 43873483 is completely irrelevant as long as it is unique.
Nov
21
comment postgres materialized views and indexes to improve aggregate performance
Oracle actually does this. When the optimizer sees a query that matches a MVIEW query, it will use the mview instead if that is cheaper (called "query rewrite")