Libove Blog

Personal Blog about anything - mostly programming, cooking and random thoughts

Lamb

Lamb

Green Paprika + Onion Pizza

Green Paprika + Onion Pizza

Materialized Views in PostgreSQL

At my hobby project Podcast de facto Standard I ran into problems querying the required data to render the report pages. My first fix for this was to query and process all data (for all reports) once per day and to store the results as one big JSON object in an additional table. This worked well until the server was running out of memory because too much data had to be loaded from the database to be processed in python.

I generally try to avoid database specific feature for as long as possible in my projects. Ideally I'm able to use SQLite in local development and automated testing and only start using Postgres in the testing and production environment. This works great with abstraction layers such as SQLAlchemy and Django and allows me to postpone the decision for a specific database system for as long as possible. As a nice bonus it keeps the testing setup very simple as I can simple use an in memory SQLite.

The growing memory requirements were the point where I had to choose a specific database. As I use PostgreSQL virtually always in production systems I looked into the options it provided to optimize my queries.

The main problems where cause by the episode data, a ~9 GB table with +11 million rows. I already had indexes in place but the queries for the audio properties of podcasts still took +1 minute per plot. This meant that "live" queries where no longer possible and I needed a solution to store (partial) results.

I've chosen to use materialized views as they are a great fit for my problems. The data presented on the page doesn't have to be updated with every newly analyzed audio file. It is sufficient to get an update once per day (even once per week would be fine). The transformations required can easily be done in SQL.

Materialized views behave almost like a regular table, but you cannot directly create or update rows in the table. Instead the content is created from an SQL statement, executed once on creation or upon refreshing the view.

Creating a materialized view is simple:

create materialized view pub_hist 
select date(publishing_date), count(*)
from episode e
group by date(publishing_date)

This command executes the selection query and stores the result in the view pub_hist. Afterwards you can simply query from this view:

select *
from pub_hist
where "date" < now()

Other than normal views the content of the materialized view is not updated automatically. Instead you have to trigger a refresh, which will reevaluate the defined query and store the result:

REFRESH MATERIALIZED VIEW pub_hist;

To refresh the views I simply run a daily (celery) job, which calls the refresh command for all views.


Pizzasauce

Servings: Für 4 Pizzen , Prep Time:

Ingredients

  • 1 Dose Tomatenstücke
  • 2 Knoblauchzehen
  • 1 EL Olivenöl
  • Oregano
  • Thymian
  • Salz und Zucker
  • Optional: 1/2 Chili (getrocknet oder frisch)

Instructions

  • Knoblauch schälen und grob hacken
  • Auf mittlerer Hitze in einem kleinen Topf Olivenöl und Knoblauch erhitzen
  • Unter ständigem rühren den Knoblauch im Öl 2-3 Minuten garen. Der Knoblauch darf nicht braun werden
    • Chilis ebenfalls mitgaren falls gewollt
  • Tomatenstücke dazugeben und zum köcheln bringen
  • Kräuter und eine Prise Salz und Zucker hinzugeben
  • Herd auf niedrige Hitze drehen und mit halb aufgelegtem Deckel für 20-30 Minuten einköcheln lassen
  • Mit dem Pürierstab zur gewollten Konsistenz zerkleinern
  • Heiß in ein verschließbarers Glas füllen, kann mindestens eine Woche im Kühlschrank gelagert werden

Rezept für Pizzateig


Schnelle Pizzasauce

Servings: Für eine Pizza , Prep Time:

Ingredients

  • 3-4 EL Passata
  • 1/2 EL Olivenöl
  • 1 TL Oregano
  • Prise Salz
  • Prise Zucker

Instructions

All Zutaten zusammengeben, gut mischen und direkt auf die Pizza. So einfach!

Wer mag kann auch noch etwas Knoblauchpulver geben. Für eine pikante Soße einfach ein bisschen Chili aus der Mühle dazu.


🐝

🐝

Snail

Snail

Best Carrots Cake



Ingredients

  • 400 g carrots
  • Juice of a lemon
  • 175 ml sunflower oil
  • 400 g flour
  • Pack of baking soda
  • 2 Packs of vanilla sugar
  • 200 g sugar
  • 100 g chopped almonds
  • 1/2 table spoon cinnamon
  • 2 cm ginger
  • pinch of salt
  • powdered sugar and lime juice

Instructions

  • Preheat your oven to 165°C
  • Grate the carrots and ginger and mix it with the oil and lemon juice
  • Mix all dry ingredients (flour, sugar, almonds, baking soda, vanilla sugar, cinnamon and salt) in another bowl.
  • Add the mixed dry ingredients to the carrots and mix everything thoroughly.
  • Fill everything in your cake pan and bake at 165°C for 60 minutes
  • Mix powdered sugar and lime juice to a viscous liquid and spread if over the finished cake

Aubergine & Onions Pan

Servings: 2 servings

Ingredients

  • 1 Aubergine (eggplant)
  • 3 spring onions
  • 1 small onion
  • 150 ml water
  • 1 table spoon soy sauce
  • 1 table spoon rice vinegar
  • 1 tea spoon curry paste
  • 2 tea spoons sugar
  • 1/2 tea spoon salt
  • 1-2 cm of ginger root
  • 3 cloves of garlic
  • 1 table spoon starch
  • peanut oil
  • rice

Instructions

  • Cut aubergine into large, long pieces (~4 cm long, ~2 cm thick)
  • Chop ginger and garlic, cut spring onions and onions into thin rings
  • Mix water, soy sauce, rice vinegar, sugar and salt
  • Mix starch with a small amount of water.
  • Fry in oil until golden brown. Press with spatula to release water
  • Put aubergine pieces aside and add new oil into the pan
  • Fry curry paste, garlic, ginger, green onions and onions for a short time
  • Add mixed water and aubergine pieces to the pan and cook everything for 2 minutes
  • Add starch to thicken the sauce
  • Serve with rice

Vegan Beluga Lentils Curry

Servings: 2-3 Servings , Prep Time:

Ingredients

  • 1 Onion
  • 1 Garlic
  • 1 cm fresh ginger root
  • 1 Carrot
  • 3 table spoons Coconut oil
  • 1,5 table spoons red curry paste
  • 300 g Beluga lentils
  • 1 can tomatos (chunky or whole)
  • 1 can Coconut milk
  • 550 ml vegetable stock
  • 1 sweet potato
  • 2 table spoons lime juice
  • Salt and pepper

Instructions

Origin: https://www.rewe.de/rezepte/veganes-beluga-linsen-curry/

  • Peel and chop the onions, garlic, ginger root and carrots.
  • Fry for 2 minutes. Add curry and lentils and fry for a short time
  • Add Tomatos, coconut milk and 200 ml vegetable stock.
  • Cook for 10 minutes and peel the sweet potato
  • Add sweet potato and cook for 20 minutes. Add vegetable stock periodically
  • Add lime juice. Season with salt and pepper.