We are currently using a summary table that aggregates information for our users on an hourly basis in UTC time. The problem we are having is that this table is becoming too large and slowing our system down immensely. We have done all the tuning techniques recommended for PostgreSQL and we are still experien…
Tag: sql
Only inserting a row if it’s not already there
I had always used something similar to the following to achieve it: …but once under load, a primary key violation occurred. This is the only statement which inserts into this table at all. So does this mean that the above statement is not atomic? The problem is that this is almost impossible to recreate…
Synchronizing client-server databases
I’m looking for some general strategies for synchronizing data on a central server with client applications that are not always online. In my particular case, I have an android phone application with an sqlite database and a PHP web application with a MySQL database. Users will be able to add and edit i…
How to store ordered items which often change position in DB
I need to be able to store a large list of ordered items in the DB. So far that’s straight-forward: In queries, I always need to get just a few items (filtered based on OtherFields) but in the correct order. Easy as well, putting an index on Position and using “order by Position”. Now the pr…
How to show row numbers in PostgreSQL query?
I’d like to show the observation number for each record returned by a PostgreSQL query. I think in 8.4 windowing functions can perform this capability.
SQL How do I query a many-to-many relationship
How do I select posts that contain a specific tag if there is a many-to-many relationship between posts and tags? The problem I am having is that because of the where tag.name = ‘xxx’, only that tag is selected. I want to select all posts that have the tag specified, together with all of their tag…
Aggregating save()s in Django?
I’m using Django with an sqlite backend, and write performance is a problem. I may graduate to a “proper” db at some stage, but for the moment I’m stuck with sqlite. I think that my write performance problems are probably related to the fact that I’m creating a large number of ro…
SQL – Multiple one-to-many relationships
Is it possible somehow to do multiple one-to-many relationships between 2 tables? Like: Table abc abcID defID message Table def defID abcID message If yes how can I then make a new abc entry with …
Slow simple update query on PostgreSQL database with 3 million rows
I am trying a simple UPDATE table SET column1 = 0 on a table with about 3 million rows on Postegres 8.4 but it is taking forever to finish. It has been running for more than 10 min. Before, I tried to run a VACUUM and ANALYZE commands on that table and I also tried to create some indexes (although
Measuring the complexity of SQL statements
The complexity of methods in most programming languages can be measured in cyclomatic complexity with static source code analyzers. Is there a similar metric for measuring the complexity of a SQL …